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
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | NPER(rate, pmt, pv, [fv], [type]) |
| Google Sheets | ✔ Built in | NPER(rate, payment_amount, present_value, [future_value], [end_or_beginning]) |
| Airtable | ≈ Workaround | LOG(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 | ≈ Workaround | ln(pmt / (pmt + pv * rate)) / ln(1 + rate)No nper() in Notion; divide natural logs. For a 0% rate the answer is -pv / pmt. |
| 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) |
Excel & Sheets use cell ranges. Paste this sample data into cell A1 of a blank sheet, then paste the formula into any empty cell.
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
| Argument | What it means |
|---|---|
rate | Interest rate per period, e.g. 18%/12. |
pmt | Payment each period, negative when you pay it. |
pv | Current balance or loan amount, positive for money you received. |
[fv] optional | Target balance at the end. Defaults to 0. |
[type] optional | 0 (default) = payments at period end, 1 = at the beginning. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=NPER(18%/12, -200, 5000) | 31.57 | Months to clear a $5,000 card balance at $200 a month. | |
=NPER(5%, 0, -1000, 2000) | 14.21 | Years for $1,000 to double at 5% with no extra deposits. | |
=NPER(0, -100, 1200) | 12 | At 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.