Xirr (PQL)

Given a list of DATES and a datapoint, calculates the discount rate at which the net present value (NPV) of cash flows equals zero, over an uneven range of date periods. Optionally accepts a ‘guess value’ and a ‘default value’ for when the irr cannot be calculated.

  • Returned Output: Numeric
  • Library: PQL \ Semantic \ Financial
  • Version: 2025.00.000
  • Compatibility: Pyramid Query Language (PQL) data sources

Syntax

Xirr( <List of Dates> , <Data Point> , OPTIONAL <Data Point> , OPTIONAL <Data Point> )

* Click on the function's arguments above for more details on the input values.

Comments
  • The list needs to be a list or set of members from a date-time attribute. The dates do NOT need to be equidistant from each other.
  • The data point needs to describe values for each of the date-time members in the list, typically its cash-flows (positive and negative).
  • The first optional data point reflects a "guess" value to improve the calculator's performance.
  • The second optional data point reflects the default value if no result can be determined.
  • For details on how to employ and use this function see the semantic calculation overview.
Different Function types
  • This function is like the Excel XIRR function.
  • Also see the IRR function that assumes the dates are equidistant from each other.
  • There are other financial calculations in the Common library

Example

In this example, XIRR is calculated from three non-sequential dates with an initial investment of 100 and cash flows of 200 and 400:

Xirr( {[Data].[Date].[1483228800000],[Data].[Date].[1483315200000],[Data].[Date].[1483401600000]}, -100, 200, 400 )