Microsoft Office Tutorials and References
In Depth Information
Creating Amortization Schedules
h Although the user is allowed to specify a term, the rows in the schedule are fixed.
Specifying a shorter or longer term would require that formulas be deleted or added to
In the next section, I address some of the flexibility issues and create a more dynamic
A dynamic amortization schedule
The example in this section builds on the previous example. Figure 13-2 shows a loan
amortization schedule that allows the user to define input parameters beyond the amount, rate, and term.
Notice that rows 22 through 114 are hidden.
Figure 13-2: A dynamic amortization schedule.
The first difference you’ll notice is that this schedule has more shaded cells, meaning there are
more cells that the user can change. Also, a column has been added for the annual percentage
rate, which now can be different for every period.
User input section
Not much has changed in Input Area at the top. The interest rate is labeled Starting Rate, and the
payment is labeled Computed Payment, indicating that they are subject to change.