PLEASE SHOW ANSWER IN EXCEL WITH FORMULAS THANKS!

Task 2 - Weighted Average cost of Capital Question 1 - IBM has the following three bonds outstanding. First, calculate the YTM for each bond in the table. Then fill in the rest of the table and calculate the market value of the debt to determine the overall cost of debt for the company. (20 points) Bond A Settlement date Maturity date Bonds outstanding Annual coupon rate Face value (% of par) Coupons per year Bond price (% of par) 01/01/17 01/01/22 240,000 4.32% 100.00 2 97.21 Bond B Settlement date Maturity date Bonds outstanding Annual coupon rate Face value ($) Coupons per year Bond price ($) 01/01/10 01/01/28 110,000 6.22% 100.00 2 105.25 Bond C Settlement date Maturity date Bonds outstanding Annual coupon rate Face value ($) Coupons per year Bond price ($) 01/01/15 01/01/39 95,000 7.96% 100.00 2 102.56 Quoted Price Market Value Weight Bond YTM Book Value Market Value Weighted YTM $ A B 4.96% 5.75% 7.72% 97.21 105.25 102.56 240,000 110,000 95,000 $ 233,304.00 115,775.00 97,432.00 446,511.00 0.5225 0.2593 0.2182 2.59% 1.49% 1.69% 5.77% C $ $ Tax rate: 27.0% After tax cost of debt: 4.21% Question 2 - Calculate the cost of equity for IBM given the following information. Use the DDM and CAPM. Then find the market value of equity. (15 points) Common stock Shares outstanding Beta Share price ($) Dividend Growth Rate Market Market risk premium Risk-free rate 7.0% 3.5% $ 6,300 0.97 114.09 2.81 7.01% Cost of equity DDM CAPM 9.65% 10.29% Question 3 - Calculate the cost of preferred stock and weighted average cost of capital for IBM given the following information. (15 points) Preferred stock MV of shares Coupon rate Share price ($) $ $ $ 95,000.00 1.50 20.81 Cost of Pref Weight of debt Weight of equity Weight of pref 000 WACC Task 2 - Weighted Average cost of Capital Question 1 - IBM has the following three bonds outstanding. First, calculate the YTM for each bond in the table. Then fill in the rest of the table and calculate the market value of the debt to determine the overall cost of debt for the company. (20 points) Bond A Settlement date Maturity date Bonds outstanding Annual coupon rate Face value (% of par) Coupons per year Bond price (% of par) 01/01/17 01/01/22 240,000 4.32% 100.00 2 97.21 Bond B Settlement date Maturity date Bonds outstanding Annual coupon rate Face value ($) Coupons per year Bond price ($) 01/01/10 01/01/28 110,000 6.22% 100.00 2 105.25 Bond C Settlement date Maturity date Bonds outstanding Annual coupon rate Face value ($) Coupons per year Bond price ($) 01/01/15 01/01/39 95,000 7.96% 100.00 2 102.56 Quoted Price Market Value Weight Bond YTM Book Value Market Value Weighted YTM $ A B 4.96% 5.75% 7.72% 97.21 105.25 102.56 240,000 110,000 95,000 $ 233,304.00 115,775.00 97,432.00 446,511.00 0.5225 0.2593 0.2182 2.59% 1.49% 1.69% 5.77% C $ $ Tax rate: 27.0% After tax cost of debt: 4.21% Question 2 - Calculate the cost of equity for IBM given the following information. Use the DDM and CAPM. Then find the market value of equity. (15 points) Common stock Shares outstanding Beta Share price ($) Dividend Growth Rate Market Market risk premium Risk-free rate 7.0% 3.5% $ 6,300 0.97 114.09 2.81 7.01% Cost of equity DDM CAPM 9.65% 10.29% Question 3 - Calculate the cost of preferred stock and weighted average cost of capital for IBM given the following information. (15 points) Preferred stock MV of shares Coupon rate Share price ($) $ $ $ 95,000.00 1.50 20.81 Cost of Pref Weight of debt Weight of equity Weight of pref 000 WACC