IPMT in Excel
IPMT is a financial function which calculates the interest payment, during a specific period of a loan or investment that is paid in constant periodic payments, with a constant interest rate. 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.
IPMT Formula in Excel
Below is the IPMT Formula in Execl:
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.
4.5 (886 ratings)
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 about the IPMT Function in Excel
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 –