стр. 17 |

We begin with a month-by-month amortization. In the п¬Ѓrst month,

amortization equals the PVF for the last month. In the second month,

amortization equals the PVF for the second-to-last month, and we con-

tinue in that fashion. Mathematically, amortization is thus equal to:

1 1 1 1

Amort ... Pymt

r)n r)n 1

r)n 2

(1 (1 (1 1 r

(A3-11)

Note that this expression is the exact reverse of a simple series of cash

п¬‚ows that solves to an end-of-year ADF with no growth, i.e., equation

(3-6d) in the body of the chapter. Thus, the total amortization equals equa-

tion (3-6d) Loan Payment Principal of the Loan. This is a rearrange-

ment of equation (3-21). Note that one should use the nominal interest

rate in this calculation.

Next we take the PV of equation (A3-11) at the nominal rate of in-

terest (when valuing a loan at a discount rate other than the nominal rate

of interest, see that discussion at the end of this chapter).

1 1 1

r)n r)n 1 r)n 2

(1 (1 (1

PV (Amort)

r)2 r)3

1 r (1 (1

1

1 r

... Pymt (A3-12)

r)n

(1

We can move the second denominator into the п¬Ѓrst denominator, and

equation (A3-12) simpliп¬Ѓes to:

1 1 1

PV (Amort)

r)n 1

r)n 1

r)n 1

