Menu
EXCEL IPMT FUNCTION
Excel IPMT Function Introduction
This function provides the interest payment for an investment with a constant payment and interest rate over a specified term.
Description of Excel IPMT Function
Syntax:
=IPMT(rate, per, nper, pv, [fv], [type])
Parameter list:
- The interest rate per period is referred to as the rate.
- Per – the time period for which you wish to calculate interest, which must be between 1 and nper.
The total number of payment periods in an annuity is referred to as nper. - Pv stands for present value, or the current value of a sequence of future payments as a lump sum.
- Fv – the future value, or the cash balance you’d like to achieve after the last payment. If fv isn’t specified, it’s presumed to be zero (the future value of a loan, for example, is 0).
- The number 0 or 1 shows whether or not payments are due. If type is not specified, it is presumed to be 0. 0 indicates the end of the period, whereas 1 indicates the start of the period.
Step By Step Guide of IPMT Function
Example :
In this example, we use the formula =IPMT(B3/12,B6,B4*12,B2) to calculate the interest amount for the first month in cell B9. We calculate the monthly rate by dividing it by 12 since we pay monthly, and the second parameter is the month number, the first month, from the entire period of 36 months. The third element is the total period, which is calculated by multiplying the month by the years to get 36, and the fourth parameter is the loan amount.
The second month is computed in cell B10 in the same way. And in cell B11, we’re calculating the interest due in the previous year (3rd year); because we’re using an annual interest rate, there’s no need to divide by 12, and per and Nper are the same because we’re looking for the interest for the previous year, which is 3, so per will be 3 and Nper will be 3 because we’re only paying three times during the loan period.
Key Points
- Make sure the first and second parameters you provide are accurate. For example, if you’re calculating a monthly payment for a loan with a ten percent interest rate over four years, the rate will be ten percent divided by twelve, and the nper will be four times twelve. If you make annual payments on the same loan, the rate will be 10% and the nper will be 4.
If it’s on a quarterly basis, the rate will be 10% /4, and the nper will be 16 (4 years multiplied by 4 quarters).
Related Functions
HLOOKUP Function
HLOOKUP Function
Explore NowVLOOKUP Function
VLOOKUP Function
Explore NowINDEX Function
INDEX Function
Explore NowMATCH Function
MATCH Function
Explore NowDATE Function
DATE Function
Explore NowIF Function
IF Function
Explore NowOR Function
OR Function
Explore NowAND Function
AND Function
Explore NowRANK Function
RANK Function
Explore NowSUMIF Function
SUMIF Function
Explore NowXIRR Function
XIRR Function
Explore NowWORKDAYS Function
WORKDAYS Function
Explore Now
