SQL中的XIRR Calc [英] XIRR Calc in SQL

问题描述

我正在尝试在SQL中找到一个出色的XIRR计算实现.

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

(取自此处)

在测试此功能后，取得了很多成功(结果与excel匹配).我已经意识到，它似乎不适用于会导致XIRR值为负的一组交易.

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)    

计算值= -0.000000001

Calculated Value = -0.000000001

期望值= -0.12879

Expected Value = -0.12879

比我更了解财务算法的人是否对此测试用例有更好的解决方案，或者在SQL中有更好的解决方案?

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

推荐答案

这与excel值有关:

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

这篇关于SQL中的XIRR Calc的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！