Pmt (function)




Returns the payment for an annuity based on periodic fixed payments and a constant rate of interest.


An annuity is a series of fixed payments made to an insurance company or other investment company over a period of time. Examples of annuities are mortgages and monthly savings plans.

The Pmt function requires the following parameters:






Double representing the interest rate per period. If the periods are given in months, be sure to normalize annual rates by dividing them by 12.



Double representing the total number of payments in the annuity.



Double representing the present value of your annuity. In the case of a loan, the present value would be the amount of the loan.



Double representing the future value of your annuity. In the case of a loan, the future value would be 0.



Integer indicating when payments are due for each payment period. A 0 specifies payment at the end of each period, whereas a 1 specifies payment at the start of each period.


Rate and NPer must be expressed in the same units. If Rate is expressed in months, then NPer must also be expressed in months.

Positive numbers represent cash received, whereas negative numbers represent cash paid out.


This example calculates the payment necessary to repay a $1,000.00 loan over 36 months at an annual rate of 10%. Payments are due at the beginning of the period.

Sub Main()
  x = Pmt((.1/12),36,1000.00,0,1)
  msg1 = "The payment to amortize $1,000 over 36 months @ 10% is: "
  MsgBox msg1 & Format(x,"Currency")
End Sub

See Also

IPmt (function); NPer (function); PPmt (function); Rate (function).

More information