NPER in Excel

NPER is also known as the number of payment periods for a loan taken, it is a financial term and in excel we have an inbuilt financial function to calculate NPER value for any loan, this formula takes rate, payment made, present value and future value as input from a user, this formula can be accessed from the formula tab or we can type =NPER().

NPER Function in Excel

NPER Formula Excel

NPER Formula in Excel

The NPER formula includes Rate, PMT, PV, [fv], [type].

  • RATE: When we take a loan, it does not come free of cost. We need to pay the interest amount to clear the loan. The rate is the interest rate we have agreed to clear the loan amount.
  • PMT: This nothing but the monthly payment we have agreed to clear the loan.
  • PV: This is the loan amount we are taking.
  • [fv]: This is not a mandatory argument. FV stands for future value. This is the future value of the loan amount. If you do not mention this argument by default, Excel will treat this as zero.
  • [type]: When we are going to make the payment. Whether it is at the beginning of the month or at the end of the month, if the payment is at the beginning of the period, we need to mention 1 as the argument, and if the payment is at the end of the month, we need to mention 0 as the argument. If we ignore this argument, Excel, by default, treats this as zero.

How to Use the NPER Function in Excel?

The best usage of the NPER function in excel can be explained by giving practical examples. Follow this article to look at the live scenarios of using NPER in excel.

You can download this NPER Function Excel Template here – NPER Function Excel Template

NPER in Excel – Example #1

Ms. Karuna started to work in a corporate company in Bangalore. She took a loan of Rs. 250,000 for her studies. She joined the company at a remuneration of Rs. 40,000 per month.

After all her monthly commitments, she can pay an EMI amount of Rs. 15,000  per month. The education loan amount interest rate is 13.5% per anum.

She has no idea in how many months she can clear the loan. We will help her in solving this and give her an estimated loan clearance date.

NPER funcrion example 1

In cell B1, we have the loan amount taken by Ms. Karuna.

NPER funcrion example 1-1

In cell B2, we have an interest rate per anum.

NPER funcrion example 1-2

In cell B3, we have how much she can pay monthly to clear the loan.

NPER funcrion example 1-3

Now we need to find how many monthly she needs to pay to clear the loan amount.

Apply NPER in excel in the cell B4.

NPER funcrion example 1-4

She can clear the loan in approximately 18.56 months.

Let me break down the formula for better understanding.

NPER funcrion example 1-5

  1. B2/12: means in the cell B2, we have an interest rate for the loan. Since we are making the payment monthly, I have divided the yearly interest rate by 12.
  2. -B3: this is the monthly payment Ms. Karuna is paying to clear the loan. Since it is a cash outflow, we need to mention this as a negative number.
  3. B1: this is the loan Ms. Karuna has taken for her educational purpose.

Therefore, by paying a monthly EMI of Rs. 15,000 per month, she can clear the loan in 18.56 months.

NPER in Excel – Example #2

Mr. John is doing financial planning for his retirement. His plan is to make an amount of Rs. 10,000,000 by investing Rs. 10,000 per month at a fixed interest rate of 14.5% per anum.

Mr. John does not know how long it is going to take for him to make an amount of Rs. 10,000,000. We will help him out to find the number of months he needs to invest.

NPER funcrion example 2

Apply NPER function in B4 cell.

NPER funcrion example 2-1

NPER funcrion example 2-2

  1. B1/12: This is the interest Mr. John gets annually. Since he is making an investment monthly, I have divided it by 12.
  2. 0: This is the PMT he has to make. Here one this, we need to remember he is not clearing any loan rather investing to accumulate the money.
  3. -B2: This is the initial investment he is making. Since this is the outflow should mention in a negative number.
  4. B3: This is the future value Mr. John targeting

So Mr. John has to invest for 575.12 months to get a lump sum amount of Rs. 10,000,000. So, Mr. John has to invest for 47.93 years (575.12/12).

Things to Remember about NPER Function in Excel

  • NPER Function in excel can be applied to find the number of months to clear the loan.
  • NPER Function in excel assumes standard interest rate, PMT.
  • All the outgoing payments should be supplied as negative numbers.
  • All the arguments should be numerical values. If any non-numerical value is found, it will return the result as #VALUE!.
  • [fv], [type] is not a mandatory argument. If omitted, it will treat as zero by default.

Recommended Articles

This has been a guide to NPER in Excel. Here we discuss the NPER Formula in excel and how to use the NPER function in Excel along with excel example and downloadable excel templates. You may learn more about excel functions from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion