Home   >  Business Tools > 

How to Use Excel Formulas
To Calculate Term Loan Info

by Charley Kyd
November, 2006
(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 amortizing and term loans would work with these facts.

How Standard Amortizing Loans Work

A standard amortizing loan has constant payments over its term. 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 principle.

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)

...where...

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

If 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 principle 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 use a different technique. Each period, you pay the amount of interest due plus a fixed amount for principle reduction. As a consequence, your payments decrease over time.

Here, for example, the amount of the principle 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 principle 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%).
• PrinPmt...the amount of the periodic principle payment (1,666.67).
• 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:

  Principle payment:
    = Loan / LoanPds

  Interest payment at time CalcPds:
    =IntRate*(Loan-(CalcPds-1)*PrinPmt).

  Cumulative principle paid at time CalcPds:
    =Pmt*CalcPds

  Loan balance at time CalcPds:
    =Loan-Pmt*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)
 

Hope this helps.

Charley

Follow this link to download a free copy of the workbook described here.