I have been handed over a code which needs some performance tuning and then a front end built on. According to the current developer, the iterative section for calculating the growth could be enhanced a little bit.
(starting WHILE @i < 100 AND ABS((@LastResidual - @Residual) / @LastResidual) > 0.000000001).
Currently its not very good at handling negative yields at the moment.
FULL CODE PORTION BELOW :
DECLARE @LastRate DECIMAL(19, 9) = 0.4,
@RateStep DECIMAL(19, 9) = 0.4,
@Residual DECIMAL(19, 9) = 10,
@LastResidual DECIMAL(19, 9) = 1,
@i TINYINT = 0,
@Rate DECIMAL(19, 9) = 0.4
SET @LastRate = @Rate
--Continue for 100 tries or accuracy of 0.000001%
WHILE @i < 100 AND ABS((@LastResidual - @Residual) / @LastResidual) > 0.000000001
BEGIN
SELECT @LastResidual = @Residual,
@Residual = 0
SELECT @Residual = @Residual + d.amount / POWER(1 + @Rate, theDelta / 365.0E)
FROM (
SELECT fst.amount,
DATEDIFF(DAY, MIN(fst.effective_datetime) OVER (), effective_datetime) AS theDelta
FROM #xirr_transactions fst
) d
SET @LastRate = @Rate
IF @Residual >=0
BEGIN
IF @Rate > 0
SET @Rate += @RateStep
ELSE SET @Rate -= @RateStep
END
ELSE IF @Rate = -0.000000001
SELECT @Rate = -0.4,
@RateStep = -0.4,
@LastResidual = -0.3
ELSE IF @RateStep > 0
SELECT @RateStep /= 2,
@Rate -= @RateStep
ELSE IF @RateStep < 0
SELECT @RateStep /= 2,
@Rate -= @RateStep
SET @i += 1
END
SET @xirr_plus_one = @LastRate + 1
Please help
Other recent topics
