NPER Function - Calculate Number of Periods Needed for a Set of Payments in Excel

How to calculate the number of periods required for an investment in order to get the desired return. 

The number of periods required for the investment tells you how long the investment must last.  This is based on a constant interest rate, a present value, and an optional future value.

Syntax

[] means the argument is optional

The syntax is rather simple and there are only three required arguments, the rate, pmt, and pv; let's take a look at some examples now.

Example 1 - Basic Loan Number of Payments Calculation

Let's get the number of payments/periods required for a very basic situation.

We have an interest rate, a payment amount, and a present value.  This is essentially like saying that we have a loan of a certain amount and we know the payment amount and rate but we don't know for how long we need to make the payments until it is paid-off.

For a loan that is .83% per period with $1,000 payments per period and a present value, or total loan amount, of $100,000, we will be paying this loan for 214.37 periods.

For this problem, we only needed to use three arguments in the function: rate, pmt, and pv.

Note the negative sign in front of the Pmt argument; that must always be negative. 

However, there is NOT a negative sign in front of the Pv argument in this example.  That is not always the case and depends entirely on the situation.  This is positive if you are performing a calculation for a loan and is negative if you are calculating a savings related problem.

Example 2 - Basic Savings Calculation Problem

Let's say that you have $5,000 saved now and that it pays 6% per year, compounded monthly.  You can pay $100 per month toward that amount and want to save $10,000.  Now, let's calculate how long that will take us.

This is what it would look like:

Look at the function here:

Notice that we now have used the Fv, or future value, argument.  Also, we put a negative sign in front of the Pmt argument AND the Pv argument. 

As mentioned in the last example, put a negative sign in front of the Pv argument basically when you are doing a savings related problem.

Also, it's important to note that the interest rate used is .50% and not 6%.  This is because all of the periods need to be in alignment and we are making monthly payments and the interest is calculated on a monthly basis, compounded monthly, and, as such, we need to divide the 6% by 12, for the number of months in the year, and that gives us .50%.

The basic thing to remember is that you need to have the rate and payment used for the calculation in the same time period, be it a month, quarter, year, or whatever.

You can divide the 6% in your head, like I did, or in the worksheet, or even in the function if you like.

Using the Type Argument

If you need to use the Type argument, you fill it in just like any other argument.

If you need to make the future value 0 or leave it empty, while keeping the Type argument, that is no problem and you would do that like one of the following two examples.

Note on Periods

When you are calculating the number of periods, you need to make sure that you keep period calculations consistent.

If there are two periods in a year but you have an interest rate that is a yearly rate, you need to divide that rate by two so that you have the correct rate for the period.  A period does NOT mean a year; it can be any segment of time.

If there are two periods in a year but you have a payment amount that is a yearly rate, you need to divide that rate by two so that you have the correct amount for the period.

Getting a Whole Number for the Number of Periods

In the examples above, we do not get whole numbers for the number of periods required to finish making our payments.  It looks silly if you use one of these decimal numbers and, in real life, the last month's payment will simply be a lower amount than those previous.

To get the full number of periods required, we use the ROUNDUP function.

In the examples above, we would use this exact function:

B8 is the cell that contains the number we want to round and 0 is to how many decimal places we want to round.

To get a better understanding of how to do this, check out our tutorial on how to round numbers in Excel.

Notes

Make sure to always put a negative sign in front of the PMT argument.

For the PV argument it depends on the type of problem that you are trying to solve.  For example, a savings related problem will require the PV argument to be negative whereas a loan payment problem will require a positive PV argument.

Make sure to download the sample file that accompanies this worksheet so you can play around with the examples listed above.