In: Finance
spreadsheet format is preferred please show all working
WACC = (weight of debt * cost of debt) + (weight of preferred stock * cost of preferred stock) + (weight of common stock * cost of common stock)
cost of debt = YTM of bond * (1 - tax rate)
YTM is calculated using RATE function in Excel with these inputs :
nper = 15*2 (15 years to maturity with 2 semiannual coupon payments each year)
pmt = 1000 * 8.2% / 2 (semiannual coupon payment = face value * annual coupon rate / 2. This is a positive figure as it is an inflow to the bondholder)
pv = -1000 * 96% (current bond price = face value * price as a % of par. This is a negative figure as it is an outflow to the buyer of the bond)
fv = 1000 (face value of the bond receivable on maturity. This is a positive figure as it is an inflow to the bondholder)
The RATE calculated is the semiannual YTM. To calculate the annual YTM, we multiply by 2. Annual YTM is 8.68%
cost of debt = YTM * (1 - tax rate)
cost of debt = 8.68% * (1 - 34%) ==> 5.73%
cost of preferred stock = dividend / current price = $7 / $108 = 6.48%
cost of equity = risk free rate + (beta * market risk premium)
cost of equity = 5.50% + (1.2 * 6.80%) ==> 13.66%
market value of debt = bonds outstanding * market price per bond
market value of preferred stock = shares outstanding * market price per share
market value of common stock = shares outstanding * market price per share
weight of debt = market value of debt / total market value
weight of debt = 23.93%
weight of preferred stock = market value of preferred stock / total market value
weight of preferred stock = 7.69%
weight of common stock = market value of common stock / total market value
weight of common stock = 68.38%
WACC = (weight of debt * cost of debt) + (weight of preferred stock * cost of preferred stock) + (weight of common stock * cost of common stock)
WACC = 11.21%