13.2 Flows To Equity

К оглавлению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 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.