IPMT Excel Function (Formula, Examples) | How to Use IPMT in Excel

Excel Functions Tutorials

IPMT in Excel (Table of Contents)

IPMT in Excel
IPMT Formula in Excel
How to Use IPMT in Excel

IPMT in Excel

IPMT Excel is a financial function in excel that returns the interest amount of a payment received based on an interest rate and a constant payment schedule. By using this function you can find out how many parts of interest is in an installment, in other words, it calculates interest repayment for each period for a loan taken out over a term at a fixed interest rate.

In simple word, IPMT function in Excel is used to calculate the interest amount for a given period.

Also, have a look at PMT Excel Function

IPMT Formula in Excel

Recommended Courses

Explanation of IPMT in Excel

There are six parameters are used for IPMT in Excel. In which four parameters are compulsory and two are optional.

Parameter details are as follows:

Compulsory Parameters:

Interest rate: An interest rate for the investment.
Period: Period or duration to calculate the interest rate. Here it is always a number between 1 and numbers of payments.
Number_payments: Payment is number of payments in the duration.
PV: PV is present value is used as a value of payments.

Optional Parameters:

[FV]: FV is optional here. It is the future value. If you receive a loan then this is the final payment at the end. It will assume 0 as FV.
[Type]: Type is optional here. It indicates that the payments are due. There are two types of parameters for the type.
0: It is used if payments are due at the end of the period and it automatically defaults.
1: It is used if payments are due at the beginning of the period.

How to use IPMT in Excel Function?

IPMT function in excel can be used as worksheet function as well as a VBA Function. Here are some examples of IPMT function to understand the working of IPMT in excel.

IPMT Function in Excel Example #1

This first example returns the interest payment for an $8,000 investment that earns 7.5% annually for 2 years. The interest payment is calculated for the 6^th month and payments that are due at the end of each month.

=IPMT(7.5%/12, 6, 2*12, 8000). So, the calculated IPMT value is $40.19

IPMT Function in Excel Example #2

This next example returns the interest payment for a $10,000 investment that earns 5% annually for 4 years. The interest payment is calculated for the 30th week and payments are due at the beginning of each week.

=IPMT(5%/52, 30, 4*52, 10000, 0 ,1). So, the calculated IPMT excel value is $8.38.

IPMT Function in Excel Example #3

This next example returns the interest payment for a $6,500 investment that earns 5.25% annually for 10 years. The interest payment is calculated for the 4^th year and payments are due at the end of each year.

IPMT function as worksheet function:=IPMT(5.25%/1, 4, 10*1, 6500). So, the calculated IPMT value is $27.89.

Things to remember about the IPMT in Excel Function:

Below are the few error details which can come in IPMT function as the wrong argument will be passed in the functions.

Error handling #NUM!: If per value is < 0 or > the value of nper then IPMT function through a #NUM error.
Error handling #VALUE!: IPMT function through a #VALUE! Error when any non-numeric value has been passed in the IPMT formula.

You can download this IPMT Function in Excel template here – IPMT Function Excel Template

IPMT Excel Function