Quick Answer

NPER Function

Calculates the number of periods for a loan or investment.

Syntax

NPER

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

Parameters

Parameter	Description	Required
rate	Interest rate per period.	Required
pmt	Payment made each period.	Required
pv	Present value or loan principal.	Required
fv	Future value (default 0).	Optional
type	0 = end of period (default), 1 = beginning of period.	Optional

Basic Example

Number of payments to pay off a loan

=NPER(0.06/12, -500, 10000)

Result22.43 months

Paying $500 per month on a $10,000 loan at 6% annual interest takes about 22.43 months.

Advanced Examples

Example 1: Reach a savings goal

Savings plan

How many months to reach $10,000 by saving $300 per month at 5%?

=NPER(0.05/12, -300, 0, 10000)

Result: 31.49 months

It takes about 32 months to reach the goal.

How NPER Works

NPER solves for the number of periods in the time-value-of-money equation. It is the inverse of PMT.

1

Enter rate

Type the rate

2

Enter pmt

Type the pmt

3

Enter pv

Type the pv

4

Enter fv

Type the fv

5

Calculate

Press Enter to get the result.

Important Notes & Limitations

Payments must remain constant.
Returns a decimal; round up if you need a whole number of periods.
Requires consistent period units.

Common Errors & Fixes

#NUM! errorThe payment is too small to reach the future value at the given rate.

Fix: Increase the payment, lower the rate, or reduce the target amount.

Decimal periodsResult is not a whole number.

Fix: Use ROUNDUP or CEILING to get the next whole period.

Download Practice File

Practice NPER with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the NPER function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Related Functions

ACCRINT

Calculates accrued interest for a security that pays periodic interest.

ACCRINTM

Calculates accrued interest for a security that pays interest at maturity.

AMORDEGRC

Calculates depreciation for a specific period using the French declining balance method.

AMORLINC

Calculates depreciation for a specific period using the French straight-line method.

COUPDAYBS

Calculates the number of days from the beginning of the coupon period to the settlement date.

COUPDAYS

Calculates the number of days in the coupon period that contains the settlement date.

Frequently Asked Questions

What is the difference between NPER and PMT?

NPER solves for the number of payments. PMT solves for the payment amount.

Can NPER handle zero interest?

Yes, NPER divides the target by the payment when rate is zero.

Why is NPER negative?

The sign depends on the cash-flow convention. Use ABS to display a positive number.