The NPV function calculates the net present value (NPV) for a series of cash flows, which can be positive (inflows) or negative (outflows), using a constant interest rate. This function can be used either with all transactions over a timescale, or with specified transactions on certain dates. It automatically adjusts the calculation based on the timescale of the cash flow data, and returns the total present value of those future cash flows.

NPV of an investment enables you to know how much a future investment is worth today. You could use the NPV function to assess whether an investment is worthwhile while accounting for depreciation.

Syntax

The NPV function has two different syntaxes. One operates on transactions expressed as cash flows with a time dimension. And the other one operates on transactions expressed as pairs of dates and cash flows.

NPV with time dimension

NPV(Discount rate, Cash flows)

NPV with dates

NPV(Discount rate, Cash flows, Dates, Transaction list)

Arguments

NPV with time dimension

ArgumentData typeDescription
Discount rateNumberAn estimate discount rate of the NPV. Note that this argument is expressed as a decimal number rather than a percentage, so 0.1 is equivalent to 10%.
Cash flowsNumber

A line item that contains a series of positive and negative values that represent cash inflow and outflow.

The line item used for this argument must have a time dimension.

NPV with dates

ArgumentData typeDescription
Discount rateNumberAn estimate discount rate of the NPV. Note that this argument is expressed as a decimal number rather than a percentage, so 0.1 is equivalent to 10%.
Cash flowsNumber

A series of positive and negative values that represent cash inflow and outflow.

This must have the list used for the Transaction list argument as a dimension.

DatesDateThe dates associated with each value of the Cash flows argument.
Transaction listListA list of transaction identifiers, which must be a common dimension of the Cash flows and Dates argument.

The NPV function returns a number.

Calculation engine functionality differences

BehaviorClassicPolaris
First cash flow selection
(applies only to the dates variant)		If the cash flows are out of order with respect to the Transaction list dimension, Classic can associate the wrong index to some cash flows, which leads to unreliable results.Polaris always gives you the right result regardless of the order of transactions.
Scaling factor (applies only to the time dimension variant)In Classic, the scaling factor is determined entirely by the time scale of the time dimension of Cash flows, regardless of the actual structure of that time dimension.

In Polaris, the scaling factor is the average number of leaf periods (of the time dimension of Cash flows) in a fiscal year.

Note: When using the general weeks calendar type, that is, when there are no fiscal years, weekly transactions are converted to daily transactions and a fixed 365 scaling factor is used, the same as in Classic.

Discount rate is NaNResult is undefined.Returns NaN.
Blank values in DatesTreated as 12/31/1899 (one day before Anaplan era).Returns NaN, regardless of cash flow values.
Target line item dimensionalityCan be dimensioned by Transaction list.For the time dimension variant, the target line item can't have a time dimension.
Transaction list is emptyThe engine rejects the formula and gives an error message.In Polaris, this function always returns 0 unless the Discount rate is NaN, in that case, the function returns NaN instead.

Additional information

NPV calculation with time dimension

NPV=∑i=0nCi(1+ρ)ikT\hspace{10 mm} \text{NPV} = \displaystyle\sum_{i = 0}^{n} \dfrac{C_i}{(1+\rho)^{ik_T}}

NPV calculation with dates

NPV=∑i=0nCi(1+ρ)di/365\hspace{10 mm} \text{NPV} = \displaystyle\sum_{i = 0}^{n} \dfrac {C_i}{(1+\rho)^{{d_i}/{365}}}

where

  • n is the total number of cash flows.
  • Ci is the cash flow at the i-th time period.
  • ρ is the discount rate.
  • kT is the scaling factor based on ‌time granularity.
  • di is the number of days between the date of the i-th transaction and the date of the earliest transaction in the sequence.

The function automatically scales based on the given time granularity and chooses the appropriate scaling factor kT . This lets users ‌express the calculation at the original time granularity of the investments being analyzed. Also, ‌users can compare the resulting NPV values with different time granularities, without any additional calculations. See below how the scaling factor is calculated in each case:

EngineCalendar typeScaling factor, kT, used
PolarisFiscal yearsNumber of leaf periods / number of years counted in the time dimension of the Cash flows line item.

General weeks

365 for weeks and days.

Weekly transactions are treated as if they were daily transactions occurring on the first day of the week.

ClassicFiscal years

Based on the timescale of the Cash flows time dimension:

1 for years
2 for half years
4 for quarters
12 for months


General weeks

365 for weeks and days.

Weekly transactions are treated as if they were daily transactions occurring on the first day of the week.

Using NPV with production lists

Using a production list as a Transaction list, such as the Users list, can unexpectedly affect calculation results as these lists are meant to change over the lifetime of the model.

Constraints

In Polaris, the dates variant can't be used if the target module has a dimension that is related to Transaction list.

Excel equivalent

Examples