Nper Formula

[Sofía Goldthwait]

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

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".

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?

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?

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:

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:

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.

Excel NPER function not workingIf 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.
#VALUE! errorOccurs 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.
The result of NPER function is a negative numberUsually, 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!

The Excel PV function is a financial function that returns the present value of an investment. You can use the PV function to get the value in today's dollars of a series of future payments, assuming periodic, constant payments and a constant interest rate.

The Excel RATE function is a financial function that returns the interest rate per period of an annuity. You can use RATE to calculate the periodic interest rate, then multiply as required to derive the annual interest rate. The RATE function calculates by iteration.

The Excel PPMT function can be used to calculate the principal portion of a given loan payment. For example, you can use PPMT to calculate the principal amount of a payment for the first period, the last period, or any period in between.

The Excel IPMT function is a financial function used to calculate the interest payment for a given period of an investment or a loan, based on constant periodic payments and a constant interest rate. For example, you can use IPMT to get the interest amount of a loan payment for the...

The Excel CUMPRINC function is a financial function that returns the cumulative principal paid on a loan between a start period and an end period. You can use CUMPRINC to calculate and verify the total principal paid on a loan, or the principal paid between any two payment periods.

The Excel CUMIPMT function is a financial function that returns the cumulative interest paid on a loan between a start period and an end period. You can use CUMIPMT to determine the total interest paid on a loan, or the interest paid between any two payment periods.

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Learn Excel with high quality video training. Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. Each video comes with its own practice worksheet.

I've been scouring the internet for the better part of two days looking for a JAVA financial library that has functions similar to Excel's, particularly NPER. the closest thing I've come to is the one here:NPER formula, except when I type that into a solver here:math calculator using the following:

I get a different number than what excel gives me. The values I'm using are 3000$ owed, 13% interest, 80$ monthly payment. Excel and calculators online indicate 49 months (48.3 rounded up), but that formula gives me 39.4 months. Does anyone know of a java library I can implement in my code to perform calculations like this?

The arithmetic errors are probably because Java is using Integer math. Integer math completely disregards the decimal point by rounding down the value after each operation. Consider the following code:

You can use NPER to determine the total number of payment periods required to reach a specific investment goal based on a constant payment amount, a fixed interest rate, and the present value of the investment.

The PV function in Google Sheets calculates the present value of a regular payment stream or a lump sum amount, based on a constant interest rate. It is commonly used in financial analysis to determine the value of investments or loans. This function returns a negative value, as it represents money flowing out from the user.

The FV function calculates the future value of an investment based on periodic constant payments and a constant interest rate. It takes into account the present value of the investment, the number of periods in which the payments are made, and the compounding frequency. This formula is commonly used in financial planning and investment analysis.

The PMT formula in Google Sheets is a financial function that calculates the periodic payment required to fully pay off a loan or investment based on a constant interest rate and a fixed number of payments. It is commonly used to determine loan payments, mortgage payments, and annuity payments.

The RATE formula returns the interest rate per period of an annuity. This formula is often used in financial analyses to calculate the rate of return on an investment. It assumes that payments are made at regular intervals and that the interest rate remains constant throughout the duration of the annuity.

Please note that: All loan payments are outgoing payments. Hence, they should be marked negative. Since the period of repayment is monthly, convert the annual interest rate into a monthly interest rate by dividing it by 12. Since future value is 0 for loans and the payments have to be made at month ends, FV and TYPE can be ignored or set to 0.

In this example, I am looking to invest 50,000 USD in a fund that promises a 9% return annually. It also requires an additional recurring monthly contribution of 1,000 USD. Now, my target is to earn 1,000,000 USD out of this investment.

I am eager to find out how long it is going to take to achieve this target.

In this guide, we just saw how the NPER Excel formula works and how to practically use it in financial calculations. I recommend you test this out for yourself to gain a better understanding of this formula.

Ready to dive deep into Excel? Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 100+ IT training courses. Click here for advanced Excel courses with in-depth training modules.

I am new to Javascript and trying to create PDF form which will calculate PV (present value) value for based on fields like Rate, Nper & PMT. Please help me regarding this. I think people with good experience about Javascript can solve this.

Hi, I used search but I could not find PV formula which I can use to get the result. Please supply the formula and code and kindly also help me how to put and where to put the code so it executes. I really don't have Javascript knowledge. Thank you

This is the image which shows what I need. In yellow cell (excel), I wnt this in PDF form and I will enter the data and PDF form which will calculate the data in the other cells. In Column C what is the method required to get the data in column D. Pls help

The code is working but thing is that until I enter the value in the form , it keeps on showing me alert messages which says "the value entered doesnt match the format of the field. How to remove this alert message?

thank you it is working. but now there are other problems as the calculations is based on the field values which are scattered in the form so some of the calculation is not happening. I tried setting calculation order also but it also didnt work well.Do you suggest another method for this?

c80f0f1006