create table txns( PmtAmount money ,PmtDate Datetime)insert into txns select -2750,'20080205'union select 1000,'20080705'union select 2000,'20090105'

DECLARE @nGuessRate NUMERIC(18,10)SET @nGuessRate = 0.1 -- 10% - as per Excel XIRR defaultDECLARE @dtFirstPmtDate DATETIMEDECLARE @nRateChange NUMERIC(18,10)DECLARE @nResidual NUMERIC(18,10)DECLARE @nPrevResidual NUMERIC(18,10)DECLARE @nXIRR NUMERIC(18,10)SET @nRateChange = 0.1 -- starting point of the rate changeSELECT @dtFirstPmtDate=MIN(PmtDate) FROM txnsSELECT @nResidual = 10 ,@nPrevResidual = 1 ,@nXIRR = @nGuessRateDECLARE @nIteration SMALLINTSET @nIteration = 0 -- 100 is a maximum number of calc. iterations as per Excel XIRRWHILE @nIteration < 100 AND ABS((@nPrevResidual - @nResidual) / @nPrevResidual) > 1.0/POWER(10,8)BEGIN SET @nPrevResidual = @nResidual SET @nResidual = 0 SELECT @nResidual = @nResidual + PmtAmount/ POWER((1.0+@nGuessRate),(DATEDIFF(d,@dtFirstPmtDate,PmtDate)/365.0)) FROM txns SET @nXIRR = @nGuessRate IF @nResidual >= 0 BEGIN SET @nGuessRate = @nGuessRate + @nRateChange END ELSE BEGIN SET @nRateChange = @nRateChange / 2 SET @nGuessRate = @nGuessRate - @nRateChange END SET @nIteration = @nIteration + 1END-- Calculation resultSELECT @nXIRR AS XIRR

CREATE TYPE dbo.MyXirrTable AS TABLE ( theValue DECIMAL(19, 9) NOT NULL, theDate DATETIME NOT NULL )GOCREATE FUNCTION dbo.XIRR(@Sample MyXirrTable READONLY,@Rate DECIMAL(19, 9) = 0.1)RETURNS DECIMAL(38, 9)ASBEGINDECLARE @X DECIMAL(19, 9) = 0.0, @X0 DECIMAL(19, 9) = 0.1, @f DECIMAL(19, 9) = 0.0, @fbar DECIMAL(19, 9) = 0.0, @i TINYINT = 0,IF @Rate IS NULL SET @Rate = 0.1SET @X0 = @RateWHILE @i < 100 BEGIN SELECT @f = 0.0, @fbar = 0.0 SELECT @f = @f + theValue * POWER(1 + @X0, (-theDelta / 365.0E)), @fbar = @fbar - theDelta / 365.0E * theValue * POWER(1 + @X0, (-theDelta / 365.0E - 1)) FROM ( SELECT theValue, DATEDIFF(DAY, MIN(theDate) OVER (), theDate) AS theDelta FROM @Sample ) AS d IF @fbar = 0 RETURN NULL SET @X = @X0 - @f / @fbar If ABS(@X - @X0) < 0.00000001 RETURN @X SET @X0 = @X SET @i += 1 END RETURN NULLENDGO