13.3 Weighted Average Cost of Capital

К оглавлению1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 
34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 
68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 
85 86 87 88 89 90 91 92 93 94 95 96 97 

Problem. Given the same firm and same project as the APV and FTE cases, calculate the project’s NPV

using the Weighted Average Cost of Capital method. Compare this result to the APV and FTE results. On

each date, calculate the present value of future cash flows to both debt and equity. Verify that this result is

the same as the APV and FTE cases.

Solution Strategy. Use the Debt and Equity amounts calculated for the FTE case to determine the Debt

Weight, Equity Weight, and the Weighted Average Cost of Capital on each date. Calculate the after-tax

cash flows available to firm, discount these cash flows, and then subtract the initial outlay for the new

investment to get the project NPV under WACC. Then calculate the present value of future cash flows to

the firm.

FIGURE 13.3 Spreadsheet for Three Valuation Methods Using The WACCapital Method.

How To Build This Spreadsheet Model.

1. Open the FTE Spreadsheet. Open the spreadsheet that you created for Corporate Financial

Planning - Flows To Equity and immediately save the spreadsheet under a new name using the

File | Save As command.

2. Equity and Debt Weights. Using the Debt (D) calculated in row 30 and the Equity (E)

calculated in row 31, determine Equity Weight = E / (D+E). Enter =B31/(B30+B31) in cell B51

and copy it across. Similarly, calculate the Debt Weight = D / (D+E). Enter =B30/(B30+B31) in

cell B52 and copy it across.

3. WACC. The formula for WACC = (Cost of Equity Capital) (Equity Weight) + (1 – Tax Rate)

(Riskfree Rate) (Debt Weight). Enter =B33*B51+(1-$B$5)*$B$7*B52 in cell B53 and copy it

across.

4. WACC Method. Calculate the firm’s net cash flows and then discount them at WACC as

follows:

o Before-Tax Cash Flows repeats row 12. Enter =C12 in cell C57 and copy it across.

o Taxes is Before Tax Cash Flow * (Tax Rate). Enter =C57*$B$5 in cell C58 and copy it

across.

o After-Tax Cash Flow is the difference. Enter =C57-C58 in cell C59 and copy it across.

5. Present Value of Net Cash Flows (at WACC). Using the weighted average cost of capital

(WACC), discount the infinite series of constant cash flows using the infinite annuity formula:

(After-tax Cash Flow) / WACC. Enter =IF(H59=0,0,H59/G53) in cell G61. The IF statement

avoids an error message that occurs when a cell in the formula is undefined. This occurs when the

Infinite Horizon After-tax Cash Flow (H59) is zero causing the Equity Weight and Debt Weight

to be undefined and thus causing WACC in cell G53 to be undefined. Discount the explicitly

forecast horizon cash flows using a recursive, one-period-at-a-time approach: PV of Future Net

Cash Flows (t) = [Net Cash Flow (t+1) + PV of Future Net Cash Flows (t+1)] / (1+WACC).

Enter =IF(G59+G61=0,0,(G59+G61)/(1+F53)) in cell F61 and copy it leftwards to the range

B61:E61. Again the IF statement avoids an error message.

6. Initial Outlay from New Investment, NPV of the Project, and PV of Future Cash Flows. To

get the NPV of the Project using the WACC method, subtract the Initial Outlay for New

Investment. Enter =-$B$4 in cell B62 and =B61+B62 in cell B63. The PV of Future Cash

Flows (WACC) is a repeat of row 61. Enter =B61 in cell B64 and copy it across.

We see that the NPV of the Project under WACC is $221.48, which is the same as the APV and FTE

calculation. We see that the PV of Future Cash Flows under WACC starts at $471.48 and declines to

$260.00, which is the same as under APV and FTE.

Finally, all of the discussion so far has focused on valuing projects. However, the same Two-Stage

spreadsheet model can be used value companies simply by zeroing out the Investment amount and

entering the firm’s total Before-tax Cash Flows and the firm’s total Debt amount. Again, all three

