Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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

	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 ]

Feedback

Gravatar

# 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.

Powered by:
Powered By Subtext Powered By ASP.NET