How to use and implement the IPMT Function in Microsoft Excel: Calculating interest portion of a loan payment

Introduction to the IPMT Function in Microsoft Excel

It was well known that when an individual is applying for a loan for the various purposes to be get fulfilled some of them are as follows:

1. Mortgage loan.
2. Loan borrowed for higher education.
3. Loan borrowed for home purchasing (home loan).
4. Debt.
5. Loan for the furniture, etc.

And in all these cases, an individual must clear the Loan or pay back all the borrowed money with the additional money (the interest amount applied on the borrowed money). In other words, Interest is the charge imposed on money when borrowed from someone ( usually from the bank) for a specific period. The charges differ on the money and the institution from where an individual has borrows the money as well.

Besides all these, the respective interest rate of any specific loan could be easily calculated by just multiplying the interest rate by the total balance. Still, for the smooth working of the calculation process, Microsoft Excel has developed an efficient built-in function to achieve this: the "IPMT FUNCTION." And this could be applied to the various version of the Microsoft Excel such as:

• Microsoft Excel 2021
• Microsoft Excel 2019.
• Microsoft Excel 365, 2016, 2003, etc.

In this tutorial, we will be discussing the below-following topics in detail:

1. What do you mean by IPMT FUNCTION in Microsoft Excel?
2. How to use the IPMT FUNCTION in Microsoft Excel?
3. What do if the Excel IPMT function is not working properly?
4. What important things need to be remembered while using IPMT Function?

What do you mean by IPMT Function?

In Microsoft Excel, the IPMT Function is primarily used to calculate a specific interest portion based on the given loan amount and the loan tenure.

And the syntax used for the IPMT function is very similar to the syntax that is used for the PV Function in Microsoft Excel. And for a better understanding, the IPMT function helps us to distinguish between the different available portions or the segments of any loan and how much amount needs to be paid out based on the Interest applicable to it, which can be calculated effectively.

IPMT Formula in Microsoft Excel:

The formula which we used for the IPMT function to be get achieved in Microsoft Excel is as follows:

Formula:

Explanation of IPMT Function formula used in Excel

Six effective parameters that are basically used for the IPMT function in which four compulsory and others are optional.

Details regarding the 6 parameters:

1) Compulsory Parameters in IPMT FUNCTION:

1. Rate: It is defined as the interest rate per Period.
2. Per: Per is abbreviated as the Period, used by the individual for which they want to find the Interest, and it must ranging from 1 to nper.
3. Nper: Nper is defined as the total number of payment periods present in the annuity.
4. PV: PV or the present value that a series of future payments is worth as of now.

2) Optional Parameters in IPMT FUNCTION:

1. [FV]: FV is primarily an optional parameter in the IPMT function, and the FV or a cash balance which we want to attain after the last payment is made. If we are omitting the FV, then excel will treat it to be 0 value.
2. [Type]: Type is an optional argument in the IPMT function, and 0 or 1 indicates when payments are due. If we omitting this argument, then Excel will treat it to be 0.

The Type can be 0 or 1, in which:

0: Zero means the respective amount of the payment made at the end of the Period.

1: One is the payment made at the Period's beginning.

How to use the IPMT FUNCTION in Excel?

The IPMT function in Excel is primarily used as a worksheet function as well as sa VBA Function. Here are some examples related to the IPMT function in order to understand the working of the IPMT function very well in Excel.

# Example: 1

We will be considering an example in which the Interest payment made for months 1 and 2 of a loan of $70,000 needs to be paid just after the duration of the 6 years. And the interest rate which is imposed on the borrowed money is about of 6%, and the payment to the Loan is made in the ending of the month.

And the outcome for the above:

# Example: 2

Now the Interest during quarters 1 and 2 of an investment is required to increase the investment from $0 to $6,000 over a period of 3 years. And the interest rate of 4.5% per year as well as the investment payment must be made at the starting of each quarter.

And the outcome for the above:

• In this, the respective annual interest rate has been converted into a quarterly rate (4.5%/4)
• And the number of periods has also been effectively converted from years to quarters (=3*4).
• Moreover, the [type] argument has been set to 1 to indicate that the payment will be made at the starting Period of each quarter.
• And also, the Interest for the first quarter is zero because the first payment is usually made at the start of the quarter.

IPMT formula used for different payment frequencies (weeks, months, and quarters)

Now to get the interest portion of a given loan payment right, we always need to convert the annual interest rate to the given Period's rate as well as converting the number of years into the total number of payment periods:

1. And for getting the rate argument divide the annual interest rate by the number of the payments per year.
2. And for the getting the particular nper argument, multiply the number of years by the number of payments made per year.

The calculation is shown under the below box as well.

• For example: Let us now find out the amount of Interest that we need to pay on the same loan amount but by making use of the different payment frequencies as well:

The Annual interest rate is: 6%

The duration of the Loan is: 2years

And the Loan amount is: $20,000

Period: 1

The balance remaining just after the last payment is $0 (the fv argument omitted), and the payments are due at the end of each Period (the type argument omitted).

Weekly:

Monthly:

Quarterly:

Semi-annual:

From the below screenshot we will be encountered that the interest amount get on decreasing with each subsequent Period as well, it is because of the reason that any payment contributes to the reduction of the loan principal, and in turn reducing the remaining balance on which Interest is being calculated.

What needs to be done if the Excel IPMT function is not working properly?

If in case our used IPMT formula returns an error or it is not working properly in the Excel sheet, then at that time several things need to be checked out:

1. Firstly, if the outcome of our IPMT formula looks significantly higher or lower than expected, then we must check to see if our rate and the nper arguments are consistent. And by using an annual interest rate with the monthly payments, it will be returning a higher monthly interest payment.
2. And if in case our used IPMT function returns the # VALUE! ERROR means that one of the arguments is in the non-numeric format. So it is our responsibility to ensure that we do not have any numbers stored as text and adjust it where it is necessary.
3. If our IPMT function gets on returning the # NUM! ERROR, then in that situation, we need to check if the per argument is used outside the range of 1 to nper respectively.

What important things need to be remembered while using IPMT Function?

The various important points, as well as things which need to be remembered by an individual while working with the IPMT Function in Microsoft Excel, are as follows:

1. The returned interest payments is basically termed to the negative numbers as Excel follows the cash flow sign convention, and an individual can also add the minus sign in front of their formula for the purpose of converting the result to a positive number.
2. And the value which are returned by the IPMT function usually corresponds to a given period, that needs to be controlled efficiently by the per argument.
3. Moreover, the IPMT function can effectively compute a loan's interest for different frequencies like yearly, quarterly, monthly, and weekly. An individual need to make sure that they must adjust the total number of periods and convert out the annual rate in order to get match with their payment frequency.