IPMT Function in Excel
IPMT Function is used to calculate a specific portion of interest on the basis of loan amount and loan tenure. The syntax of IPMT is quite similar to the syntax of PV Function in Excel which all have seen earlier. To understand better, IPMT helps used to distinguish between different portions or segments of any loan and to what time how much amount is to be paid on the basis of interest applicable, can be calculated.
IPMT Formula in Excel:
Below is the IPMT Formula in Excel.
Explanation of IPMT Function in Excel
There are six parameters are used for IPMT function. In which four parameters are compulsory and two are optional.
Parameter details are as follows:
- Rate: The interest rate per period.
- Per: The period for which you want to find the interest and must be in the range 1 to nper.
- Nper: The total number of payment periods in an annuity.
- Pv: The present value, or the lump-sum amount that a series of future payments is worth right now.
- [FV]: It is an optional argument. The FV or a cash balance you want to attain after the last payment is made. If FV is omitted, excel assumes it to be 0 (the future value of a loan, for example, is 0).
- [Type]: This is also an optional argument. The number 0 or 1 and indicates when payments are due. If this argument is omitted, excel assumes it to be 0.
The Type can be 0 or 1, where:
0 = The payment is made at the end of the period;
1 = The payment is made at the start of the period.
How to Use the IPMT Function in Excel?
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 function in Excel.
Interest payment made for months 1 and 2 of a loan of $70,000 which is to be paid after 6 years. Interest rate of 6% per year and the payment to the loan is to be made at the end of each month.
Result is :
- To convert the annual interest rate of 6% into the monthly rate (=6%/12), and the number of periods from years to months (=6*12).
Interest during quarters 1 and 2 of an investment that is required to increase an investment from $0 to $6,000 over a period of 3 years. Interest rate of 4.5% per year and the payment into the investment is to be made at the beginning of each quarter.
Result is :
- The annual interest rate has been converted into a quarterly rate (4.5%/4)
- The number of periods has been converted from years to quarters (=3*4).
- The [type]argument has been set to 1, to indicate that the payment is to be made at the start of each quarter.
- The interest for the first quarter is zero, as the first payment is made at the start of the quarter.
* Use N%/12 for rate and N*12 for nper when there are monthly payments. N%/4 for rate and N*4 for nper when quarterly payments and N% for rate and N for nper when there are annual payments.
*Cash paid out is shown as negative numbers. Cash received is shown as positive numbers.
Things to Remember
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.
2. Error handling #VALUE!: IPMT function through a #VALUE! Error when any non-numeric.
VBA Function Example:
The IPMT function can also be used in VBA code.
This has been a guide to IPMT in Excel. Here we discuss the IPMT Formula in Excel and how to use IPMT function in Excel along with practical examples and downloadable excel templates. You can also go through our other suggested articles –