Excel IPMT Function

While applying for a loan, whether it's a mortgage, debt, home loan, education loan, or furniture loan, you need to pay back the borrowed money along with the additional applied interest. In other words, interest is the charge of leading someone's (usually a bank's) money.

The interest rate of any loan can be easily calculated by multiplying the interest rate by the balance. But to ease the process, Excel has developed a built-in function for this - the IPMT function.

This tutorial will cover the definition the IPMT function, the method to calculate the number of periods to return the loan, or the number of investments needed to reach the target amount, and many more!

What is IPMT Function?

The IPMT Function in a built-in Excel function that helps to calculate the interest payment. This function returns the interest amount of loan payment for a specified period, considering that the rate of interest and the total payment are constant in all periods.

Syntax

Parameters

1. Rate (required) - This argument represents the rate of interest per period. If you want to make yearly payments on a loan with a yearly interest rate of 10 percent, use 10% or 0.10 for rate.
2. Per (required) - This argument signifies the period for which you want to calculate the interest. It must be an integer in the range from 1 to nper.
3. Nper (required) - It signifies the total number of payment periods for the length of an annuity.
4. PV (required) - This parameter represents the present value, or total value of all payments now.
5. fv [optional]: This parameter represents the future value, or a cash balance the user want after the last payment is made. Since it is an optional parameter therefore its default value is 0.
6. type [optional]: This parameter specifies the info concerning when payments are due. Since it is an optional parameter therefore its default value is 0.
   
   • If the user supplies value 0 it represents the end of period
   • If the user gives value 1, it represents beginning of period.

Return

The Excel IPMT function returns the interest amount of a loan payment in a given period, assuming the interest rate and the total amount of a payment are constant in all periods.

Points to Remember

There could be situations where you are not getting accurate output, or the output is far beyond your expectations. This could happen if you forgot to convert the annual period interest rate and the number of years to the total number of payment periods. Follow the below points to get a reliable output:

• While supplying the rate parameter, ensure to divide the annual rate of interest by the number of payments per year, considering the payments per year are equivalent to the number of compounding periods per year.
• In many cases, the loan value (pv) is provided as a negative value.

Example 1: Calculate the interest payment for a $7,000 investment if it's given that the interest rate is 7.2%, where the IPMT is calculated for the 6th month and payments that are due at the end of each month.

Considering the above details, we will have the following data values:

To calculate the Monthly IPMT value follow the below given steps:

Step 1: Insert the helper row

We will add the helper row named 'Monthly Payment' in cell C12. This row will use the IPMT function to calculate the monthly payment using the given Interest rate, period, NPER, PV, FV, and type value.

Refer to the given below image:

Step 2: Enter the IPMT formula

• IPMT is an inbuilt function that helps to calculate the interest amount of a loan payment.
• In the "Monthly Payment" helper column, start the formula with the equal sign (=) and type the IPMT function. So our formula becomes: = IPMT (

It will look similar to the below image:

STEP 3: Add the parameter to your formula

• At this point, the IPMT function will ask you to enter the first parameter i.e., the interest rate per period. Since we have to calculate the monthly interest so divide the annual rate of interest by the number of payments per year. In the above table, the interest rate is provided in cell C5. Therefore, our formula becomes: =IPMT (C5/12,
• In the second parameter, this function will ask for the PER parameter. Here, we will specify the period for which you want to calculate the interest. The PER is mentioned in cell C6. So our formula becomes: =IPMT (C5/12, C6
• The NPER argument represents the total number of payment periods. It is mentioned in cell C5. Since the PER argument is given annually so we will covert it as well in annual figures. So our formula becomes: = IPMT (C5/12, C6, C7*12
• Next, this function will ask for the Pv parameter that represents the current value, or total value of all payments. It is mentioned in cell C6. So our formula becomes: =IPMT(C5/12,C6,C7*12,C8)

The overall formula will look similar to the below image:

STEP 4: IPMT will return the output

• Once you have typed the formula, press the Enter button to complete it.
• The above formula will return the interest payment after calculating the above values.
  

Example 2: Calculate the interest payment for a $7,000 investment if it's given that the interest rate is 7.2%, where the IPMT is calculated for the "seven" month and payments that are due at the end of each month.

Please note in the above question the period (mentioned in months) is specified as a text format.

Considering the above details, we will have the following data values:

To calculate the Monthly IPMT value follow the below given steps:

Step 1: Insert the helper row

We will add the helper row named 'Monthly Payment' in cell C12. This row will use the IPMT function to calculate the monthly payment using the given Interest rate, period, NPER, PV, FV, and type value.

Refer to the given below image:

Step 2: Enter the IPMT formula

• IPMT is an inbuilt function that helps to calculate the interest amount of a loan payment.
• In the "Monthly Payment" helper column, start the formula with the equal sign (=) and type the IPMT function. So our formula becomes: = IPMT (

It will look similar to the below image:

STEP 3: Add the parameter to your formula

• At this point, the IPMT function will ask you to enter the first parameter i.e, the interest rate per period. In the above table, the interest rate is provided in cell C5. Therefore, our formula becomes: =IPMT (C5,
• In the second parameter, this function will ask for the PER parameter. Here, we will specify the period for which you want to calculate the interest. The PER is mentioned in cell C6. So our formula becomes: =IPMT (C5, C6
• The NPER argument represents the total number of payment periods. It is mentioned in cell C5. So our formula becomes: = IPMT (C5, C6, C7
• Next, this function will ask for the Pv parameter that represents the current value, or total value of all payments. It is mentioned in cell C6. So our formula becomes: = IPMT (C5, C6, C7, C8).
• This last parameter, i.e., type, is optional. So it will automatically take the default value 0 since we have omitted.

The overall formula will look similar to the below image:

STEP 4: IPMT will return the output

• Once you have typed the formula, press the Enter button to complete it.
• You will notice that Excel will immediately throw the #VALUE! error since all the period argument in specified in text format.

NOTE: While working with Excel IPMT function, always ensure that all the parameters re specified with numeric data.

The IPMT function is not working in Excel

Unlike, the PMT and another financial function, IPMT function also throws errors. Though there could be other reasons as well but commonly it occurs because of one of the following reasons:

• #NUM! error
  This error occurs if the given per argument is out of the range 1 to nper.
• #VALUE! error
  Excel throws this error when any of the parameter specified in your IPMT formula is non-numeric. To fix this error, always make sure that all the arguments are in numeric format and all the given numbers are not formatted as text.