Calculate XIRR in SQL Server and Use It in Bold BI

This knowledge base article explains how to calculate the Extended Internal Rate of Return (XIRR) for cash-flow data using a custom scalar-valued function in Microsoft SQL Server and display the calculated result in Bold BI.

Introduction to XIRR

What is XIRR?

Extended Internal Rate of Return (XIRR) is a financial metric used to calculate the annualized rate of return for investments involving multiple cash flows that occur on different dates. XIRR considers the actual dates of cash inflows and outflows. This makes it suitable for investment scenarios where contributions, distributions, and valuations occur at irregular intervals.

What is the use of XIRR?

XIRR is commonly used to:

• Measure the annualized return of an investment.
• Evaluate investment performance when cash flows occur on different dates.
• Analyze contributions, distributions, and ending investment valuations.
• Compare the performance of different funds or investment portfolios.

How can XIRR be calculated in Bold BI?

Bold BI does not provide native XIRR calculation support for this scenario. Therefore, XIRR can be calculated using a custom scalar-valued function in Microsoft SQL Server. The function processes the cash-flow amounts and their corresponding dates, calculates the annualized return using an iterative numerical method, and returns the XIRR value. The returned value can then be integrated into Bold BI using SQL Code View and displayed in the dashboard.

Calculating XIRR Using a Custom SQL Server Function

Follow the steps below to implement the XIRR function and validate the calculated result in SQL Server Management Studio (SSMS).

Step 1: Sample Data

Consider this is the data table that you have in your SQL Server.

Step 2: Create the XIRR function

Create the following scalar-valued function in the same database as the cash-flow table. This implementation uses an iterative Newton–Raphson calculation.

CREATE OR ALTER FUNCTION dbo.CalcXIRR
(
    @Contributor VARCHAR(50) = NULL,
    @Rate FLOAT = -0.9
)
RETURNS FLOAT
AS
BEGIN
    DECLARE
        @X FLOAT,
        @X0 FLOAT,
        @F FLOAT,
        @FBar FLOAT,
        @Iteration INT = 0,
        @Found BIT = 0;

    IF @Rate IS NULL
        SET @Rate = 0.1;

    SET @X0 = @Rate;

    WHILE @Iteration < 100
    BEGIN
        SET @F = 0.0;
        SET @FBar = 0.0;

        SELECT
            @F = SUM(
                Amount * POWER(
                    1.0 + @X0,
                    -DATEDIFF(DAY, FirstDate, CashflowDate) / 365.0
                )
            ),
            @FBar = SUM(
                -(DATEDIFF(DAY, FirstDate, CashflowDate) / 365.0)
                * Amount
                * POWER(
                    1.0 + @X0,
                    -DATEDIFF(DAY, FirstDate, CashflowDate) / 365.0 - 1.0
                )
            )
        FROM
        (
            SELECT
                Amount,
                CashflowDate,
                MIN(CashflowDate) OVER () AS FirstDate
            FROM dbo.XIRR_Cashflow
            WHERE @Contributor IS NULL
               OR Contributor = @Contributor
        ) AS D;

        IF @FBar IS NULL OR ABS(@FBar) < 1.0E-15
            RETURN NULL;

        SET @X = @X0 - (@F / @FBar);

        IF @X <= -1.0 OR @X IS NULL
            RETURN NULL;

        IF ABS(@X - @X0) < 0.00000001
        BEGIN
            SET @Found = 1;
            BREAK;
        END;

        SET @X0 = @X;
        SET @Iteration = @Iteration + 1;
    END;

    IF @Found = 1
        RETURN @X;

    RETURN NULL;
END;
GO

Function parameters

The function returns a decimal rate (for example, 0.0456 represents approximately 4.56%). A NULL result indicates that the function did not converge with the current inputs and initial estimate, or that the derivative was too close to zero.

Step 3: Validate the function in SSMS

Test the combined cash flows:

SELECT dbo.CalcXIRR(NULL, -0.9) AS XIRR;

Test a single Contributor:

SELECT dbo.CalcXIRR('Fund', -0.9) AS XIRR;

Calculate XIRR for each Contributor type:

SELECT
    I.Contributor,
    dbo.CalcXIRR(I.Contributor, -0.9) AS XIRR
FROM
(
    SELECT DISTINCT Contributor
    FROM dbo.XIRR_Cashflow
) AS I;

Confirm that the function returns the expected results in SSMS before configuring Bold BI.

Integrating the XIRR Output into Bold BI

After validating the function in SSMS, configure the Bold BI data source to execute the function and retrieve its output.

Step 1: Grant the Bold BI database user permission to execute the function

The database user used by the Bold BI data source must have permission to execute the custom function.

Run the following statement using an authorized SQL Server administrator account in the database containing the function.

GRANT EXECUTE
ON OBJECT::dbo.CalcXIRR
TO [YourBoldBIDatabaseUser];
GO

Replace [YourBoldBIDatabaseUser] with the actual database user or role used by the Bold BI data source.

Note:
If the data source uses Windows authentication, grant permission to the appropriate Windows principal or database role. Follow your organization's least-privilege policy; do not grant broad administrative roles solely to resolve this error. 

Step 2: Configure the SQL Server data source

1. Open Bold BI and edit or create the SQL Server data source.
2. Configure the connection to the database containing dbo.XIRR_Cashflow and dbo.CalcXIRR.
3. Open the query editor and switch to Code View.
   
   

Step 3: Execute the XIRR function in Code View

Use the following queries according to the required output.

Calculate XIRR for all cash flows:

SELECT
    CAST(dbo.CalcXIRR(NULL, -0.9) AS FLOAT) AS XIRR;

Calculate XIRR for a specific contributor:

SELECT
    dbo.CalcXIRR('Fund', -0.9) AS XIRR;

Calculate XIRR for each contributor:

SELECT
    I.Contributor,
    dbo.CalcXIRR(I.Contributor, -0.9) AS XIRR
FROM
(
    SELECT DISTINCT Contributor
    FROM dbo.XIRR_Cashflow
) AS I;

Execute the query and confirm that the XIRR field appears in the data preview.

Step 4: Display the XIRR value in a dashboard

1. Save the data source.
2. Add a suitable widget, such as a card for a single XIRR value or a table for contributor-wise results.
3. Bind the XIRR field to the widget.
4. Configure the number format to display the result as a percentage.
   
   
   
   XIRR is returned as a decimal rate. If you want the SQL query to return the percentage value directly, multiply the result by 100:

SELECT
    dbo.CalcXIRR(NULL, -0.9) AS XIRR_Decimal,
    dbo.CalcXIRR(NULL, -0.9) * 100 AS XIRR_Percentage;

For example, a decimal result of 0.0456 corresponds to approximately 4.56%. If the widget is configured to format decimal values as percentages, use the original decimal output rather than multiplying by 100, to avoid displaying an incorrect percentage.

The XIRR calculation is now available in Bold BI through the custom SQL Server function, allowing the calculated investment return to be displayed in dashboard widgets.