# Thinking outside the box

Patron Saint of Lost Yaks

## Excel XIRR function

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
(
@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
```

Print | posted on Thursday, August 19, 2010 9:46 PM | Filed Under [ SQL Server 2008 Algorithms ]

## #re: Excel XIRR function

Very interesting post. really informative. of all the blogs I have read on the same topic, this one is actually enlightening.
10/24/2012 8:25 AM | longchamp handbags
Comments have been closed on this topic.