IPMT Excel Function

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.

Syntax

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.

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

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.
   
2. Error handling #VALUE!: IPMT function through a #VALUE! Error when any non-numeric value has been passed in the IPMT formula.