# The Excel NPER Function

Related Functions:
PMT Function
RATE Function

## Function Description

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

The syntax of the function is:

NPER( rate, pmt, pv, [fv], [type] )

Where the arguments are as follows:

 rate - The interest rate, per period. pmt - The amount paid per period. pv - The present value of the loan. [fv] - An optional argument that specifies the future value of the loan, after the final payment.If omitted, [fv] takes on the default value of 0. [type] - An optional argument that defines whether the payment is made at the start or the end of the period.The [type] argument can have the value 0 or 1, meaning:0   -   the payment is made at the end of the period;1   -   the payment is made at the beginning of the period.If the [type] argument is omitted, it takes on the default value of 0 (denoting payments made at the end of the period).

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

### Example 1

In the following spreadsheet, the Excel Nper function is 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:
A
1Number months required to
pay off a loan of \$50,000 with
an interest rate of 4% per year
and payments of \$1000 per
month (payment made at end
of each mth):
2=NPER( 4%/12, -1000, 50000 )
Result:
A
1Number months required to
pay off a loan of \$50,000 with
an interest rate of 4% per year
and payments of \$1000 per
month (payment made at end
of each mth):
254.78757726

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).
• 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 function call.
• The value returned from the Nper function is in months - i.e. the result (rounded to the nearest whole month) is 55 months = 4 years, 7 months.

### Example 2

In the example below, 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:
A
1Number of quarterly payments of
\$1,200 that are required to reduce
a loan of \$9,000 to \$5,000, with an
interest rate of 6% per year (payment
made at beginning of each quarter):
2=NPER( 6%/4, -1200, 9000, 5000, 1 )
Result:
A
1Number of quarterly payments of
\$1,200 that are required to reduce
a loan of \$9,000 to \$5,000, with an
interest rate of 6% per year (payment
made at beginning of each quarter):
211.90373729

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).
• The [type] argument has been set to 1, to indicate that the payment is to be made at the beginning of each quarter.
• The value returned from the Nper function is in quarters - i.e. the result (rounded to the nearest whole quarter) is 12 quarters = 3 years.

Further examples of the Excel Nper function are provided 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:

Common Errors
 #NUM! - Occurs if the specified future value will never be met for the supplied periodic interest rate and payments. You may need to increase the payment amount or reduce the interest rate to get a valid result. #VALUE! - Occurs if any of the supplied arguments are not recognised as numeric values.

Also, the following problem is encountered by some users:

Common Problem

The Excel NPER function gives a negative result, when a positive one is expected.

Solution

This problem usually occurs when the present value and the specified periodic payment both have the same arithmetic sign. If a loan is being paid off, present value should be negative and the payment should be positive (or vice versa).

This problem can be avoided if you ensure that all signs adhere to the usual Cash Flow Sign Conventions.