25 Grand

26 Whole Yrs Frac Yr Total Total

27 t S 1 5.99506 0.13548 6.13054

28 PV Factor 0.73018 0.73018

29 t 0 4.33747 0.09892 4.447640 4.47640

31 Assumptions:

33 S Beginning year of cash ¬‚ows (valuation at t 2.25) 3.25

34 n Ending year of cash ¬‚ows-whole year 12.25

35 z Ending year of cash ¬‚ows-stub year 12.60

36 r Discount rate 15.0%

37 g Growth rate in cash ¬‚ow 5.1%

38 p Proportion of year in the stub period 0.35

39 This row is not used

40 x (1 g)/(1 r) 0.913913

41 Gordon model multiple GM 1/(r g) 10.101010

43 Spreadsheet formulas:

45 C17, D17: p*(1 g) (n s 1) stub period cash ¬‚ow

46 E17: 1/(1 r) (z S 1) stub period present value factor at t 2.25

47 G17: 1/(1 r) z stub period present value factor for t 0

48 B27: GM*(1 x (n S 1)) ADF for years 3.25 to 32.25 at t 2.25

49 C27: p*(1 g) (n S 1)/(1 r) (z S 1) PV of stub period CF at t 2.25

50 B28, C28: 1/(1 r) (S 1) present value factor at t S 1 2.25

51 E29: (GM*(1 x (n S 1)) p*(1 g) (n S 1)/(1 r) (z S 1))/(1 r) (S 1)

Note: E29 is the formula for the Grand Total

PART 1 Forecasting Cash Flows

96

Column E. The only exception to the PVF formula is cell E17, the frac-

tional year. Its formula is

1

PVF n S 1 0.5p

(1 r)

(in the EOY formula, the exponent is z S 1). This formula appears

in the spreadsheet at A46. The total present value at t 2.25 of the cash

¬‚ows from t 3.25 through t 12.25 is $6.42899 (F18). The present value

of the fractional year cash ¬‚ow is $0.13883 (F19), for a total of $6.56782

(F20). In F21 we show the present value factor of 0.73018 to discount from

0.18 Multiplying G20 by G21, we come to the PV of the

t 2.25 to t

cash ¬‚ows in F22 at t 0 of $4.79569 for each $1.00 of starting cash ¬‚ows.

Thus, if our annuity were actually $100,000 at the beginning, with all

other assumptions remaining the same, the PV would be $479,569.

Column G contains the present value factors for t 0, the formula

of which is the more usual

1

PVF

r)t 0.5

(1

When we multiply Column D by Column G to get Column H, the latter

is the PV of the cash ¬‚ows as of time zero. Note that the ¬nal sum in

H20 is identical to F22, as it should be.

So far we have come to the PV of the cash ¬‚ows using the brute force

method. In the next section we will test the formulas in the preceding

pages to see if they produce the same result.

Testing Equations (A3-3) and (A3-4)

Cell B27 contains the formula for the PV of the ¬rst 10 whole years of

cash ¬‚ows (see A48 for the spreadsheet formula). It is the same as equa-

tion (A3-2) without the rightmost term.19 The result of $6.42899 in B27

matches F18, thereby demonstrating the accuracy of that portion of equa-

tion (A3-2).

Cell C27 is calculated using the rightmost term in equation (A3-2)

and comes to $0.13883 (see A49 for the spreadsheet formula), which

matches F19, thus proving that portion of the formula. The sum of the

two is $6.56782 (D27), which matches F20.

Row 29 is the result of multiplying Row 27 by Row 28, the latter of

which is the present value factor to discount the cash ¬‚ows from t 2.25

to t 0 (it is the same as F21). We total B29 and C29 to $4.79569 (D29),

which matches F22 and H20. Finally, in E29 we use the complete formula

in equation (A3-3) to produce the same result of $4.79569 (see cell A51

for the spreadsheet formula). Thus, we have demonstrated the accuracy

r)S 1 1/1.152.25

