Calculating the WACC - Excel LE DATA REVIEW E INSERT PAGE LAYOUT FORMULAS VIEW 12 A A Arial Conditional Format as Cell Editing Alignment Number Cells IU Formatting Table Styles board Font Styles C E F G Given the following information for Watson Power Co., find the WACC. Assume th company's tax rate is 21 percent Debt 10,000 6.4 percent coupon bonds outstanding, $1,000 par value, 25 years maturity, selling for 108 percent of par, the bonds make semiannual payments Common stock 495,000 shares outstanding, selling for $63 per share, the beta is 1.15 Preferred stock 35,000 shares of 3.5 percent preferred stock outstanding, curren selling for $72 per share Market 7 percent market risk premium and 3.2 percent risk-free rate Tax rate 21% Debt Bonds outstanding 10,000 Settlement date 01/01/00 Maturity date Annual coupon rate Coupons per year Bond price (% of par) Redemption value (% of par) 01/01/25 6.40% 2 108 100 $ Par value 1,000 X Calculating the WACC - Excel E INSERT FORMULAS OME PAGE LAYOUT DATA REVIEW VIEW Arial A A 12 Conditional Format as Cell Editing Alignment Number Cells B IU Formatting Table Styles oard Font Styles Given the following information for Watson Power Co., find the WACC. C D E G Bond price (% of par) Redemption value (% of par) 108 100 Par value 1,000 Common stock Shares outstanding 495,000 1.15 Beta Share price $ 63 Preferred stock outstanding Shares outstanding Dividend percentage Share price 35,000 3.50% $ 72 Market Market risk premium Risk-free rate 7.00% 3.20% Complete the following analysis. Do not hard code values in your calculations. Leave the "Basis" input blank in the YIELD function. You must use the built-in Excel function to answer this question. Itt Arial A A 12 Alignment Number Conditional Format as Formatting Table Styles Cell Cells Editing IU board Font Styles Preferred stock outstanding C A D E F G Market value of debt Market value of equity Market value of preferred Market value of firm Market value weight of debt Market value of weight equity Market value of weight preferred Pretax cost of debt Aftertax cost of debt Cost of equity Cost of preferred WACC Sheet1 DY Calculating the WACC - Excel LE DATA REVIEW E INSERT PAGE LAYOUT FORMULAS VIEW 12 A A Arial Conditional Format as Cell Editing Alignment Number Cells IU Formatting Table Styles board Font Styles C E F G Given the following information for Watson Power Co., find the WACC. Assume th company's tax rate is 21 percent Debt 10,000 6.4 percent coupon bonds outstanding, $1,000 par value, 25 years maturity, selling for 108 percent of par, the bonds make semiannual payments Common stock 495,000 shares outstanding, selling for $63 per share, the beta is 1.15 Preferred stock 35,000 shares of 3.5 percent preferred stock outstanding, curren selling for $72 per share Market 7 percent market risk premium and 3.2 percent risk-free rate Tax rate 21% Debt Bonds outstanding 10,000 Settlement date 01/01/00 Maturity date Annual coupon rate Coupons per year Bond price (% of par) Redemption value (% of par) 01/01/25 6.40% 2 108 100 $ Par value 1,000 X Calculating the WACC - Excel E INSERT FORMULAS OME PAGE LAYOUT DATA REVIEW VIEW Arial A A 12 Conditional Format as Cell Editing Alignment Number Cells B IU Formatting Table Styles oard Font Styles Given the following information for Watson Power Co., find the WACC. C D E G Bond price (% of par) Redemption value (% of par) 108 100 Par value 1,000 Common stock Shares outstanding 495,000 1.15 Beta Share price $ 63 Preferred stock outstanding Shares outstanding Dividend percentage Share price 35,000 3.50% $ 72 Market Market risk premium Risk-free rate 7.00% 3.20% Complete the following analysis. Do not hard code values in your calculations. Leave the "Basis" input blank in the YIELD function. You must use the built-in Excel function to answer this question. Itt Arial A A 12 Alignment Number Conditional Format as Formatting Table Styles Cell Cells Editing IU board Font Styles Preferred stock outstanding C A D E F G Market value of debt Market value of equity Market value of preferred Market value of firm Market value weight of debt Market value of weight equity Market value of weight preferred Pretax cost of debt Aftertax cost of debt Cost of equity Cost of preferred WACC Sheet1 DY