Excel NPER Function: How Long to Pay Off a Loan or Save

The NPER function in Excel answers the question "how long?": how many payments it takes to pay off a loan, or how many deposits it takes to reach a savings goal. You give it the interest rate, the payment and the starting amount, and it returns the number of periods. This guide is for anyone planning a loan, a savings target or a finance test question, and it explains each result in plain numbers. It is a calculation guide, not financial advice.

What does the NPER function do?#

NPER stands for number of periods. It works out how many equal payments, at a fixed interest rate, bring a balance to a target. The syntax has three required parts and two optional ones:

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

NPER works in every version of Excel on Windows, Mac and the web. It belongs to the same family as PMT, PV, FV and RATE. Each one solves for a different unknown with the same arguments. The PMT function guide solves for the payment, and its short "how long" table is where NPER first appears. This guide goes deeper.

How to work out how long it takes to pay off a loan#

Put the inputs in cells, then point NPER at them. Here is a 20,000 loan at 6% a year with a 500 monthly payment:

B4 returns 44.74. Here is what each part does:

• B2/12 turns the yearly rate into a monthly rate (0.5%), because the payments are monthly.
• -B3 is the payment. It is negative because it is money you pay out.
• B1 is the loan. It is positive because it is money you received.

The decimal matters. 44.74 means 44 full payments of 500 and a smaller 45th payment of about 370. Nobody makes 0.74 of a payment, so B5 uses ROUNDUP to show 45. Don't use ROUND here: it would give 45 in this case, but a result like 44.2 would round down to 44 and leave a balance unpaid. The guide to ROUND, ROUNDUP and ROUNDDOWN explains the difference, and the ROUND function reference lists the syntax.

How to show the result in years and months#

Split the months into whole years and leftover months with INT and MOD. With 45 in B5:

=INT(B5/12)&" years "&MOD(B5,12)&" months"

The result is 3 years 9 months. INT(45/12) drops the decimal from 3.75 to give 3. MOD(45,12) returns the remainder, 9. The article on MOD and INT in Excel shows more ways to split numbers like this.

If your payments are yearly, use the yearly rate and a yearly payment instead. =NPER(6%,-5000,20000) returns about 4.71 years for a 20,000 loan paid at 5,000 a year.

How much faster do bigger payments pay off a loan?#

Bigger payments cut the time and the total interest, and NPER shows by how much. List some payment options in A9:A11 and fill one formula down:

=ROUNDUP(NPER($B$2/12,-A9,$B$1),0)

The $ signs keep the rate and the loan fixed while A9 moves down to A10 and A11. Press F4 on Windows (⌘T on a Mac) to add them, and fill down with Ctrl+D (⌘D on a Mac). See how the dollar sign locks a reference if this is new.

The interest column is the payment times the exact NPER minus the loan, for example =500*44.74-20000. It is close to the real figure but not exact, because the last payment is smaller. For an exact split of interest and principal, build a full schedule. On this loan, paying 600 instead of 500 finishes 8 months sooner and saves about 437 in interest.

How long does it take to reach a savings goal?#

For savings, start from 0 and put the target in fv. To save 10,000 with 200 a month at 3% a year:

=NPER(3%/12,-200,0,10000)

The result is 47.17, so 48 deposits. The deposits are negative because the money leaves your account, and the target is positive because you get it back at the end. Without interest it would take exactly 50 months (10,000 ÷ 200), so interest saves two deposits here.

If you already have some savings, enter them as a negative pv, because that money also goes into the account. With 2,000 already saved, =NPER(3%/12,-200,-2000,10000) returns about 37.28, so 38 deposits.

Why does NPER return #NUM! or a negative number?#

NPER fails when the numbers describe a loan that can never end. These are the usual causes:

1. The payment doesn't cover the interest. At 6% a year, 20,000 costs 100 a month in interest (20,000 × 0.5%). A payment of 100 or less never reduces the balance, so =NPER(6%/12,-100,20000) returns #NUM!. Raise the payment.
2. The signs are the same. =NPER(6%/12,500,20000) treats both as money coming in. Excel returns a negative number of periods, about -36.56, which makes no sense. Make the payment negative.
3. A yearly rate with monthly payments. =NPER(6%,-500,20000) treats 6% as the monthly rate, so the interest is 1,200 a month and the result is #NUM!. Divide the yearly rate by 12.
4. The rate typed as 6 instead of 6%. Excel reads 6 as 600%. Type the % sign or use 0.06.

A rate of 0 is fine. NPER simply divides: =NPER(0,-200,2450) returns 12.25, which you round up to 13 payments.

The type argument, and how NPER relates to NPV#

Use type 1 when each payment is made at the start of the period instead of the end. Paying earlier means a little less interest: =NPER(6%/12,-500,20000,0,1) returns about 44.49 instead of 44.74. Most loans charge at the end of the period, so leave type out unless the terms say otherwise.

NPER assumes the same payment every period at one fixed rate. If the amounts change from year to year, NPER can't model them. For uneven cash flows, the NPV function guide shows how to value a series of different amounts. NPER also doesn't include fees, rate changes or missed payments, so treat it as a planning estimate and check the lender's own figures for a real decision.

Practise it#

NPER questions test whether you can match units, set the signs and round the result the right way. Practise relative and absolute references in a live spreadsheet: you type the formula and it checks it instantly. Then try the department budget practice, which combines SUM, AVERAGE, ROUND and locked references the way a finance sheet does, and finish with the finance Excel test when you're ready.