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.

Advertisement

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

Excel Simple Amortization Sheet
Screenshot

Example: Loan Amortization Formulas in Excel

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.

No Installation, No Macros - Just a simple spreadsheet

License: Personal Use Only

See Also

References

Disclaimer: This article is meant for educational purposes only. You may want to consult with a qualified professional regarding financial decisions.

Cite This Article

To reference this article from your website or blog, please use something similar to the following citation:

- Wittwer, J.W., "Excel Amortization Formulas," From Vertex42.com, March 26, 2005.

Like This Page?

Become a Fan

       
Master Excel - Spreadsheet Tips Workbook