The NPER function in Excel is useful when you need to find out the number of periods for an investment based on periodic constant payments and a constant interest rate.
You can use the NPER function to find out how long it will take to pay back a loan or reach a certain investment target.
The rules for using the
NPER function in Excel are as follows:
- The function requires an interest rate, the monthly payment amount, and the present value of the investment. Users also have the option to specify a future value.
- The function then outputs the number of periods it will take to pay off the loan or investment.
Let’s take a look at a quick example where we can use the
Suppose you would like to invest $1,000 at an annual interest rate of 5%. Each month you will make an additional contribution of $500 each month. Your goal is to reach $10,000 eventually.
How many monthly investments will this take?
NPER function, it becomes quite easy to solve for this value. Outside of investments, you can also use the
NPER function to compute how long it will take to pay off a loan with a constant interest rate.
Now that we know when to use the
NPER function, let’s learn how to use it and work on an actual sample spreadsheet.
The Anatomy of the NPER Function
The syntax of the
NPER function is as follows:
=NPER(rate, pmt, pv, [fv], [type])
Let’s dissect this formula and understand what each of these terms means:
- = the equal sign is how we must start all functions in Excel.
- NPER() is our
NPERfunction. It returns the number of periods in an investment given a constant interest rate and the payment amount.
- Rate refers to the interest rate per period
- Pmt refers to the payment made for each period. It must be constant. The pmt usually contains the principal and interest and no other fees.
- Pv refers to the present value or the lump-sum amount in the future after a series of payments.
- Fv is an optional value that specifies the cash balance you want to attain after the last payment is made.
- Type refers to the date when payments are due. A value of 0 indicates that payments are due at the end of the period while a value of 1 indicates that payments are due at the beginning of the period.
- If Fv is not specified, then the formula assumes zero.
A Real Example of Using NPER Function
Let’s look into a real example of the
NPER function used in an Excel spreadsheet.
The table below gives a few details of our investment, such as the annual interest rate and the monthly payment amount. We’ve used the
NPER function to return the number of months it will take to reach the future target value.
To get the value in cell B7, we just need to use the following formula:
In this second example, we’ve used the
NPER to answer how long it will take in years to repay a loan. We only need to specify the annual interest rate, the yearly payment, and the amount loaned.
You can make your own copy of the spreadsheet above using the link attached below.
If you’re ready to try out the
NPER function in Excel, let’s begin writing it ourselves!
How to Use NPER Function in Excel
This section will guide you through each step needed to start using the
NPER function in Excel. You’ll learn how we can use this function to calculate how long it will take to reach a future investment goal.
In this guide, we’ll look into how long it will take to reach a value of $20,000 from an investment with an annual interest rate of 15%. We’ve specified a monthly payment of $500 and an initial investment of $2000.
Follow these steps to start using the
- First, select the cell that you plan on holding the result of the
NPERfunction. In this example, we’ll use cell B7.
- Next, we just simply type the equal sign ‘=‘ to start the function, followed by the string ‘NPER(‘.
- Our first argument is the monthly rate of the investment. Since we were given the annual rate, we can simply divide this number by 12.
- Next, we must fill in the rest of
NPER’s arguments. Cell B2 contains the amount for each monthly payment, and Cell C2 contains our initial deposit. These values are negative since they are outgoing payments. Since we have a target of $20,000, we must fill in the optional 4th argument. Lastly, we’ve specified that payments are due at the end of the period.
- Once all arguments are set, the user can now hit the Enter key to evaluate the function. In this example, we’ve found out that it would take 28.712 periods to reach our goal of $20,000.
- If you wish to return a whole number, you can use the
ROUNDUPfunction to round up the value to the nearest integer.
Frequently Asked Questions (FAQ)
- Why does my formula return a #NUM! error?
If the specified future value is impossible to achieve with the provided interest rate and payment, then the
NPERfunction will return a #NUM! error.
- Why does my formula return a negative number?
A negative number of periods may occur when outgoing payments are represented with positive numbers. Remember to use positive numbers for inflows and negative numbers for outflows.
You should now understand how we can use the
NPER function in Excel. This step-by-step guide shows how easy it is to calculate the number of periods needed to pay a loan or reach an investment goal.
NPER function is just one example of a financial function available in Microsoft Excel. With so many other Excel functions out there for you to use, you will certainly find a ton that can help you create the perfect spreadsheet.
Are you interested in learning more about what Excel can do?
Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.