NPER Function

Calculates how many payments it takes to pay off a loan or reach a goal.

Category

Financial

Available in

All versions, Microsoft 365

Returns

A number of periods

Want to practice NPER in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

NPER answers "how long?" — how many payments to clear a balance, or how many deposits to reach a target, at a fixed rate and payment.

The result is in the same units as the rate: a monthly rate gives months.

Syntax

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

Arguments

ArgumentRequired?What it does
rateYesThe interest rate per period (annual rate ÷ 12 for monthly payments).
pmtYesThe payment each period (negative if you pay it).
pvYesThe amount owed now (or 0 when saving from scratch).
fvNoThe balance you want left after the last payment. Omitted = 0 (a loan paid off in full).
typeNoWhen payments fall: 0 or omitted = end of each period, 1 = start of each period.

Examples

Example 1 — Paying off a balance

Northstar owes $20,000 on a 9% credit line and pays $600 a month.

B2fx=NPER(9%/12,-600,20000)
AB
1GoalMonths
2Clear $20,000 debt38.50
3Save $50,000 at 4%, $800/mo56.87

Round up with ROUNDUP — 38.5 means a 39th, smaller payment.

Common mistakes

  • #NUM! error. The payment is too small to cover the interest, so the debt never clears. Raise the payment.
  • Signs. pmt and pv need opposite signs for a loan.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.