1. Calculation functions
2. All functions
3. Financial functions
4. NPER

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.

## Syntax

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

## Arguments

 Argument Data type Description 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) Number The present value of the instrument, or initial investment. Residual value Number The amount of loan left after the final payment is made. If omitted, this is assumed to be 0. Timing Number 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.

### Positive and negative values

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.

## How the NPER function is calculated

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)

## Calculation engine functionality differences

Most financial functions are unavailable in Polaris, but you can use FV, IPMT, NPER, PPMT, and PV. Learn more about the differences between Anaplan calculation engines.

NPER

## Examples

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 1 Contract 2 Contract 3 Contract 4 Contract 5 Interest rate 4% 4% 8% 1% 15% Payments 300 300 300 1,500 -5,000 Present value 10,000 10,000 10,000 -50,000 -50,000 Residual value 0 0 0 25,000 0 Timing 0 1 0 0 0 Number of periods NPER(Interest rate, Payments, Present value, Residual value, Timing) -21.6 -21.04 -16.88 -13.14 -6.556

Disclaimer

We may update our documentation occasionally, but will only do so in a way that does not negatively affect the features and functionality of the Anaplan service.