Функция IRR для возврата также отрицательных значений

Question

    GO
    /****** Object:  UserDefinedFunction [dbo].[fn_IRR]    Script Date: 7/28/2014 11:43:40 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER  FUNCTION [dbo].[fn_IRR]
    (
        @str varchar(max),
        @precision DECIMAL(30,10)
    )
    RETURNS DECIMAL(30, 10)
    AS 
    BEGIN
        declare  @AdjValue decimal(30,10)
                ,@guess Decimal(30,10)
                ,@guess_new Decimal(30,10)

        Select @AdjValue = 0.1, @guess=0

        DECLARE @t_IDs TABLE (
            id INT IDENTITY(0, 1),
            value DECIMAL(30, 10)
        )
        Declare @NPV DECIMAL(30, 10)
               ,@iter_cnt int

        INSERT INTO @t_IDs 
            select * from dbo.fn_SplitString(@str,',')

        SET @guess = CASE WHEN ISNULL(@guess, 0) <= 0 THEN 0 ELSE @guess END

        SELECT @NPV = SUM(value / POWER(1 + @guess, id)) FROM @t_IDs
        WHILE ((@NPV > 0 or @AdjValue > @precision) and (isnull(@iter_cnt,0) < 8192))
        BEGIN
            SET @guess_new = @guess + @AdjValue
            SELECT @NPV = SUM(value / POWER(1 + @guess_new, id)) FROM @t_IDs
            set @iter_cnt = isnull(@iter_cnt,0) + 1
            if (@NPV > 0)
                select @guess=@guess_new
            else
                select @AdjValue=@AdjValue/10
        END
        RETURN @guess
    END


USE [absmart_v442]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_SplitString]    Script Date: 7/28/2014 11:47:58 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fn_SplitString](
     @str nvarchar(max)
    ,@sep nvarchar(max)
)
RETURNS TABLE
AS
RETURN
    WITH a AS(
        SELECT CAST(0 AS BIGINT) as idx1,CHARINDEX(@sep,@str) idx2
        UNION ALL
        SELECT idx2+1,CHARINDEX(@sep,@str,idx2+1)
        FROM a
        WHERE idx2>0
    )
    SELECT SUBSTRING(@str,idx1,COALESCE(NULLIF(idx2,0),LEN(@str)+1)-idx1) as StrValue
    FROM a

Итак, у меня есть этот код, который рассчитывает IRR для набора значений. Функция отлично работает (я получил код из другого вопроса stackru). Моя единственная проблема в том, что я не могу найти способ, чтобы она показала также отрицательные значения, если IRR > 0, функция дает хороший результат, если IRR < 0, она возвращает 0, но я бы хотела, чтобы она возвращала отрицательные значения. результат.

Проблема в том, что я не совсем понимаю, как рассчитывается значение IRR или почему он печатает 0, если IRR отрицателен. Также я не могу использовать отладчик из SQL из-за некоторых проблем (я пробовал). Любой знает, как убедиться, что функция всегда возвращает IRR, даже если это отрицательное значение.

select [dbo].[fn_IRR]('-45000,15000,20000,25000.00,10000.00,5000',0.00000000001) as IRR should return 0.2299339513




select [dbo].[fn_IRR]('-170000,32000,35000,33000.00,29000.00,36000',0.00000000001) as IRR

должен вернуть -0,98, как это возвращается в Excel

Answer 1

Мне удалось найти решение самостоятельно, вы можете инициализировать @guess с -0,99

Answer 2

Исходный пример выше отлично подходит для положительных чисел, но не для отрицательных. Проблема связана с добавлением POWER 1, и поэтому она всегда положительна в цикле. Я расширил вышеупомянутое решение, которое обслуживает как положительные, так и отрицательные числа.

Примечание: в блоке ELSE один завершен, вы должны вычесть значение из 1, а затем преобразовать в отрицательное число.

ALTER FUNCTION [dbo].[fn_IRR]

(@str varchar (max), @precision DECIMAL(30,10)) ВОЗВРАЩАЕТСЯ DECIMAL (30, 10) КАК НАЧИНАЕТСЯ объявить @AdjValue decimal(30,10),@guess Decimal(30,10),@guess_new Decimal(30,10)

Select @AdjValue = 0.1, @guess=0

DECLARE @t_IDs TABLE (
    id INT IDENTITY(0, 1),
    value DECIMAL(30, 10)
)
Declare @NPV DECIMAL(30, 10)
       ,@iter_cnt int

INSERT INTO @t_IDs 
    select * from dbo.fn_SplitString(@str,',')

SET @guess = CASE WHEN ISNULL(@guess, 0) <= 0 THEN 0 ELSE @guess END

SELECT @NPV = SUM(value / POWER(1 + @guess, id)) FROM @t_IDs

IF @NPV > 0
    BEGIN
        WHILE ((@NPV > 0 or @AdjValue > @precision) and (isnull(@iter_cnt,0) < 8192))
        BEGIN
            SET @guess_new = @guess + @AdjValue
            SELECT @NPV = SUM(value / POWER(1 + @guess_new, id)) FROM @t_IDs
            set @iter_cnt = isnull(@iter_cnt,0) + 1
            if (@NPV > 0)
                select @guess=@guess_new
            else
                select @AdjValue=@AdjValue/10
        END
    END
ELSE
    BEGIN
        WHILE ((@NPV > 0 or @AdjValue > @precision) and (isnull(@iter_cnt,0) < 8192))
            BEGIN
                SET @guess_new = @guess + @AdjValue
                SELECT @NPV = SUM(value / POWER(@guess_new, id)) FROM @t_IDs
                set @iter_cnt = isnull(@iter_cnt,0) + 1
                if (@NPV > 0)
                    select @guess=@guess_new
                else
                    select @AdjValue=@AdjValue/10
            END

        SET @guess = 1 - @guess
        SET @guess = -@guess
    END

RETURN @guess

КОНЕЦ