How to use IPMT Function in Excel

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.

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:

Note:

1. 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.
2. Cash paid out (as on a loan) is shown as negative numbers. Cash received (as from an investment) is shown as positive numbers.
3. #NUM! error – Occurs if the supplied per argument is less than zero or is greater than the supplied value of nper.
4. #VALUE! error – Occurs when any of the given arguments are non-numeric