Microsoft Office Tutorials and References

In Depth Information

Figure 11-5:
A loan amortization schedule.

This workbook is available on this book's website. The file is named loan amortization

schedule.xlsx.

The loan parameters are entered into C1:C4, and the formulas beginning in row 9 use these values for the calcu-

lations. Table 11-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).

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.