## What Is IPMT Google Sheets Function?

The

IPMTGoogle Sheets function is a built-inFinancialfunction. 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

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

##### Table of contents

###### Key Takeaways

- The
**IPMT**Google Sheets function evaluates the interest payment for a loan or an investment for a specified duration. However, we must use the**IPMT**function when the loan or investment is regular, implying fixed payments and a fixed interest rate. - The function
**IPMT**in Google Sheets is useful in evaluating the changes in interest payments over a specified duration for an amortizing loan. The function also helps in computing the tax-deductible interest for a cited payment duration on a loan. - The function
**IPMT**in Google Sheets takes six arguments:**rate**,**period**,**number_of_periods**,**present_value**,**future_value**, and**end_or_beginning**, as input. While the first four arguments are compulsory, the remaining two are optional. And if we ignore the last input value in the**IPMT**function, the function takes its default value of**0**for the interest payment computations.

### 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:

**Access the function from the ribbon.****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

- Choose the cell where we wish to display the result.
- Type
**=IPMT(**in the cell. [ Alternatively, type**=I**or**=IP**and click the function name**IPMT**from the listed suggestions to select the function.] - Enter the argument values, separated by commas, and close the brackets.
- 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**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)

**1. How to use IPMT in Google Docs Sheets for yearly interest calculation?**

We can use **IPMT** in Google Docs Sheets for yearly interest calculation, as shown below with an illustration.

The loan amount is $150,000, which we are required to settle in annual installments over 15 years, with the yearly interest rate being 7%.

We must determine the interest payment in the first annual period for the concerned loan, with the payment made at the end of the period. We shall use cell B8 to show the result.

Then, we can implement the **IPMT()** in the specific cell to fetch the required result.**Step 1: **Choose cell B8, enter the **IPMT()**, and press **Enter**.*=IPMT(B2,B3,B4,B5)*

**2. Why is the IPMT Google Sheets function not working?**

The **IPMT **Google Sheets function is not working** **due to the following reasons:

The **period **argument value is outside the **1 **–** number_of_periods **range, leading to the function returning the **#NUM!** error value.

The supplies argument values are non-numeric, leading to the function returning the **#VALUE!** error value.

**3. What is the difference between IPMT vs. PMT in Google Sheets?**

The difference between **IPMT** vs. **PMT** in Google Sheets is that the **PMT()** computes the regular payment on the concerned loan. For instance, the monthly loan payments a debtor owes. In comparison, the **IPMT** function evaluates only the interest amount payable.

### 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 –

## Leave a Reply