XIRR Calc в SQL

Question

Я пытаюсь найти достойную реализацию вычисления XIRR Excels в SQL.

Я нашел следующую функцию онлайн:

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

(взято отсюда)

После тестирования эта функция с большим успехом (результаты совпадают, чтобы превзойти). Я понял, что он не работает с набором транзакций, которые приведут к отрицательному XIRR.

Я не полностью понимаю внутренности алгоритма, и попробовал отладку без особой удачи.

Вот тестовый пример, который не удался:

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)    

Расчетное значение = -0,000000001

Ожидаемое значение = -0,12879

У кого-нибудь, кто понимает финансовые алгоритмы лучше, чем у меня, есть исправление для этого теста или лучшее решение в SQL?

Answer 1

Это связано с ценностями Excel:

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