Site icon Best Excel Tutorial

How to Use NPER Function in Excel

Simple NPER Formula

Simple NPER Formula

In this lesson, you can learn how to use NPER function in Excel.

The NPER function is similar to the Future Value and Present Value functions. The difference, however, is that the NPER formula will return the investment amount using periodic, constant payments and interest. The NPER function calculates the number of periods for an investment or loan, assuming constant payments at regular intervals and a constant interest rate.

Syntax for the Nper function

Syntax is:


The rate parameter should be provided for the payment period, ie if the interest rate is given annually and monthly payments are then divide the interest rate for 12 months.

In the case of an investment, PMT, PV, and FV are negative and positive respectively. For a loan, PMT is negative, PV is positive and FV is negative, if only partial repayment is made.

How NPER works

NPER works by using a trial and error method to find the number of periods required to pay off the loan. It starts with the guess value you provide and then iteratively increases or decreases the number of periods until the present value reaches the future value.

The optional guess value can be helpful if you have a good estimate of the number of periods required. If you don’t provide a guess value, Excel will use a default value of 10.

Nper function examples

Here are some examples of how to use NPER:

Basic calculation of NPER

How many payments you must pay to reach a value of 1 000 000 $ for investment with an annual interest rate of 10%? You pay monthly payments in the amount of 1 000 $, having already set aside 150 000 $. You pay at the end of each period (month).

Formula is =NPER(0.10/12,-1000,-150000,1000000,0)

Answer: Result is 171,43 what means, that you need 172 payments to reach 1 000 000$


How much do you need to pay off your loan?

In another example, to find out how many payments are needed to pay off a $100,000 loan with a monthly installment of $700 (principal and interest) and an annual interest rate of 8%, the formula would be =NPER(0.08/12, -700, 100000, 0, 0). The result shows that 459 payments would be needed to pay off the loan.

Answer: Result is 458,2 what means, that you need 459 payments to pay off this loan.


NPER when you pay at the beginning of the month

You can also find the number of payments needed to reach a future value, such as in the case of saving $18,000 with monthly payments of $300 and an annual interest rate of 3.5%, with payments made at the beginning of each month. The formula for this scenario would be =NPER(0.035/12, -300, 0, 18000, 1). The result shows that 56 months of payments would be needed to reach the future value.

Answer: Result is 55,22 what means, that you need 56 months to reach $18 000.


Exit mobile version