Excel PPMT 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.

  PPMT follows the same convention for its result: a negative principal payment means money you pay out, and a positive principal 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 and has no accrued interest, so =PPMT(10%,1,2,2100,0,1) returns -1100, the entire first payment. Later payments can include 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

  PPMT assumes equally spaced, equal total payments and one rate for all periods. The principal 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, PPMT returns -(pv+fv)/nper because the entire payment is principal. For example, =PPMT(0,3,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 PPMT needs at least one payment period. The selected per must be between 1 and nper. Use 1 for the first payment.

• PPMT returns principal, not interest or the total payment

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

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