To calculate interest payment of a loan there are two financial functions in Google Sheets. They are IPMT and ISPMT. I have already explained how to use IPMT in Sheets. In this tutorial, you can learn how to use the ISPMT function in Google Sheets. Also, there is an IPMT Vs ISPMT comparison.
From my understanding, IPMT is probably the short form of interest payment. But I don’t have any clue on the letter ‘S’ in ISPMT. Let’s discuss more on these two functions in the comparison section after a few paras below.
Syntax of the ISPMT Function in Google Sheets:
ISPMT(rate, period, number_of_periods, present_value)Arguments:
rate – The interest rate of the loan/investment.
period – The period between 1 to number_of_periods (Nper) for which you want to calculate the interest payment.
number_of_periods (Nper)- The total number of payments to be made.
present_value (Pv)- The present value of the annuity/investment. For a loan, it will be the loan amount.
Usage Notes:
It’s important to use consistent units for the rate and number_of_periods. Let’s take an example of a 5-year car loan at 10% annual interest rate. If you make monthly payments, the rates should be 10%/12 and the Nper will be 60 (5*12).
The ISPMT function counts each period beginning with zero, not with one. This may contradict with the second argument, which is the period, in ISPMT. To clear your doubt, see the ISPMT formula for period 1. See how I have used the second argument.
=ISPMT(10%,1-1,5,20000)The below formula examples to Google Sheets ISPMT function will give you an even more clear picture.
Example to ISPMT Function in Google Sheets
Assume I have taken a car loan @10% annual interest rate. The loan amount is $20,000.00 and the duration is 5 years.
I will repay the loan amount on a monthly basis. Here are the input values for the ISPMT formula.
Example # 1: Interest Amount in Any Month Within Nper 1 – 60
| A | B | C | |
| 1 | Present Value (Loan Amt) | $20,000.00 | |
| 2 | Rate | 0.83% | =10%/12 |
| 3 | Number of Periods | 60 | =5*12 |
Note: Please just ignore the values in column C. It just shows the underlying formula in corresponding cells in column B.
The Formula in Cell F2 as per screenshot below:
=ispmt(B2,E2-1,B3,B1)See the interest amount changes when I change the period in cell E2.
Example # 2: Interest Amount in Any Year Within Nper 1 – 5
| A | B | C | |
| 1 | Present Value (Loan Amt) | $20,000.00 | |
| 2 | Rate | 10% | Annual Interest Rate |
| 3 | Number of Periods | 5 | Nper In years |
Here also the ISPMT formula will be almost the same. The only change in the formula is the reference cell of the Period argument. Earlier it was cell E2, here it is cell D3.
=-ISPMT($B$2,D3-1,$B$3,$B$1)The formula would return the interest amount as -$2,000.00 for the first period. See the below payment schedule.
The ISPMT formula is in cell F3 which copied down to change the period and to get the ISPMT of each period.
You can see that the principal payment in the range E3:E7 is a fixed amount in each year, i.e. equal to loan amount / Nper. The straight horizontal line on the chart displays the same.
No doubt, to get the yearly loan payment in column G I have added the principal payment with the interest payment. The formula in cell G2 is =E3+F3, which copied down.
From the above schedule and chart, you can understand that the principal payments are even. That means use the ISPMT function in Google Sheets if your loan repayment schedule involves even principal payments.
Learn the Difference Between Google Sheets IPMT and ISPMT Functions
I have already explained how to use the Google Sheets ISPMT function. Pay your attention to the last sentence in the paragraph above. What does it mean?
It means you can use the ISPMT function if principal payments in your loan repayment schedule are even.
Then what about IPMT? First, see the loan repayment schedule and the chart below.
Use IPMT function to calculate interest if payments (periodic payments) in your loan repayment schedule is even. Most loans use a repayment schedule with even payments (periodic payments).
- ISPMT – In even principal payments.
- PMT – In even (periodic) payments.
See the column G. You can see that the periodic payments are even in that column not the principal payments in column E.
Tip: In these types of loans (fixed periodic payment) to calculate principal payment use the function PPMT.
Hope you could understand the difference between the functions ISPMT and IPMT in Google Sheets.
That’s all about ISPMT financial function. Enjoy!
