locked
Calculate XIRR in T-Sql RRS feed

  • 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



    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 2008
    • Proposed as answer by Naomi N Friday, 6 August 2010 8:23 PM
    • Marked as answer by KJian_ 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 Naomi N Friday, 6 August 2010 8:24 PM
    • Marked as answer by KJian_ 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

     

     


    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 2008
    • Proposed as answer by Naomi N Friday, 6 August 2010 8:23 PM
    • Marked as answer by KJian_ 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