Excel NPER Function

NPER calculates the number of periods needed to repay a loan or reach a savings goal, assuming a fixed interest rate and equal periodic payments.

Use it to find how long a repayment plan will take or how many regular deposits you need to reach a target balance.

1
Set the rate and fixed payment Use a rate per period that matches the payment interval.
2
Enter the starting and ending amounts Specify the amount received today and the balance to leave or accumulate at the end. Choose whether payments occur at the beginning or end of each period.
3
Calculate how many periods are needed NPER accounts for interest to find the period count that connects the starting amount, fixed payments, and target balance.

NPER syntax & arguments

Syntax

=NPER(rate, pmt, pv, [fv], [type])
Required Optional
  1. 1

    rate

    Required

    The constant interest or discount rate per period. Use an annual rate for annual cash flows or a monthly rate for monthly cash flows. To convert an annual rate to a monthly rate, divide by 12 if it is a nominal annual rate compounded monthly. If it is an effective annual rate, use (1+annual_rate)^(1/12)-1 to find the equivalent monthly rate.

  2. 2

    pmt

    Required

    The fixed payment each period. Use a negative amount for money paid out, or 0 when growing only an initial lump sum.

  3. 3

    pv

    Required

    The balance or lump sum at time zero. Money received is positive and money paid out is negative.

  4. 4

    fv

    Optional

    The balance or lump sum at the end of the final period. Money received is positive and money paid out is negative. Defaults to 0.

  5. 5

    type

    Optional

    When periodic payments occur. This changes payment timing, not the dates of pv or fv.

    • 0: Payments at the end of each period. This is the default when the argument is omitted.
    • 1: Payments at the beginning of each period.

Example

=NPER(10%,-1100,2100,0,1)

Return the number of years needed to repay a 2100 loan with 1100 paid at the beginning of each year, given a constant annual interest rate of 10% and a final balance of 0.

NPER caveats

  • What positive and negative amounts mean

    The sign of pv describes the starting amount: positive for money you receive, such as loan proceeds, and negative for money you pay out, such as an initial savings deposit. The sign of pmt describes each periodic payment: positive for money received and negative for money paid out.

    The sign of fv describes the ending amount: positive for money you’ll receive or have available, and negative for money you’ll owe or pay out. An fv of 0 means no ending amount.

  • Rate and payments must use matching periods

    For monthly payments, use the monthly payment amount for pmt and a monthly interest rate for rate. If the annual rate is nominal 6% compounded monthly, use 6%/12. If it is an effective annual rate of 6%, use (1+6%)^(1/12)-1 instead. Entering 6% directly would apply 6% every month.

  • Payment timing changes the period count

    Omitted type or 0 means end-of-period payments; 1 means beginning-of-period payments. Paying earlier can reduce the number of periods needed at a positive rate. The pv amount stays at time zero and fv stays at the end of the final period.

  • The result can include a fraction of a period

    NPER does not round to a whole number of payments. For example, =NPER(0,-300,1000) returns about 3.33 periods. With payments on fixed dates, this loan needs a fourth payment with its amount adjusted to repay the remaining balance.

  • Payments and rate stay constant

    NPER assumes equally spaced, equal payments and one rate for all periods. A changing rate, irregular payment dates, or extra repayments need to be modeled separately.

  • Zero interest makes the calculation simpler

    At rate=0, NPER returns -(pv+fv)/pmt. For example, =NPER(0,-500,2000) returns 4 periods, regardless of payment timing. If pmt is also 0, the calculation divides by zero and returns #DIV/0!.

  • The payment must be enough to repay the loan

    For a loan repaid in full with end-of-period payments and a positive rate, the payment must exceed the interest charged each period so the balance can fall. For example, =NPER(10%,-100,2000) returns #NUM!: each payment is less than the first period’s interest of 200, so the loan balance grows.

  • A lump sum needs a zero payment

    To find how long an initial deposit takes to grow to a target balance without further deposits, enter 0 for pmt and opposite signs for pv and fv. For example, =NPER(10%,0,-1000,1210) returns 2 periods. Putting the initial deposit in pmt would repeat it every period.

Comparing repayment plans? Use PMT to find the fixed payment for a chosen period count, or FV to check the balance after a set number of periods.

Intro NPER practice problem

Solve the intro problem directly here, or open it on its own page.

Open full problem

Advanced NPER practice problems

Use NPER alongside other Excel functions in realistic, less-prescriptive challenges.

No advanced NPER problems are currently available.