Page 341 - Excel 2007 Bible

21_044039 ch16.qxp 11/21/06 11:07 AM Page 298
Part II
Working with Formulas and Functions
FIGURE 16.5
A loan amortization schedule.

This workbook available on the companion CD-ROM. The file is named loan amortization
ON the CD-ROM schedule.xlsx.
ON the CD-ROM
The loan parameters are entered into B1:B4, and the formulas beginning in row 9 use these values for the
calculations. Table 16.2 shows the formulas in row 9 of the schedule. These formulas were copied down to
row 488. Therefore, the worksheet can calculate amortization schedules for a loan with as many as 480 pay-
ment periods (40 years of monthly payments).
NOTE Formulas in the rows that extend beyond the number of payments return an error value. The
NOTE
worksheet uses conditional formatting to hide the data in these rows. See Chapter 21 for more
information about conditional formatting.

TABLE 16.2
Formulas Used to Calculate an Amortization Schedule

Cell Formula Description
A9 =A8+1 Returns the payment number
B9 =PMT($B$2*($B$3/12),$B$4,-$B$1) Calculates the periodic payment amount
C9 =C8+B9 Calculates the cumulative payment amounts
D9 =IPMT($B$2*($B$3/12),A9,$B$4,-$B$1) Calculates the interest portion of the periodic payment
E9 =E8+D9 Calculates the cumulative interest paid

298