Using SQL To Calculate XIRR (Internal Rate of Return)

Thanks to binaryworld.net, I was finally able to get a sql way to calculate XIRR. After 2 long hours of search I found this site and the logic as well as the code works perfectly well!

XIRR is a function in excel that calculates Internal Rate of Return based on payments/income over a period of time.

Without further ado, here is the code (a slightly modified version from
BinaryWorld.net. Happy XIRRing!



-- First, CREATE XIRR Table to store values
CREATE TABLE XIRRTempData(
amt float,
dt datetime,
guid varchar(128)
)
go



create function dbo.XIRR(
@d datetime,
@GUID varchar(128)
) returns decimal(18,10)
as
begin

/*
USAGE: select @IRR = dbo.xirr(null, guid)
select @IRR IRR, @IRR * 100 'IRR %'

Note: Leave the first parameter (date) null if you wish to see the XIRR calculated as
of the maximum date in the dataset provided else provide a specific date to see
the XIRR calculated as the given date.

Created By: Ankeet Shah
Created On: 7/16/2008

*/
IF @d is null
SELECT @d = max(d) from IncomeTable

declare @irrPrev float set @irrPrev = 0
declare @irr float set @irr = -0.1
declare @PresentValuePrev float
declare @PresentValue float

set @PresentValuePrev = ( select sum(amt) from XIRRTempData where guid = @GUID )
set @PresentValue = (select sum(amt/power(1e0+@irr,cast(dt-@d as float)/360)) from XIRRTempData where guid = @GUID )

while abs(@PresentValue) >= 0.0001
begin
declare @t float

set @t = @irrPrev
set @irrPrev = @irr
set @irr = @irr + (@t-@irr)*@PresentValue/(@PresentValue-@PresentValuePrev)
set @PresentValuePrev = @PresentValue
set @PresentValue = (select sum(amt/power(1e0+@irr,cast(dt-@d as float)/365)) from XIRRTempData where guid = @GUID )
end

return @irr
end
go


-- usage
-- generate a GUID
DECLARE @GUID varchar(128)
SELECT @GUID = NewID()

-- INSERT test data INTO XIRRTempData table
insert into XIRRTempData values(-50000, '1/1/2007', @GUID)
insert into XIRRTempData values(500, '1/10/2007', @GUID)
insert into XIRRTempData values(500, '6/1/2007', @GUID)
insert into XIRRTempData values(500, '10/25/2007', @GUID)
insert into XIRRTempData values(500, '12/31/2007', @GUID)
insert into XIRRTempData values(500, '3/1/2008', @GUID)
insert into XIRRTempData values(51000, '6/15/2008', @GUID)

declare @IRR numeric(18,10)

select @IRR = dbo.xirr(getdate(), @GUID)
select @IRR IRR, @IRR * 100 'IRR %', getdate() DateParam


select @IRR = dbo.xirr(null, @GUID)
select @IRR IRR, @IRR * 100 'IRR %', NULL DateParam

go

drop function xirr
go

select * from XIRRTempData
go

drop table XIRRTempData
go