Anyone know how to do it in excel with formula? thank you
Boom Mechanics is trying to determine its optimal capital structure, which now consists of only debt and common equity. The firm does not currently use preferred stock in its capital structure, and it does not plan to do so in the future. Its treasury staff has consulted with investment bankers. On the basis of those discussions, the staff has created the following table showing the firm's debt cost at different debt levels. 0.4 5 0.6 0.4 7 Debt-to- Equity-to- Debt-to- Capital Capital Equity After Tax Bond Before-Tax Cost of Cost of Ratio Ratio Cost of WACC Ratio Beta Rating Debt(ra) Equity Debt 2 (W) (wa) (D/E) 3 0 1 OA 4.00% . 0.2 0.8 0.25 BBB 4.75% 5 0.6 0.666667 BB 3.00% 1.5 C 10.00% 0.8 0.2 40 13.00% Boom uses the CAPM to estimate its cost of common equity, ry, and estimates that the risk-free rate is 2%, the market risk premium is 6%, and its tax rate is 25%. Boom estimates that if it had no debt, its "unlevered" beta, bu, would be 1.2. What is the firm's optimal capital structure, and what would be its WACC at the optimal capital structure? Fill in the cells F3:17. Please use the cells below in your calculations. Fill in A18:C18 with the optimal weights and WACC 1 Risk Free 2% MRP 6% 25% Unlev B 5 6 Optimal Weights 7 Debt Equity 8 0 2 3 Tax rate 4 1.20 WACC Boom Mechanics is trying to determine its optimal capital structure, which now consists of only debt and common equity. The firm does not currently use preferred stock in its capital structure, and it does not plan to do so in the future. Its treasury staff has consulted with investment bankers. On the basis of those discussions, the staff has created the following table showing the firm's debt cost at different debt levels. 0.4 5 0.6 0.4 7 Debt-to- Equity-to- Debt-to- Capital Capital Equity After Tax Bond Before-Tax Cost of Cost of Ratio Ratio Cost of WACC Ratio Beta Rating Debt(ra) Equity Debt 2 (W) (wa) (D/E) 3 0 1 OA 4.00% . 0.2 0.8 0.25 BBB 4.75% 5 0.6 0.666667 BB 3.00% 1.5 C 10.00% 0.8 0.2 40 13.00% Boom uses the CAPM to estimate its cost of common equity, ry, and estimates that the risk-free rate is 2%, the market risk premium is 6%, and its tax rate is 25%. Boom estimates that if it had no debt, its "unlevered" beta, bu, would be 1.2. What is the firm's optimal capital structure, and what would be its WACC at the optimal capital structure? Fill in the cells F3:17. Please use the cells below in your calculations. Fill in A18:C18 with the optimal weights and WACC 1 Risk Free 2% MRP 6% 25% Unlev B 5 6 Optimal Weights 7 Debt Equity 8 0 2 3 Tax rate 4 1.20 WACC
