How To Use NPER Function in Excel

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 NPER function.

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?

With the 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 NPER function. 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.

 

example of NPER Function in Excel

To get the value in cell B7, we just need to use the following formula:

=NPER(B1/12,B2,B3,B4,B5)

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.

 

using NPER function in Excel to calculate loan payments

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 NPER function:

  1. First, select the cell that you plan on holding the result of the NPER function. In this example, we’ll use cell B7.
  2. Next, we just simply type the equal sign ‘=‘ to start the function, followed by the string ‘NPER(‘.
     type in the keyword associated with the function
  3. 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.
     divide the annual interest by 12 to get monthly interest
  4. 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.
    plug in the other arguments of the NPER function in Excel
  5. 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.
    Hit the Return key to evaluate
  6. If you wish to return a whole number, you can use the ROUNDUP function to round up the value to the nearest integer.
    use the ROUNDUP formula to get a whole number result from NPER function in Excel

 

 

Frequently Asked Questions (FAQ)

  1. 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 NPER function will return a #NUM! error.
  2. 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. 

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

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published.

You May Also Like