<<

. 24
( 100 .)



>>

24 Calculation of PV by formulas:
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

<<

. 24
( 100 .)



>>