The IPMT function in Google Sheets is all about calculating the interest payment for an investment or loan for a given period. The ‘I’ in IPMT stands for interest.
Only use the IPMT function to calculate the interest payment, if the loan or investment is based on periodic, that means, constant payments and a constant interest rate. This is very important.
To calculate the payment (both principal and interest payment) for an investment or loan, we can use the function PMT (payment). I have already featured this financial function on this blog.
From that payment (PMT), we can separate the interest payment portion using the IPMT function for any given period in the duration of the payment.
What about the principal payment portion then?
For that, there is another function that I will detail in another tutorial later HERE.
The Syntax of the IPMT Function in Google Sheets
Syntax:
IPMT(rate, period, number_of_periods, present_value, [future_value], [end_or_beginning])rate – This is the annual interest rate.
period – The period within the Nper range for which you want to find the interest.
number of periods (Nper) – The number of payments to be made.
present value (Pv) – The current value of the annuity.
future value (Fv) – Optional and 0 by default. This is the future value/closing balance remaining after the final payment has been made.
end or beginning – Optional and 0 by default. It determines whether payments are due at the end (zero) or beginning (one) of each period.
Calculate the Interest Portion of a Loan Payment in Google Sheets
Here is an IPMT formula example. Before that, I am going to calculate the payment for a home loan using the PMT function.
Sample Data in the Spreadsheet Range A1:B3:
Interest rate (mortgage rate): 4%
Number of periods (mortgage length [years]): 30
Loan amount: $100,000.00
See the formula to calculate the EMI of this housing loan.
=pmt(B1/12,B2*12,B3)The monthly EMI of this payment is -$477.42. This EMI includes both the principal and interest payment.
With IPMT function in Google Sheets, we can separate the interest payment from this payment amount. Before that here is the screenshot of the above formula example.
Here now I am going to calculate the interest portion of the house loan for the first period, I mean in the first installment.
IPMT Function in Google Sheets – Monthly Interest
The formula is almost similar to the PMT. There is only one change in the input value, i.e. the ‘period’ which you want to find the interest.
Sample Data in the Spreadsheet Range A1:B4:
Interest rate (mortgage rate): 4%
Period: 1
Number of periods (mortgage length [years]): 30
Loan amount $100,000.00
Formula:
=ipmt(B1/12,B2,B3*12,B4)This formula returns the amount -$333.33. That means, in the monthly payment of -$477.42, the amount to the tune of $333.33 is the interest!
No secret the interest outgo will be too high in the first period and will be getting lower and lower in the subsequent periods.
Curious to know about the interest of the final installment of this housing loan?
Change the value in B2 from 1 to 360. You know why? The mortgage length is 30 years and the payment is monthly. That means there are 30*12 installments, i.e. equal to 360.
Here to your surprise, you can see that the interest payment is mere $1.59.
In the above example, the housing loan is paid monthly. That’s why I have divided the annual interest rate by 12 and multiplied the mortgage length in years by 12.
Assume you have to pay a different type of loan of the same length on a quarterly or yearly basis. What you will do to find the interest portion then?
IPMT Quarterly Interest Calculation
Here the annual percentage rate must be divided by 4 (not 12) and the number of payments must be multiplied by 4 (not 12).
Use the same sample data that I have used in the above example. Just change the formula as below.
=ipmt(B1/4,B2,B3*4,B4)The interest portion of the first quarterly loan repayment is $1,000.
IPMT in Google Docs Sheets for Yearly Interest Calculation
Here there is no division and multiplication. You can go ahead with the input values present.
=ipmt(B1,B2,B3,B4)
This way you can use the IPMT function in Google Sheets. Thanks for the stay, enjoy!
