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 is the interest rate for each period.
- PMT is the fixed payment made in each period.
- PV is the present value of the investment or loan.
- FV FV is the future value of the investment or loan.
- Type is the timing of payment during the period. 0 means payments are made at the end of each period and 1 means 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.
In the case of an investment, PMT, PV, and FV are negative and positive respectively. For a loan, PMT is negative, PV is positive and FV is negative, if only partial repayment is made.
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?
In another example, to find out how many payments are needed to pay off a $100,000 loan with a monthly installment of $700 (principal and interest) and an annual interest rate of 8%, the formula would be =NPER(0.08/12, -700, 100000, 0, 0). The result shows that 459 payments would be needed to pay off the loan.
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
You can also find the number of payments needed to reach a future value, such as in the case of saving $18,000 with monthly payments of $300 and an annual interest rate of 3.5%, with payments made at the beginning of each month. The formula for this scenario would be =NPER(0.035/12, -300, 0, 18000, 1). The result shows that 56 months of payments would be needed to reach the future value.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
