NPER function

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

NPER function calculates number of periods for an investment or a loan with the assumption of constant payments at regular intervals and a fixed interest rate. 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.

 

Example 1 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

 

Example 2 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

 

Example 3 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