NPER in Excel

• NPER formula is available under the Formulas tab and the Financial functions section.
• NPER in excel is one of the Financial functions in excel. NPER stands for “Number of Periods.” The number of periods required to clear the loan amount at the specified interest rate and set the monthly EMI amount.
• Using the NPER Function in Excel, we can adjust our loan amount based on our savings to clear the EMI amount.

The NPER formula includes rate, PMT, PV, , .

• 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 agreed to complete the loan amount.
• PMT: This is nothing but the monthly payment we have agreed to clear the loan.
• PV: This is the loan amount we are taking.
• : This is not a mandatory argument. FV stands for future value. It is the future value of the loan amount. If we do not mention this argument by default, Excel will treat this as zero.
• : When we are going to make the payment. Whether it is at the beginning of the month or the end of the month, if the payment is at the beginning of the period, we must mention 1 as the argument. If the payment is at the end of the month, we need to say 0. If we ignore this argument, Excel, by default, treats this as zero.

We can explain the best usage of the NPER function in Excel by giving practical examples. Follow this article to look at the live scenarios of using NPER in Excel.

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

After her monthly commitments, she can pay an EMI amount of ₹15,000 per month. The education interest rate on a loan amount is 13.5% per annum.

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

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

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

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

We need to find how monthly she must pay to clear the loan amount.

Apply NPER in Excel in cell B4.

She can clear the loan in approximately 18.56 months.

Let us break down the formula for better understanding.

1. B2/12: In cell B2, we have an interest rate for the loan. Since making monthly payments, we have divided the yearly interest rate by 12.
2. -B3: This is Ms. Karuna's monthly payment 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 ₹15,000 per month, she can clear the loan in 18.56 months.

Mr. John is doing financial planning for his retirement. He plans to make an amount of ₹10,000,000 by investing ₹ 10,000 per month at a fixed interest rate of 14.5% per annum.

Mr. John does not know how long it will take him to make an amount of ₹10,000,000. Therefore, we will help him find the number of months he needs to invest.

Apply NPER function in B4 cell.

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

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

• We can apply the NPER function in Excel to find the number of months to clear the loan.
• The NPER function in Excel assumes the 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!.
• , is not a mandatory argument. If omitted, it will be treated as zero by default.