Quick Navigation

NPER Function

Excel 2007+

Summary

The NPER function calculates the number of payment periods required to reach a specified financial goal given constant payments, interest rate, and present value. It's essential for loan term analysis, savings planning, and investment duration calculations.

Syntax

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

Parameters

Parameter	Type	Required	Description
rate	`Number`	Yes	Interest rate per period (e.g., monthly rate for monthly payments)
pmt	`Number`	Yes	Constant payment amount made each period (use negative for cash outflows)
pv	`Number`	Yes	Present value - current value of investment or loan amount
fv	`Number`	No	Future value or target balance (defaults to 0) - optional
type	`Number`	No	Payment timing: 0=end of period (default), 1=beginning of period - optional

Using the NPER Function

NPER helps financial analysts determine how long it will take to pay off debt or reach savings goals. Convert annual rates to periodic rates by dividing by payment frequency (e.g., 6% annual ÷ 12 = 0.5% monthly). Use negative values for loan payments and positive for investment contributions.

Common NPER Examples

Investment Growth Periods

=NPER(A2/12,A3,A4,A5,1)

With 12% annual rate, $100 monthly payments, $1,000 initial investment targeting $10,000, payments at period start: 59.67 periods (≈5 years)

Loan Payoff Periods

=NPER(A2/12,A3,A4)

Same terms but FV=0 (loan fully paid): -9.58 periods (negative indicates cash outflow convention)

Standard End-of-Period Loan

=NPER(0.06/12,-200,15000)

Monthly payment $200 on $15,000 car loan at 6% APR: calculates total months required

Frequently Asked Questions

Negative results occur when pv and pmt have opposite signs (typical for loans). Take the absolute value for actual periods.

Divide annual rate by 12: =NPER(annual_rate/12, pmt, pv)

Type 0=payments at end of period (ordinary annuity), Type 1=payments at beginning (annuity due). Type 1 results in fewer periods.

Common Errors and Solutions

#NUM!

Cause: Rate near 0 with pv and pmt same sign

Solution: Verify rate > 0 and pv/pmt signs are opposite

#VALUE!

Cause: Non-numeric arguments

Solution: Ensure all inputs are numbers

Unexpected positive result

Cause: pv and pmt same sign

Solution: Use negative for payments/outflows

Notes

Always match rate period to payment period (monthly rate for monthly payments)
Loans: pv positive, pmt negative; Investments: pv negative, pmt positive
Omitted fv assumes 0 (balance reaches exactly 0)
Works with Excel 2007 and later versions

Compatibility

Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365

Not available in: Excel 2003 and earlier

Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+