Question

in an excel format, can you please solve the following!

thanks!

Calculate the weighted Average Cost of Capital Given Rate Below (60 points) Show Work Cost of Capital Weighted Average Bonds Preferred Stock Internal Equity Common Stock % of Equity 25.00% 10.00% 10.00% 55.00% Remember Flotation Costs and Taxes Par Value Market Price Flotation Costs Coupon Rate Maturity $1.001.00 $1.100.00 S100.00 X% 10 years Tax Rate 30.01% Preferred Stock Remember Flotation Costs Dividend Selling Price Flotation Costs $6.00 S85 0 59.00 Retained Earnings (Use CAPM) 1.25 Beta Risk Free Rate Market Rate of Return 2.50% 7. 50% New Common Stock (Dividend Growth Rate) Remember Flotation Costs Price of Common Stock Dividend Previous Year Growth Rate Flotation Cost S100.00 $3.25 8.00% 59.00 Debt Preferred Stock Retained Earnings Common Stock 0.066 0.0789 0.13 0.1427 0.25 0.0165 0.1 0.00789 0.1 0.013 0.55 0.078485 10.115875

Answer 1

Answer:

Calculate the Weighted Average Cost of Capital: Cost of Weighted Capital Average Banks Preferred Stock Internal Equity Common Stock % of Equity 25.00% 10.00% 10.00% 55.00% Bond Remember Flotation costs and Taxes Par Value Market Price Flotation Costs Coupon Rate Maturity $1,000.00 $1,100.00 $100 8.00% 10 Years Tax Rate 30.00% Preferred Stock Remember Flotation costs Dividend Selling Price Flotation Costs $6.00 $85.00 $9.00 Retained Earnings (Use CAPM) Beta Risk Free Rate Market Rate of Return 1.25 2.50% 7.50% New Common Stock (Dividend Growth Rate) Remember Flotation Costs Price of Common Stock Dividend Previous Year Growth Rate Flotation costs $100.00 $3.25 8.00% $9.00 Debt Preferred Stock Retained Earnings Common Stock WACC 0.25 0.10 0.10 0.55 1.00 0.05600 0.07895 0.08750 0.11857 0.01400 0.00789 0.00875 0.06521 0.09586

KINDLY NOTE: In the last table (last 5 rows), I have made 1st column as '% capital component' and 2nd column for 'Cost of individual component' (in your format for this table you have 'cost of individual component' in first column and % capital structure' in second column). I changed this is to comply the top headers (please see headers in row # 4 and 5). In case you want the columns as given, you may change.

The excel above with 'show formula' is as below (it cannot be accommodated in one image; hence given in two images):

1 Calculate the Weighted Average Cost of Capital: Cost of Capital Weighted Average Banks Preferred Stock Internal Equity Common Stock % of Equity 0.25 0.1 0.1 0.55 15 Bond Remember Flotation costs and Taxes Par Value Market Price Flotation Costs Coupon Rate Maturity 1000 1100 100 0.08 Years Tax Rate Remember Flotation costs 24 Preferred Stock 25

А D B Remember Flotation costs 24 Preferred Stock 25 Dividend Selling Price Flotation Costs 31 Retained Earnings (Use CAPM) _32 33 1.25 Beta Risk Free Rate Market Rate of Return 0.025 34 0.075 35 36 -37 38 New Common Stock (Dividend Growth Rate) Remember Flotation Costs 39 Price of Common Stock 40 Dividend Previous Year Growth Rate Flotation costs 100 3.25 0.08 Debt Preferred Stock Retained Earnings Common Stock WACC =(RATE(D20,D16*D19,-(D17-D18),D16,0))*(1-D22) =D26/(D27-D28) =C8 =D33+D32*(D34-D33) =C9 =((D40*(1+D41)/(D39-D42))+D41 =SUM(C46:C49) =C46*D46 =C47*D47 =C48*D48 =C49*D49 =SUM(E46:E49)