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, Amazon Redshift, Snowflake, Databricks, 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 Lambda 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 Lambda UDF manually:

Create a Lambda UDF called atscale-xirr using the following code:

import json
from math import isnan


def _compute_xirr(a_str, initial_guess, epsilon, max_iterations, default_value):
    A = [json.loads(a) for a in a_str.split("#")]

    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

    x = initial_guess
    last_x = None
    i = 0
    while i < max_iterations:
        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)

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

        if abs(x - last_x) < epsilon:
            i = max_iterations
        else:
            i += 1

        if x < -1:
            x = -0.999

    if abs(x - last_x) > epsilon:
        x = default_value

    return x


def lambda_handler(event, context):
    results = [_compute_xirr(*row) for row in event["arguments"]]
    return json.dumps({"success": True, "num_records": len(results), "results": results})

Create an IAM Role for the Lambda UDF:
1. In the IAM console, go to Roles and click Create role. Name the role redshift-lambda-udf-role.
2. On the Permissions tab, click Add permissions and select Create inline policy.
3. Switch to the JSON editor and paste in the following:
```
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": "lambda:InvokeFunction",
      "Resource": "arn:aws:lambda:<REGION>:<ACCOUNT_ID>:function:atscale-xirr"
    }
  ]
}
```
  Where <REGION> is your region (e.g. us-east-1), and <ACCOUNT_ID> is your account ID.
4. Name the policy (for example, invoke-atscale-xirr).
5. Click Create policy.
6. Attach the role to your Redshift cluster or workgroup:
  - Provisioned clusters:
    1. In the Redshift console, go to your cluster.
    2. Under Actions, select Manage IAM roles.
    3. On the Manage IAM roles page, select redshift-lambda-udf-role, then select Add IAM role.
    4. Click Done.
  - Serverless clusters:
    1. In the Redshift Serverless Console, go to your namespace.
    2. Under Security and encryption, click Manage IAM roles.
    3. Select redshift-lambda-udf-role, then click Associate IAM role.

CREATE OR REPLACE EXTERNAL FUNCTION public.ATSCALE_XIRR
  (VARCHAR(MAX), FLOAT8, FLOAT8, BIGINT, FLOAT8)
RETURNS FLOAT8
IMMUTABLE
LAMBDA 'atscale-xirr'
IAM_ROLE 'arn:aws:iam::<ACCOUNT_ID>:role/redshift-lambda-udf-role';

Where <ACCOUNT_ID> is your account ID.

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:

On Redshift, edit the beginning of the first line in the SQL above as follows:
```
CREATE OR REPLACE EXTERNAL 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.
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.

Syntax​

Input parameters​

Examples​

Using XIRR on Redshift data warehouses​

Manual creation of the XIRR Lambda UDF is required​

65k character limit​