IPMT 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.
- 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.
- [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?
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.
=IPMT(7.5%/12, 6, 2*12, 8000). So, the calculated IPMT value is $40.19
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.
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 the few error details that can come in IPMT Excel 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.
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.