Related Functions:

The NPER function returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.

Syntax

=NPER(rate,pmt,pv,[fv],[type])

Arguments

Argument Description
rate The interest rate per period
pmt The payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes
pv The present value, or the lump-sum amount that a series of future payments is worth right now
[fv] Optional. The future value, or a cash balance you want to attain after the last payment is made. If [fv] is omitted, it is assumed to be 0, i.e. the future value of a loan is 0
[type] Optional. Defines whether the payment is made at the start or the end of the period. The [type] argument can have the value 0 or 1, meaning:

  0 the payment is made at the end of the period
  1 the payment is made at the beginning of the period

If omitted, the [type] argument takes on the default value of 0, denoting payments made at the end of the period.

Examples

  A B C D
1 Data Description    
2 12% Annual interest rate    
3 -$150 Payment made each period    
4 $10,000 Present value    
5 $1,000 Future value    
6        
7 Formula Result Notes
8 =NPER(A2/12,A3,A4,A5,0) 116.8957 Periods for the investment with the above terms
9 =NPER(A2/12,A3,A4,A5,1) 114.8628 Periods for the investment with the above terms, except payments are made at the beginning of the period
10 =NPER(A2/12,A3,A4) 110.4096 Periods for the investment with the above terms, except with a future value of 0

Note: In the example, the annual interest rate of 12% had to be converted into a monthly rate (=12%/12)

Cash Flow Convention: In line with general cash flow conventions, outgoing payments are represented by negative numbers and incoming payments are represented by positive numbers.

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if any of the supplied arguments are not recognized as numeric values
#NUM! Occurs if the specified [fv] will never be met for the rate and pmt. You may need to increase the payment amount or reduce the interest rate to get a valid result
Returns a negative result when a positive one is expected Usually occurs when the pv and the specified pmt both have the same arithmetic sign. If a loan is being paid off, pv should be positive and the pmt should be negative, or vice versa