Microsoft Office Tutorials and References
In Depth Information
Creating Amortization Schedules
The absolute column means that every column in the selection will refer to column G; the relative
row means the row applies to the row above, regardless of which row you’re in.
For more information on conditional formatting, refer to Chapter 19.
The formulas are present in a row beyond row 127 (they exist for up to 360 months), but they are
hidden using conditional formatting to make the table size dynamic as well.
Using payment and interest tables
The preceding example allows the user to input data directly in the calculation and reporting section
of the schedule. This affords maximum flexibility and adds a level of intuitiveness to customizing the
schedule. Depending on the intended user, however, it could be dangerous and lead to errors. In
particular, overwriting formulas, like changing the interest rate in the last example, does not lend itself
to undoing or correcting errors. Unless the user is intimately familiar with the workings of the
spreadsheet, those hard-coded values can stick around when the user thinks they’re formulas.
Another method — and some would argue a better method — is to keep the user input section
separate from the calculation and reporting section. If all user inputs are relegated to one area,
it’s easier to determine what has been inputted and whether any inputs are missing.
This example uses the same basic data as the previous two examples. It adds an additional
payment table, an interest rate table, and a late payment table in the user input section, and the
formulas are adjusted. Figure 13-3 shows the user input section of this flexible schedule.