How do I answer the questions in excel

WACC Shell for Video2.xlsx A Saveta landya Mukherjet Ablebits Tools 27 V Formulas Data Review View Help Ablebits Data General Conditional Formatting Insert $ -% 9 Format as Table 9X Delete Cell Styles Format 5 Ideas Sens Number Styles Cells Editing Ideas Sensi H F G J K L M Calculate the WACC for ABC Corp assuming it has the following target capital structure 60% Equity 30% Debt and 10% Preferred ABC is AA Rated; 10 Year AA rate = 2.3%, 1 year AA rate = 2% The tax rate is 30% 1) ABC has 6% semi-annual coupon bonds that mature in 10 years. The bonds are currently priced at 1090. The face amount of the bonds is 1000. 2) ABC has 5% preferred with a 100 face value that is currently priced at 105, Flotation cost for new preferred is 5% 3) ABC's beta is 0.9, the Risk-Free rate is 3% and the MRP is 6%; the stock is currently priced at $100 and the expected dividend is $4.00 and forecast to grow at 4% forever. Ignore flotation costs for your cost of equity calculation. Take the average of your two calculations for the cost of equity. 4) Calculate the cost of equity using the market implied cost of equity approach only if flotation costs are 30%. Using this number what is the new WACC: erred Stock Flotation for Common S... Clipboard KI Font KI F32 A B C D E G 1 Calculate the WACC for ABC Corp 2 Weighted Average Cost of Capital Calculation 60% Equity 30% Debt and 10% P t= tax rates 30% Component ABC AA Rated: 10 Year AA rate 5 Asset Class Weight Cost WACC The tax rate is 30% 6 Bonds 30% 1) ABC has 6% semi-annualco 7 Equity 60% currently priced at 1090. T 8 Preferred 10% 2) ABC has 5 preferred with 9 Flotation cost for new prefe 10 Step 1. Cost of debt [bonds) 3) ABC's beta is 0.9, the Risk-F 11 (d) - YTM of existing bonds X (1-1) priced at $100 and the exp 12 Usually a longter term borrowing rate forever. Ignore flotationc 13 of your two calculations for 14 4) Calculate the cost of equity 15 FV if flotation costs are 30%. L 16 17 18 19 20 21 22 = D(1) PIM + 9 23 24 r - D(1 PMX (1-1)] + 9 25 26 cost of equity using new stock > cost of equity using retained earnings 27 rret ern) 29 PM r(new Issue) 30 g 31f 32 33 34 35 36 Cost of preferred 37 38 39 40 41 42 43 49 28 (1)