NPer (function)




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


An annuity is a series of fixed payments paid to or received from an investment over a period of time. Examples of annuities are mortgages, retirement plans, monthly savings plans, and term loans.

The NPer function requires the following parameters:






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



Double representing the amount of each payment or income. Income is represented by positive values, whereas payments are represented by negative values.



Double representing the present value of your annuity. In the case of a loan, the present value would be the amount of the loan, and the future value (see below) would be zero.



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



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


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


This example calculates the number of $100.00 monthly payments necessary to accumulate $10,000.00 at an annual rate of 10%. Payments are made at the beginning of the month.

Sub Main()
  ag# = NPer((.10/12),100,0,10000,1)
  MsgBox "The number of monthly periods is: " & Format(ag#,"Standard")
End Sub

See Also

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

More information