Spreadsheet formulas explained, side by side

NPER Function in Excel, Google Sheets, Airtable & Notion

NPER returns the number of periods needed to pay off a loan, or to grow savings to a target, with fixed payments and a constant interest rate.

Syntax in each app

AppSupportSyntax & notes
Excel✔ Built inNPER(rate, pmt, pv, [fv], [type])
Google Sheets✔ Built inNPER(rate, payment_amount, present_value, [future_value], [end_or_beginning])
Airtable≈ WorkaroundLOG(pmt / (pmt + pv * rate), 1 + rate)
No NPER function in Airtable; this LOG formula is exact for fv = 0 and payments at period end. It returns an error or NaN when the payment never covers the interest.
Notion≈ Workaroundln(pmt / (pmt + pv * rate)) / ln(1 + rate)
No nper() in Notion; divide natural logs. For a 0% rate the answer is -pv / pmt.
Try NPER with your own values
Negative: money you pay
%
Result (as Excel & Google Sheets return it)
31.57
Your formula, ready to paste
Excel=NPER(18%/12, -200, 5000) open excel.new ↗
Google Sheets=NPER(18%/12, -200, 5000) open sheets.new ↗
Airtable
workaround
LOG(-200 / (-200 + 5000 * 0.18/12), 1 + 0.18/12)
Notion
workaround
ln(-200 / (-200 + 5000 * 0.18/12)) / ln(1 + 0.18/12)

The result is a number of payment periods (months when paying monthly), and is usually not a whole number: the last payment is smaller. Divide by 12 for years.

Arguments

ArgumentWhat it means
rateInterest rate per period, e.g. 18%/12.
pmtPayment each period, negative when you pay it.
pvCurrent balance or loan amount, positive for money you received.
[fv] optionalTarget balance at the end. Defaults to 0.
[type] optional0 (default) = payments at period end, 1 = at the beginning.

Examples

FormulaResultWhyCopy
=NPER(18%/12, -200, 5000)31.57Months to clear a $5,000 card balance at $200 a month.
=NPER(5%, 0, -1000, 2000)14.21Years for $1,000 to double at 5% with no extra deposits.
=NPER(0, -100, 1200)12At 0% it is simply balance รท payment.
=NPER(1%, -10, 5000)#NUM!The $10 payment is less than the $50 monthly interest, so the loan is never repaid.

Examples use Excel / Google Sheets syntax unless noted.

How NPER works

NPER is the "how long?" function: months to pay off a credit card, years until savings reach a goal, or how much sooner a loan ends if you pay more each month. The answer is in the same periods as the rate, so with a monthly rate (18%/12) you get months. Round it up with ROUNDUP to count the final, smaller payment as a whole period.

Signs matter more for NPER than for most functions, because the wrong combination has no answer. A loan you received (positive pv) needs a negative payment; savings you put in (negative pv) grow toward a positive fv. If the payment does not even cover the interest, the balance never falls and Excel returns #NUM!.

Airtable and Notion have no NPER, but for fv = 0 the period count has an exact log formula: log(pmt / (pmt + pv × r)) / log(1 + r). Airtable's LOG(number, base) takes the base directly; Notion divides two ln() values. The copy buttons convert the annual rate the same way as the Excel formula.

Frequently Asked Questions

Why does NPER return #NUM!?
Either the payment is too small to cover the interest, or pv and pmt have the same sign. For a loan, enter the balance as positive and the payment as negative.

Part of the Financial functions. Results in the tester follow Excel and Google Sheets behaviour; always double-check important work in your own file.