For business users of Microsoft Excel Free guides and templates

Excel's Financial Functions

How to Use Excel Formulas to Calculate a Term-Loan Amortization Schedule

Term loans use a different amortizing method than traditional amortizing loans. Here's how to calculate amortization schedules for both term loans and traditional amortizing loans.

by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports
"How do I calculate cumulative principal and interest for term loans? I have scoured the web for a function that will perform this task, with no avail. "
-- Lake M.

(Download the workbook.)

This is an interesting question. It touches on standard amortizing loans, and it even involves a bright young student who grew up to become a well-known mathematician.

To answer the question, I'll use a simple example. Suppose you were to borrow $100,000 for five years at 6% interest, with monthly payments. Let's see how standard amortizing loans and term loans would work with these facts.

How Standard Amortizing Loans Work

A standard amortizing loan--also called an even-payment loan--has constant payments over its life. With this approach, a large percentage of your monthly payment is applied to interest in the early years of the loan. But in the later years, as the loan balance slowly declines, more and more of each month's payment is applied to the principal.

In Excel, you use the PMT function to calculate the periodic payment for a standard amortizing loan. It has the form:

=PMT(rate, nper, pv)


  rate...The periodic rate. With monthly payments, the rate would be:
6%/12 = .5% in this example.

  nper...The number of periods. In this example, we have 60 monthly periods.

  pv...The present value, which is the original loan amount, or $100,000 in this example.

That is, your formula would be: =PMT(0.005,60,100000).

Amortization Table for an Amortizing LoanIf you were to set up an amortization schedule in Excel, the first and last few periods of your loan would look like the figure shown here.

Again, notice that the principal payment increases each period as the amount of the interest declines.

Excel provides a number of worksheet functions for working with amortizing loans:

  PMT. Calculates the payment for a loan based on constant payments and a constant interest rate.

  FV. Returns the future value of an investment based on periodic, constant payments and a constant interest rate.

  IPMT. Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.

  NPER. Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.

  RATE. Returns the interest rate per period of an annuity.

  CUMIPMT. Returns the cumulative interest paid on a loan between start_period and end_period. (Analysis ToolPak)

  CUMPRINC. Returns the cumulative principal paid on a loan between start_period and end_period. (Analysis ToolPak)

How Term Loans Work

Term loans--also known as straight-line or even principal loans--use a different technique. Each period, you pay the amount of interest due plus a fixed amount for principal reduction. As a consequence, your payments decrease over time.

Amortization table for a straight-line loanHere, for example, the amount of the principal paid each period is equal to $100,000 divided by 60, or $1,666.67.

Also notice that the total payment decreases each month as the amount of interest decreases while the principal stays the same.

Excel doesn't provide worksheet functions to support term-loan calculations. Therefore, we must use spreadsheet formulas.

Calculating Term Loan Values

With one exception, it's quite easy to calculate the values for a term loan. To illustrate, I'll use the following abbreviations. In parentheses I show the values from the example above.

  • Loan...the amount of the loan (100,000).
  • IntRate...the periodic interest rate (.5% per month).
  • PrinPmt...the amount of the periodic principal payment (1,666.67 per month).
  • LoanPds...the total number of loan payments (60).
  • CalcPds...the number of loan payments that we choose to calculate from the beginning of a loan. In the above example, this number could range from 1 to 60.

Using these abbreviations, here are the formulas for a term loan:

  Principal payment:
    = Loan / LoanPds

  Interest payment at time CalcPds:

  Cumulative principal paid at time CalcPds:

  Loan balance at time CalcPds:

  Cumulative interest paid at time CalcPds:
    =IntRate*(CalcPds*Loan - ((CalcPds-1)*((CalcPds-1)+1)/2)*PrinPmt)

Until the final formula above, the term-loan calculations were quite easy. Let's conclude this article by examining how this final formula was derived.

Calculating Total Interest Paid for a Term Loan

When you work with periodic cash flows, and you want to derive a general formula for this purpose, it often helps to show how each periodic amount is calculated. Then you look for a pattern.

To illustrate, the amounts for the first three interest payments are:

IntRate * (Loan - 0 * Pmt)
IntRate * (Loan - 1 * Pmt)
IntRate * (Loan - 2 * Pmt)

To calculate the total of these three interest payments, we simply combine the terms, like this:

= IntRate * (3 * Loan - (0 + 1 + 2) * Pmt)

= .005 * (3 * 100,000 - 3 * 1,666.67)

= 1,475

You can check this calculation by adding up the interest amounts for the first three payments in the Term Loan Amortization table above.

To create a general formula to calculate the cumulative interest rate, we first must find a way to calculate the sum of an arithmetic series like this:
0 + 1 + 2 + 3 + 4 ...

The story is that the mathematician Carl Gauss (1777 – 1855) derived the formula when he was a young student. His class was asked to add up the numbers 1 through 100. The other students laboriously added 1 + 2 + 3 and so on. But Gauss took a shortcut. He noticed that:

  • 1 + 100 =  101
  • 2 + 99 = 101
  • 3 + 98 = 101
  • and so on.

This pattern happens 50 times, so the total of all 100 numbers must be 50 times 101, or 5050.

After some more work, Gauss derived a general formula for the sum of any such series:
n * (n + 1) / 2. 

That is, 100 * 101 / 2 = 5050.

So, with the help of a young student, we can find the cumulative interest for a term loan. After the number of months specified by CalcPds, the total interest paid is:

=IntRate*(CalcPds*Loan - ((CalcPds-1)*((CalcPds-1)+1)/2) * PrinPmt)

Take Your Next Steps

First, you can follow this link to download a free copy of the workbook described here.

Second, if you're looking for additional help with this topic, I can help you in three ways. To learn more, see Excel Training, Coaching, and Consulting.

Help with Excel's Most Confusing Time-Value-of-Money Functions

Excel’s Five Annuity Functions

Find Future and Present Values from Scheduled Cash Flows in Excel

Free Excel Dashboards

Charley's SwipeFile charts