Description

Calculates the net present value of an investment based on future cashflows on specific dates for a given time.

Syntax

XNPV(Rate Metric, Payment Metric [, Compute All Cells] [, Ranking Dimension] [, Days Used])

Argument	Type	Dimensions	Description
Rate Metric (required)	Number or Integer	No Dimension or subset of Dimensions of Payment Metric	This is the rate that will be used as the discount rate. If defined on Ranking Dimension, a different rate will be used for each item of Payment Metric
Payment Metric (required)	Number or Integer	Ranking Dimension	This represents all cashflow of the investment.
Compute All Cells (optional)	boolean	No Dimension	Defines if the computation should be done on all Ranking Dimension’s items. If TRUE, it should be done from the first non empty Ranking Dimension item until the last non empty. If FALSE, the computation should be done only on the first non empty item of Ranking Dimension. By default, set to FALSE.
Ranking Dimension (optional)	Time Dimension		Optional only if Payment Metric is defined on one dimension only
Days Used (optional)	date	Ranking Dimension	Should be a property of Ranking Dimension. Enables to define exactly on which days of the Ranking Dimension the payments/investments are made. It is mandatory if Ranking Dimension is not a dimension from the Calendar. If Ranking Dimension is a dimension from the calendar and Days Used is not indicated, Pigment will take the Start Date of the Ranking Dimension.

Return type

Type	Dimensions
Number	No Dimension, or subset of Payment Metric’s Dimensions

If Compute All Cells is FALSE, will return for the first item of Ranking Dimension for which Payment Metric was not empty the sum of all future discounted payments.

If Compute All Cells is TRUE, will return for all items of Ranking Dimension the sum of future discounted payments.

In case you have a dataset where the payments & incomes happens on evenly spaced dates or on other type of items, you can use the NPV formula.

The formula only considers the numeric values, and if there are blanks or text values, these would be ignored.

When using the Days used argument, if the dates are not ordered chornologicaaly, the formul will return blank values. Also if there are black cells in the days used argument, the corresponding payment will be ignored.

The Pigment calculation of XNPV mimics the one done on most other softwares meaning that it does not behave like the NPV: no investment/payment is ignored and no adjustment is needed.

Formula	Result	Description
`XNPV('Single Variable','Investment Cashflow',TRUE)`	Click on image to enlarge. Example with single Rate Metric	This example uses a single discount rate in the Rate Metric that has no dimensions, which is why its listed under Total. The function returns the values starting in Jan 24, as this is when the first data is in the Payment Metric. Because Compute all cells is `TRUE` the calculation is performed for every item. Because the dimesonality of the Payment Metric uses only `Month` which is from the calendar, Ranking Dimension and Days Used is optional.
`XNPV('Variable Rates over time','Investment Cashflow',TRUE)`	Click on image to enlarge. Example with variable Rate Metric	This example uses a variable discount rate in the Rate Metric. The function returns the values starting in Jan 24, as this is when the first data is in the Payment Metric. Because Compute all cells is `TRUE` the calculation is performed for every item. Because the dimesonality of the Payment Metric uses only `Month` which is from the calendar, Ranking Dimension and Days Used is optional.
`XNPV('Single Variable','Investment Cashflow by Country',TRUE,Month,Month.'Start Date')`	Click on image to enlarge. Example with single variable and multiple dimensions used in Payment Metric	This example uses a single discount rate in the Rate Metric that has no dimensions, which is why its listed under Total. The Payment Metric in this example is dimensioned by `Month` and `Country`, with `Month` being the ranking dimension. Compute all cells is set to `TRUE` so calculations are performed across all items.
`XNPV('Variable Rates over time','Investment Cashflow by Country',TRUE,Month,Month.'Start Date')`	Click on image to enlarge.	This example uses a variable value Rate Metric. The Payment Metric in this example is dimensioned by `Month` and `Country`, with `Month` being the ranking dimension. Compute all cells is set to `TRUE` so calculations are performed across all items.

See also

Excel: NPV, XNPV

Related articles:

NPV

Finance Functions

Product

Use Cases

Resources

Company

Follow