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.
|"How do I calculate cumulative principle 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 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
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
6%/12 = .5% in this example.
nper...The number of periods. In this example, we have 60
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).
you were to set up an amortization schedule in Excel, the first
and last few periods of your loan would look like the figure
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
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.
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
Excel doesn't provide worksheet functions to support
term-loan calculations. Therefore, we must use spreadsheet
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
- Loan...the amount of the loan (100,000).
- IntRate...the periodic interest rate (.5%).
- PrinPmt...the amount of the periodic principle
- 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 / LoanPds
Interest payment at time CalcPds:
Cumulative principle 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
To illustrate, the amounts for the first three interest
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)
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) *
Hope this helps.
this link to download a free copy of the workbook described