IPMT Excel Function

Last Updated :

-

Blog Author :

Tanuj Kumar

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

IPMT Function in Excel

The IPMT function in Excel calculates the interest paid on a given loan where the interest and periodic payments are constant. It is a built-in function in Excel and a type of financial function. This function calculates the interest portion for the payment done for a given period.

For example, suppose the loan amount is $20,000, which needs to be paid in 5 years. The interest rate is 5% per year, and the loan payment needs to be made at the end of every month. Therefore, we need to calculate the interest payments during the first month.

In such a scenario, we can use the IPMT function. Therefore, the IPMT formula will be as follows:

IPMT( 2%/12, 1, 60, 1*12, 20000)

IPMT = $388

So, the calculated IPMT Excel value is $388.

Table of contents

Syntax

IPMT formula in excel

Compulsory Parameters:

  • Interest rate: An interest rate for the investment.
  • Period: Period or duration to calculate the interest rate. 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 the present value used as a value of payments.

Optional Parameters:

  • : FV is optional here. It is the future value. For example, if you receive a loan, then this is the final payment at the end. Therefore, it will assume 0 as FV.
  • : 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? (Examples)

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 6th month, and payments are due at the end of each month.

IPMT Function in Excel Example - 1

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

IPMT Function in Excel Example - 2

=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 4th year, and payments are due at the end of each year.

IPMT Function in Excel Example - 3

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 a few error details in the IPMT function as the wrong argument will be passed in the functions.

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