IPMT Excel Function | How to use IPMT in Excel? (with Examples)

IPMT in Excel

IPMT function in excel is used to calculate the interest to be paid on a given loan where the interest and periodic payments are constant, this is an inbuilt function in excel and is also a type of financial function, this function calculates the portion of interest for the payment done for a given period.

Syntax

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 a number of payments.
Number_payments: Payment is a 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 the IPMT Function in Excel?

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

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

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.

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

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

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

Below are the few error details that can come in IPMT Excel 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.