March 1, 2024

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
At the end of the period

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)


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)


  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
READ:  How to use PRICEMAT Function in Excel

Leave a Reply

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