Chapter 15: Creating Formulas for Financial Applications
337
Creating a loan amortization schedule .....................................................................
A loan amortization schedule is a table of values that shows various types of information for each
payment period of a loan. Figure 15.5 shows a worksheet that uses formulas to calculate an amor-
tization schedule.FIGURE 15.5
A loan amortization schedule.On the CD
This workbook available on the companion CD-ROM. The file is named loan amortization schedule
.xlsx.
The loan parameters are entered into B1:B4, and the formulas beginning in row 9 use these values
for the calculations. Table 15.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 payment periods (40 years of monthly payments).Note
Formulas in the rows that extend beyond the number of payments return an error value. The worksheet uses
conditional formatting to hide the data in these rows. See Chapter 20 for more information about conditional
formatting. n