Skip to main content

XIRR

Calculates the rate of return (within .000001%) of investments that have irregular payment schedules.

Note

XIRR is a public preview feature, and is only supported for the following data warehouses: Google BigQuery, Redshift, Snowflake, and PostgreSQL.

Syntax​

XIRR(Payment_Metric, Date_Attribute[, Initial_Guess][, Default_Value])

Input parameters​

Payment_Metric

Required. The metric that represents the payment. This must have the same size and order as the Date_Attribute argument.

Date_Attribute

Required. The attribute that represents the payment schedule. This should be either a secondary attribute or an attribute of a single-level degenerate dimension, with values of type date, datetime, or timestamp. Additionally, Date_Attribute must have the same size and order as the Payment_Metric argument.

Initial_Guess

Optional. The starting value of the rate. This must be a static floating point value. If this argument is not specified, it defaults to 0.1.

Default_Value

Optional. The value that is returned if the rate cannot be accurately calculated. If this argument is not specified, it defaults to NULL.

Examples​

The following example calculates the rate of return for [Measures].[Reseller Sales Amount Local] based on the schedule [DateCustom].[Retail445].[Reporting Day], using the default default values for the initial guess and default return value:

XIRR([Measures].[Reseller Sales Amount Local], [DateCustom].[Retail445].[Reporting Day])

The following example calculates the rate of return for [Measures].[Internet Sales Amount Local] based on the schedule [DateCustom].[Retail445].[Reporting Day], using 0.1 as the initial guess and 0 as the default return value:

XIRR([Measures].[Internet Sales Amount Local], [DateCustom].[Retail445].[Reporting Day], 0.1, 0)

Using XIRR on Redshift data warehouses​

The XIRR function has the following requirements and limitations on Redshift data warehouses.

Manual creation of the XIRR UDF is required​

Because Redshift does not support temporary user defined functions (UDFs), XIRR cannot be used on Redshift data warehouses out of the box. If you use Redshift, you must create the XIRR UDF by running the following:

create function public.ATSCALE_XIRR (A_STR VARCHAR(MAX), INITIAL_GUESS FLOAT8, EPSILON FLOAT8, MAX_ITERATIONS BIGINT, DEFAULT_VALUE FLOAT8)
  returns FLOAT8
IMMUTABLE
as $$
from math import isnan
from json import loads
A_ARR = A_STR.split("#")
A = [loads(a) for a in A_ARR]
i = 0
max_iters = MAX_ITERATIONS
x = INITIAL_GUESS
lastX = None

filtered_A = [a for a in A if a["m"] is not None and not isnan(a["m"])]

has_positive = any(a["m"] > 0 for a in filtered_A)
has_negative = any(a["m"] < 0 for a in filtered_A)

if not has_positive or not has_negative:
    return DEFAULT_VALUE

while i < max_iters:
    fx = sum(a["m"] * (1.0 + x) ** (-a["d"] / 365.0) for a in filtered_A)
    dfx = sum(((1.0 / 365.0) * (-a["d"]) * a["m"]) * ((x + 1.0) ** (((-a["d"]) / 365.0) - 1.0)) for a in filtered_A)

    lastX = x
    x = x - (fx / dfx)

    if abs(x - lastX) < EPSILON:
        i = max_iters
    else:
        i += 1

    if x < -1:
        x = -0.999

if abs(x - lastX) > EPSILON:
    x = DEFAULT_VALUE

return x
$$ language plpythonu;

This creates XIRR with the fully qualified name public.ATSCALE_XIRR. If you want to use a different name, you can specify one when creating the function:

  1. On Redshift, edit the beginning of the first line in the code above as follows:

    create function <schema>.<XIRR_function_name>

    Where <schema> is the schema you want to use, and <XIRR_function_name> is the function name. Leave the rest of the code as-is.

  2. In Design Center, update the query.redshift.xirr.name global setting to reflect the new schema and function name. The default value is public.ATSCALE_XIRR.

65k character limit​

AtScale's implementation of XIRR requires data to be transformed into an array of objects to pass into the UDF function, which Redshift does not support. As a workaround, the implementation on Redshift turns the array of objects into a JSON string.

Note, however, that Redshift limits strings to 65k characters. Therefore, the array of JSON objects cannot exceed 65k characters in the textual representation. If that happens, the query will fail.