Microsoft Office Tutorials and References

In Depth Information

=F14*(Rate/12)

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

=C15–D15

Finally, the balance is updated to reflect the principal portion of the payment. The formula in E15 is

=F14–E15

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.

Limitations

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

period.

• 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.