Расчет XIRR в SQL

Question

Я пытался найти решение для расчета XIRR в SQL (TSQL). В идеале значения должны соответствовать значениям, которые Excel вычисляет с использованием одних и тех же входных данных. Я нашел несколько предлагаемых решений в Интернете, но все они, похоже, имеют недостатки. Ниже приведен пример, который мы использовали, который работает почти во всех случаях.

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

GO

Тем не менее, с данными ниже,

мы получаем ошибку

An invalid floating point operation occurred.

Это происходит на линии

SELECT @f = @f + value * POWER(1 + @X0, (-theDelta / 365.0E))

По сути дела сводится к конкретному расчету

POWER(-0.635634780,-0.0849315)

Может ли быть так, что есть некоторая простая настройка синтаксиса, которая может исправить эту ошибку, или сама функция не будет работать? Excel, кажется, обрабатывает этот расчет без проблем.

Это только один из многих примеров, которые я пытался использовать. Я могу разбить другой пример, если это необходимо. Я могу отредактировать пост, чтобы он соответствовал стандарту Stack Overflow с указаниями. Я нахожу очень необычным то, что нет ясного обсуждения того, как вычислять XIRR в SQL. Кажется, что у каждого решения есть проблемы, но Excel выкладывает значения так легко.

Answer 1

Предполагая, что ваше изображение в A1 вверху слева. Не решает ваш SQL, но я надеюсь, что может помочь

Во-первых, мой Excel также возвращает -0.6719218,

Во-вторых, расчет Excel детализирован в функции XIRR, из которой часть:

Тем не менее, это для серии денежных потоков, для которых Excel использует итерацию, и ваш конкретный пример - просто пара парных записей, для которых разрешимое уравнение может быть выражено как:

=10^(LOG(-A2/A1)*365/(B2-B1))

Эта часть: 365/(B2-B1) является взаимностью вашего 0.0849315так что, возможно, стоит 0.635634780 повнимательнее (мне не ясно, как вы к этому пришли).