First create a table type like this
CREATE TYPE dbo.MyXirrTable AS TABLE
(
theValue DECIMAL(19, 9) NOT NULL,
theDate DATETIME NOT NULL
)
GO
And then you create a function like this
CREATE FUNCTION dbo.XIRR
(
@Sample MyXirrTable READONLY,
@Rate DECIMAL(19, 9) = 0.1
)
RETURNS DECIMAL(38, 9)
AS
BEGIN
DECLARE @LastRate DECIMAL(19, 9),
@RateStep DECIMAL(19, 9) = 0.1,
@Residual DECIMAL(19, 9) = 10,
@LastResidual DECIMAL(19, 9) = 1,
@i TINYINT = 0
IF @Rate IS NULL
SET @Rate = 0.1
SET @LastRate = @Rate
WHILE @i < 100 AND ABS((@LastResidual - @Residual) / @LastResidual) > 0.00000001
BEGIN
SELECT @LastResidual = @Residual,
@Residual = 0
SELECT @Residual = @Residual + theValue / POWER(1 + @Rate, theDelta / 365.0E)
FROM (
SELECT theValue,
DATEDIFF(DAY, MIN(theDate) OVER (), theDate) AS theDelta
FROM @Sample
) AS d
SET @LastRate = @Rate
If @Residual >= 0
SET @Rate += @RateStep
ELSE
SELECT @RateStep /= 2,
@Rate -= @RateStep
SET @i += 1
END
RETURN @LastRate
END
GO
Then all you have to do is to test it! Like this...
DECLARE @Test MyXirrTable
INSERT @Test
VALUES (-10, '20080101'),
(2.75, '20080301'),
(4.25, '20081030'),
(3.25, '20090215'),
(2.75, '20090401')
SELECT dbo.XIRR(@Test, 0.1) --0.373362535