CREATE FUNCTION dbo.XIRR_1(@d DATETIME,@GUID VARCHAR(128)) RETURNS DECIMAL(18,10)ASBEGIN/*USAGE: SELECT @IRR = dbo.xirr(null, guid)SELECT @IRR IRR, @IRR * 100 'IRR %'Note: Leave the first parameter (date) null if you wish to see the XIRR calculated asof the maximum date in the dataSET provided else provide a specific date to seethe XIRR calculated as the given date.Created By: Ankeet ShahCreated On: 7/16/2008*/IF @d IS NULLSELECT @d = MAX(d) FROM IncomeTableDECLARE @irrPrev FLOAT SET @irrPrev = 0DECLARE @irr FLOAT SET @irr = -0.1DECLARE @PresentValuePrev FLOATDECLARE @PresentValue FLOATSET @PresentValuePrev = ( SELECT SUM(amt) FROM XIRRTempData WHERE guid = @GUID )SET @PresentValue = (SELECT SUM(amt/POWER(1e0+@irr,CAST(dt-@d as FLOAT)/360)) FROM XIRRTempData WHERE guid = @GUID )WHILE abs(@PresentValue) >= 0.0001BEGINDECLARE @t FLOATSET @t = @irrPrevSET @irrPrev = @irrSET @irr = @irr + (@t-@irr)*@PresentValue/(@PresentValue-@PresentValuePrev)SET @PresentValuePrev = @PresentValueSET @PresentValue = (SELECT SUM(amt/POWER(1e0+@irr,CAST(dt-@d as FLOAT)/365)) FROM XIRRTempData WHERE guid = @GUID )ENDRETURN @irrENDGO

;WITH SumFlows AS ( SELECT project_name ,C0=SUM(ABS(CASE time_period WHEN 0 THEN payment_amt END)) ,A=SUM(CASE WHEN time_period <> 0 THEN payment_amt END) ,n=COUNT(payment_amt) FROM #CashFlows GROUP BY project_name), IRR AS ( SELECT time_period, project_name, payment_amt, NPV=payment_amt ,NPV0=CAST(0 AS DECIMAL(12,4)) ,A, C0, n, r=CAST(r AS FLOAT), rn0=CAST(0 AS FLOAT) FROM #CashFlows a CROSS APPLY ( SELECT A, C0, n, r=POWER(A/C0, 2./n)-1.0 FROM SumFlows b WHERE a.project_name = b.project_name ) b WHERE time_period = 0 UNION ALL SELECT a.time_period, a.project_name, a.payment_amt ,NPV=CAST(a.payment_amt/POWER(1.+r, a.time_period) AS DECIMAL(12,4)) ,NPV0=NPV ,b.A, b.C0, b.n ,CASE a.time_period -- Second initial guess (a cheat) WHEN 1 THEN r-.01 -- POWER(1. + b.r, LOG10(b.A/b.C0)/LOG10(b.A/?) -- Secant method without correction ELSE r-((r-rn0)/(NPV-NPV0)) END ,r FROM #CashFlows a INNER JOIN IRR b ON b.time_period + 1 = a.time_period AND a.project_name = b.project_name )SELECT project_name, rFROM IRRWHERE time_period = 4