In this article:

IPMT in Google Sheets: Explained (How to Use It in 2024)

May 8, 2024

IPMT Syntax

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.

How to Use IPMT in Google Sheets

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

Step 1: Set Up Your Spreadsheet

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:

ipmt google sheets

Below those, calculate the monthly interest rate in B4 by dividing the annual rate by 12: =B1/12.

 

google sheets ipmt

Calculate the total number of payment periods (months) in B5 by multiplying the loan term by 12: =B2*12.

Step 2: Use the IPMT Function for the First Month

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

Step 3: Calculate Interest Payment for Month 120

Label cell A8 as "Interest Payment for Month 120".

Enter the formula in B8: 

=IPMT(B4, 120, B5, -B3)

Step 4: Analyze the Results

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.

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->