18. This is 1/(1 0.73018 (see formulas in cell A50).

19. The formulas are the same; however, in the spreadsheet we have substituted GM (Gordon

multiple) for 1 r/(r g) and x for (1 g)/(1 r). Additionally, we have factored out

the GM.

CHAPTER 3 Annuity Discount Factors and the Gordon Model 97

of equation (A3-3) as a whole as well as showing how we can calculate

the parts.

Table A3-2 is identical to Table A3-1, except that we use end-of-year

present values, and equation (A3-4) is the relevant ADF formula. The end-

of-year formula gives a grand total of 4.47640 (F22, H20, D29, and E29).

TABLE A3-3: LOAN AMORTIZATION

In the chapter we demonstrated how ADFs are useful in calculating loan

payments and the present value of a loan. This section on loan amorti-

zation complements the material we presented in the chapter.

The amortization of loan principal in any time period is the PV of

the loan at the beginning of the period, less the PV at the end of the

period. While this is conceptually easy, it is a cumbersome procedure.

Let™s develop some preliminary results that will lead us to a more ef¬cient

way to calculate loan amortization.

Section 1: Traditional Loan Amortization Schedule

Table A3-3 is a loan amortization schedule that is divided into three sec-

tions. Section 1 is a traditional amortization schedule for a $1 million loan

at 10% for 5 years. The loan begins on February 28, 1998 (B7), and the

¬rst payment is on March 31, 1998 (B8). During the calendar year 1998

there will be 10 payments, leaving 50 more. There will be 12 monthly

payments in each of the years 1999“2002, and the ¬nal two payments are

in the beginning of 2003, with February 28, 2003 (B67), being the ¬nal

payment.

Column A is the payment number. There are 60 months of the loan,

hence 60 payments. Columns D and E are the interest and principal for

the particular payment, while columns G and H are interest and principal

cumulated in calendar year totals. Because the loan payments begin on

March 31, 1998, the ¬rst year™s totals in columns G and H are totals for

the ¬rst 10 payments only. Column I is the present value factor (PVF) at

10%, and column J is the present value of each loan payment. Column K

is the sum of the present values of the loan payments by calendar year.

Note that the PV of the loan payments sum to $1 million (J68).

Section 2: Present Values of Yearly Loan Payment

In Section 2 we calculate the present value of each year™s loan payment

using the ADF equation for no growth, no stub period, and end-of-year

cash ¬‚ows. We could use equation (3-11b) from the chapter, but ¬rst we

will simplify it further by setting g 0, so equation (3-11b) reduces to:

1 1 1 1

ADF

r)n S1

r)S v1

r (1 r (1

1 1 1

1 (A3-5)

r)n S1

r)S v1

