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.  | 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 Because the dimesonality of the Payment Metric uses only |
XNPV('Variable Rates over time','Investment Cashflow',TRUE) | Click on image to enlarge.  | 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 Because the dimesonality of the Payment Metric uses only |
XNPV('Single Variable','Investment Cashflow by Country',TRUE,Month,Month.'Start Date') | Click on image to enlarge.  | 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 Compute all cells is set to |
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 Compute all cells is set to |
See also
Related articles: