Microsoft Office Tutorials and References
In Depth Information
Creating Amortization Schedules
The payment column simply references the PMT function from the user input section. Because
that formula was rounded, no further rounding is necessary.
The interest column computes a monthly interest based on the previous balance. The formula in
The previous balance, in cell E14, is multiplied by the annual interest rate, which is divided by 12.
The annual interest rate is in cell B7, named Rate. Each month’s balance must be rounded to the
penny, so every interest calculation is rounded as you go.
Whatever portion of the payment doesn’t go toward interest goes toward reducing the principal
balance. The formula in D15 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 the Principal components. The sum of these values should
equal the original loan amount.
This type of schedule is excellent 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, it leaves a little to be desired.
You may have noticed that the balance at the end of the loan, as well as the total principal paid in
the summary section, is off by $4.07. This is because of the rounding of each month’s payment and
interest calculation. Although rounding those results is necessary, a more flexible schedule would
allow you to adjust the final payment so the balance is zero when the final payment is made.
This schedule lacks flexibility in other ways as well:
h The payment is computed and applied every month but cannot account for over- or
h Many loans have variable interest rates, and this schedule provides no way to adjust the
interest rate per period.