r (1 (1

Cells D77 through D82 list the PV of the various calendar years™ cash

PART 1 Forecasting Cash Flows

98

¬‚ows discounted to the inception of the loan, February 28, 1998. Note

that these amounts exactly match those in column K of Section 1, and the

total is exactly $1 million”the principal of the loan”as it should be. This

demonstrates the accuracy of equation (A3-5), as all amounts calculated

in D77 through D82 use that equation (note that v, the valuation date in

months, appears in Row 86).

In Column E we are viewing the cash ¬‚ows from January 1, 1999,

i.e., immediately after the last payment in 1998 and one month before the

¬rst payment in 1999. Therefore, the 1998 cash ¬‚ows drop out entirely

and the PV of the 1999“2003 cash ¬‚ows increase relative to column D

because we discount the cash ¬‚ows 10 months less. The difference be-

tween the sum of the 1998 PVs discounted to February 28, 1998, and the

1999 payments discounted to January 1, 1999,20 is $1 million (D84)

$865,911 (E84) $134,089 (E85). We follow the same procedure each year

to calculate the difference in the PVs (Row 85), and ¬nally we come to a

total of the reductions in PV of $1 million, in K85, which is identical with

the original principal of the loan.

There are some signi¬cant numbers that repeat in southeasterly-

sloped diagonals in Section 2. The PV $241,675 appears in cells E78, F79,

G80, and H81. This means that the 1999 payments as seen from the be-

ginning of 1999 have the same PV as the 2000 payments as seen from the

beginning of 2000, etc. through 2002. Similarly, the PV of $218,767 repeats

in cells E79, F80, and G81. The interpretation of this series is the same as

before, except everything is moved back one year, i.e., the 2000 payments

as seen from the beginning of 1999 have the same PV as the 2001 pay-

ments as seen from the beginning of 2000 and the 2002 payments as seen

from the beginning of 2001.

This downward-sloping pattern gives us a clue to a more direct for-

mula for loan amortization. At the start of the loan, we have 60 payments

of $21,247. In the ¬rst calendar year, 10 payments will be made, for a total

of $212,470. At the end of the ¬rst year, which effectively is the same as

January 1, 1999, 50 payments will remain. The PV of the ¬nal 50 payments

discounted to January 1, 1999, is the same as the PV of the ¬rst 50 pay-

ments discounted to March 1, 1998 (using March 1 synonymously with

February 28 in a present value sense), because the entire time line will

have shifted by 10 months (10 payments). Therefore, the ¬rst calendar

year™s loan amortization can be represented by the PV of the ¬nal 10

payments discounted to March 1, 1998, as that would make up the only

difference in the two series of cash ¬‚ows as perceived from their different

points in time. This is illustrated graphically in Figure A3-2.

Figure A3-2 is a time line of payments on the ¬ve-year (60-month)

loan. The top portion of the ¬gure, labeled A, graphically represents the

entire payment schedule. In the bottom ¬gure the loan is split into several

pieces: payments 1“10, which are not labeled;21 payments 1“50, labeled

20. Technically, we discount to the end of December 31, 1998, but in PV terms it is easier to think

of January 1, 1999.

21. In all cases the zero is there only as a valuation date. There are no loan payments (cash ¬‚ows)

that occur at zero.

CHAPTER 3 Annuity Discount Factors and the Gordon Model 99

F I G U R E A3-2

Payment Schedule

A

0 11 20 30 40 50 60

±PV = C

B

0 50 60

D

11 60

3/1/98 1/31/99

PV = PV0(A) - PV10(D) = PV0(A) - PV0(B) = PV0(C)

$134,089 = $1 Million - $865,911 = $1 Million - $865,911 $134,089

B; payments 11“60, labeled D; and payments 51“60, labeled C (t 50 is

the end of B, not the beginning of C).

The equation at the bottom of Figure A3-2, which we explain below

in 1“3, is: PV PV0(A) PV10(D) PV0(A) PV0(B) PV0(C). The

amortization of the loan principal during any year is the change in the

present value of the loan between years. That is equal to each of the

following three expressions:

1. PV0(A) PV10(D): The PV at t 0 of A (all 60 months of the

loan) minus the PV at t 10 of D, the last 50 payments of the

loan. Notice that the valuation dates are different, t 0 versus

t 10. The PV at t 0 of A is the principal, $1 million (Table

A3-3, Section 2, D84). The PV at t 10 of D is $865,911 (E84).

The difference of the two is the amortization of $134,089 (E85).

2. PV0(A) PV0(B): The PV at t 0 of A (all 60 months of the

loan), which is $1 million, minus the PV at t 0 of the ¬rst 50

months of the loan. The latter calculation does not appear

directly in Table A3-3. However, using equation (3-6d) from the

chapter with g 0, r 0.83333%, and n 50 periods leads to

the ADF of 40.75442. Multiplying the ADF by the monthly

payment of $21,247.04 gives us the PV of B, which is $865,911.

The difference of the two PVs is $134,089, the same as above.

3. PV0(C): The PV at t 0 of C, payments 51“60. This is the most

important of the expressions because it is the most compact and

the easiest to use. The other expressions are the difference of

two formulas, while this one requires only a single formula. It is

stated in mathematical terms below in equation (A3-10). The

reduction in the principal is the PV of the opposite or mirror-

image series of cash ¬‚ows working backward from the end of

the loan.

Section 3: A Better Way to Calculate Loan Amortization

In Section 3 we calculate the principal reduction using equation (A3-10).

Let™s look ¬rst at the 1998 cash ¬‚ows in Row 93. The amortization of

principal in 1998 is equal to the PV of the last 10 payments of the loan.

PART 1 Forecasting Cash Flows

100

Letting n (the ¬nal payment period) 60, we want to calculate the PV

of payments 51“60, discounted to month 0. If we let F ¬nishing month

10, the formula n F 1 describes S, the starting month in C93