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
=NPER(rate, pmt, pv, [fv], [type])=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
| Argument | Required | Description |
|---|---|---|
rate | Required | The interest or return per period; for monthly payments, the annual rate divided by 12. |
pmt | Required | The amount paid or saved each period; negative because it leaves you. |
pv | Required | The debt today (positive) or a starting balance (negative); 0 if a savings goal starts from nothing. |
fv | Optional | The amount to reach; 0 for a debt, the target as a positive number for savings. |
type | Optional | 0 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.
=NPER(C2/12,D2,B2)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Debt | Balance | Annual Rate | Monthly Payment | Months | Payments (Whole) |
| 2 | Credit card | 4,000.00 | 22.00% | -200.00 | 25.14 | 26 |
| 3 | Personal loan | 12,000.00 | 9.00% | -400.00 | 34.11 | 35 |
| 4 | Car loan | 20,000.00 | 7.50% | -450.00 | 52.23 | 53 |
- 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.
=NPER(D2/12,C2,0,B2)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Goal | Target | Monthly Deposit | Annual Return | Months Needed |
| 2 | House deposit | 40,000.00 | -1,000.00 | 4.50% | 37.34 |
| 3 | Holiday | 5,000.00 | -400.00 | 4.50% | 12.24 |
| 4 | Car | 60,000.00 | -1,200.00 | 4.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
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 tableBring 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).