Answered by:
Calculate XIRR in TSql
Question

Hi,
I am going to calculate XIRR Function in my TSQL 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.Friday, 6 August 2010 7:43 AM
Answers

The following maybe your problem:
POWER(1e0+@irr,CAST(d@d AS NUMERIC(38,6))/365e0
You have to check the domain first then evaluate the POWER function.
Example for valid math domain filtering:
SELECT 1/ListPrice FROM AdventureWorks2008.Production.Product WHERE ListPrice > 0
Kalman Toth, SQL Server & Business Intelligence Training; BI TRIO 2008Friday, 6 August 2010 9:38 AM 
See for correct implementation of XIRR in Excel.
http://office.microsoft.com/engb/excelhelp/xirrHP005209341.aspxFriday, 6 August 2010 10:25 AM
All replies

You get this error when performing an invalid math operation. The following sample executed on SQL Server 2008. 1 is not in the domain of the SQRT (squareroot) function:
 SQL Server illegal math operation DECLARE @Nbr money = 1 SELECT 10* SQRT(@Nbr) /* Msg 3623, Level 16, State 1, Line 3 An invalid floating point operation occurred. */
Kalman Toth, SQL Server & Business Intelligence Training; BI TRIO 2008Friday, 6 August 2010 8:11 AM 
how could i solve the problem and i am getting an error regarding data like
Amount Date
1000.00 20100708875.25 20100830
Maulik A. Dusara Tech Lead.Friday, 6 August 2010 9:12 AM 
The following maybe your problem:
POWER(1e0+@irr,CAST(d@d AS NUMERIC(38,6))/365e0
You have to check the domain first then evaluate the POWER function.
Example for valid math domain filtering:
SELECT 1/ListPrice FROM AdventureWorks2008.Production.Product WHERE ListPrice > 0
Kalman Toth, SQL Server & Business Intelligence Training; BI TRIO 2008Friday, 6 August 2010 9:38 AM 
This is giving me correct result for my data. Perfectly matching with Excel calculation. Thanks!!
Regards, Anil Nagthane
Tuesday, 9 July 2019 9:50 AM