<<

. 26
( 100 .)



>>

110 Form-Prin 1,000,000
111 Start month S 3
112 x (1 g)/(1 r) 0.9917
113 y 1/(1 r) 0.9917
114 GM 1/r 120

Notes:
[1] Formula for D77 according to (A3-5): GM*(1 x ($D93 $C93 1))*y ($C93 A$86 1)*PYMT
n # months of cash ¬‚ow $D93 $C93 1, which is the ending month - beginning month 1. The exponent of y is the ending month - the valuation date); thus it is the discounting period. This formula copies both down and across,
i.e., it is the formula for all cells from D77 to I82. D78 D77 because there are 10 payments in 1998 and 12 in 1999“2002.
All the bracketed terms in equation (A3-13) are identical. Thus, the
PV of the amortization of principal, which we denote below as PV(P), is
equal to n any one of these terms the loan payment.
n
PV (Amort) PV (P) Pymt
r)n 1
(1
PV of principal payments (A3-14)
Restating equation (3-21) as equation (A3-15),
P
Pymt , (A3-15)
ADF
where ADF is de¬ned by equation (3-6d). Substituting equation (A3-15)
into equation (A3-14), we get:
n P
PV(P) (A3-16)
r)n 1
(1 ADF
The next section, in which we develop equations (3-16a) and (3-16b),
is somewhat of a digression from the previous and the subsequent dis-
cussion. We do not use equations (A3-16a) and (A3-16b) in our subse-
quent work. However, these formulas can be useful alternative forms
of (A3-16). Substituting in the de¬nition of the ADF, dividing through
by the principal, and solving the equation,22 another form of equation
(A3-16) is:
PV(P) n
(A3-16a)
r)n
P [(1 1](1 r)
Table A3-4 veri¬es the accuracy of this formula, which is my own
formula, to the best of my knowledge. For a ¬ve-year (60-month) loan at
12% per year, or 1% per month (A5 and A4, respectively), the present


T A B L E A3-4

PV of Principal Amortization

A B

4 r 1%
5 n 60
6 PV(P)/Pmt 32.69997718
7 Pmt/P $0.0222444
8 PV(P)/P $0.7273929
9 PV(P)/P $0.7273929
11 Cell Formulas:
13 B6: n/(1 r) (n 1)
14 B7: PMT(.01,60, 1)
15 B9: B7*B8
16 B10: (n*r)/(((1 r) n 1)*(1 r))




22. We do not show the steps to the solution, as we are not using this equation in our subsequent
work.


CHAPTER 3 Annuity Discount Factors and the Gordon Model 107
value of the principal divided by the loan payment is 32.69997718 (B6).
The formula for that cell appears in cell A13, and that formula is equation
(A3-14) after dividing both sides of the equation by the payment. In B7
we show the monthly payment per dollar of loan principal, which we
calculate using a standard spreadsheet ¬nancial function for a $1 loan
with 60 monthly payments at 1% interest (see cell A14 for the formula).
In B8 we multiply B6 B7. In B9 we test equation (A3-16a), and it comes
to the same answer as B8, i.e., the present value of the principal is
$0.7273929 per $1 of principal. That the two answers are identical dem-
onstrates the accuracy of equation (A3-16a). Of course, the present value
of the interest on a pretax basis is one minus that, or approximately $0.273
per $1 of principal.
In algebraic terms, the present value of the interest portion of a loan
per dollar of principal on a pretax basis is one minus (A3-16a), or:
PV(Int) n
1 (A3-16b)
n
P [(1 r) 1](1 r)
Resuming our discussion after the digression in the last several par-
agraphs, the PV of the interest portion of the payments is simply the PV
of the loan payments”which is the principal”minus the PV of the prin-
cipal portion, or:
PV(Int) P PV(P) (A3-17)
Substituting equation (A3-16) into equation (A3-17), we get:
n P n 1
PV(Int) P P1 (A3-18)
r)n 1
r)n 1
(1 ADF (1 ADF
The PV of the after-tax cost of the interest portion is (1 t) * (A3-18),
where t is the tax rate, or:
n 1
PV(Int)After-Tax (1 t) P 1 (A3-19)
r)n 1
(1 ADF
Thus, the after-tax cost of the loan, L, is (A3-16) plus (A3-19), or:
n P n 1
L (1 t)P 1 (A3-20)
r)n 1
r)n 1
(1 ADF (1 ADF
Factoring terms, we get:
n P
L [1 (1 t)] (1 t)P (A3-21)
r)n 1
(1 ADF
which simpli¬es to:
n P
L t (1 t)P (A3-22)
r)n 1
(1 ADF
Switching terms, our ¬nal equation for the after-tax cost of a loan is:
n P
L (1 t)P t after-tax cost of a loan (A3-23)
r)n 1
(1 ADF
Alternatively, using

PART 1 Forecasting Cash Flows
108
P
Loan Payment
ADF
we can restate equation (A3-23) as:
n
L (1 t)P t Pymt
r)n 1
(1 (A3-23a)
alternative expression”after-tax cost of loan
Equation (A3-23) gives us the equation for the after-tax cost of a loan
in dollars. We can restate equation (A3-23) to give us the after-tax cost of
the loan for each $1.00 of loan principal by dividing through by P.
L n 1
(1 t) t
r)n 1
P (1 ADF
after-tax cost of loan per each $1.00 of principal (A3-24)
Analyzing equation (A3-24), we can see the after-tax cost of the loan
is made up of two parts:
1. The after-tax cost of the principal, as if the entire loan payment
was tax-deductible, plus
2. The tax rate times the PV of the principal payments on the loan.
In item 1 we temporarily assume that principal and interest are tax-
deductible. This is actually true for ESOP loans, and the PV of an ESOP
loan is item 1. To adjust item 1 upwards for the lack of tax shield on the
principal of ordinary loans, in item 2 we add back the tax shield included
in item 1 that we do not really get. Of course, we can substitute the exact
expression for ADF in equation (A3-24) to keep the solution strictly in
terms of the variables t, n, and r.
We can derive an alternative expression for equation (A3-24) by di-
viding equation (A3-23a) by P:
L n Pymt
(1 t) t
r)n 1
P (1 P
alternative expression”after-tax cost of loan/$1 of principal
(A3-24a)
We demonstrate the accuracy of equations (A3-23a) and (A3-24a) in
Table A3-3. In Section 1, Column L is the after-tax cost of each loan pay-
ment. It is equal to the sum of [Principal (Column E) (1 Tax Rate)
Interest (Column D)] Present Value Factor (Column I). We assume
a 40% tax rate in this table. Thus cell L8, the after-tax cost of the ¬rst
month™s loan payment, is equal to [$12,914 (E8) (1 40%) $8,333
(D8)] 0.9917 (I8) $17,766. The sum of the after-tax cost of the loan
payments is $907,368 (L68).
We now move to Section 3, F102 to J109. Here we use equation (A3-
24a) to test if we get the same answer as the brute force approach in L68.
In I104 we show the PV of the principal after tax, corresponding to item
1 above, as $600,000 (H104 is the same, but for each $1.00 of principal).
In I105 we show the tax shield on the principal that we do not get at

CHAPTER 3 Annuity Discount Factors and the Gordon Model 109
$307,368. The sum of the two is $907,368 (I106), which matches L68 and
thus proves equation (A3-24a). Note that I106, which we calculate ac-
cording to equation (A3-23a), equals $0.907368, which is the correct after-
tax cost of the loan per each dollar of principal. When we multiply that
by the $1 million principal, we get the correct after-tax cost of the loan
in dollars, as per cell I106 and equation (A3-23a).


T A B L E A3-5

Present Value of a Loan at Discount Rate Different than Nominal Rate


A B C D E F G

5 Pmt
6 # Pmt Int Prin Bal PVF (r1) PV(P)

7 0 1,000,000 1.0000
8 1 21,247 8,333 12,914 987,086 0.9901 12,786
9 2 21,247 8,226 13,021 974,065 0.9803 12,765
10 3 21,247 8,117 13,130 960,935 0.9706 12,744
11 4 21,247 8,008 13,239 947,696 0.9610 12,723
12 5 21,247 7,897 13,350 934,346 0.9515 12,702
13 6 21,247 7,786 13,461 920,885 0.9420 12,681
14 7 21,247 7,674 13,573 907,312 0.9327 12,660
15 8 21,247 7,561 13,686 893,626 0.9235 12,639
16 9 21,247 7,447 13,800 879,826 0.9143 12,618
17 10 21,247 7,332 13,915 865,911 0.9053 12,597
18 11 21,247 7,216 14,031 851,880 0.8963 12,576
19 12 21,247 7,099 14,148 837,732 0.8874 12,556
20 13 21,247 6,981 14,266 823,466 0.8787 12,535
21 14 21,247 6,862 14,385 809,081 0.8700 12,514
22 15 21,247 6,742 14,505 794,576 0.8613 12,494
23 16 21,247 6,621 14,626 779,951 0.8528 12,473
24 17 21,247 6,500 14,747 765,203 0.8444 12,452
25 18 21,247 6,377 14,870 750,333 0.8360 12,432
26 19 21,247 6,253 14,994 735,339 0.8277 12,411
27 20 21,247 6,128 15,119 720,220 0.8195 12,391
28 21 21,247 6,002 15,245 704,974 0.8114 12,370
29 22 21,247 5,875 15,372 689,602 0.8034 12,350
30 23 21,247 5,747 15,500 674,102 0.7954 12,330
31 24 21,247 5,618 15,630 658,472 0.7876 12,309
32 25 21,247 5,487 15,760 642,712 0.7798 12,289
33 26 21,247 5,356 15,891 626,821 0.7720 12,269
34 27 21,247 5,224 16,024 610,798 0.7644 12,248
35 28 21,247 5,090 16,157 594,641 0.7568 12,228
36 29 21,247 4,955 16,292 578,349 0.7493 12,208
37 30 21,247 4,820 16,427 561,922 0.7419 12,188
38 31 21,247 4,683 16,564 545,357 0.7346 12,168
39 32 21,247 4,545 16,702 528,655 0.7273 12,148
40 33 21,247 4,405 16,842 511,813 0.7201 12,128
41 34 21,247 4,265 16,982 494,831 0.7130 12,108
42 35 21,247 4,124 17,123 477,708 0.7059 12,088
43 36 21,247 3,981 17,266 460,442 0.6989 12,068
44 37 21,247 3,837 17,410 443,032 0.6920 12,048
45 38 21,247 3,692 17,555 425,476 0.6852 12,028
46 39 21,247 3,546 17,701 407,775 0.6784 12,008
47 40 21,247 3,398 17,849 389,926 0.6717 11,988
48 41 21,247 3,249 17,998 371,928 0.6650 11,968
49 42 21,247 3,099 18,148 353,781 0.6584 11,949




PART 1 Forecasting Cash Flows
110
T A B L E A3-5 (continued)

Present Value of a Loan at Discount Rate Different than Nominal Rate


A B C D E F G

5 Pmt
6 # Pmt Int Prin Bal PVF (r1) PV(P)

50 43 21,247 2,948 18,299 335,482 0.6519 11,929
51 44 21,247 2,796 18,451 317,031 0.6454 11,909
52 45 21,247 2,642 18,605 298,425 0.6391 11,890
53 46 21,247 2,487 18,760 279,665 0.6327 11,870
54 47 21,247 2,331 18,917 260,749 0.6265 11,850
55 48 21,247 2,173 19,074 241,675 0.6203 11,831
56 49 21,247 2,014 19,233 222,442 0.6141 11,811
57 50 21,247 1,854 19,393 203,048 0.6080 11,792
58 51 21,247 1,692 19,555 183,493 0.6020 11,772
59 52 21,247 1,529 19,718 163,775 0.5961 11,753
60 53 21,247 1,365 19,882 143,893 0.5902 11,734

<<

. 26
( 100 .)



>>