The Excel NPER Function

Home » Excel-Built-In-Functions » Excel-Financial-Functions » Excel-Nper-Function Search this site: Custom Search

Related Function:  PMT Function

Basic Description

The Excel NPER function calculates the number of periods required to pay off a loan, for a specified constant periodic payment and a constant interest rate.

The syntax of the function is :

Where the arguments are as follows:

Cash Flow Convention :

Note that, in line with the general cash flow convention, outgoing payments are represented by negative numbers and incoming payments are represented by positive numbers. This is seen in the examples below.

Nper Function Examples

In each of the examples below, the spreadsheet on the left shows the format of the Nper function, and the spreadsheet on the right shows the result.

Example 1

The following spreadsheet shows the Excel Nper function used to calculate the number of months required to pay off in full, a loan of $50,000 at a rate of $1,000 per month. Interest is charged at a rate of 4% per year, and the payment to the loan is to be made at the end of each month.

Formula:

Result:

Note that in this example :

• The payment for the loan is input as a negative value, as this represents an outgoing payment (for the individual taking out the loan)
• The payments are made monthly, so we have had to convert the annual interest rate of 4% into the monthly rate (=4%/12). Also the returned value from the Nper function is in months - i.e. the result (rounded to the nearest whole month) is 55 months = 4 years, 7 months.
• As the forecast value is zero, and the payment is to be made at the end of the month, the [fv] and [type] arguments can be omitted from the above function.

Example 2

In this example, the spreadsheet below shows the Excel Nper function used to calculate the number of quarterly payments of $1,200 that are required to reduce a loan of $9,000 to $5,000. Interest is charged at a rate of 6% per year and the payment to the loan is to be made at the beginning of each quarter.

Formula:

Result:

Note that, in this example :

• The payment for the loan is input as a negative value, as this represents an outgoing payment (for the individual taking out the loan)
• The payments are made quarterly, so the annual interest rate of 6% is converted into a quarterly rate (6%/4). Also the returned value from the Nper function is in months - i.e. the result (rounded to the nearest whole month) is 12 months = 3 years.
• the [type] argument has been set to 1, to indicate that the payment is to be made at the beginning of each quarter.

Further examples of the Excel Nper function can be viewed on the Microsoft Office website.

Nper Function Errors

If you get an error from the Nper function, this is likely to be one of the following:

Also, the following problem is encountered by some users: