Question

NEED EXCEL SOLUTION: EXCEL FORMULA ONLY PLEASE

Calculate WACC:
Debt Weight:	60.00%
Equity Weight:	40.00%
Tax rate:	35.00%

WACC: Use CAPM Cost of Equity

The below information is given. I need to solve for WACC.

Cost of Equity:
Stock quote link
Stock price:	$                                           65.95
Dividend:	$                                             1.66
Key statistics link
Beta:	1.43
Shares outstanding:	148,610,000
Analysts' estimates link
5-year dividend growth:	6.85%
Bond center link:
Risk-free rate:	0.13%
Market Risk Premium:	7.00%
Cost of Equity:	Use DDM: r = D/P + g
Cost of Equity:	Use CAPM: r = rf + B * (Rm - Rf)
2. Find Cost of Debt:
Suppose the average bond information is below for an annual coupon paying bond:
Maturity	5
Coupon	4.00%
FV	$5,000.00
P	$4,500.00
YTM	6.40%

Answer 1

Using CAPM Approach

Cost of equity, Ke = (Rf) + ($\beta$)*{(Rm) - Risk free Rate of Return(Rf)}

, Where Rf = Risk free Rate of Return,

  $\beta$ = Beta, and,

Rm = Market Risk Premium.

Therefore, Ke = 0.13 + 1.43*(7 - 0.13)

-= 7 %

Using Yield to maturity Approach

Cost of debt (Kd) = Yield to maturity(YTM) rate* (1-tax rate)

= 6.40*(1-0.35)

= 4.16 %

Therefore, Weighted Average Cost of Capital(WACC) = Kd*weight of debt + Ke*Weight of equity

= 4.16*0.6 + 7*0.4

= 5.296%