SQL SERVER – Calculating XIRR in SQL Server – Internal Rate of Return Available

Note: Download the XLeratorDB to play along this blog post.

XIRR is a widely-used used financial calculation that lends itself to evaluating the rate of return on either historic cash flows or anticipated future cash flows. Thus it is a tool that can be used to measure investment performance or to evaluate the comparative desirability of future investment alternatives. Today we will look at Westclintech’s ingenious implementation of the XIRR function in SQL Server using SQL CLR.

According to the Microsoft Office documentation, “XIRR is closely related to XNPV, the net present value function. The rate of return calculated by XIRR is the interest rate corresponding to XNPV = 0.”

Let’s look at the example from the Microsoft Office documentation and try it out in SQL Server.

SELECT
wct.XIRR([values],[dates],NULL)AS XIRR
FROM (VALUES
(-10000,'2008-01-01'),
(2750,'2008-03-01'),
(4250,'2008-10-30'),
(3250,'2009-02-15'),
(2750,'2009-04-01')
)n([values],[dates])

This provides us with exactly the same answer as in the Microsoft Office documentation.

XIRR

----------------------

0.373362533509583

The Westclintech developers have taken advantage of the multi-input aggregate capabilities that were added to the SQL CLR with the release of SQL Server 2008. Their implementation of XIRR is just one of more than 750 functions contained in their XLeratorDB function library.

Interestingly, in playing around with the function I discovered cases where it seems like the Wesclintech function produces a result and the Excel function cannot. Here’s the calculation of one example

SELECT
wct.XIRR([values],[dates],NULL)AS XIRR
FROM (VALUES
(0,'2002-06-17'),
(-42814703.5,'2008-08-19'),
(44768952.93,'2002-11-16'),
(0,'2003-10-31'),
(-192485819.41,'2012-10-21'),
(-128316512.7,'2003-01-19'),
(-57458663.82,'2006-08-12'),
(0,'2006-01-01'),
(121072521.7,'2001-07-28'),
(59000533.69,'2008-01-17'),
(48964026.98,'2003-05-31')
)n([values],[dates])

where the Westclintech result is

XIRR

----------------------

0.101489308050466

But when I put the same cash flows into EXCEL 2013 I get an answer of 2.9802322388E-09, which seems kind of wrong since it is pretty much zero.

Remembering the Microsoft Office documentation, I decided to check the XNPV calculation with the value returned by XIRR. The XLeratorDB library includes the XNPV calculation, making it pretty easy to double-check the calculation using SQL.

WITH mycte AS (
SELECT
*
FROM (VALUES
(0,'2002-06-17'),
(-42814703.5,'2008-08-19'),
(44768952.93,'2002-11-16'),
(0,'2003-10-31'),
(-192485819.41,'2012-10-21'),
(-128316512.7,'2003-01-19'),
(-57458663.82,'2006-08-12'),
(0,'2006-01-01'),
(121072521.7,'2001-07-28'),
(59000533.69,'2008-01-17'),
(48964026.98,'2003-05-31')
)n([values],[dates])
)
SELECT
wct.XNPV(r,[values],[dates]) AS [NPV using XLDB value],
wct.XNPV(2.9802322388E-09,[values],[dates]) AS [NPV using Excel value]
FROM (
SELECT
wct.XIRR([values],[dates],NULL)AS r
FROM
mycte
)n
,mycte

This showed the XLeratorDB value did in fact return an XNPV value pretty close to zero while the Excel value did not.

NPV using XLDB value   NPV using Excel value

---------------------- ----------------------

2.23517417907715E-08   -147269656.931966

I then tried to double check the calculations in EXCEL.

As you can see, Excel didn’t like either of the solutions! A little further research explains why. It turns out that the Excel XNPV function expects the cash flows to be in date order but the Microsoft Office documentation for the XIRR function says “Dates may occur in any order.” Here’s what happens when we put the cash flows in order.

The first thing that I noticed is that Excel has calculated a new XIRR value! Now the XLeratorDB result and the Excel result agree to the first 6 decimal places.

Now I am really intrigued. I am not an expert on financial calculations, but I know that Excel is pretty much the gold standard for this type of financial calculation. Is it possible that there is something wrong with the Excel calculation? Since I now have two tools that should be generating the same answers I keep testing, looking for differences. Here’s another example.

SELECT
wct.XIRR([values],[dates],NULL)AS XIRR
FROM (VALUES
(138516675.3,'2010-09-30'),
(-33772930.31,'2003-06-21'),
(34598742.99,'2009-12-14'),
(42298808.27,'2008-01-15'),
(0,'2011-10-27'),
(-122807295.9,'2003-03-11'),
(4808700.25,'2003-04-06'),
(-49206052.38,'2011-11-21'),
(0,'2012-11-11'),
(0,'2002-06-30'),
(10152269.87,'2002-07-17')
)n([values],[dates])

I get this answer in SQL Server.

XIRR

----------------------

0.0259274294071075

And this answer in Excel.

