Excel NPER FunctionStock analysts or financial admin often want to know how long it will take to obtain the required output when raising corporate funds. In normal banking as well, one may want to determine the total payments to repay while applying for a loan. Mostly the loans or mortgages are paid in monthly installments. However, some people prefer to pay them in quarterly or semiannually installments. Before applying for a loan, it is good to calculate the number of required periodic payments to return the entire loan amount. Now, the question arises how one case easily find the number of periodic payments? To cater to these requirements, Excel provides an inbuilt function, the NPER function, which stands for "number of periods". In this tutorial, we will cover the brief insight of NPER function, including its definition, the steps to calculate the number of periods to return the loan, or the number of investments needed to reach the target amount, various examples, situations where NPER may not work in Excel and more! What is NPER function?The Excel NPER calculates the number of payments required based on the fixed interest rate, constant periodic payments. The NPER function is a financial function in Excel used to return the number of periods for an investment or bank loan. There are many uses of the NPER function, such as it assists in fetching the number of payment periods for a loan, for a specified amount, the rate of interest, and the periodic payment amount. The function was introduced with Excel 2007 and since then is available in all versions including Excel 2010,Excel 2013, Excel 2016, Excel 2017 and Excel 365. SyntaxParameterRate (required) This argument represents the rate of interest per period. pmt (required) This argument represents the payment made for each period. pv (required) This argument represents the current value, or total value of all payments now. fv [optional]: This argument represents the future value, or a cash balance the user want after the last payment is made. Since it is an optional parameter therefore its default value is 0. type [optional]: This argument provides the information regarding when payments are due.
Points to Remember about NPER FunctionKindly have a look at the below given point to efficiently utilize the NPER function in your worksheets and bypass common mistakes in Excel:
ExamplesExample 1: Calculate the number of periodic payments for a loan using NPER function for the below given values.To determine the periodic rate payment follow the below given steps: Step 1: Add helper row at the bottom of table Put your cursor below the table and add your helper row. In this row we will type the NPER formula and will fetch the periodic payment for the given data. Refer to the below image: Step 2: Enter the NPER formula Move to next cell of your helper row and start typing the formula = NPER( It will look similar to the below image: STEP 3: Add the parameter to your formula
The overall formula will look similar to the below image: STEP 4: NPER will return the output Press the enter button as soon as you are done typing the formula. Excel will return the output for your NPER formula. As shown below it will return 59 as an output of your periodic payment. Therefore, we can conclude that ABC will have to make 59 monthly payments of $1,500. Example 2: Suppose you want to invest an amount of $2,000 at an annual interest rate of 4% and make an extra contribution of $100 at the end of every month. Your aim is to reach at $20,000. Calculate the number of monthly investments including the given future value (fv) in the NPER formula.NOTE: In the above table the inputs pmt and pv are negative numbers (represented by red text color) because they denote an outflow.To determine the number of monthly investments follow the below given steps: Step 1: Add helper row at the bottom of table Put your cursor below the table and type the heading of your helper row i.e., "NPER". In this row we will type the NPER formula and will fetch the periodic payment for the given data. Refer to the below image: Step 2: Enter the NPER formula Move to next cell of your helper row and start typing the formula = NPER( It will look similar to the below image: STEP 3: Add the parameter to your formula
The overall formula will look similar to the below image: STEP 4: NPER will return the output Press the enter button as soon as you are done typing the formula. Excel will return the output for your NPER formula. As shown below it will return 33.6 as an output of your periodic payment. Therefore, we can conclude that it will take ABC 33.6 monthly investments to reach the target amount. Example 3: In the below table, few of the arguments are nonnumeric. Let's see what happens if you specify nonnumeric arguments in NPER formula.To fetch the NPER output for the above table follow the below given steps: Step 1: Add helper row at the bottom of table Put your cursor below the table and add your helper row. In this row we will type the NPER formula and will fetch the periodic payment for the given data. Refer to the below image: STEP 2: Add the formula and its parameters
NOTE: We have omitted the type parameter as we have assumed the payments are due at the end of the period.STEP 3: NPER will return a #VALUE! error Press the enter button as soon as you are done typing the formula. Excel will return the output for your NPER formula. As shown below it will throw the #VALUE! error. Refer to the below image: This error occurs if any of the arguments specified in your NPER formula is nonnumeric. To fix this error in Excel, always ensure that all the parameters are numeric and all the specified numbers are not formatted as text. Excel NPER function not workingSometimes while working with the NPER function, there are possibilities you might run into an error or a wrong result, and it mostly occurs because of one of the following reasons:
This error occurs if the given fv argument is never obtained with the specified rate (interest rate) and pmt (payment) parameters. Suppose you want to obtain a valid NPER result, attempt to raise a periodic interest rate and/or a payment amount.
This error occurs if any of the arguments specified in your NPER formula is nonnumeric. To fix this error in Excel, always ensure that all the parameters are numeric and all the specified numbers are not formatted as text.
This problem occurs in the NPER formula when you have used positive numbers to represent the outflow payments. For example, when you calculate the payment periods for a loan, provide the pv (loan value) parameter as a positive number and the pmt argument as a negative number.
Next TopicExcel PV Function
