IPMT Google Sheets Function

What Is IPMT Google Sheets Function?

The IPMT Google Sheets function is a built-in Financial function. It evaluates the interest due for payment on an investment according to consistent-amount regular payments and a periodic interest rate.

Users can utilize the IPMT formula in Google Sheets to evaluate the interest part of a regular loan payment and compare the interest figures of two different loans. 

For example, assume we received a loan amount of $15,000, which we must pay in monthly installments during the next 5 years with an annual interest rate of 8%.

We must compute the interest payment in the first month for the specific loan, with the payment made at the end of the period. We shall display the result in cell B8.

Then, according to the definition of the IPMT Google Sheets function explained earlier, we can implement the IPMT(), which works like the Excel IPMT function, in the specific cell to acquire the required outcome.

The IPMT() accepts four mandatory argument values. The first argument, rate, is the reference to cell B2 holding the yearly interest rate, which we divide by 12, as we must convert the yearly interest rate to the monthly interest rate.

Next, the second argument, period, value is 1. It is the period for which we aim to determine the interest paid. The third argument, number_of_periods, value is the reference to the cell B4 holding the years of the specific loan, which we multiply by 12, as we must convert years to months. Finally, the last argument, present_value, value is the reference to the cell B5 holding the loan’s present value of $15,000. 

So, based on the furnished inputs, the IPMT Google Sheets function returns the value of -$100 as the required output. The reason for the function return value to be negative is that we must pay this amount. In other words, the interest due for payment is an outgoing amount.

IPMT() Google Sheets Formula

The IPMT formula in Google Sheets syntax is the following:

Where,

• rate: The interest rate.
• period: The amortization period. It is the number of periods, with the value being between 1 to number_of_periods, inclusive.
• number_of_periods: The count of payments we must make.
• present_value: The annuity’s present value.
• future_value: The future amount outstanding after we make the last payment.
• end_or_beginning: Whether the payments are pending at the finish (0) or start (1) of each duration, with 0 being the default value.

Furthermore, please provide the first four arguments when using IPMT Google Sheets function, as they are mandatory. However, the last two argument values are optional.

Also, to secure the correct interest part of a loan settlement, we must change the yearly interest rate to the corresponding period’s rate and the count of years to the total count of payment durations:

• In the case of the rate argument, we must divide the yearly interest rate by the count of payments per annum, considering the latter equals the count of compounding durations per annum.
• In the case of the number_of_periods argument, we must multiply the count of years by the count of payments each year.

The table below displays the computation:

How To Use IPMT Google Sheets Function?

We can implement the IPMT formula in Google Sheets in the following two ways:

1. Access the function from the ribbon.
2. Enter the function into the sheet manually.

Method #1 – Access The Function From The Ribbon

Choose a target cell for showcasing the output value – The Insert tab – The Function option right arrow – The Financial function group right arrow – The IPMT function.

The selected function will get inserted into the cited cell, with the cursor within the function brackets. We can now supply the IPMT Google Sheets function arguments inside the brackets.

Furthermore, we can choose the ‘?’ symbol adjacent to the function name to know its syntax.

Next, choose the down arrow in the syntax window to know the meaning of the function IPMT Google Sheets function explained with an illustration.

Finally, once we supply the IPMT Google Sheets function arguments, separated by commas, press Enter to secure the function output value.

Method #2 – Enter The Function Into The Sheet Manually

