Ross et al.: Fundamentals
of Corporate Finance, Sixth
Edition, Alternate EditionIII. Valuation of Future
Cash Flows- Discounted Cash Flow
Valuation
(^216) © The McGraw−Hill
Companies, 2002
after making payments for four years, you still owe the present value of the remaining
payments. There are 120 payments in all. After you make 48 of them (the first four
years), you have 72 to go. By now, it should be easy for you to verify that the present
value of $282.10 per month for 72 months at 0.6875 percent per month is just under
$16,000, so you still have a long way to go.
Of course, it is possible to rack up much larger debts. According to a 2001 article in
Medical Economics,two married MDs, fresh out of med school, had a combined edu-
cation debt of $544,000! Ouch! Is there a finance doctor in the house? The smallerof
the two loans had a balance of $234,000, and the payments on just this portion were
$1,750 per month. The interest rate was 7 percent. The article says it will take 22 years
just to pay off the loan. Is that right?
In this case, we have an ordinary annuity of $1,750 per month for some unknown
number of months. The interest rate is 7/12 .5833 percent per month, and the present
value is $234,000. See if you agree that it will take about 260 months, or just under 22
years, to pay off the loan. Maybe MD really stands for “mucho debt!”
186 PART THREE Valuation of Future Cash Flows
SPREADSHEET STRATEGIES
Loan Amortization Using a Spreadsheet
Loan amortization is a very common spreadsheet application. To illustrate,
we will set up the problem that we examined earlier, a five-year, $5,000,
9 percent loan with constant payments. Our spreadsheet looks like this:
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
AB C D E F G H
Loanamount: $5,000
Interestrate: 0.09
Loanterm: 5
Loanpayment: $1,285.46
Note:paymentiscalculatedusingPMT(rate,nper,-pv,fv)
Amortization table:
Year Beginning Total Interest Principal Ending
Balance Payment Paid Paid Balance
1 $5,000.00 $1,285.46 $450.00 $835.46 $4,164.54
2 4,164.54 1,285.46 374.81 910.65 3,253.88
3 3,253.88 1,285.46 292.85 992.61 2,261.27
4 2,261.27 1,285.46 203.51 1,081.95 1,179.32
5 1,179.32 1,285.46 106.14 1,179.32 0.00
Totals 6,427.31 1,427.31 5,000.00
Formulas in the amortization table:
Year Beginning Total Interest Principal Ending
Balance Payment Paid Paid Balance
1 =+D4 =$D$7 =+$D$5C13 =+D13-E13 =+C13-F13
2 =+G13 =$D$7 =+$D$5C14 =+D14-E14 =+C14-F14
3 =+G14 =$D$7 =+$D$5C15 =+D15-E15 =+C15-F15
4 =+G15 =$D$7 =+$D$5C16 =+D16-E16 =+C16-F16
5 =+G16 =$D$7 =+$D$5*C17 =+D17-E17 =+C17-F17
Note:totalsintheamortizationtablearecalculatedusingtheSUMformul a.
Using a spreadsheet to amortize a loan