NPer (function)

Syntax

NPer(Rate,Pmt,Pv,Fv,Due)

Description

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

Comments

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:

 

Parameter

Description

 

Rate

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

 

Pmt

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

 

Pv

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.

 

Fv

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.

 

Due

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.

Example

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

N