Amortization Formulas in Excel

This article lists some of the built-in Excel formulas that can be used for amortization calculations. You can download the Excel templates to see examples of how the formulas might be used. Most of the examples apply to loans, because amortization generally refers to paying off a loan through regular installments (payments). In the beginning, you are mostly paying interest, but as the principal (the amount still owed) decreases, more of the payment is going towards paying off the principal rather than interest.

* These formulas require you to install the Analysis ToolPak, which comes with Excel but is often not installed automatically. To install the add-in, open up Excel and go to the Tools menu > Add-Ins... and check the box next to "Analysis ToolPak".

I generally do not like to use built-in formulas unless I understand how they work. For amortization formulas, I think the best way to understand the equations is to create amortization tables or schedules to see what is actually going on from one payment period to the next.

To get started, the following Excel spreadsheet creates a very basic amortization table. In this worksheet, the only special Excel formula that is used is the PMT function to determine the monthly payment.

One thing that you should do with the above spreadsheet is look at what happens as you change the term of the loan. Pay particular attention to the graph that compares the cumulative interest vs. principal paid.

The following spreadsheet was made specifically to provide an example of using the PMT, NPER, CUMIPMT, and CUMPRINC formulas. It includes two amortization tables. The first shows the monthly payments and the second shows the cumulative sum of interest and principal from year to year. Payments are being made monthly, but the CUMIPMT and CUMPRINC functions can be used to calculate the cumulative totals if the interest rate is fixed and the payments are constant (assuming no extra payments are being made).

Screenshot

Example Loan Amortization Formulas in Excel A fixed-rate loan amortization calculator that creates a payment schedule for monthly payments on a simple home mortgage or other loan with a term between 1 and 30 years. (Contains no macros)

Download Now File Type: .xls Size: ~100 KB Required: Microsoft Excel 2000/XP/2003 License: Free (For Personal Use)

Note: This spreadsheet and documentaion on this page are meant for educational purposes only. We believe the calculations to be correct, but do not guarantee the results. Please consult your financial advisor or lending institution before making any final financial decisions.