Ipmt Function In Excel How To Use Step By Step

IPMT Function in Excel

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]: 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]: 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(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 4th 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 a few error details in the 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!:Error Handling #VALUE!:#VALUE! Error in Excel represents that the reference cell the user has either entered an incorrect formula or used a wrong data type (mostly numerical data). Sometimes, it is difficult to identify the kind of mistake behind this error.read more IPMT function through a #VALUE! Error when any non-numeric value has been passed in the IPMT formula.