13.3 Weighted Average Cost of Capital
К оглавлению1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 1617 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.