How to use NPER function in Excel?

In this lesson, you can learn how to use NPER function in Excel.

The NPER function is similar to the Future Value and Present Value functions. The difference, however, is that the NPER formula will return the investment amount using periodic, constant payments and interest. The NPER function calculates the number of periods for an investment or loan, assuming constant payments at regular intervals and a constant interest rate.

Syntax for the Nper function

Syntax is:

=NPER(rate,PMT,PV,FV,[Type])

  • Rate - the rate of interest for the period
  • PMT - fixed payment payed in each period,
  • PV - present value of the investment or loan,
  • FV - future value of an investment or loan (the value you want to achieve at the end of all periods)
  • Type - specifies the time to make a payment during the period: 0 - payments are made at the ends of periods, 1 - payments are made at the beginning of each period.

The rate parameter should be provided for the payment period, ie if the interest rate is given annually and monthly payments are then divide the interest rate for 12 months.

For investment parameters PMT, PV and FV are negative positive, while for the credit PMT - negative PV - positive, FV - negative (if calculations are made assuming a partial repayment of a loan.

Nper function examples

Basic calculation of NPER

How many payments you must pay to reach a value of 1 000 000 $ for investment with an annual interest rate of 10%? You pay monthly payments in the amount of 1 000 $, having already set aside 150 000 $. You pay at the end of each period (month).

Formula is =NPER(0.10/12,-1000,-150000,1000000,0)

Answer: Result is 171,43 what means, that you need 172 payments to reach 1 000 000$

Explanation:

  • 0.10/12 - rate is 10% what must be divided by count of months
  • -1000 - monthly payment (with minus because you have to pay)
  • -150000 - your current cash (with minus because you pay)
  • 1000000 - your future value (without minus because it's your cash)
  • 0 - because you pay at the end of the period

 

How much do you need to pay off your loan?

How many payments you need to pay off 100 000 $ loan repaid at the end of the month the installment of 700 $ (principal + interest). Annual interest rate is 8%.

Formula is =NPER(0.08/12,-700,100000,0,0)

Answer: Result is 458,2 what means, that you need 459 payments to pay off this loan.

Explanation:

  • 0.08/12 - rate is 8% what must be divided by count of months
  • -700 - monthly payment (with minus because you have to pay)
  • 150000 - your current cash (without minus because it's your cash)
  • 0 - your future value because you want to pay off this debt
  • 0 - because you pay at the end of the period

 

NPER when you pay at the beginning of the month

How many months you have to pay to the bank at $300 to save the amount of $18 000. Annual interest rate is 3,5%. You pay at the beginning of each month.

Formula is =NPER(0.035/12,-300,0,18000,1)

Answer: Result is 55,22 what means, that you need 56 months to reach $18 000.

Explanation:

  • 0.035/12 - rate is 3.5% what must be divided by count of months
  • -300 - monthly payment (with minus because you have to pay)
  • 0 - your current cash
  • 18 000 - your future value (without minus because it's your cash)
  • 1 - because you pay at the beginning of the period

Simple NPER Formula

The example is based on finding a solid NPER of the investment that we are about to make, so we can fully comprehend how the whole situation would work. This is something that would work with a full understanding of the formula, and we are getting the answer that we strive after with the formula.

Simple NPER Formula

Complete NPER Formula

The investment is going quite well and we need to know about the future value. This is a continuation of the previous example and requires you to compare the full formula with the previous example and we will use the same data as in the previous one so that you can see the differences between the results.

An example is getting to know the benefits of our investments and how it will affect the way we plan the implementation of additional plans.

Complete NPER Formula

NPER Formula with Bigger than Symbol

The formula in this example uses a math symbol that will be beneficial in the long run. You can know how it plays out when the stakes are higher than 10 percent. This example explains the possibility of having solid information about the number of investments when the rate contains such a mathematical symbol. One great thing about this formula is that it gives you the opportunity to know exactly how a math symbol affects the result.

NPER Formula with Bigger than Symbol

Usage of Formula with Future Value Increment

The formula is going to have a very specific formula that also include the increment of a future value. In this example, we would like to know how many times we needs to make an investment when we expect the future value to increase by 50%, and how it would affect the business deal and its overall performance.

Usage of Formula with Future Value Increment

A Formula with both NPER & AND

This example is a complete understanding of the formula, and we are going to use both NPER & AND for successfulness of acknowledging the information about how many times we will be making an investment when using two formulas simultaneously.

A Formula with both NPER  AND

Additional Percentage with AND

In this example, we want to add a few percent to the current value in circumstances where the company needs to add 12% to the current value because we have accepted a custom contract with a company that asks for an interest rate of 12 percent from the current value. At this point, the NPER formula would be an excellent formula for how many times a company needs to add an investment to its overall performance.

Additional Percentage with AND

NPER with Texts

The example is about clarifying that under the circumstances that we have already labeled the cells with texts, to make it more understanding for us to understand which part of the formula the cell is talking about, even after some time has passed by, and we might have forgotten about what part of parameters belong to where in the NPER formula.

NPER with Texts

Simultaneously Using NPER with SUM

This example is using both the NPER and OR formulas simultaneously to find out how many times the company needs to perform the investments for it to successfully achieve a goal that has been set for the business. We have set a flexible rates for the business, and we would like to know how we would be making the investments.

Simultaneously Using NPER with SUM

Numeric NPER Formula

This is a more complicated formula, and we need to have a numeric formula of the NPER formula to get the answer that we are looking for with the formula. We want to make some evaluations, which is why we are rather having this kind of formula.

Numeric NPER Formula

Understanding Usage of NPER Formula with another Spreadsheet

There are many different reasons that you'd might needs to have the NPER formula on another spreadsheet. But in this example, we are using a different spreadsheet, because we would like to have a full understanding of how many times we need to perform the investment for it to yield us the estimated future value, and we are using a different spreadsheet for it.

Understanding Usage of NPER Formula with another Spreadsheet