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.
NPER
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])
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.
Additional information
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 currently unavailable in Polaris. Learn more about the differences between Anaplan calculation engines.
Excel equivalent
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
| -21.6 | -21.04 | -16.88 | -13.14 | -6.556 |
Disclaimer
We update Anapedia content regularly to provide the most up-to-date instructions.