Calculating Extended Internal Rate of Return (XIRR) in SQL Server with T-SQL

Calculating Extended Internal Rate of Return (XIRR) in SQL Server with T-SQL xirr-800x458 The Extended Internal Rate of Return (XIRR) is a financial metric used to calculate the annualized rate of return for a series of irregularly timed cash flows. It is an extension of the Internal Rate of Return (IRR), which considers equally spaced cash flows. As real-world investments often involve irregular cash flows, XIRR is a more versatile and realistic metric. In this blog post, we will demonstrate how to calculate XIRR in SQL Server using T-SQL. I had fun building functions while consulting for a prominent financial institute during Comprehensive Database Performance Health Check. They had a very complex XIRR function, which I helped them to simplify.

What is the Extended Internal Rate of Return (XIRR)?

The XIRR function calculates the internal rate of return for a series of cash flows that may not be periodic. It’s the rate at which the net present value (NPV) of the cash flows equals zero. Essentially, it’s the rate that equates the initial investment (outflow) with the present value of future returns (inflows).

Calculating XIRR in SQL Server

To compute XIRR in SQL Server, we can create a user-defined function that utilizes the Newton-Raphson method for finding the roots of an equation. Here is a step-by-step guide:

1. Create a table to store your cash flows and their corresponding dates.

CREATE TABLE CashFlows (
    ID INT PRIMARY KEY IDENTITY(1,1),
    CashFlowDate DATE NOT NULL,
    CashFlowValue DECIMAL(18, 4) NOT NULL
);

2. Insert your cash flows into the table.

INSERT INTO CashFlows (CashFlowDate, CashFlowValue)
VALUES ('2020-01-01', -10000),
       ('2020-03-31', 1200),
       ('2020-07-30', 2500),
       ('2020-12-31', 2000),
       ('2021-02-28', 1800),
       ('2021-06-30', 2200),
       ('2021-10-31', 3000),
       ('2021-12-31', 3000),
       ('2022-04-30', 3500),
       ('2022-08-31', 4000),
       ('2022-12-31', 4000);

3. Create the user-defined function to calculate XIRR.

CREATE FUNCTION dbo.XIRR (@Tolerance DECIMAL(18, 10), @InitialGuess DECIMAL(18, 10))
RETURNS DECIMAL(18, 10)
AS
BEGIN
    DECLARE @MaxIterations INT = 100;
    DECLARE @Iteration INT = 0;
    DECLARE @Rate DECIMAL(18, 10) = @InitialGuess;
    DECLARE @PrevRate DECIMAL(18, 10);
    DECLARE @F DECIMAL(18, 10);
    DECLARE @FPrime DECIMAL(18, 10);
    WHILE (@Iteration < @MaxIterations)
    BEGIN
        SET @PrevRate = @Rate;
        SET @F = 0;
        SET @FPrime = 0;
        SELECT
            @F = @F + CashFlowValue / POWER(1 + @Rate, DATEDIFF(DAY, (SELECT MIN(CashFlowDate) 
FROM CashFlows), CashFlowDate) / 365.0),
            @FPrime = @FPrime - CashFlowValue * DATEDIFF(DAY, (SELECT MIN(CashFlowDate) 
FROM CashFlows), CashFlowDate) / (365.0 * POWER(1 + @Rate, DATEDIFF(DAY, (SELECT MIN(CashFlowDate) 
FROM CashFlows), CashFlowDate) / 365.0 + 1))
        FROM CashFlows;
        SET @Rate = @Rate - @F / @FPrime;
        IF (ABS(@Rate - @PrevRate) < @Tolerance)
        BEGIN
            BREAK;
        END
        SET @Iteration = @Iteration + 1;
    END
    RETURN @Rate;
END

4. Call the function to calculate the XIRR.

DECLARE @Tolerance DECIMAL(18, 10) = 0.0000000001;
DECLARE @InitialGuess DECIMAL(18, 10) = 0.1;
SELECT dbo.XIRR(@Tolerance, @InitialGuess) AS XIRR;

This approach assumes a 365-day year for date calculations. You can adjust the day count convention as needed.

The answer in our case would be 0.8134432073, which shows around 81.34 % return.

Understanding Key Parameters

Tolerance

Tolerance is a small positive value determining when the Newton-Raphson method has converged. If the difference between two successive XIRR approximations is less than the tolerance, the method stops. A lower tolerance means a more accurate XIRR but a longer computation time.

InitialGuess

This is the starting point for the Newton-Raphson method. A good initial guess helps the method converge faster. If the initial guess is too far from the actual XIRR, the method may not converge, or it might converge to a different root. In our example, we’ve used `0.1` as the initial guess, representing a 10% annualized return.

365-day Year Convention

The XIRR calculation involves finding the present value of each cash flow, which requires the time elapsed since the initial cash flow. We’ve used a 365-day year convention for date calculations in our example, meaning we assume a year has 365 days. This simplifies the conversion from days to years. There are other day count conventions like 30/360, Actual/360, and Actual/Actual, which may be more appropriate for specific contexts such as bond investments. Modify the day count convention in the T-SQL code as needed for your specific use case.

