IPMT Excel Function

IPMT Function 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? (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 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.

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

Recommended Articles

This has been a guide to IPMT Function in Excel. Here we discuss the how to use IPMT formula (step by step) along with examples and templates. You can learn more about excel from the following articles –

• Excel Index Match Function
• Excel Mathematical Function
• Excel VBA Type
• PMT Formula Excel
• Excel Time Card Template