Use the excel index function to determine the weight of debt at the lowest WACC

Question

Use the excel index function to determine the weight of debt at the lowest WACC calculated (the Optimum Debt Ratio). Make sure the INDEX functions work correctly by matching a new lowest WACC with the NEW correct weight of debt from changes to original values.

34.000%

Stewart Racing has consulted with investment bankers and determined the interest rate it would pay for different capital structures, as shown below. Data for the risk-free rate, the market risk premium, an estimate of Stewart Racing's unlevered beta, and the tax rate are also shown below. Based on this information, what is the firm's optimal capital structure and what is the weighted average cost of capital at the optimal structure?

Explanation / Answer

For Formulas refer notes at the end

Levered Beta

(Note 2)

0.0

(0.0/1.0)

1.848%

(2.8*(1-0.34))

2.25

2.25*(1+(1-0.34)*(0))

18.94%

(3.75 + 2.25 (6.75))

18.94%

(0.0 * 1.848) + (1 * 18.94)

0.1111

(0.1/0.9)

2.211%

(3.35 * (1-0.34))

2.42

(2.25*(1 + (1-0.34)*(0.1111))

20.085%

(3.75 + 2.42(6.75))

18.30%

(0.1*2.211) + (0.9*20.085)

0.25

(0.2/0.8)

2.64%

(4.00 * (1-0.34))

2.62

(2.25 * (1 + (1-034)* (0.25))

21.435%

(3.75 + 2.62 * (6.75))

17.68%

(0.2*2.64)+ (0.8*21.435)

0.428571

(0.3/0.7)

3.135%

(4.75 * (1-0.34))

2.88

(2.25 * (1 + (1 - 0.34)* (0.4285))

23.19%

(3.75 + 2.88 (6.75))

17.17%

(0.3 * 3.135) + (0.7*23.19)

0.6666

(0.4/0.6)

3.894%

(5.90 * (1-0.34))

3.24

(2.25 * (1 + (1-0.34) * (0.6666))

25.62%

(3.75 + 3.24 (6.75))

16.93%#

(0.4*3.894) + (0.6 * 25.62)

1

(0.5/0.5)

6.27%

(9.5 * (1-0.34))

3.735

(2.25 * (1 + (1-0.34) * (1)

28.96%

(3.75 + 3.735(6.75))

17.62%

(0.5*6.27) + (0.5*28.96)

1.5

(0.6/0.4)

7.854%

(11.9 * (1-.0.34))

4.4775

(2.25 * (1+ (1 - 0.34)* (1.5)

33.97%

(3.75 + 4.4775 (6.75))

2.3333

(0.7/0.3)

9.405%

(14.25 * (1-0.34)

5.72

(2.25 * (1 + (1-0.34) * (2.3333))

42.36%

(3.75 + 5.72(6.75))

19.29%

(0.7*9.405) + (0.3*42.36)

# Looking at the above we find that the Optimum Debt Ratio is 0.4 where WACC is minimum.

WACC at Optimum Debt Ratio is 16.93%

Notes

1. After Tax Cost of Debt = Before tax Cost of Debt * ( 1 - Tax Rate)

2. Levered Beta = Unlevered Beta * (1 + (1 - Tax Rate) * (Debt/Equity))

3. Cost of Equity = Rf + LB (Rm - Rf)

Where Rf = Risk Free Rate

LB = Levered Beta

Rm - Rf = Market Risk Premium

4.WACC = (Debt Proportion * After tax cost of Debt) + (Equity Proportion * Cost of Equity)

Debt/Value Ratio(wd) Equity/Value Ratio (ws) Debt/Equity (wd/ws) After Tax Cost of Debt (rd)(Note 1)

Levered Beta

(Note 2)

Cost of Equity (Note 3) WACC (Note 4) 0.0 1.0

0.0

(0.0/1.0)

1.848%

(2.8*(1-0.34))

2.25

2.25*(1+(1-0.34)*(0))

18.94%

(3.75 + 2.25 (6.75))

18.94%

(0.0 * 1.848) + (1 * 18.94)

0.1 0.9

0.1111

(0.1/0.9)

2.211%

(3.35 * (1-0.34))

2.42

(2.25*(1 + (1-0.34)*(0.1111))

20.085%

(3.75 + 2.42(6.75))

18.30%

(0.1*2.211) + (0.9*20.085)

0.2 0.8

0.25

(0.2/0.8)

2.64%

(4.00 * (1-0.34))

2.62

(2.25 * (1 + (1-034)* (0.25))

21.435%

(3.75 + 2.62 * (6.75))

17.68%

(0.2*2.64)+ (0.8*21.435)

0.3 0.7

0.428571

(0.3/0.7)

3.135%

(4.75 * (1-0.34))

2.88

(2.25 * (1 + (1 - 0.34)* (0.4285))

23.19%

(3.75 + 2.88 (6.75))

17.17%

(0.3 * 3.135) + (0.7*23.19)

0.4 0.6

0.6666

(0.4/0.6)

3.894%

(5.90 * (1-0.34))

3.24

(2.25 * (1 + (1-0.34) * (0.6666))

25.62%

(3.75 + 3.24 (6.75))

16.93%#

(0.4*3.894) + (0.6 * 25.62)

0.5 0.5

1

(0.5/0.5)

6.27%

(9.5 * (1-0.34))

3.735

(2.25 * (1 + (1-0.34) * (1)

28.96%

(3.75 + 3.735(6.75))

17.62%

(0.5*6.27) + (0.5*28.96)

0.6 0.4

1.5

(0.6/0.4)

7.854%

(11.9 * (1-.0.34))

4.4775

(2.25 * (1+ (1 - 0.34)* (1.5)

33.97%

(3.75 + 4.4775 (6.75))

18.30%(0.6*7.854) + (0.4*33.97) 0.7 0.3

2.3333

(0.7/0.3)

9.405%

(14.25 * (1-0.34)

5.72

(2.25 * (1 + (1-0.34) * (2.3333))

42.36%

(3.75 + 5.72(6.75))

19.29%

(0.7*9.405) + (0.3*42.36)

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.