Question

Do not change data in the green shaded areas. Only enter formulas and cell references in the blue shaded areas. INPUT DATA Common Stock Information $100.00 Common shares outstanding (000) Beta risk-free rate required return on market Flotation cost on new common stock Preferred Stock Information 1.30 2.00% 8.00% 0.25% $42.16 $3.60 15 $0.35 Preferred shares outstanding (000) Flotation cost on preferred per share Debt Information Total market value of debt ($000) Price of existing bond Number of years until maturity Annual coupon interest rate Face Value of Debt Flotation cost on debt $1,300 $1,375.50 11.50% $1,000.00 2% Tax rate 35.00% a. What is the market value capital structure and the values for the w's in the WACC equation? Market Value Cap Structure Percentage (w) - Debt Preferred Stock - Common Stock Total Market Value Mkt Value (SOXXO) $1,300 $632 $6,000

Through excel please

Answer 1

	Value	Weight	Cost
Debt	1,300	16.4%	8.11%	0.86%
Preferred	632	8.0%	8.61%	0.69%
Stock	6,000	75.6%	9.80%	7.41%
Total	7,932		WACC	8.96%

Weights = Value / Total

Cost of debt can be calculated using I/Y function

N = 23, PMT = 11.5% x 1000 = 115, PV = -1,375.50 x (1 - 2%), FV = 1,000

=> Compute I/Y = 8.11% is the before-tax cost of debt

Cost of preferred = Dividend / (Price - Flotation) = 3.6 / (42.16 - 0.35) = 8.61%

Cost of equity = Rf + beta x (Rm - Rf) = 2% + 1.3 x (8% - 2%) = 9.80%

WACC = wd x rd x (1 - tax) + wp x rp + we x re

= 16.4% x 8.11% x (1 - 35%) + 8.0% x 8.61% + 75.6% x 9.80%

= 8.96%