Financial

NPER Function in Excel

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

Syntax

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

Arguments

  • rate (required): Interest rate per period
  • pmt (required): Payment per period
  • pv (required): Present value
  • fv (optional): Future value (default 0)
  • type (optional): 0=end of period, 1=beginning

Examples

  • =NPER(5%/12, -500, 25000) - Months to pay off loan - Result: ~56 months

Excel NPER function documentation

  • NPER returns number of periods for an investment or loan given rate, payment, and present value.
  • Syntax: =NPER(rate, pmt, pv, [fv], [type]).
  • Answer how many payments to pay off a loan at fixed rate.
  • Related: [PMT](/functions/pmt/), [RATE](/functions/rate/), [PV](/functions/pv/), [FV](/functions/fv/).

NPER syntax and signs

  • rate per period — annual/12 for monthly loans.
  • pmt typically negative for borrower payment outflow.
  • pv positive loan amount received.
  • fv balloon optional; type 0 end / 1 beginning of period.

Step-by-step: months to repay loan

  • Step 1 — Rate 6%/12 in E1, payment -500, loan 20000.
  • Step 2 — =NPER(E1, -500, 20000).
  • Step 3 — Result ~45 months — round up for planning.
  • Step 4 — Verify with [PMT](/functions/pmt/) on same inputs.
  • Step 5 — Sensitivity table rate vs NPER for scenarios.

NPER vs manual amortization

  • NPER — closed-form period count.
  • Amortization schedule — row-by-row balance table.
  • Goal Seek — alternative what-if tool.
  • Financial category: [/categories/financial/](/categories/financial/).

Worked examples to copy

  • Basic: =NPER(0.06/12, -200, 5000).
  • With FV: =NPER(rate, pmt, pv, 1000).
  • Years display: =NPER(rate,pmt,pv)/12.
  • Beginning pay: type=1 last argument.

People also ask

  • NPER negative result? — Payment too small to ever repay — check rate signs.
  • NPER fractional? — Can return decimal periods — interpret carefully.
  • NPER vs NPER for savings? — Same function; sign convention flips.
  • NPER #NUM!? — No solution rate/pmt/pv combo.

Common errors

  • Rate must match payment period

Use cases

  • Loan payoff time
  • Savings goals
  • Investment planning

Frequently asked questions

  • How do I calculate how long to pay off a credit card? Use =NPER(APR/12, -payment, balance). For $5000 balance at 18% APR with $200/month payments: =NPER(18%/12, -200, 5000) = 31 months. Remember: rate must be monthly if payments are monthly.
  • How do I calculate time to reach a savings goal? Use =NPER(rate, -deposit, -current_balance, goal). To save $50,000 starting with $10,000, adding $500/month at 5%: =NPER(5%/12, -500, -10000, 50000) = ~62 months.
  • Why does NPER return a negative or very large number? This happens when payments are too small to ever pay off the loan (interest exceeds payment). Check that your payment is larger than the monthly interest: payment > balance × monthly_rate. Also verify signs: pmt should be negative if pv is positive.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use NPER

  • Loan payoff time — common Financial scenario for NPER.
  • Savings goals — common Financial scenario for NPER.
  • Investment planning — common Financial scenario for NPER.

NPER in the Financial category

  • Browse all Financial functions at /categories/financial/ for related formulas.
  • NPER syntax: =NPER(rate, pmt, pv, [fv], [type])
  • rate (required): Interest rate per period
  • pmt (required): Payment per period
  • pv (required): Present value
  • fv (optional): Future value (default 0)
  • Confirm NPER arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm NPER arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • PMT (/functions/pmt/): Calculates the payment for a loan based on constant payments and a constant interest rate.
  • RATE (/functions/rate/): Returns the interest rate per period of an annuity.
  • PV (/functions/pv/): Returns the present value of an investment - the total amount that future payments are worth now.
  • FV (/functions/fv/): Returns the future value of an investment based on periodic, constant payments and a constant interest rate.

Errors to watch for

  • Rate must match payment period — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter NPER on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

NPER worked examples to copy

  • =NPER(5%/12, -500, 25000) — Months to pay off loan. Expected result: ~56 months.

NPER reference summary for crawlers and offline review

  • NPER belongs to the Financial category in Excel. Returns the number of periods for an investment based on periodic payments and a constant interest rate.
  • Full syntax: =NPER(rate, pmt, pv, [fv], [type]). Open /functions/nper/ for parameters, FAQs, and related pages.
  • Common mistakes: Rate must match payment period
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.