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

C15 is

=ROUND(E14*Rate/12,2)

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

=B15–C15

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

=E14–D15

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.

Limitations

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

under-payments.

h
Many loans have variable interest rates, and this schedule provides no way to adjust the

interest rate per period.