Skip to content

Excel function · Financial

Excel NPER function

NPER tells you how many periods it takes to pay off or save a given amount at a fixed rate: how many months a 20,000 debt takes to clear at 450 a month, for example.

In Turkish Excel: TAKSİT_SAYISI

What it does

The result can have decimals: 49.6 months means the last payment after 49 full ones is smaller. For a payment plan, round the result up with ROUNDUP.

The sign rule: for a debt, the balance is positive and the payment negative; for savings, the monthly deposit is negative and the target (fv) positive. If the signs are mixed up, or the payment does not even cover the interest (so the debt never falls), the result makes no sense or you get #NUM!.

To find the payment use PMT, for the rate RATE, and for the value at the end FV. The rates in the examples are assumptions.

Syntax

English Excel=NPER(rate, pmt, pv, [fv], [type])
Turkish Excel=TAKSİT_SAYISI(oran; devresel_ödeme; bd; [gd]; [tür])

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
rateRequiredThe interest or return per period; for monthly payments, the annual rate divided by 12.
pmtRequiredThe amount paid or saved each period; negative because it leaves you.
pvRequiredThe debt today (positive) or a starting balance (negative); 0 if a savings goal starts from nothing.
fvOptionalThe amount to reach; 0 for a debt, the target as a positive number for savings.
typeOptional0 or omitted: payments at the end of each period. 1: at the start.

Examples

1How many months each debt takes to clear

The remaining balance is positive and the monthly payment negative. Column E gives the months needed and column F rounds that up to the number of whole payments. The rates are assumptions.

E2fx=NPER(C2/12,D2,B2)
How many months each debt takes to clear: example table
ABCDEF
1DebtBalanceAnnual RateMonthly PaymentMonthsPayments (Whole)
2Credit card4,000.0022.00%-200.0025.1426
3Personal loan12,000.009.00%-400.0034.1135
4Car loan20,000.007.50%-450.0052.2353
  • E2 (filled down to E4)=NPER(C2/12,D2,B2)
  • F2 (filled down to F4)=ROUNDUP(E2,0)

The car loan needs 52.23 months: the fraction shows the last payment will be smaller, and the number of payments is 53. The credit card balance clears in 26 payments.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2How many months to reach a goal

There are no savings at the start, so pv is 0; the target is fv, positive, and the monthly deposit negative. The returns are assumptions.

E2fx=NPER(D2/12,C2,0,B2)
How many months to reach a goal: example table
ABCDE
1GoalTargetMonthly DepositAnnual ReturnMonths Needed
2House deposit40,000.00-1,000.004.50%37.34
3Holiday5,000.00-400.004.50%12.24
4Car60,000.00-1,200.004.00%46.32
  • E2 (filled down to E4)=NPER(D2/12,C2,0,B2)

Saving 1,000 a month reaches the house deposit target in 37.34 months. Without any return it would take the target divided by the monthly deposit; the difference is what the return contributes.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

#NUM!

Cause: The payment does not cover the interest: at 2% a month on a 10,000 debt, paying 150 a month never reduces it.

Fix: Raise the payment; it must be at least debt*rate.

Negative result

Cause: The payment and the debt have the same sign, so Excel returned a meaningless negative number of periods.

Fix: Enter the debt as positive and the payment as negative.

Term far too short

Cause: The annual rate was used with monthly payments without dividing by 12.

Fix: Put the rate in the same unit as the periods.

Good to know

  • To see how much an extra payment shortens the term, raise the payment and compare the results.
  • To show the whole number of payments as years and months: =INT(F2/12)&" years "&MOD(F2,12)&" months".

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Add columns to my debt list showing how many months each debt takes to clear at its monthly payment and the number of whole payments

Convert to table

Bring your debt list into the tool and type this. Nexell adds both columns with real Excel formulas and shows them in the preview; in the downloaded file, the terms change when you change a monthly payment.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

How many months until my debt is paid off?

Use =NPER(annual_rate/12,-monthly_payment,debt). If the result has decimals, the last payment is smaller; for the number of whole payments, use =ROUNDUP(NPER(…),0).

Related functions

All financial functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free