CREATE FUNCTION dbo.XIRR_1
(
@d DATETIME,
@GUID VARCHAR(128)
) 
RETURNS DECIMAL(18,10)
AS
BEGIN

/*
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 as
of the maximum date in the dataSET provided else provide a specific date to see
the XIRR calculated as the given date.

Created By: Ankeet Shah
Created On: 7/16/2008

*/
IF @d IS NULL
SELECT @d = MAX(d) FROM IncomeTable

DECLARE @irrPrev FLOAT SET @irrPrev = 0
DECLARE @irr FLOAT SET @irr = -0.1
DECLARE @PresentValuePrev FLOAT
DECLARE @PresentValue FLOAT

SET @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.0001
BEGIN
DECLARE @t FLOAT

SET @t = @irrPrev
SET @irrPrev = @irr
SET @irr = @irr + (@t-@irr)*@PresentValue/(@PresentValue-@PresentValuePrev)
SET @PresentValuePrev = @PresentValue
SET @PresentValue = (SELECT SUM(amt/POWER(1e0+@irr,CAST(dt-@d as FLOAT)/365)) FROM XIRRTempData WHERE guid = @GUID )
END

RETURN @irr
END
GO

;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, r
FROM IRR
WHERE time_period = 4