IPMT function 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.
IPMT in Excel (Table of Contents)
IPMT in Excel
In simple word, IPMT function in Excel is used to calculate the interest amount for a given period.
Also, have a look at PMT Excel Function
IPMT Formula in Excel
Recommended Courses
Explanation of IPMT in Excel
There are six parameters are used for IPMT in Excel. In which four parameters are compulsory and two are optional.
Parameter details are as follows:
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 numbers of payments.
- Number_payments: Payment is 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 IPMT in Excel Function?
IPMT function in excel can be used as a worksheet function as well as a VBA Function. Here are some examples of IPMT function to understand the working of IPMT in excel.
IPMT Function in Excel 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 that are due at the end of each month.
4.9 (1,353 ratings)
=IPMT(7.5%/12, 6, 2*12, 8000). So, the calculated IPMT value is $40.19
IPMT Function in Excel 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.
IPMT Function in Excel 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 about the IPMT in Excel Function:
Below are the few error details which can come in 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!: 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 IPMT Formula in excel and how to use IPMT in Excel along with practical examples and downloadable excel templates.
- Excel VBA Type
- PMT Formula Excel
- VBA Type Mismatch | Examples
- VBA Error Handling in Excel
- Excel NPER
- PMT Function Excel
- MIRR Function in Excel
- Column Function in Excel
- Yield Function
- FV function in Excel
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
