The basic syntax of IPMT in Google Sheets is as follows:
=IPMT(rate, period, number_of_periods, present_value, [future_value], [type])
Where:
rate: The interest rate for each period.
period: The period for which you want to find the interest and must be in the range 1 to number_of_periods.
number_of_periods: The total number of payment periods.
present_value: The current value of the loan or investment.
future_value (optional): The future value remaining after the final payment has been made.
type (optional): When payments are due. 0 indicates the end of the period, and 1 indicates the beginning.
Suppose you have taken out a home loan of $300,000 at an annual interest rate of 4.5% (0.375% monthly), to be repaid over 20 years (240 months). You want to know the interest portion of your payment for the first month, and then see how the interest payment changes by the 120th month (10 years into the loan).
It's helpful to label your inputs for clarity. For instance, in cell A1, you could write "Annual Interest Rate", in A2, "Loan Term (years)", in A3, "Loan Amount", in A4, "Monthly Interest Rate", and in A5, "Total Payment Periods".
Enter the corresponding values next to your labels: 4.5% (0.045) in B1, 20 in B2, and $300,000 in B3.
Here's what it looks like:
Below those, calculate the monthly interest rate in B4 by dividing the annual rate by 12: =B1/12.
Calculate the total number of payment periods (months) in B5 by multiplying the loan term by 12: =B2*12.
Now, let's calculate the interest payment for the first month. In cell A7, you can label it as "Interest Payment for Month 1".
In cell B7, enter the IPMT formula:
=IPMT(B4, 1, B5, -B3)
Here, B4 is the monthly interest rate, 1 is the current period (month 1), B5 is the total number of payment periods, and -B3 is the negative loan amount (indicating cash outflow).
Label cell A8 as "Interest Payment for Month 120".
Enter the formula in B8:
=IPMT(B4, 120, B5, -B3)
You'll notice that the interest payment decreases over time. This is because, with each payment, a portion goes towards reducing the principal balance, thus reducing the interest calculated on a smaller principal amount.
We hope that this article has helped you and given you a better understanding of how to use IPMT in Google Sheets. If you enjoyed this article, you might also like our articles on how to change the orientation in Google Sheets and how to use the MAP function in Google Sheets.
