The Excel IPMT function can be used to calculate the interest portion of a given loan payment in a given payment period. For example, you can use IPMT to get the interest amount of a payment for the first period, the last period, or any period in between.
Syntax:= IPMT (rate, per, nper, pv, [fv], [type])
The IPMT function syntax has the following arguments:
- Rate Required. The interest rate per period.
- Per Required. The period for which you want to find the interest and must be in the range 1 to nper.
- Nper Required. The total number of payment periods in an annuity.
- Pv Required. The present value, or the lump-sum amount that a series of future payments is worth right now.
- Fv Optional. The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).
- Type Optional. The number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.
Set type equal to | If payments are due |
---|---|
At the end of the period | |
1 |
At the beginning of the period |
Example: Let’s look at some Excel IPMT function examples and explore how to use the IPMT function as a worksheet function in Microsoft Excel:
Syntax: =IPMT(B1/12,B2,B3*12,B4)
Result:
Based on the Excel spreadsheet above, the following IPMT examples would return:
Syntax: =IPMT(B1/12,B2,B3*12,B4)
Result: ($7,500.00)
Syntax: =IPMT(B1,3,B3,B4)
Result: ($48,868.42)
Note:
- We need to ensure that the units we use for specifying the rate and nper arguments are proper. If we make monthly payments on a 4-year loan at 24% annual interest, we need to use 24%/12 for rate and 4*24 for nper. If you make annual payments on the same loan, use 24% for rate and 4 for nper.
- Cash paid out (as on a loan) is shown as negative numbers. Cash received (as from an investment) is shown as positive numbers.
- #NUM! error – Occurs if the supplied per argument is less than zero or is greater than the supplied value of nper.
- #VALUE! error – Occurs when any of the given arguments are non-numeric