The NPER function calculates the required number of periods to achieve a certain value for a loan or investment. This is based on a given interest rate, consistent payments, and opening and closing balance.

For example, you can use the NPER function to calculate how long it will take to pay off a loan.

NPER(Interest rate, Payments, Present value [, Residual value] [, Timing])

ArgumentData typeDescription
Interest rate (required)Number

The interest rate of the loan.

This argument is best used with the Percentage format, as 0.1 equals 10%, and so on.

Payments (required)Number

The amount paid each period.

You can omit this argument by entering 0, but in this case, you must provide a value for the Present value argument.

Present value (required)NumberThe present value of the instrument, or initial investment.
Residual valueNumberThe amount of loan left after the final payment is made. If omitted, this is assumed to be 0.
TimingNumber

Whether interest payments are made at the start or end of each period.

0 means payments are made at the start of each period. A non-zero value means payments are made at the end.

If omitted, payments are made at the start of each period.

The NPER function returns a number.

For any values you give the NPER function via an argument, or that the function returns:

  • A positive value represents money you receive, such as a dividend or loan.
  • A negative value represents money paid, such as a deposit or interest payment.

You can recreate the behavior of the NPER function. To do this, you can use the formula below with the values you would otherwise use as arguments for the NPER function.

IF Interest rate = 0 THEN (-Present value - Residual value) / Payments ELSE LOG((-Interest rate * Residual value + Payments * (1 + Interest rate * Timing))/(Interest rate * Present value + Payments * (1 + Interest rate * Timing)))/LOG(1 + Interest rate)

Most financial functions are currently unavailable in Polaris. Learn more about the differences between Anaplan calculation engines.

NPER

In this example, there is a Contracts list on columns, and line items on rows. There are five line items, one for each argument of the NPER function. The sixth line item uses the NPER function to calculate the required payments.


Contract 1Contract 2Contract 3Contract 4Contract 5
Interest rate4%4%8%1%15%
Payments3003003001,500-5,000
Present value10,00010,00010,000-50,000-50,000
Residual value00025,0000
Timing01000

Number of periods

NPER(Interest rate, Payments, Present value, Residual value, Timing)

-21.6-21.04-16.88-13.14-6.556