1. Choose the cell where we wish to display the result.
2. Type =IPMT( in the cell. [ Alternatively, type =I or =IP and click the function name IPMT from the listed suggestions to select the function.]
3. Enter the argument values, separated by commas, and close the brackets.
4. Press Enter to secure the value the IPMT Google Sheets function returns.

Examples

Let us see the effective ways of using IPMT Google Sheets function with illustrations.

Example #1

The loan amount is $25,000, which we must pay in semi-annual installments over the next 10 years, with the yearly interest rate being 10%.

We must determine the interest payment in the first semi-annual period for the specific loan, with the payment made at the end of the period. Let us showcase the outcome in cell B8.

Then, we can utilize the IPMT() in the cited cell to secure the required outcome.

Step 1: Select cell B8, enter the IPMT(), and press Enter.

=IPMT(B2/B6,B3,B4*B6,B5)

[Alternatively, select the required cell and then Insert – Function – Financial – IPMT function.

The above step inserts the chosen function in the specific cell.

We can now supply the function argument values and close the bracket to complete the formula.

Finally, pressing Enter will execute the IPMT().]

The IPMT function takes four mandatory argument values as input. The first argument, rate, is the reference to cell B2 holding the yearly interest rate, which we divide by 2, as we must convert the yearly interest rate to a semi-annual interest rate.

Next, the second argument, period, value is 1. It indicates the period for which we want to compute the interest paid. The third argument, number_of_periods, value is the reference to cell B4 containing the years of the concerned loan, which we multiply by 2, as we must convert the years to semi-annual periods. Finally, the last argument, present_value, value is the reference to the cell B5 holding the loan’s current value of $25,000. 

So, based on the inputs, the IPMT function returns the value of -$1,250 as the interest due in the first semi-annual loan repayment period.

Example #2

The loan amount is $200,000, which we must pay in monthly installments over the next 1 year at an annual interest rate of 5%.

The task is to compute the interest payment in each of the 12 months during the concerned loan period, with the payments made at the end of the periods. We shall take cells E2:E13 as the target range.

Then, applying the IPMT() in the specified cells will fetch us the required outcome.

Step 1: Select cell E2, enter the IPMT(), and press Enter.

=IPMT($B$2/$B$5,D2,$B$3*$B$5,$B$4)

Step 2: Utilizing the fill handle, feed the formula into the other target cells.

In this case, all the argument values in the IPMT() remain the same in all the target cells except the second argument, period. It keeps changing according to the corresponding column D period values.

So, we use the absolute reference, similar to Excel absolute reference, method when referencing the cells and ranges to supply the first, third, and fourth arguments. It makes copying the formulas in the other target cells more straightforward.

So, since we must pay the loan installments on a monthly basis, we divide the yearly interest rate by 12 and multiply the years of the loan by 12. These conversions help us achieve the accurate interest parts of the loan settlement.

Example #3

The loan amount is $100,000, which we are obliged to settle in quarterly installments over 12 years, with the yearly interest rate being 9%.

We must determine the interest payment in the first quarterly period for the concerned loan, with the payment made at the end of the period. However, the result must appear as a positive value. Consider cell B8 as the cell to display the output.

Then, here is how we can tweak the IPMT() formula and apply it in the cited cell to secure the required result.

Step 1: Choose cell B8, enter the IPMT() with a ‘–‘ sign preceding the function name, and press Enter.

=-IPMT(B2/B6,B3,B4*B6,B5)

Or, select cell B8, enter the following IPMT() with the supplied present_value argument value being negative, and press Enter.

=IPMT(B2/B6,B3,B4*B6,-B5)

Important Things To Note

• The IPMT Google Sheets function output is a negative value since an amount paid out (such as a loan repayment) will be negative.
• The units we utilize for supplying the rate and number_of_periods arguments to the IPMT formula in Google Sheets should be correct. For instance, assume the payments are monthly on a 5-year loan at 10% yearly interest. Then, we must use 10%/12 as the rate argument value and 5*12 as the number_of_periods argument value.
• The IPMT formula in Google Sheets returns the #NUM! error when the supplied period argument value is below zero or exceeds the cited number_of_periods argument value.
• The function IPMT in Google Sheets returns the #VALUE! error when any of the supplied argument values are non-numerical.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the IPMT Google Sheets Function, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to What Is IPMT Google Sheets Function. Here we learn how to use IPMT function in Google Sheets with examples and points to remember. You can learn more from the following articles –

• INDEX MATCH Function In Google Sheets
• Standard Deviation in Google Sheets
• SQRT Google Sheets Function