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

<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