Microsoft Office Tutorials and References
In Depth Information
The previous balance, in cell F14, is multiplied by the annual interest rate, which is divided by 12. The annual
interest rate is in cell B7, named Rate.
Whatever portion of the payment doesn't go toward interest goes toward reducing the principal balance. The
formula in E15 is
Finally, the balance is updated to reflect the principal portion of the payment. The formula in E15 is
Loan amortization schedules are self-checking. If everything is set up correctly, the final balance at the end of
the term is 0 (or very close to 0, given rounding errors). Another check is to add all of the monthly Principal
components. The sum of these values should equal the original loan amount.
This type of schedule is suitable for loans that will likely never change. It can be set up one time and referred to
throughout the life of the loan. Further, you can copy it to create a new loan with just a few adjustments.
However, this schedule lacks flexibility:
• The payment is computed and applied every month but cannot account for overpayments. Borrowers often
pay an additional amount that is applied to the principal, thereby reducing the pay-off period.
• Many loans have variable interest rates, and this schedule provides no way to adjust the interest rate per
• The schedule has a fixed term of 30 years. A loan with a shorter or longer term would require that formulas
be deleted or added to compensate.
In the next section, I address some of the flexibility issues and create a more dynamic amortization schedule.
A dynamic amortization schedule
The example in this section builds on the previous example. Figure 13-2 shows part of a loan amortization
schedule that allows the user to define input parameters beyond the loan amount and rate.