## Excel NPER Function

NPER function is a type of function that is categorized under financial functions. NPER excel functions help in calculating the number of payments if we make an investment in certain periods. NPER excel function helps in calculating the number of payments for a specific amount of target. This function returns the number of payments that are required to complete a loan through the regular number of payment periods and a fixed interest rate.

This function is used in a single cell of an Excel, in order to calculate the number of payment periods for a loan that you want to pay off.

**NPER Formula in Excel**

Please find below the description of parameters used in NPER formula:

**Interest_rate =**It represents the interest rate per period. This is the required parameter.**Payment =**It represents the amount of payment per period or per year. This is the required parameter. It cannot be changed annually & this should be entered as a negative value. This is the required parameter.**PV =**It represents the present value of the payments or a single payment worth of future payments. This is the required parameter.**FV =**It represents the future value of the payments which we want at the last that is after the last payment. If fv is omitted, it shall be taken as zero. This is the optional parameter.**Type =**It represents the payments that are due. So, if the Type value is 0 then it means Payments are left to pay at the last of the period. (default) & if Type value is 1, then it means Payments are due at the start of the period.

Below is the screenshot for the formula in excel,

### How to use the NPER Function in Excel?

We do not have any specific function to round a number. Here are a few steps to round a number using different-different functions. They are:

**Step #1 –** First select the cell in which you want to calculate the number of payments for the given data.

**Step #2 –** Now, use the NPER formula to calculate the number of payments required to repay a loan.

**= NPER( interest_rate, payment, PV, [FV], [Type] )**

**Step #3 –** Now, Press Enter to see the result after using the NPER function to calculate the number of periods

### Examples of NPER Function in Excel

The formula will be more clear with the help of a few examples.

So let us learn the use of NPER excel function by a few examples.

#### Example #1

Let us consider this sample data to calculate NPER. We have data for the loan taken on a specific rate with a specific monthly payment and we will have to calculate the number of payment periods required to pay off the loan. Have a look at the data below.

- Data of the amount of loan taken on a specific rate with a specific monthly payment with the compounding periods per year is shown below:

- Now, select the cell in which you want to calculate the number of payments for the given data, as shown below.

- Now, use the NPER excel function to calculate the number of payments required to repay the loan.

- Press Enter to see the result after using the NPER excel function to calculate the number of periods

#### Example #2

Let us consider this sample data to calculate NPER. We have data for a loan taken on a specific rate with a specific monthly payment and we will have to calculate the number of payment periods required to pay off the loan. Have a look at the data below

- Data of the amount of loan taken on a specific rate with a specific monthly payment with the compounding periods per year is shown below:

- Now, select the cell in which you want to calculate the number of payments for the given data, as shown below.

- Now, use the NPER function in excel to calculate the number of payments required to repay a loan.

- Press Enter to see the result after using the NPER excel function to calculate the number of periods.

#### Example #3

Let us consider this sample data to calculate NPER. We have data for a loan taken on a specific rate with payment each period, present value; future value and we will have to calculate the number of payment periods required to pay off the loan. Have a look at the data below

- Data of the amount of loan taken on a specific rate with payment each period, present value; future value is shown below:

- Now, select the cell in which you want to calculate the number of payments for the given data, as shown below.

- Now, use the NPER function in excel to calculate the number of payments required to repay a loan.

- Press Enter to see the result after using the NPER function in excel to calculate the number of periods.

### Things to Remember

- The amount of payment per period or per year should always be taken as a negative value.
- The future value shall be taken as zero when omitted.

### Recommended Articles

This has been a guide to NPER Function in Excel. Here we discuss how to use excel NPER function using formula along with examples and downloadable excel template. You may also look at these useful functions in excel –

- Create Marksheet in Excel
- PERCENTRANK Function
- What is Infographics in Excel?
- PMT in Excel
- PPMT in Excel
- PMT Function
- Rate Excel Formula

- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion