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 is available under the Formula tab and under 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 specified monthly EMI amount.
- Using NPER Function Excel we can adjust our loan amount based on our savings to clear the EMI amount.
NPER Formula Excel
The NPER formula includes Rate, PMT, PV, [fv], [type].
- RATE: When we take loan it does not comes at a 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 the 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 NPER Function in Excel?
The best usage of 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.
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. 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 estimated loan clearance date.
In cell B1 we have loan amount taken by Ms. Karuna.
In cell B2 we have interest rate per anum.
In cell B3 we have how much she can pay monthly to clear the loan.
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.
She can clear the loan in approximately 18.56 months.
Let me break down the formula for better understanding.
- B2/12: means in the cell B2 we have interest rate for the loan. Since we are making the payment monthly I have divided the yearly interest rate by 12.
- -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.
- 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 making the 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.
Apply NPER function in B4 cell.
- B1/12: This is the interest Mr. John gets annually. Since he is making investment monthly I have divided it by 12.
- 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.
- -B2: This is the initial investment he is making. Since this is the outflow should mention in a negative number.
- 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.
This has been a guide to NPER in Excel. Here we discuss the NPER Formula in excel and how to use NPER function in Excel along with excel example and downloadable excel templates. You may learn more about excel functions from the following articles –