Calculate XIRR in T-Sql

• Question

• 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

Friday, 6 August 2010 7:43 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 2008
• Proposed as answer by Friday, 6 August 2010 8:23 PM
• Marked as answer by Wednesday, 11 August 2010 10:08 AM
Friday, 6 August 2010 9:38 AM
• See for correct implementation of XIRR in Excel.
http://office.microsoft.com/en-gb/excel-help/xirr-HP005209341.aspx

• Proposed as answer by Friday, 6 August 2010 8:24 PM
• Marked as answer by Wednesday, 11 August 2010 10:08 AM
Friday, 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 2008
Friday, 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 2010-07-08

-875.25 2010-08-30

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 2008
• Proposed as answer by Friday, 6 August 2010 8:23 PM
• Marked as answer by Wednesday, 11 August 2010 10:08 AM
Friday, 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