Excel XIRR function

Thu Aug 19, 2010 by Peter Larsson in sql-server-2008, algorithms

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

Legacy Comments


longchamp handbags 2012-10-24	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.