Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use NPER Function in Excel (3 Relevant Examples)

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.

Overview of Excel NPER function


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:

Introduction to the NPER function in excel

     =NPER( rate, pmt, pv, [fv], [type] )
  • 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.

Dataset for utilizing NPER function in Excel

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.

Yearly repayment calculation

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.

Monthly repayment calculation

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.

Similar to our previous example we scale down our yearly interest rate to weekly by dividing it by 56. Since, we all know a year consists of 56 weeks.

Weekly repayment calculation

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.

Input a negative sign before the pmt argument in the NPER function

  • 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.

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.


Related Articles

Mohammad Shah Miran

Mohammad Shah Miran

Miran is a highly motivated individual with a strong educational background in engineering. He is interested in technology and passionate about creating engaging and informative content. After graduation, Miran decided to pursue a career in content development and has been working in the field for some time. Miran is dedicated to staying current with the latest trends and technologies and is eager to continue learning and growing as a professional.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo