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 companys 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