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
For the above values excel gives irr = 43.5976392, but this sp gives -2.9%.
At first it was giving an error:
invalid float operation
To fix that I have changed the formula from
SUM(power(1e0 + @Rate, DATEDIFF(d, PDate, @EndDate) / 365e0) * PAmount)
to
SUM(power(1e0 + @Rate, DATEDIFF(m,@EndDate , PDate)) * PAmount).
This resolved the issure which i think was square root of negative but still values are wrong.
