Formuladrill

Financial Updated November 2026

NPER in Excel

NPER(rate, pmt, pv, [fv], [type]) returns the number of periods it takes to pay off a loan, or to reach a savings target, with a fixed payment and a fixed interest rate. Rate and payment must be for the same period, so a yearly rate is divided by 12 for monthly payments. Money you pay out is negative.

Syntax

NPER(rate, pmt, pv, [fv], [type])
PartWhat it is
rateThe interest rate per period. For monthly payments, the yearly rate divided by 12.
pmtThe payment each period. Negative when you pay it.
pvThe present value: the loan balance, or the amount already saved.
fvOptional. The balance wanted at the end. 0 for a loan paid off in full.
typeOptional. 0 for payments at the end of each period (default), 1 for the beginning.

Examples

=NPER(0.18/12, -150, 3000)

Result: About 24.0

Months to clear a 3,000 balance at 18% a year paying 150 a month.

=ROUNDUP(NPER(0.18/12, -150, 3000), 0)

Result: 24

Payments come in whole months, so round up.

=NPER(0.04/12, -200, 0, 10000)

Result: About 46.3

Months of saving 200 at 4% a year to reach 10,000.

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

Result: #NUM!

The first month of interest is 500, so a payment of 500 never reduces the balance.

Common mistakes

  • #NUM! means the payment does not cover the interest, or payment and balance have the same sign.
  • Using the yearly rate with a monthly payment gives far too few periods. Divide the rate by 12.
  • Reading the result as years when the payment is monthly. Divide by 12 for years.
  • Entering the payment as a positive number. Money you pay out is negative.

Practice NPER

These drills check your answer on live data, free, no account needed.

  • Months to clear a balanceHow many monthly payments does it take to clear the card balance in B1, paying the amount in B3 every month at the yearly rate in B2? The last payment may be smaller, so round up to whole months. Answer in B4.
  • Loan installmentCalculate the monthly installment with PMT. Rates and terms are yearly, payments are monthly. For this exercise, round the installment up to a whole amount. Put the answer in B4 as a positive number.
  • Total interest on a loanA loan is repaid monthly over the years in B3 at the yearly rate in B2. Calculate the total interest paid over the life of the loan, as a positive number, in B4.

Questions

How do I calculate how long it takes to pay off a loan in Excel?

=NPER(yearly rate/12, -monthly payment, balance) returns the number of months. Wrap it in ROUNDUP for whole payments.

Why does NPER return #NUM!?

The payment is not larger than the interest for one period, or the payment was entered with the same sign as the balance. Enter the payment as a negative number.

Can NPER be used for savings?

Yes. Put what you have now in pv (0 if nothing), the deposit as a negative pmt, and the target in fv.

Related

  • PMTHow the Excel PMT function calculates the payment on a loan or the deposit for a savings goal, with mortgage and car loan examples and the monthly rate rule.
  • RATEHow RATE works in Excel: find the interest rate per period from the number of payments, the payment and the amount, then turn it into a yearly rate.
  • IPMT and PPMTHow to split a loan payment in Excel: IPMT returns the interest part and PPMT the principal part of any payment, the building blocks of a loan schedule.
  • NPVHow the Excel NPV function discounts future cash flows at a rate, why the initial investment is added outside the function, with a worked example and XNPV.

Previous: CEILING, FLOOR and MROUNDNext: RATE