Hi,
I am going to calculate XIRR Function in my T-SQL as inline function and some times it gives me an error like
Msg 3623, Level 16, State 1, Line 1
A domain error occurred.
and my function is below:
CREATE FUNCTION [dbo].[irr](@d DATETIME,@Policy_No VARCHAR(50),@FundIndex INT) RETURNS DECIMAL(18,10)
AS BEGIN
DECLARE @irrPrev FLOAT SET @irrPrev = 0
DECLARE @irr FLOAT SET @irr = 0.1
DECLARE @pvPrev FLOAT
DECLARE @pv FLOAT
SET @d = (SELECT MIN(d) FROM IncomeStream WHERE Policy_No = @Policy_No AND FundIndex = @FundIndex)
SET @pvPrev = (SELECT sum(amt) FROM IncomeStream WHERE Policy_No = @Policy_No AND FundIndex = @FundIndex)
SET @pv = (SELECT SUM(amt/POWER(1e0+@irr,CAST(d-@d as NUMERIC(38,6))/360e0)) FROM IncomeStream WHERE Policy_No = @Policy_No AND FundIndex = @FundIndex)
IF ISNULL(@d,0) = 0
BEGIN
RETURN 0
END
WHILE ABS(@pv) >= 0.0001 BEGIN
DECLARE @t FLOAT
SET @t = @irrPrev
SET @irrPrev = @irr
SET @irr = @irr + (@t-@irr)*@pv/NULLIF((@pv-@pvPrev),0)
SET @pvPrev = @pv
SET @pv = (SELECT SUM(amt/POWER(1e0+@irr,CAST(d-@d AS NUMERIC(38,6))/365e0)) FROM IncomeStream WHERE Policy_No = @Policy_No AND FundIndex = @FundIndex )
END
RETURN CASE WHEN @IRR =0.1 THEN 0 ELSE @IRR * 100 END
END
Maulik A. Dusara Tech Lead.
