XNPV function

  • 24 February 2023
  • 0 replies
  • 263 views

Userlevel 4
Badge +3

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 

 


This topic has been closed for comments