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.
List of Excel Amortization Formulas
Excel's help file does a good job of explaining the following functions, but the spreadsheet examples will demonstrate how some of these formulas might be used.
- ISPMT(rate,per,nper,pv) - The amount of interest paid during a specific period.
- PMT(rate,nper,pv) - The amount of the periodic payment
- NPER(rate,pmt,pv) - The number of payments
- *CUMIPMT(rate,nper,pv,n1,n2,0) - Cumulative interest payment for the periods n1 through n2
- *CUMPRINC(rate,nper,pv,n1,n2,0) - Cumulative principal payment for the periods n1 through n2
- *EFFECT(nominal_rate,compounding_periods_per_year) - Calculates the effective annual interest rate. See the Excel help file on this function.
* 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".
- rate - The interest rate per period.
- per, n1, n2 - Specific period (between 1 and nper).
- nper - The number of payment periods.
- pv - The present value of the loan (i.e. the loan amount)
- pmt - The payment per period.
Example Amortization Spreadsheets
In Excel, I generally do not like to use built-in financial formulas unless I understand how they work. For amortization formulas, I think the best way to understand the equations is to create a loan amortization schedule or table 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 and chart. In this worksheet, the only special Excel formula that is used is the PMT function to determine the monthly payment.
Download the Simple Amortization Spreadsheet
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 ExcelThis spreadsheet is 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. Download Now (.xlsx) Excel 2003 Version (.xls) No Installation, No Macros - Just a simple spreadsheet License: Personal Use Only |
See Also
- Auto Loan Amortization Calculator
- Extra Payment Mortgage Calculator
- Home Ownership Expense Calculator
- Balloon Loan Amortization
- Home Equity Loan Calculator
References
- Amortization Calculation at Vertex42.com
- Definition of Amortization at Answers.com