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.
Set type equal to If payments are due
0 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:

IPMT Function - How to use IPMT Function in Excel

Syntax:  =IPMT(B1/12,B2,B3*12,B4)

Result:

IPMT Function in Excel - How to use IPMT Function in Excel

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:

  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

Add a Comment

Your email address will not be published. Required fields are marked *