Mohammad Shah Miran
Financial analysts frequently want to know the duration of repayment time while making a business deal. Therefore, if you are a financial analyst, you might wish to learn how many installments are necessary to repay the loan in full before applying for one. However, Excel provides a handy way, the NPER function. to aid your need. So in this article, we are going to discuss how to use the NPER function in Excel with 3 useful examples.
The image attached below is an overview image of the article and depicts the use of the NPER function with 3 different scenarios.
Download Practice Workbook
Additionally, you can download and practice with the dataset that we have used to prepare this article.
Introduction to NPER Function
- Function Objective:
Calculate the total number of periods over which the loan is fixed with a constant interest rate.
- Syntax:
- Arguments Explanation:
| Argument | Required/Optional | Explanation |
|---|---|---|
| rate | Required | The interest rate per period. |
| pmt | Required | The amount that is paid out each period; it cannot alter over the annuity’s lifetime. |
| pv | Required | The Present value of the resources. It could be a lump sum present value of any future transaction which is supposed to be held later. |
| [fv] | Optional | Represents the future value of the present transaction which is estimated based on the time value of the money. |
| [type] | Optional | The number 0 or 1 indicates when the payments are due. |
- Return Parameter:
Period of time to repay the loan.
- Version:
The function is available in all versions Excel 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel 2007.
3 Suitable Examples of Using NPER Function in Excel
Firstly, let’s assume we have a dataset, namely “Loan Specification Offered by ABC Bank”. But, you can use any dataset suitable for you.
Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.
1. Using NPER Function with a Yearly Payment Period
In accordance with our selected dataset, we will evaluate the required numbers of repayment schedules when you are asked to pay it on yearly basis. Since you already had an idea of relevant arguments in the previous section, we will accomplish our task using the NPER function.
📌 Steps:
- Go to cell no C10 >> Write the following formula >> Hit on ENTER key.
=NPER(C6,C7,C8)Here, C6, C7, and C8 represent annual interest rate, yearly loan payment, and amount of loan respectively.
Read More: How to Calculate IRR (Internal Rate of Return) in Excel (8 Ways)
2. Utilizing NPER Function with a Monthly Payment Period
In our second example, we will keep our dataset the same, however, we are asked to find the number of required periods to repay the loan on monthly basis. Similar to our previous example, we will incorporate the NPER function. Following that, we will divide our yearly interest rate by 12 to scale down it.
📌 Steps:
- Go to cell no C10 >> Type the formula given below >> Subsequently, Hit on ENTER key.
=NPER(C6/12,C7,C8)Here, C6, C7, and C8 represent annual interest rate, yearly loan payment, and amount of loan respectively.
Read More: How to Use NOMINAL Function in Excel
3. Employing NPER Function with a Weekly Payment Period
Lastly, if you are subjected to repaying your loan weekly, you might wonder how you will utilize the NPER function. No problem! Follow the instruction described below.
📌 Steps:
- Select cell no C10 >> Insert the formula given below >> Press on ENTER key.
=NPER(C6/56,C7,C8)
Here, C6, C7, and C8 represent annual interest rate, yearly loan payment, and amount of loan respectively.
Read More: XIRR vs IRR in Excel (Differences with Examples)
Things to Remember
- It is worth mentioning that, if you input a positive value as your loan repayment amount, you need to put a negative sign (–) before the pmt argument.
- In addition, an image has been attached below for your better understanding.
- Secondly, the arguments should be numerical values. Otherwise, the function will return the #VALUE!
- Finally, [fv], [type] is not mandatory arguments. Therefore, the NPER function will treat those values as Zero if you don’t input their values.
Practice Section
In addition, we have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it yourself.
Conclusion
Summing up, we have discussed how to use NPER Function in Excel. Since there are plenty of ways to do this task. So before going through a specific method, ensure your chosen method aligns with your work. Further, If you have any queries, feel free to comment below and we will get back to you soon. Also, you may follow our website, ExcelDemy, a one-stop Excel solution provider to explore more.