(1 (1 (1

1

... Pymt [n terms] (A3-13)

r)n 1

(1

PART 1 Forecasting Cash Flows

102

T A B L E A3-3

Amortization of Principal with Irregular Starting Point

A B C D E F G H I J K L M N O

4 SECTION 1: LOAN AMORTIZATION SCHEDULE

5 Pmt NPV Annual Aft-Tax

6 # Date Pmt Int Prin Bal Int Prin PVF Pymt NPV Cost-

Loan

7 0 02/28/98 1,000,000 1.0000

8 1 03/31/98 21,247 8,333 12,914 987,086 0.9917 21,071 17,766 12807 4959

9 2 04/30/98 21,247 8,226 13,021 974,065 0.9835 20,897 17,661 12807 4854

10 3 05/31/98 21,247 8,117 13,130 960,935 0.9754 20,725 17,558 12807 4751

11 4 06/30/98 21,247 8,008 13,239 947,696 0.9673 20,553 17,455 12807 4648

12 5 07/31/98 21,247 7,897 13,350 934,346 0.9594 20,383 17,353 12807 4546

13 6 08/31/98 21,247 7,786 13,461 920,885 0.9514 20,215 17,252 12807 4445

14 7 09/30/98 21,247 7,674 13,573 907,312 0.9436 20,048 17,152 12807 4345

15 8 10/31/98 21,247 7,561 13,686 893,626 0.9358 19,882 17,052 12807 4245

16 9 11/30/98 21,247 7,447 13,800 879,826 0.9280 19,718 16,954 12807 4147

17 10 12/31/98 21,247 7,332 13,915 865,911 78,381 134,089 0.9204 19,555 203,048 16,856 12807 4049

18 11 01/31/99 21,247 7,216 14,031 851,880 0.9128 19,393 16,759 12807 3952

19 12 02/28/99 21,247 7,099 14,148 837,732 0.9052 19,233 16,663 12807 3856

20 13 03/31/99 21,247 6,981 14,266 823,466 0.8977 19,074 16,567 12807 3760

21 14 04/30/99 21,247 6,862 14,385 809,081 0.8903 18,917 16,473 12807 3666

22 15 05/31/99 21,247 6,742 14,505 794,576 0.8830 18,760 16,379 12807 3572

23 16 06/30/99 21,247 6,621 14,626 779,951 0.8757 18,605 16,286 12807 3479

24 17 07/31/99 21,247 6,500 14,747 765,203 0.8684 18,451 16,194 12807 3387

25 18 08/31/99 21,247 6,377 14,870 750,333 0.8612 18,299 16,102 12807 3295

26 19 09/30/99 21,247 6,253 14,994 735,339 0.8541 18,148 16,011 12807 3204

27 20 10/31/99 21,247 6,128 15,119 720,220 0.8471 17,998 15,921 12807 3114

28 21 11/30/99 21,247 6,002 15,245 704,974 0.8401 17,849 15,832 12807 3025

29 22 12/31/99 21,247 5,875 15,372 689,602 78,656 176,309 0.8331 17,701 222,428 15,744 12807 2937

30 23 01/31/00 21,247 5,747 15,500 674,102 0.8262 17,555 15,656 12807 2849

31 24 02/28/00 21,247 5,618 15,630 658,472 0.8194 17,410 15,569 12807 2762

32 25 03/31/00 21,247 5,487 15,760 642,712 0.8126 17,266 15,482 12807 2675

33 26 04/30/00 21,247 5,356 15,891 626,821 0.8059 17,123 15,397 12807 2590

34 27 05/31/00 21,247 5,224 16,024 610,798 0.7993 16,982 15,312 12807 2505

35 28 06/30/00 21,247 5,090 16,157 594,641 0.7927 16,842 15,228 12807 2421

36 29 07/31/00 21,247 4,955 16,292 578,349 0.7861 16,702 15,144 12807 2337

37 30 08/31/00 21,247 4,820 16,427 561,922 0.7796 16,564 15,061 12807 2254

103

104

T A B L E A3-3 (continued)

Amortization of Principal with Irregular Starting Point

A B C D E F G H I J K L M N O

4 SECTION 1: LOAN AMORTIZATION SCHEDULE

5 Pmt NPV Annual Aft-Tax

6 # Date Pmt Int Prin Bal Int Prin PVF Pymt NPV Cost-

Loan

38 31 09/30/00 21,247 4,683 16,564 545,357 0.7732 16,427 14,979 12807 2172

39 32 10/31/00 21,247 4,545 16,702 528,655 0.7668 16,292 14,898 12807 2091

40 33 11/30/00 21,247 4,405 16,842 511,813 0.7604 16,157 14,817 12807 2010

41 34 12/31/00 21,247 4,265 16,982 494,831 60,194 194,771 0.7542 16,024 201,345 14,737 12807 1930

42 35 01/31/01 21,247 4,124 17,123 477,708 0.7479 15,891 14,657 12807 1850

43 36 04/29/61 21,247 3,981 17,266 460,442 0.7417 15,760 14,579 12807 1772

44 37 03/31/01 21,247 3,837 17,410 443,032 0.7356 15,630 14,501 12807 1694

45 38 04/30/01 21,247 3,692 17,555 425,476 0.7295 15,500 14,423 12807 1616

46 39 05/31/01 21,247 3,546 17,701 407,775 0.7235 15,372 14,346 12807 1539

47 40 06/30/01 21,247 3,398 17,849 389,926 0.7175 15,245 14,270 12807 1463

48 41 07/31/01 21,247 3,249 17,998 371,928 0.7116 15,119 14,194 12807 1387

49 42 08/31/01 21,247 3,099 18,148 353,781 0.7057 14,994 14,119 12807 1312

50 43 09/30/01 21,247 2,948 18,299 335,482 0.6999 14,870 14,045 12807 1238

51 44 10/31/01 21,247 2,796 18,451 317,031 0.6941 14,747 13,971 12807 1164

52 45 11/30/01 21,247 2,642 18,605 298,425 0.6884 14,626 13,898 12807 1091

53 46 12/31/01 21,247 2,487 18,760 279,665 39,799 215,166 0.6827 14,505 182,260 13,826 12807 1019

54 47 01/31/02 21,247 2,331 18,917 260,749 0.6770 14,385 13,754 12807 947

55 48 02/28/02 21,247 2,173 19,074 241,675 0.6714 14,266 13,682 12807 875

56 49 03/31/02 21,247 2,014 19,233 222,442 0.6659 14,148 13,612 12807 805

57 50 04/30/02 21,247 1,854 19,393 203,048 0.6604 14,031 13,541 12807 734

58 51 05/31/02 21,247 1,692 19,555 183,493 0.6549 13,915 13,472 12807 665

59 52 06/30/02 21,247 1,529 19,718 163,775 0.6495 13,800 13,403 12807 596

60 60 07/31/02 21,247 1,365 19,882 143,893 0.6441 13,686 13,334 12807 527

61 54 08/31/02 21,247 1,199 20,048 123,845 0.6388 13,573 13,267 12807 460

62 55 09/30/02 21,247 1,032 20,215 103,630 0.6335 13,461 13,199 12807 392

63 56 10/31/02 21,247 864 20,383 83,247 0.6283 13,350 13,133 12807 326

64 57 11/30/02 21,247 694 20,553 62,693 0.6231 13,239 13,066 12807 259

65 58 12/31/02 21,247 522 20,725 41,969 17,268 237,697 0.6180 13,130 164,984 13,001 12807 194

66 59 01/31/03 21,247 350 20,897 21,071 0.6129 13,021 12,936 12807 129

67 60 02/28/03 21,247 176 21,071 0 525 41,969 0.6078 12,914 25,935 12,871 12807 64

68 Totals 1,274,823 274,823 1,000,000 274,823 1,000,000 1,000,000 1,000,000 907,368

T A B L E A3-3 (continued)

Amortization of Principal with Irregular Starting Point

A B C D E F G H I J K L M N O

73 SECTION 2: SCHEDULE OF PRESENT VALUES CALCULATED BY ADF EQUATION (A3-5)

75 As Seen From The Beginning of Year

76 1998 1999 2000 2001 2002 2003 2004 Total

77 NPV 1998 payments [1] 203,048

78 NPV 1999 payments 222,428 241,675

79 NPV 2000 payments 201,345 218,767 241,675

80 NPV 2001 payments 182,260 198,031 218,767 241,675

81 NPV 2002 payments 164,984 179,260 198,031 218,767 241,675

82 NPV 2003 payments 25,935 28,179 31,130 34,390 37,991 41,969

83 NPV 2004 payments 0

84 Sum NPVs-all pymts 1,000,000 865,911 689,602 494,831 279,665 41,969 0 0

85 Reduction in NPV 134,089 176,309 194,771 215,166 237,697 41,969 1,000,000

86 Valuation date v 10 22 34 46 58

0

88 SECTION 3: AMORTIZATION CALCULATED AS THE PYMT * THE ADF in (A3-16)

90 Formulas For Principal Amortization, where:

91 Starting Finishing Prin I Monthly Interest 0.833%, n 60 Months,

92 Month Month Amort Pymt $21,247/Month

93 Calendar 1998 1 10 134,089 PYMT*(1/r)*((1/(1 r) (N $D93) (1/(1 r) (N $C93 1))))

94 Calendar 1999 11 22 176,309 PYMT*(1/r)*((1/(1 r) (N $D94) (1/(1 r) (N $C94 1))))

95 Calendar 2000 23 34 194,771 PYMT*(1/r)*((1/(1 r) (N $D95) (1/(1 r) (N $C95 1))))

96 Calendar 2001 35 46 215,166 PYMT*(1/r)*((1/(1 r) (N $D96) (1/(1 r) (N $C96 1))))

97 Calendar 2002 47 58 237,697 PYMT*(1/r)*((1/(1 r) (N $D97) (1/(1 r) (N $C97 1))))

98 Calendar 2003 59 60 41,969 PYMT*(1/r)*((1/(1 r) (N $D98) (1/(1 r) (N $C98 1))))

99 Total 1,000,000

105

106

T A B L E A3-3 (continued)

Amortization of Principal with Irregular Starting Point

A B C D E F G H I J K L M N O

102 Assumptions: After-Tax Cost of the Loan

104 Prin 1,000,000 (1 t) * Prin 0.600000 600,000

105 Int 10.0000% t*n/(1 r) (n 0.307368 307,368

1)*PYMT

106 Int-Mo 0.8333% Total L68 0.907368 907,368

107 Years 5

108 Months n 60 H106: (1 t) [t*N/(1 r) (N 1)*PYMT/P] Equation (A3-24a)

109 Pymt 21,247 I106: (1 t)*P [t*N/(1 r) (N 1)*PYMT] Equation (A3-23a)

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

стр. 17 |