Excel PMT 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

   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.

3. 3

   pv

   Required

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

4. 4

   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.

5. 5

   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.

  PMT follows the same convention for its result: a negative payment means money you pay out, and a positive 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. Paying earlier reduces the amount needed per payment at a positive rate. 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

  PMT assumes equally spaced, equal payments and one rate for all periods. A changing rate or payment schedule needs to be modeled separately.

• Zero interest still has a payment

  At rate=0, PMT returns -(pv+fv)/nper. For example, =PMT(0,4,2000,0,1) returns -500, regardless of payment timing.

• The period count must be greater than zero

  nper must be greater than zero because PMT needs at least one payment period. Using 0 returns #DIV/0!.

Checking a repayment plan? Use RATE to infer its rate, or FV to calculate the balance after a fixed series of deposits.