When you borrow money for a fixed period with periodic payments, you could have two types of loans: even-payment or straight-line.
Microsoft recognizes those loan types by giving us the Excel functions ISPMT and IPMT, which tell us the amount of interest we’ll pay with each type of loan on a specific period. But unfortunately, the two help topics for these functions don’t make it clear which type of loan each function supports.
In this article, I’m going to explain both types of loans, show you how to create amortization tables for them, and then use those tables to illustrate the ISPMT and IPMT functions in action.
The second article in this series, How to Use Excel Formulas to Calculate Cumulative Term-Loan Interest Payments, uses the same amortization tables to explain how to calculate cumulative principal and interest for both even-payment and straight-line loans.
Two Types of Time-Value-of-Money (TVM) Functions for Two Types of Loans
Most mortgages, car loans, and other amortizing loans in the US use a repayment schedule with identical 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 real estate often is called an annuity 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’ll call this type of loan.)
To illustrate these two repayment methods, the left amortization table below uses the even-payment method, where each amount in the payment (Pmt) column is identical. And the second table uses the straight-line method, where each amount in the principal (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 straight-line loan. Notice that with the straight-line loan, total payments decline every period.
Now let’s see how to create both amortization tables, and how to use both functions…
Even-Payment Loans and Excel’s IPMT Function
This figure shows the complete setup to create an even-payment amortization table…
To create this figure, first create the Settings and Side Calculation areas. The yellow area contains the values shown.
Then, to assign the first four range names to the cells in the yellow area, select the range B2:C5; in the Formulas, Defined Names group choose 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 name the two Side Calculations cells.
The periodic rate (PdRate) cell contains this formula:
F2: =Rate/PdsInYr
That is, the periodic rate equals the annual rate divided by the number of payment periods in a year.
The payment (Pmt) cell contains this formula:
F3: =ABS(PMT(PdRate,Nper,PV))
The PMT function returns the periodic payment determined by the settings in the yellow area above. The ABS function does two things. First, it returns the absolute value of the payment returned by the PMT function. This is useful, because Excel automatically shows cash inflows as positive numbers and cash outflows as negative numbers. But here, we just want to know the number.
Second, because the PMT function is surrounded by a non-TVM (time value of money) function, Excel doesn’t automatically assign the currency number format to the cell. This is useful because Excel users typically want to manage our own number formatting.
Create the Even-Payment Amortization Table in Excel
So that you can follow along, you’ll find the same amortization table below. Here are its formulas for the cells shown:
F9: =PV
This formula merely returns the setting from the yellow area.
C10: =Pmt
And this formula returns the payment calculation from above.
D10: =PdRate*F9
This formula finds the current period’s interest amount by multiplying the ending loan balance for the previous period by the periodic rate.
E10: =C10-D10
The principal portion of the payment is equal to the payment minus the interest portion.
F10: =F9-E10
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 C10:F10 down their columns as needed.
Excel’s IPMT Function
Excel’s IPMT function returns the interest payment for any period. It uses this syntax…
=IPMT(rate, per, nper, pv, fv, type)
…where:
- rate is the periodic (not the annual) interest rate.
- per is the period for which you want to calculate the interest amount.
- nper is the number of periods for the loan.
- pv is the original loan amount.
- fv, which is optional, is the future cash balance you must reach after the last payment is made. If fv is omitted, it is assumed to be 0, that is, you pay the loan in full.
- type, which is optional, is the number 0 of payments are due at the end of the period, and 1 if payments are due at the beginning of the period. If type is omitted, it is assumed to be 0.
Here’s the previous figure again…
The Calc column of the IPMT Examples table shows the IPMT function in action. And the Formula Text column shows the specific formulas for all four of those Calc cells.
As you can see, the Int column in the left table matches the Calc column’s result in the right table. 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 Excel’s ISPMT Function
This figure shows the straight-line method’s amortization table.
In the Side Calculations section, we still have two cells:
F2: =Rate/PdsInYr
The PdRate formula is the same as in the even-payment version.
F3: =PV/Nper
Here, the periodic principal payment is equal to the total amount of the loan divided by the number of payment periods.
And in the amortization table…
F10: =PV
This is the same as before. The PV is the initial amount of the loan.
C10: =PdPrin
In straight-line loans, the principal payment each period always equals the calculated PdPrin value.
D10: =PdRate*F9
The interest amount for the period is equal to the periodic interest rate times the prior period’s ending balance.
E10: =C10+D10
The payment for the period equals the sum of the periodic principal payment and the current period’s interest amount.
F10: =F9-C10
The new loan balance is equal to the prior loan balance less the current periodic principal payment.
And, again, copy row 10 of the table down the columns as needed.
Excel’s ISPMT Function
Excel’s ISPMT function returns the amount of the interest payment at a specific period. It uses this syntax…
=ISPMT(rate, per, nper, pv)
…where…
- rate is the periodic (not the annual) interest rate.
- per is the period for which you want to calculate the interest amount.
- nper is the number of periods for the loan.
- pv is the original loan amount.
Here’s the ISPMT figure again:
The Calc column of the ISPMT Examples table contains the formulas displayed in the Formula Text column. As you can see, the ISPMT function in the Calc column successfully returns the interest amount for each period of the straight-line loan.
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, as you can see here in the Pd column, 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.