Conclusion

XIRR is a powerful financial metric allowing investors and analysts to evaluate investments and projects with irregular cash flows. By implementing a user-defined function in SQL Server, we can calculate XIRR for any series of cash flows and use it in our financial analysis. Understanding the significance of tolerance, initial guess, and day count convention will help you fine-tune the XIRR calculation for your needs. SQL Server’s T-SQL provides a versatile platform for implementing such financial computations.

You can always reach out to me on LinkedIn.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Mathematical Function, SQL Function
Previous Post
Solving Quadratic Equations with T-SQL
Next Post
SQL SERVER – Setting up a Robust Password Policy

Related Posts

1 Comment. Leave new

  • Good morning. I want to ask you a question about the code in your function. There is a lot of code repetition that could be removed through the use of CROSS APPLY clauses. In my view of things, that way the code looks cleaner and more readable. Is there a problem using such clauses I am not aware of that you know?

    This is the code I wrote with yours as a reference:

    –CREATE TABLE CashFlows (
    — ID INT PRIMARY KEY IDENTITY(1,1),
    — CashFlowDate DATE NOT NULL,
    — CashFlowValue DECIMAL(18, 4) NOT NULL
    –);

    –INSERT INTO CashFlows (CashFlowDate, CashFlowValue)
    –VALUES (‘2020-01-01’, -10000),
    — (‘2020-03-31’, 1200),
    — (‘2020-07-30’, 2500),
    — (‘2020-12-31’, 2000),
    — (‘2021-02-28’, 1800),
    — (‘2021-06-30’, 2200),
    — (‘2021-10-31’, 3000),
    — (‘2021-12-31’, 3000),
    — (‘2022-04-30’, 3500),
    — (‘2022-08-31’, 4000),
    — (‘2022-12-31’, 4000);

    CREATE FUNCTION dbo.XIRR_V2 (@Tolerance DECIMAL(18, 10), @InitialGuess DECIMAL(18, 10))
    RETURNS DECIMAL(18, 10)
    AS
    BEGIN
    DECLARE @MaxIterations INT = 100;
    DECLARE @Iteration INT = 0;
    DECLARE @Rate DECIMAL(18, 10) = @InitialGuess;
    DECLARE @PrevRate DECIMAL(18, 10);
    DECLARE @F DECIMAL(18, 10);
    DECLARE @FPrime DECIMAL(18, 10);
    WHILE (@Iteration < @MaxIterations)
    BEGIN
    SET @PrevRate = @Rate;
    SET @F = 0;
    SET @FPrime = 0;
    SELECT
    — @F = @F + CashFlowValue / POWER(1 + @Rate, DATEDIFF(DAY, (SELECT MIN(CashFlowDate)
    –FROM CashFlows), CashFlowDate) / 365.0),
    — @FPrime = @FPrime – CashFlowValue * DATEDIFF(DAY, (SELECT MIN(CashFlowDate)
    –FROM CashFlows), CashFlowDate) / (365.0 * POWER(1 + @Rate, DATEDIFF(DAY, (SELECT MIN(CashFlowDate)
    –FROM CashFlows), CashFlowDate) / 365.0 + 1))
    — @FPrime =
    — @FPrime
    — –
    — CashFlowValue
    — *
    — DATEDIFF(DAY, (SELECT MIN(CashFlowDate) FROM CashFlows), CashFlowDate)
    — /
    — (365.0 * POWER(1 + @Rate, DATEDIFF(DAY, (SELECT MIN(CashFlowDate) FROM CashFlows), CashFlowDate) / 365.0 + 1))

    — — POWER(1 + @Rate, DD.DateDifference / 365.0),
    @F = @F + CF.CashFlowValue / F.FactorForF,
    — –POWER(1 + @Rate, DATEDIFF(DAY, (SELECT MIN(CashFlowDate) FROM CashFlows), CashFlowDate) / 365.0 + 1)
    @FPrime = @FPrime – CF.CashFlowValue * DD.DateDifference / (365.0 * F.FactorForFPrime)
    FROM
    CashFlows CF
    CROSS APPLY
    (
    SELECT
    MIN(CashFlowDate) AS MinDate
    FROM
    CashFlows
    ) MD
    CROSS APPLY
    (
    SELECT
    DATEDIFF(DAY, MD.MinDate, CF.CashFlowDate) AS DateDifference
    ) DD
    CROSS APPLY
    (
    SELECT
    POWER(1 + @Rate, DD.DateDifference / 365.0) AS FactorForF,
    POWER(1 + @Rate, DD.DateDifference / 365.0 + 1) AS FactorForFPrime
    ) F
    ;
    SET @Rate = @Rate – @F / @FPrime;
    IF (ABS(@Rate – @PrevRate) < @Tolerance)
    BEGIN
    BREAK;
    END
    SET @Iteration = @Iteration + 1;
    END
    RETURN @Rate;
    END

    Thank you.
    Ariel.

    Reply

Leave a Reply