Excel IPMT Function

1. 1

   rate

   Required

   The constant interest or discount rate per period. Use an annual rate for annual cash flows or a monthly rate for monthly cash flows. To convert an annual rate to a monthly rate, divide by 12 if it is a nominal annual rate compounded monthly. If it is an effective annual rate, use (1+annual_rate)^(1/12)-1 to find the equivalent monthly rate.

2. 2

   per

   Required

   The payment period to examine, counted from 1 through nper.

3. 3

   nper

   Required

   The total number of periods. This should correspond to the rate period. For instance, use months with a monthly rate or years with an annual rate.

4. 4

   pv

   Required

   The balance or lump sum at time zero. Money received is positive and money paid out is negative.

5. 5

   fv

   Optional

   The balance or lump sum at the end of the final period. Money received is positive and money paid out is negative. Defaults to 0.

6. 6

   type

   Optional

   When periodic payments occur. This changes payment timing, not the dates of pv or fv.

   • 0: Payments at the end of each period. This is the default when the argument is omitted.
   • 1: Payments at the beginning of each period.

• What positive and negative amounts mean

  The sign of pv describes the starting amount: use a positive pv for money you receive and a negative pv for money you deposit. A pv of 0 means no starting balance.

  The sign of fv tells Excel what you want left at the end: a positive fv means money you’ll have, such as savings; a negative fv means money you’ll still owe, such as an unpaid loan balance. An fv of 0 means nothing saved or owed.

  IPMT follows the same convention for its result: a negative interest payment means money you pay out, and a positive interest payment means money you receive.

• Rate and periods must use matching units

  For 36 monthly payments at a nominal annual rate of 6% compounded monthly, use 6%/12 for rate and 36 for nper. Entering 6% directly would apply 6% every month. If 6% is an effective annual rate, use (1+6%)^(1/12)-1 to find the equivalent monthly rate. For comparison, 1% per month compounds to about 12.68% per year.

• Payment timing changes the payment

  Omitted type or 0 means end-of-period payments; 1 means beginning-of-period payments. The first beginning-of-period payment occurs immediately, so =IPMT(10%,1,2,2100,0,1) returns 0 because no interest has accrued yet. Later payments can contain interest. The pv amount stays at time zero and fv stays at the end of the final period.

• The default final balance is zero

  Omitting fv assumes the loan is fully repaid. For savings, set pv to 0 and enter the target in fv.

• Payments and rate stay constant

  IPMT assumes equally spaced, equal total payments and one rate for all periods. The interest portion can change from one payment to the next. A changing rate or payment schedule needs to be modeled separately.

• Zero interest still has a payment

  At rate=0, IPMT returns 0 for every valid period. For example, =IPMT(0,3,4,2000,0,1) returns 0, regardless of payment timing. The loan still has principal payments.

• The period count must be greater than zero

  nper must be greater than zero because IPMT needs at least one payment period. The selected per must be between 1 and nper. Use 1 for the first payment.

• IPMT returns interest, not principal or the total payment

  =IPMT(10%,2,2,2100) returns -110, the interest portion of the second payment. That payment also includes -1100 in principal, making the total payment -1210. IPMT returns only the interest for the selected period, not the total interest paid so far or the remaining loan balance.

Building a payment breakdown? Pair IPMT with PPMT for principal. With identical arguments, their results add up to the total payment returned by PMT, which takes no per argument.