Excel Functions Tutorials
- Excel Formulas Cheatsheet
- Financial Functions in Excel
- Logical Functions in Excel
- TEXT Functions in Excel
- Lookup Reference in Excel
- Address Function in Excel
- Choose Function in Excel
- Column Function in Excel
- Columns Function in Excel
- REPLACE Function in Excel
- GetPivotData in Excel
- HLOOKUP in Excel
- Hyperlink Excel Function
- INDIRECT Function in Excel
- LOOKUP Excel Function
- Match Excel Function
- VLOOKUP Excel Function
- INDEX Excel Function
- VLOOKUP vs HLOOKUP
- Maths Functions in Excel
- POWER Function in Excel
- EVEN Function in Excel
- ODD Function in Excel
- ABS Function in Excel
- SUM Function in Excel
- SUMPRODUCT Function in Excel
- SUBTOTAL Excel Function
- ROUND in Excel
- AGGREGATE Excel Function
- PRODUCT Excel Function
- RAND Excel Function
- LOG Excel Function
- EXPONENTIAL Excel Function
- SUMIF in Excel
- TAN Excel Function
- CEILING Excel Function
- LN Excel Function
- SIGN Excel Function
- COS Excel Function
- FLOOR Function in Excel
- SIN Excel Function
- Date and Time Function in Excel
- Statistical Function in Excel
IPMT in Excel (Table of Contents)
IPMT in Excel
IPMT Excel is a financial function in excel that returns the interest amount of a payment received based on an interest rate and a constant payment schedule. By using this function you can find out how many parts of interest is in an installment, in other words, it calculates interest repayment for each period for a loan taken out over a term at a fixed interest rate.
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
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:
- 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.
- [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 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.
=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.
You can download this IPMT Function in Excel template here – IPMT Function Excel Template
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.