Calculates the Net Present Value (NPV) of an investment using a constant discount rate and a sequence of periodic cash flows (time-dimensioned) or dated cash flows.

NPV measures the current value of future cash inflows and outflows by factoring in the time value of money. A positive NPV means your projected earnings are higher than your expected costs. A negative NPV means your investment falls short of the required rate of return.

Syntax

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 rateNumber

An estimate discount rate of the NPV.

Note: Enter the rate as a decimal. For example, 0.1 for 10%.

Cash flowsNumber

A line item showing cash inflows and outflows.

This line item must have a time dimension.

NPV with dates

ArgumentData typeDescription
Discount rateNumber

An estimate discount rate of the NPV.

Note: Enter the rate as a decimal. For example, 0.1 for 10%.

Cash flowsNumber

A line item showing cash inflows and outflows.

This line item 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.

NPV returns a number.

Calculation engine functionality differences

BehaviorClassicPolaris
First cash flow selection
(Dates variant only)		Processes cash flows sequentially based on the order of items in the Transaction list. If transactions are out of order, some cash flows may be misindexed, leading to unexpected calculation results.Automatically evaluates cash flows in the correct order based on the Dates line item, regardless of items in the Transaction list.
Scaling factor (kT)
(Time dimension variant only)		Determined strictly by the timescale of the Cash flows line item, regardless of the actual structure of that time dimension.

Calculated dynamically as the average number of leaf periods in the Cash flows timescale per fiscal year.

Note: If using the Weeks: General calendar type (no fiscal years), weekly cash flows are converted to daily transactions using a fixed 365 scaling factor, matching Classic.

Discount rate is NaNResult is undefined.Returns Not a Number (NaN).
Blank values
(Dates variant only)		Evaluates blank dates as 12/31/1899 (one day before the start of the 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 contain a time dimension.
Transaction list is empty
(Dates variant only)		The engine rejects the formula and shows an error.Returns 0 by default. If the Discount rate argument evaluates to NaN, it returns NaN.

Additional information

IRR has two syntaxes. One uses cash flows with a time dimension. The other uses pairs of dates and cash flows, like Excel’s XIRR. 

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 time i.
  • ρ is the discount rate.
  • kT is the scaling factor based on ‌time granularity.
  • di is the number of days between the i-th transaction and the first transaction.

NPV scales automatically based on the given time granularity and selects the right scaling factor, kT. You can ‌calculate NPV at your investment’s original timescale or compare NPV values across different time granularities without any extra calculations. See how the scaling factor is calculated for each case.

Calendar typeClassicPolaris
Fiscal years

Based on the timescale of the Cash flows time dimension:

  • 1 for years
  • 2 for half years
  • 4 for quarters
  • 12 for months
  • 365 for weeks
  • 365 for days
Calculated dynamically as the average number of leaf periods in the Cash flows timescale per fiscal year.
General weeks

365 for weeks.

Weekly transactions are treated like daily transactions that happen on the first day of the week.

365 for weeks.

Weekly transactions are treated like daily transactions that happen on the first day of the week.

Using NPV with production lists

Using a production list like the Users list as a Transaction list can give unexpected results as these lists change over time.

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