valuation methods will generate the same valuation of the firm.

Problem. Given the same firm and same project as the APV and FTE cases, calculate the project’s NPV

using the Weighted Average Cost of Capital method. Compare this result to the APV and FTE results. On

each date, calculate the present value of future cash flows to both debt and equity. Verify that this result is

the same as the APV and FTE cases.

Solution Strategy. Use the Debt and Equity amounts calculated for the FTE case to determine the Debt

Weight, Equity Weight, and the Weighted Average Cost of Capital on each date. Calculate the after-tax

cash flows available to firm, discount these cash flows, and then subtract the initial outlay for the new

investment to get the project NPV under WACC. Then calculate the present value of future cash flows to

the firm.

FIGURE 13.3 Spreadsheet for Three Valuation Methods Using The WACCapital Method.

How To Build This Spreadsheet Model.

1. Open the FTE Spreadsheet. Open the spreadsheet that you created for Corporate Financial

Planning - Flows To Equity and immediately save the spreadsheet under a new name using the

File | Save As command.

2. Equity and Debt Weights. Using the Debt (D) calculated in row 30 and the Equity (E)

calculated in row 31, determine Equity Weight = E / (D+E). Enter =B31/(B30+B31) in cell B51

and copy it across. Similarly, calculate the Debt Weight = D / (D+E). Enter =B30/(B30+B31) in

cell B52 and copy it across.

3. WACC. The formula for WACC = (Cost of Equity Capital) (Equity Weight) + (1 – Tax Rate)

(Riskfree Rate) (Debt Weight). Enter =B33*B51+(1-$B$5)*$B$7*B52 in cell B53 and copy it

across.

4. WACC Method. Calculate the firm’s net cash flows and then discount them at WACC as

follows:

o Before-Tax Cash Flows repeats row 12. Enter =C12 in cell C57 and copy it across.

o Taxes is Before Tax Cash Flow * (Tax Rate). Enter =C57*$B$5 in cell C58 and copy it

across.

o After-Tax Cash Flow is the difference. Enter =C57-C58 in cell C59 and copy it across.

5. Present Value of Net Cash Flows (at WACC). Using the weighted average cost of capital

(WACC), discount the infinite series of constant cash flows using the infinite annuity formula:

(After-tax Cash Flow) / WACC. Enter =IF(H59=0,0,H59/G53) in cell G61. The IF statement

avoids an error message that occurs when a cell in the formula is undefined. This occurs when the

Infinite Horizon After-tax Cash Flow (H59) is zero causing the Equity Weight and Debt Weight

to be undefined and thus causing WACC in cell G53 to be undefined. Discount the explicitly

forecast horizon cash flows using a recursive, one-period-at-a-time approach: PV of Future Net

Cash Flows (t) = [Net Cash Flow (t+1) + PV of Future Net Cash Flows (t+1)] / (1+WACC).

Enter =IF(G59+G61=0,0,(G59+G61)/(1+F53)) in cell F61 and copy it leftwards to the range

B61:E61. Again the IF statement avoids an error message.

6. Initial Outlay from New Investment, NPV of the Project, and PV of Future Cash Flows. To

get the NPV of the Project using the WACC method, subtract the Initial Outlay for New

Investment. Enter =-$B$4 in cell B62 and =B61+B62 in cell B63. The PV of Future Cash

Flows (WACC) is a repeat of row 61. Enter =B61 in cell B64 and copy it across.

We see that the NPV of the Project under WACC is $221.48, which is the same as the APV and FTE

calculation. We see that the PV of Future Cash Flows under WACC starts at $471.48 and declines to

$260.00, which is the same as under APV and FTE.

Finally, all of the discussion so far has focused on valuing projects. However, the same Two-Stage

spreadsheet model can be used value companies simply by zeroing out the Investment amount and

entering the firm’s total Before-tax Cash Flows and the firm’s total Debt amount. Again, all three

valuation methods will generate the same valuation of the firm.