XIRR Calc in SQL

Question

I'm trying to find a decent implementation of excels XIRR calculation in SQL.

I found the following function online:

CREATE TYPE dbo.MyXirrTable AS TABLE
        (
            theValue DECIMAL(19, 9) NOT NULL, 
            theDate DATETIME NOT NULL
        )
GO


CREATE FUNCTION dbo.XIRR
(
    @Sample MyXirrTable READONLY,
    @Rate DECIMAL(19, 9) = 0.1
)
RETURNS DECIMAL(38, 9)
AS
BEGIN
    DECLARE @LastRate DECIMAL(19, 9),
        @RateStep DECIMAL(19, 9) = 0.1,
        @Residual DECIMAL(19, 9) = 10,
        @LastResidual DECIMAL(19, 9) = 1,
        @i TINYINT = 0

    IF @Rate IS NULL
        SET @Rate = 0.1

    SET @LastRate = @Rate

    WHILE @i < 100 AND ABS((@LastResidual - @Residual) / @LastResidual) > 0.00000001
        BEGIN
            SELECT  @LastResidual = @Residual,
                @Residual = 0

            SELECT  @Residual = @Residual + theValue / POWER(1 + @Rate, theDelta / 365.0E)
            FROM    (
                    SELECT  theValue,
                        DATEDIFF(DAY, MIN(theDate) OVER (), theDate) AS theDelta
                    FROM    @Sample
                ) AS d

            SET @LastRate = @Rate

            If @Residual >= 0
                SET @Rate += @RateStep
            ELSE
                SELECT  @RateStep /= 2,
                    @Rate -= @RateStep

            SET @i += 1
        END

    RETURN  @LastRate
END
GO

(taken from here)

After testing this function with a lot of success (results matching up to excel). I've realised that it doesn't seem to work with a set of transactions that will result in a negative XIRR.

I don't fully understand the internals of the algorithm, and have tried debugging without much luck.

Here is a test case that failed:

DECLARE @Test MyXirrTable

INSERT  @Test
VALUES  (-4471762.56680002, '2008-11-13 00:00:00.000'),
    (+2607759.77, '2008-11-14 00:00:00.000'),
    (+12263.33, '2008-11-25 00:00:00.000'),
    (+1658.89, '2008-11-25 00:00:00.000'),
    (+1834423.33, '2008-12-04 00:00:00.000'),
    (-0.000245418674579822,'2013-11-14 00:00:00.000')       

SELECT  dbo.XIRR(@Test, 0.1)    

Calculated Value = -0.000000001

Expected Value = -0.12879

Does anyone that understands financial algorithms better than I do have a fix for this test case or a better solution in SQL?

Answer 1

This ties up with the excel values:

CREATE TYPE [dbo].[XIRRTable] AS TABLE(
    [Value] [decimal](19, 9) NOT NULL,
    [Date] [datetime] NOT NULL
)
GO   

CREATE FUNCTION [dbo].[CalcXIRR]
(
    @Sample XIRRTable READONLY,
    @Rate DECIMAL(19, 9) = 0.1
)
RETURNS DECIMAL(38, 9)
AS
BEGIN
    DECLARE @X DECIMAL(19, 9) = 0.0,
    @X0 DECIMAL(19, 9) = 0.1,
    @f DECIMAL(19, 9) = 0.0,
    @fbar DECIMAL(19, 9) = 0.0,
    @i TINYINT = 0,
    @found TINYINT = 0

IF @Rate IS NULL
    SET @Rate = 0.1

SET @X0 = @Rate

WHILE @i < 100
    BEGIN
        SELECT  @f = 0.0,
            @fbar = 0.0

        SELECT      @f = @f + value * POWER(1 + @X0, (-theDelta / 365.0E)),
        @fbar = @fbar - theDelta / 365.0E * value * POWER(1 + @X0, (-theDelta / 365.0E - 1))
        FROM    (
                SELECT  Value,
                    DATEDIFF(DAY, MIN(date) OVER (), date) AS theDelta
                FROM    @Sample
            ) AS d

        SET @X = @X0 - @f / @fbar

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

        SET @X0 = @X
        SET @i += 1
   END

If @found = 1
    RETURN  @X

RETURN NULL
END