For business users of Microsoft Excel Free guides and templates

Time-Value-of-Money Formulas

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

IPMT and ISPMT probably are Excel's most-confusing time-value-of-money 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, 2005-2014
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 even-payment 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 "straight-line." The ISPMT function returns the interest payment for a given period for this type of loan. (Because the "S" probably stands for straight-line, 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 even-payment method, where each amount in the Pmt column is identical. And the second table uses a straight-line method, where each amount in the Prin column is identical.

two amortization tables

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 straight-line loan. Notice that with the straight-line loan, total payments decline every period.

Even payment and straight-line loan charts

As I'll show you in a minute, you use the IPMT function for even-payment loans, and the ISPMT function for straight-line 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...

Even-Payment Loans and the IPMT Function

This figure shows the complete setup to illustrate the even-payment method...

Even-payment loan amortization

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 non-TVM (time value of money) function, Excel doesn't assign a number format to the cell.

The Even-Payment 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:   =C9-D9

The principal portion of the payment is equal to the payment minus the interest portion.

F9:   =F8-E9

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

Even-payment loan amortization

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 even-payment loan.

Straight-Line Loans and the ISPMT Function

This figure shows the complete setup to illustrate the straight-line method...

Even principal loan examples

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:   =F8-C9

The new loan balance is equal to the prior loan balance less the current period's even-principal amount.

And, again, copy row 9 of the table down the columns as needed.

The ISPMT Function Examples

Here's the figure again:

Even principal loan examples

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 straight-line 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, #straight-line mortgages, #even-payment loan, #even-principal loan, #time value of money, #TVM

Excel’s Five Annuity Functions

Find Future and Present Values from Scheduled Cash Flows in Excel

Free Excel Dashboards

Charley's SwipeFile charts