13.2 Flows To Equity
К оглавлению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 case, calculate the project’s NPV using the
Flows To Equity method. Compare this result to the APV result. 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 case.
Solution Strategy. Use the PV of Future Cash Flows from the APV case to determine the about of Equity
used on each date and the resulting Cost of Equity Capital on each date. Calculate the amount of cash
flows available to equityholders after debtholders are paid off. Discount these flows to equity and subtract
the initial outlay by shareholders to get the project NPV under FTE. Then calculate the present value of
future cash flows to both debt and equity.
FIGURE 13.2 Spreadsheet for Three Valuation Methods Using The FTE Method.
How To Build This Spreadsheet Model.
1. Open the APV Spreadsheet. Open the spreadsheet that you created for Corporate Financial
Planning - Adjusted Present Value and immediately save the spreadsheet under a new name using
the File | Save As command.
2. Debt, Equity, and the Total. The Debt row is a repeat of the Debt input. Enter =B22 in cell
B30 and copy it across. Equity is the Present Value of Future Cash Flows (APV) less Debt. Enter
=B27-B30 in cell B31 and copy it across. Sum these two rows to get Debt + Equity. Enter
=B30+B31 in cell B32 and copy it across.
3. Cost of Equity. The infinite horizon formula is Cost of Equity = (Unlevered Cost of Capital) + (1
- Tax Rate) * (Unlevered Cost of Capital - Riskfree Rate) * (Debt / Equity). Enter =$B$6+(1-
$B$5)*($B$6-$B$7)*(G30/G31) in cell G33. The finite horizon formula for is
Date t Cost of Equity
= (Unlevered Cost of Capital) + (Unlevered Cost of Capital - Riskfree Rate)
* {(Date t Debt) * [1 + (Riskfree Rate)*(1 - Tax Rate)] - PV of Future Tax Shield (t+1) }
/ [(Date t Equity) * (1 + Riskfree Rate)]
Enter =$B$6+(($B$6-$B$7)*(B30*(1+$B$7*(1-$B$5))-C25))/(B31*(1+$B$7)) in cell B33 and
copy it to the range C33:F33. The term PV of Future Tax Shield (t+1) is the present value of
future tax shields as of date t+1. We simply reference the identical calculation done under the
APV method in cell C25.
4. Flows to Equity. Calculate the cash flows to the equityholders (net of the debtholders) as
follows:
o EBIT repeats Before-Tax Cash Flows. Enter =C12 in cell C37.
o Interest Expense (t+1) is Debt (t) * (Riskfree Rate). Enter =B22*$B$7 in cell C38.
o Before-Tax Cash Flow is the difference. Enter =C37-C38 in cell C39.
o Taxes is Before Tax Cash Flow * (Tax Rate). Enter =C39*$B$5 in cell C40.
o After-Tax Cash Flow is the difference. Enter =C39-C40 in cell C41.
o New Borrowing (Repayment) is Debt (t+1) – Debt (t). Enter =C30-B30 in cell C42.
o Flows to Equity is the sum. Enter =C41+C42 in cell C43.
o Copy the range C37:C43 to the range D37:H43.
5. Present Value of Future FTE. Using the cost of equity capital, discount an infinite series of
constant cash flows using the infinite annuity formula: (Flows To Equity) / (Cost of Equity
Capital). Enter =IF(H43=0,0,H43/G33) in cell G45. The IF statement avoids an error message
that occurs when a cell in the formula is undefined. This occurs when the Infinite Horizon Flow
to Equity (H43) is zero causing the prior period sum of Debt and Equity to be zero and thus
causing the Cost of Equity Capital calculation in cell G33 to be undefined. Discount the explicitly
forecast horizon cash flows using a recursive, one-period-at-a-time approach: PV of Future Flows
To Equity (t) = [Flows To Equity (t+1) + PV of Future Flows To Equity (t+1)] / (1 + Cost of
Equity Capital). Enter =IF(G43+G45=0,0,(G43+G45)/(1+F33)) in cell F45 and copy it leftwards
to the range B45:E45. Again the IF statement avoids an error message that occurs when a cell in
the formula is undefined. You can verify by comparing row 45 to row 31 that Present Value of
the Future FTE is equal to the Equity (E).
6. Initial Outlay from Shareholders, NPV of the Project, and PV of Future Cash Flows. The
Initial Outlay from Shareholders = -(Initial Outlay for the New Investment) + (Initial Outlay from
the Debtholders). Enter =-$B$4+B30 in cell B46. The NPV of the Project using the FTE method
= (Present Value of Future FTE) + (Initial Outlay from Shareholders). Enter =B45+B46 in cell
B47. The PV of Future Cash Flows (FTE) = Debt + Present Value of Future FTE = Debt +
Equity. Enter =B30+B45 in cell B48 and copy it across.
We see that the NPV of the Project under FTE is $221.48, which is the same as the APV calculation. We
see that the PV of Future Cash Flows under FTE starts at $471.48 and declines to $260.00, which is the
same as under APV.
Problem. Given the same firm and same project as the APV case, calculate the project’s NPV using the
Flows To Equity method. Compare this result to the APV result. 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 case.
Solution Strategy. Use the PV of Future Cash Flows from the APV case to determine the about of Equity
used on each date and the resulting Cost of Equity Capital on each date. Calculate the amount of cash
flows available to equityholders after debtholders are paid off. Discount these flows to equity and subtract
the initial outlay by shareholders to get the project NPV under FTE. Then calculate the present value of
future cash flows to both debt and equity.
FIGURE 13.2 Spreadsheet for Three Valuation Methods Using The FTE Method.
How To Build This Spreadsheet Model.
1. Open the APV Spreadsheet. Open the spreadsheet that you created for Corporate Financial
Planning - Adjusted Present Value and immediately save the spreadsheet under a new name using
the File | Save As command.
2. Debt, Equity, and the Total. The Debt row is a repeat of the Debt input. Enter =B22 in cell
B30 and copy it across. Equity is the Present Value of Future Cash Flows (APV) less Debt. Enter
=B27-B30 in cell B31 and copy it across. Sum these two rows to get Debt + Equity. Enter
=B30+B31 in cell B32 and copy it across.
3. Cost of Equity. The infinite horizon formula is Cost of Equity = (Unlevered Cost of Capital) + (1
- Tax Rate) * (Unlevered Cost of Capital - Riskfree Rate) * (Debt / Equity). Enter =$B$6+(1-
$B$5)*($B$6-$B$7)*(G30/G31) in cell G33. The finite horizon formula for is
Date t Cost of Equity
= (Unlevered Cost of Capital) + (Unlevered Cost of Capital - Riskfree Rate)
* {(Date t Debt) * [1 + (Riskfree Rate)*(1 - Tax Rate)] - PV of Future Tax Shield (t+1) }
/ [(Date t Equity) * (1 + Riskfree Rate)]
Enter =$B$6+(($B$6-$B$7)*(B30*(1+$B$7*(1-$B$5))-C25))/(B31*(1+$B$7)) in cell B33 and
copy it to the range C33:F33. The term PV of Future Tax Shield (t+1) is the present value of
future tax shields as of date t+1. We simply reference the identical calculation done under the
APV method in cell C25.
4. Flows to Equity. Calculate the cash flows to the equityholders (net of the debtholders) as
follows:
o EBIT repeats Before-Tax Cash Flows. Enter =C12 in cell C37.
o Interest Expense (t+1) is Debt (t) * (Riskfree Rate). Enter =B22*$B$7 in cell C38.
o Before-Tax Cash Flow is the difference. Enter =C37-C38 in cell C39.
o Taxes is Before Tax Cash Flow * (Tax Rate). Enter =C39*$B$5 in cell C40.
o After-Tax Cash Flow is the difference. Enter =C39-C40 in cell C41.
o New Borrowing (Repayment) is Debt (t+1) – Debt (t). Enter =C30-B30 in cell C42.
o Flows to Equity is the sum. Enter =C41+C42 in cell C43.
o Copy the range C37:C43 to the range D37:H43.
5. Present Value of Future FTE. Using the cost of equity capital, discount an infinite series of
constant cash flows using the infinite annuity formula: (Flows To Equity) / (Cost of Equity
Capital). Enter =IF(H43=0,0,H43/G33) in cell G45. The IF statement avoids an error message
that occurs when a cell in the formula is undefined. This occurs when the Infinite Horizon Flow
to Equity (H43) is zero causing the prior period sum of Debt and Equity to be zero and thus
causing the Cost of Equity Capital calculation in cell G33 to be undefined. Discount the explicitly
forecast horizon cash flows using a recursive, one-period-at-a-time approach: PV of Future Flows
To Equity (t) = [Flows To Equity (t+1) + PV of Future Flows To Equity (t+1)] / (1 + Cost of
Equity Capital). Enter =IF(G43+G45=0,0,(G43+G45)/(1+F33)) in cell F45 and copy it leftwards
to the range B45:E45. Again the IF statement avoids an error message that occurs when a cell in
the formula is undefined. You can verify by comparing row 45 to row 31 that Present Value of
the Future FTE is equal to the Equity (E).
6. Initial Outlay from Shareholders, NPV of the Project, and PV of Future Cash Flows. The
Initial Outlay from Shareholders = -(Initial Outlay for the New Investment) + (Initial Outlay from
the Debtholders). Enter =-$B$4+B30 in cell B46. The NPV of the Project using the FTE method
= (Present Value of Future FTE) + (Initial Outlay from Shareholders). Enter =B45+B46 in cell
B47. The PV of Future Cash Flows (FTE) = Debt + Present Value of Future FTE = Debt +
Equity. Enter =B30+B45 in cell B48 and copy it across.
We see that the NPV of the Project under FTE is $221.48, which is the same as the APV calculation. We
see that the PV of Future Cash Flows under FTE starts at $471.48 and declines to $260.00, which is the
same as under APV.