TimeValueofMoney Formulas
Help with
Excel's Most Confusing TimeValueofMoney Functions
IPMT and ISPMT probably are Excel's mostconfusing timevalueofmoney functions, because their differences haven't been clear. Here's what you need to know
about these useful functions.
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

(Download the workbook.)
According to Excel help topics, the PMT and ISPMT functions both return the interest paid during a specific period of
an investment. But in the past, those help topics haven't explained
the difference between these functions.
And also, the help topics have discussed investments, even
though both functions are used more frequently with loans.
The Excel help topics probably will improve soon, or they might
already have improved. But just to be sure, I thought I'd better
explain the difference between these two useful functions.
Two Types of TVM Functions for Two Types of Loans
Most mortgages, car loans, and other amortizing loans in the US use a repayment schedule with even,
periodic payments. This type of loan is called an
evenpayment loan. Or, because an annuity in this context is a series of equal payments at regular intervals,
this type of loan for the purchase of homes often is called an annunity mortgage.
The IPMT function returns the interest
payment for a given period for this type of loan.
On the other hand, some loans use a repayment schedule with even principal
payments. This type of loan is variously called "even principal",
"level debt service", or
"straightline." The ISPMT function returns the interest payment for a
given period for this type of loan. (Because the "S"
probably stands for straightline, that's what I've started to call this type
of loan.)
To illustrate these two repayment methods, the left amortization table below uses an
evenpayment method, where each amount in the Pmt column
is identical. And the second table uses a straightline
method, where each amount in the Prin column is identical.
The stacked area charts below plot the interest and principal
for each type of loan. The straight horizontal line shown for the principal
repayments in the second chart illustrates the reason this is
called a straightline loan. Notice that with the straightline
loan, total payments decline every period.
As I'll show you in a minute, you use the IPMT function for
evenpayment loans, and the ISPMT function for straightline
loans.
So now, I'll borrow from my upcoming training about TVM
functions and explain how to create both amortization tables, and how to use
both functions...
EvenPayment Loans and the IPMT Function
This figure shows the complete setup to illustrate the evenpayment
method...
To create this figure, first create the Settings and
Side Calculation areas. Then use the Create Names command to
assign four range names.
To assign the first three names, select the range B2:C4; choose
Formulas, Defined Names, Create From Selection (or press
Ctrl+Shift+F3) to launch the Create Names dialog; make sure that
only Left Column is checked; then choose OK.
Then follow the same method to assign the name Pmt to cell
F2.
The yellow area contains the values shown. The Pmt cell
contains this formula:
F2: =ABS(PMT(Rate,Nper,PV))
The PMT function returns the periodic payment determined by the
settings in the yellow area. The ABS function does two things.
First, it returns the absolute value of the payment returned by
the PMT function. Second, because the PMT function is surrounded
by a nonTVM (time value of money) function, Excel doesn't
assign a number format to
the cell.
The EvenPayment Amortization Table
To create the amortization table, set up the labels and
general structure, with the Pd (period) numbers as shown.
Then enter these formulas:
F8: =PV
This formula merely returns the setting from the yellow area.
C9: =Pmt
And this formula returns the payment calculation from above.
D9: =Rate*F8
This formula finds the current period's interest amount by
multiplying the periodic rate by the ending loan balance for the
previous period.
E9: =C9D9
The principal portion of the payment is equal to the payment
minus the interest portion.
F9: =F8E9
The ending loan balance for the period is equal to the prior
loan balance minus the principal portion for the period.
Finally, to complete the table, copy the range C9:F9 down
the columns as needed.
The IPMT Function Examples
Here's the previous figure again...
The Calc column of the IPMT Examples table contains the four
possible questions that the IPMT function can answer about the
loan shown here. And the Formula Text column shows all four of
the formulas in the Calc column.
Compare the contents of the Int column in the left table with
the contents of the Calc column in the right table. With the
exception of the signs of the numbers, and their number format,
the results are the same. That is, the IPMT function returns the
interest portion of the periodic payment for any specified period during
the course of an evenpayment loan.
StraightLine Loans and the ISPMT Function
This figure shows the complete setup to illustrate the straightline method...
To create this figure, first set up and name the three
settings shown. (There's no need for a payment calculation.)
Then set up the general structure of the amortization table.
Here are the key formulas for the table:
F8: =PV
C9: =PV/Nper
The even principal is equal to the amount of the loan divided by the number of periods.
D9: =Rate*F8
The interest amount for the period is equal to the periodic
interest rate.times the prior ending balance.
E9: =C9+D9
The payment for the period equals the sum of the even principal
and the current
period's interest amount.
F9: =F8C9
The new loan balance is equal to the prior loan balance less
the current period's evenprincipal amount.
And, again, copy row 9 of the table down the columns as
needed.
The ISPMT Function Examples
Here's the figure again:
The Calc column of the ISPMT Examples table contains the questions that the ISPMT
function can answer about the loan shown here. As you can see,
the ISPMT function in the Calc column successfully returns the
interest amount for each period of the straightline
loan...again, ignoring the sign and the number format.
However, notice one big difference between the IPMT and ISPMT
functions. The second argument of both functions is named
Per, for Period. But the IPMT function counts
its periods beginning with 1 (one) and the ISPMT function counts
its periods beginning with 0 (zero). Keep that difference in
mind when you use either function.
Summing Up
I hope this article ends the confusion between the IPMT and
ISPMT functions. Excel has given us a function for two different
types of loans. Once the two types of loans are clear, the
functions should be clear, as well.
You can
follow
this link to download a free copy of the workbook described
here.
Also, 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.
Tags: #excel, #ISPMT, #IPMT, #amortization, #loans, #straightline mortgages,
#evenpayment loan, #evenprincipal loan, #time value of money,
#TVM