Again a big difference from the XLeratorDB answer. But, we know that we can check the answer using the XNPV calculation, and we know that we need to put the cash flows in order.

Once again, the XIRR calculation in Excel changed (oddly enough, to the same value as in our first Excel example), but we were smart enough to preserve the value that was originally calculated, but this produces the #NUM! value in Excel. And finally, we see that the XLeratorDB value produces the XNPV = 0 value that the Microsoft Office documentation talks about. There is no explanation in the Microsoft Office documentation for Excel 2013 about the #NUM! value, though earlier versions of the documentation suggest that the rate value passed into XNPV must be greater than zero.

On the off-chance that the Excel calculation might be correct, I double-check the calculation using XLeratorDB (notice that I have moved from checking XLeratorDB against Excel to checking Excel against XLeratorDB).

WITH mycte AS (
SELECT
*
FROM (VALUES
(138516675.3,'2010-09-30'),
(-33772930.31,'2003-06-21'),
(34598742.99,'2009-12-14'),
(42298808.27,'2008-01-15'),
(0,'2011-10-27'),
(-122807295.9,'2003-03-11'),
(4808700.25,'2003-04-06'),
(-49206052.38,'2011-11-21'),
(0,'2012-11-11'),
(0,'2002-06-30'),
(10152269.87,'2002-07-17')
)n([values],[dates])
)
SELECT
wct.XNPV(r,[values],[dates]) AS [NPV using XLDB value],
wct.XNPV(-0.637033665925265,[values],[dates]) AS [NPV using Excel value]
FROM (
SELECT
wct.XIRR([values],[dates],NULL)AS r
FROM
mycte
)n
,mycte

As I expected, the Excel XIRR value does not return an XNPV = 0

NPV using XLDB value   NPV using Excel value

---------------------- ----------------------

2.84984707832336E-07   1268.43783508614

At this point, I am completely convinced that the XLeratorDB function is at least as good as the Excel function, the only question is how does it perform?

I randomly create a little more than 2.7 million rows of test data and dropped them into a table which consists of three columns: projectno, amt_cf, and date_cf. Projectno is simply a way of grouping cash flows together, amt_cf is the cash flow amount, and date_cf is the date of the cash flow. I didn’t do anything to avoid duplicate dates with a project number. I then ran the following SQL:

SET NOCOUNT ON
DECLARE @time_start AS DATETIME
DECLARE @time_end AS DATETIME
DECLARE @count_records AS INT
DECLARE @count_processed AS INT
DECLARE @results AS TABLE (
[Input Rows] INT,
[Output Rows] INT,
[Elapsed Time] FLOAT
)
CREATE TABLE #x (
ctr INT,
projectno INT,
xirr FLOAT
)
DECLARE @ctr AS INT = 0
WHILE @ctr < 100
BEGIN
SET @ctr = @ctr + 1
SET @count_records = (SELECT COUNT(*) FROM dbo.XNPV_cashflows)
SET @time_start = GETDATE()
INSERT INTO
#x
SELECT
@ctr,
X.projectno,
wct.XIRR(amt_cf,date_cf, NULL) AS IRR
FROM
dbo.XNPV_cashflows X
GROUP BY
X.projectno
SET @count_processed = @@ROWCOUNT
SET @time_end = GETDATE()
INSERT INTO
@results
SELECT
@count_records AS [Input rows],
@count_processed AS [Output rows],
DATEDIFF(ms,@time_start,@time_end)/1000e+00 AS [Elapsed Time]
END
SELECT
AVG([Input Rows]) AS [Input Rows],
AVG([Output Rows]) AS [Output Rows],
AVG([Elapsed Time]) AS [Elpased Time],
SUM([Input Rows])/SUM([Elapsed Time]) AS Throughput
FROM
@results
DROP TABLE
#x

The results of this test showed an average throughput of over 158,000 rows per second, which means that on average were able to complete and store the results of the XIRR calculation on 2.7 million rows of cash flows in about 17 seconds.

Input Rows  Output Rows Elpased Time           Throughput

----------- ----------- ---------------------- ----------------------

2700080     100045      17.0445555555556       158413.048154836

I did this test on my laptop which is a 64-bit Dell Precision M6600 with an Intel Core i7 2760QM CPU @ 2.40 GHz with 8 cores, 8GB of memory, and running Windows 7. Since the machine has 8 cores, there is plenty of opportunity for parallelization, which is handled automatically by the SQL CLR architecture for aggregate functions, which is what gives this function such astonishing throughput.

I think that the XLeratorDB package makes a compelling case for getting these calculations out of Excel and into SQL Server. Their calculations seem to be more accurate, scale up to millions of rows with absolutely no changes to the SQL, and are blazingly fast. Additionally, since they run on the database, it is easy to integrate these calculations into any other platform or programming language that can open a database connection. All you really need to know is a few lines of SQL. You should download the 15-day free trial today and you should check out some of the other interesting articles about the XIRR functio.

Reference: Pinal Dave (https://blog.sqlauthority.com)