sql-server - 尝试使用 sql 存储过程计算 irr，需要帮助

Question

CREATE procedure testXIRR
(
@AmountEstimate as float = 0,
@AmountGoal as float = 0,
@AmountPrecision as float = 1e-7 ,
@AmountPrevious as float = 0,
@EndDate as date = null,
@FlagError as bit = 0,
@FlagLoop as bit = 1,
@Rate as float = 0.1,
@RateDelta as float = 1e-2,
@RateInitial as float = 0,
@RateUpDown as smallint = 0,
@TableName as varchar(20) = NULL,
@query as varchar(20) = NULL
)
AS
BEGIN 
Declare @powerVariable as float

Declare @MyTable Table(
PDate date,
PAmount float
)
insert into @MyTable values('01-Jan-13',-500),('01-Feb-13',20),('01-Mar-13',50),('01-Apr-13',80),('01-May-13',110),('01-Jun-13',140),('01-Jul-13',170)
set @Rate = @RateInitial
select @AmountGoal = -PAmount, @EndDate = PDate from @MyTable where PAmount < 0 

while @FlagLoop = 1 
    begin
        if abs(@AmountEstimate - @AmountGoal) < @AmountPrecision or ABS(@Rate) > 1
            set @FlagLoop = 0
        else
            begin
                if (@AmountPrevious is not null) 
                    begin   
                        if @AmountPrevious < @AmountGoal and @AmountEstimate < @AmountGoal
                            set @RateUpDown = 1
                        if @AmountPrevious > @AmountGoal and @AmountEstimate > @AmountGoal 
                            set @RateUpdown = -1 
                        if (@AmountPrevious < @AmountGoal and @AmountEstimate > @AmountGoal) or (@AmountPrevious > @AmountGoal and @AmountEstimate < @AmountGoal) 
                            set @RateDelta = @RateDelta / 2e0
                    end
                set @AmountPrevious = @AmountEstimate 
                set @Rate = (@Rate + (@RateDelta * @RateUpDown))
                select @AmountEstimate = SUM(power(1e0 + @Rate, DATEDIFF(m,@EndDate , PDate)) * PAmount)  from @MyTable where PAmount >= 0

            end
        end
  print @Rate  
if Abs(@Rate) > 1 
    begin
        set @FlagError = 1
        print   -100000000
    end
else
    begin
        print   @Rate
        end
END
GO

对于上述值，excel 给出 irr = 43.5976392，但这个 sp 给出 -2.9%。

起初它给出了一个错误：

无效的浮动操作

为了解决这个问题，我已将公式从 更改
SUM(power(1e0 + @Rate, DATEDIFF(d, PDate, @EndDate) / 365e0) * PAmount)
为
SUM(power(1e0 + @Rate, DATEDIFF(m,@EndDate , PDate)) * PAmount)。

这解决了我认为是负数平方根但值仍然错误的问题。

标签： sql-servertsqlstored-proceduresirr