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 files to see examples of the formulas in use. 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 payment periods
• 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 functions use similar definitions for the arguments:

• rate - The interest rate per period.
• per, n1, n2 - Specific period (between 1 and nper).
• nper - The number of periods.
• pv - The present value of the loan (i.e. the loan amount)
• pmt - The payment per period.

Example Amortization Spreadsheets

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

Example: Loan Amortization Formulas in Excel

Download

⤓ Download

For: Excel 2007 or later

License: Persona Use Only

Description

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

See Also

• Compound Interest Calculator for Excel
• Auto Loan Amortization Calculator
• Extra Payment Mortgage Calculator
• Home Ownership Expense Calculator
• Balloon Loan Amortization
• Home Equity Loan Calculator

Financial Calculators

Mortgage Calculators

Loan Calculators

Amortization Calculators

Retirement Calculators

Debt Calculators