by Svetlana Cheusheva, updated on
In this tutorial, you will learn: What does nper mean in Excel? How to use the NPER function to calculate the number of periods required to pay off a loan or the number of investments needed to earn the target amount? And more!
When building corporate funds, financial analysts often wish to know how long it will take to reach the desired corpus. When applying for a loan, you may want to find out how many payments are required to repay it in full. For such tasks, Excel provides the NPER function, which stands for "number of periods".
NPER is an Excel financial function that calculates the number of payment periods for a loan or investment based on equal periodic payments and a constant interest rate.
The function is available in all versions Excel 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 and Excel 2007.
The syntax is as follows:
Where:
To effectively use the Excel NPER function in your worksheets and avoid common mistakes, please remember the following things:
Knowing the theory, let's build an NPER formula in its simplest form to get the number of periods to clear the loan based on the following data:
Putting the arguments together, we get this simple formula:
=NPER(C2, C3, C4)
The optional [fv] and [type] arguments are omitted because the future value is irrelevant in this case, and the payments are due at the end of the year, which is the default type.
Assuming the $10,000 loan is given to you at an 8% annual interest rate, and you will pay $3,800 to the bank each year, the formula shows that you will have to make 3 yearly payments to pay back the loan amount.
Please notice that pmt is a negative number because it is outgoing cash.
If you enter a periodic payment as a positive number, then put the minus sign before the pmt argument directly in the formula:
=NPER(C2, -C3, C4)
Below, you will find a few more examples of Excel NPER formula that show how to calculate the number of payment periods for different scenarios.
Most mortgage and other long-term loans are paid in monthly installments. Some other are paid quarterly or semiannually. The question is - how do you find the number of periodic payments required to pay back the total loan amount?
The key is to convert an annual interest rate to a periodic rate. For this, simply divide the yearly rate by the number of periods per year:
Monthly payments: rate = annual interest rate / 12
Quarterly payments: rate = annual interest rate / 4
Semiannual payments: rate = annual interest rate / 2
Let's assume you borrowed $10,000 at an 8% annual interest rate, with a monthly payment of $500.
We start with entering the data in the corresponding cells:
To get a correct value for the rate argument, divide C2 by 12. And you'll come up with the following formula to calculate the number of monthly payments on the loan:
=NPER(C2/12, C3, C4)
The result shows that it will take 22 months (or 1 year and 10 months) to pay off the loan:
If you make quarterly payments, use C2/4 for rate to force NPER to return periods in quarters:
=NPER(C2/4, C3, C4)
Alternatively, you can enter the number of periods per year in a separate cell (C5) and divide the annual interest rate (C2) by that cell:
=NPER(C2/C5, C3, C4)
When investing or saving money, you generally know how much you'd like to earn at the end of the term. In this case, you can include the desired future value (fv) in the formula.
Suppose you wish to invest $1,000 at an annual interest rate of 3% and make an additional contribution of $500 at the end of each month. Your goal is to reach $10,000.
To find the number of monthly investments, input the following data in predefined cells:
Enter this formula in C8:
=NPER(C2/C6, C3, C4, C5)
And you will see that it takes 18 monthly investments to reach the target amount:
Please notice that:
Handling specific cases was easy, wasn't it? How about building a general formula that does a perfect job of calculating NPER in Excel whether you are borrowing or saving and no matter how often you make payments? For this, we will be using the NPER function in its full form and allocate cells for all the arguments, including the optional ones. In our case, that will be:
Let's say you wish to invest initial $10,000 at an annual interest rate of 5%, and then make an additional payment of $1,000 at the beginning of each quarter, aiming to reach $50,000. How many quarterly payments you will have to make?
To get the correct periodic rate, we divide the annual interest rate (C2) by the number of periods per year (C7). For the other arguments, simply supply the corresponding cell references:
=NPER(C2/C7, C3, C4, C5, C6)
Tips:
In the above examples, the calculated number of periods is displayed as an integer because the formula cell is formatted to show no decimal places. If you format the cell to show one or more decimal places, you will see that the result of NPER is actually a decimal number:
In real life, however, there can be no decimal periods. In such situations, the last payment will just be lower than in previous periods.
To get the correct number of required periods, you can wrap your NPER formula in the ROUNDUP function that rounds the value upward to a specified number of digits. Since we want the result to be an integer, we set the num_digits argument to zero:
With the annual interest rate in C2, payment amount in C3 and loan amount in C4, the formula takes this form:
=ROUNDUP(NPER(C2, C3, C4), 0)
As you see, the full number of periods is 4, and not 3 as you may think looking at the above screenshot. You will just have to pay a very small amount in the 4th year.
If your NPER formula returns an error or a wrong result, most likely that will be one of the following.
Occurs if the specified future value (fv) can never be achieved with a given interest rate (rate) and payment (pmt). To get a valid result, try to increase a periodic interest rate and/or a payment amount.
Occurs if any argument is non-numeric. To fix the error, make sure that no numbers used in a formula are formatted as text. For more information, please see How to convert text to number.
Usually, this problem occurs when outgoing payments are represented by positive numbers. For example, when calculating payment periods for a loan, supply the loan amount (pv) as a positive number and the periodic payment (pmt) as a negative number. When investing or saving money, the initial investment (pv) and periodic payment (pmt) should both be negative and the future value (fv) positive.
That's how to how to find NPER in Excel. I thank you for reading and hope to see you on our blog next week!
Examples of NPER formula in Excel (.xlsx file)
Table of contents