You need to complete this problem in a spreadsheet and show all your work. Creat

Question

You need to complete this problem in a spreadsheet and show all your work. Create an excel file that shows your calculations. Clearly indicate your final answer(s).

(a) Several years ago the Haverford Company sold a $1,000 par value bond that now has 25 years to maturity and an 8.00% annual coupon that is paid quarterly. The bond currently sells for $900.90, and the company’s tax rate is 40%. What is the component cost of debt for use in the WACC calculation? (3pts)

(b) Haverford uses debt, common equity and preferred stock in their capital structure. Using the following information AND the cost of debt calculated in part (a) above, determine the WACC for the company: (7pts)  

Preferred Dividend is $2.50 per share

Price of Preferred is $22.72

Weight of Debt is 40% and Weight of Equity is 45%

Haverford estimates the cost of equity based on their debt yield plus a risk premium of 5.5%

I need this in an excel file that I can save and upload OR provide the formulas so I can re-create the spreasheet

Explanation / Answer

a) Current cost of debt is the rate which makes present value of bond equal to future receipt of bond. In present case present value of bond is $900.90 and future interest receipts are $80 for next 25 years and $1000 at the end of 25th year.You need to calculate YTM for this bonds. By using RATE function in Excel you can find quarterly rate = 2.25% so annual rate is = 2.25%*4 = 9% This YTM is before cost of debt so after tax cost of debt is =9%(1-0.4) = 5.4%

=RATE(100,20,-900.9,1000)

Nper= 100(25*4),pmt=20, pv= $900.9, fv=$1000,

=2.25%

Annua coupan rate = 2.25% *4 = 9%

b) WACC is 10.34% for Haverford as follows:

Cost% Weight WACC% Equity = 9%+5.55%= 14.5 0.45        6.53 Preferred = $2.5/$22.72*100=      11.00 0.15        1.65 Debt = 5.4 0.4        2.16 1      10.34

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.