# Peter Larsson Blog

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
```<span style="COLOR: blue">IF</span> @Rate <span style="COLOR: blue">IS</span> <span style="COLOR: blue">NULL</span>
<span style="COLOR: blue">SET</span>	@Rate = 0.1

<span style="COLOR: blue">SET</span> @LastRate = @Rate

<span style="COLOR: blue">WHILE</span> @i &lt; 100 <span style="COLOR: blue">AND</span> <span style="COLOR: magenta">ABS</span>((@LastResidual - @Residual) / @LastResidual) &gt; 0.00000001
<span style="COLOR: blue">BEGIN</span>
<span style="COLOR: blue">SELECT</span>	@LastResidual = @Residual,
@Residual = 0

<span style="COLOR: blue">SELECT</span>	@Residual = @Residual + theValue / <span style="COLOR: magenta">POWER</span>(1 + @Rate, theDelta / 365.0E)
<span style="COLOR: blue">FROM</span>	(
<span style="COLOR: blue">SELECT</span>	theValue,
<span style="COLOR: blue">DATEDIFF</span>(<span style="COLOR: magenta">DAY</span>, <span style="COLOR: magenta">MIN</span>(theDate) <span style="COLOR: blue">OVER</span> (), theDate) <span style="COLOR: blue">AS</span> theDelta
<span style="COLOR: blue">FROM</span>	@Sample
) <span style="COLOR: blue">AS</span> d

<span style="COLOR: blue">SET</span>	@LastRate = @Rate

<span style="COLOR: blue">If</span> @Residual &gt;= 0
<span style="COLOR: blue">SET</span> @Rate += @RateStep
<span style="COLOR: blue">ELSE</span>
<span style="COLOR: blue">SELECT</span>	@RateStep /= 2,
@Rate -= @RateStep

<span style="COLOR: blue">SET</span>	@i += 1
<span style="COLOR: blue">END</span>

<span style="COLOR: blue">RETURN</span>	@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
```