Practice free

NPER function

NPER returns how many payments it takes to pay off a loan or reach a savings goal. It is often used to see the effect of paying more each month.

Example

Read it as a sentence: find how many periods it takes to pay off a 5000 loan at 1% per period with 200 paid each period, which gives 28.9118097374808.

Practice this in the app

Syntax

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

Arguments in [square brackets] are optional.

rate (required)
Interest rate per period.
pmt (required)
Payment each period.
pv (required)
Present value.
fv (optional)
Desired future value; 0 if omitted.
type (optional)
0 = payments at period end (default), 1 = at start.

Returns: Number of periods needed.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=NPER(0.01,-100,1000) 10.58864446 Loan
=NPER(0.01,-100,-1000,10000,1) 59.67386567 With fv and type 1
=NPER(0,-100,1000) 10 Rate 0
=NPER(0.05,0,-100,200) 14.20669908 Pmt 0 with rate
=NPER(0.01,100,1000) -9.57859404 Same sign pv and pmt
=NPER("0.01","-100","1000") 10.58864446 Text number
=NPER(0,0,1000) #DIV/0! Pmt 0 rate 0
=NPER(0.1,-50,1000) #NUM! Payment too small (never repaid)

Watch out for

  • Returns #NUM! if the payment cannot cover the interest.
  • Pay and receive amounts need opposite signs.

Where it works

Excel
NPER is in every version of Excel from 2007 on.
Google Sheets
Same name, same behaviour for everyday use.

Part of Financial functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .