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