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 function at www.westclintech.com.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

SQL SERVER – Three Questions – Do You Know Your Servers? – Book Gift

It has been almost 2 years since we had a poll on this blog so here a poll which ask you three simple questions. Trust me these questions, I have carefully picked after doing a beta testing on a small group of people. It was very intriguing to see that not everyone knows the answer to these simple questions. When I asked the users to answer these questions and later on asked to find out the real number they were extremely surprised to see that their estimate and answer were way off to the reality. Let us do this exercise together.

The contest has two parts. You need to take part in the both the contest.

Part 1:Answer following questions

Click here to take survey

Part 2: Subscribe to our infrequent newsletter

Click here to subscribe to the newsletter.

If you are already subscribed to the newsletter, you have already completed this second part.

The winner will be announced on Monday 21st April via Newsletter only.

The winner will get a very inspiring book which I have been reading recently – Drive: The Surprising Truth about What Motivates us by Daniel Pink (Amazon | Flipkart).

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Finding Last Backup Time for All Database – Last Full, Differential and Log Backup – Optimized

Four years ago, I wrote a blog post SQL SERVER – Finding Last Backup Time for All Database. It has been a very popular script. SQL Expert Sravani has posted a fantastic script which also displays last full, differential and log backup for the database – I have blogged about it here SQL SERVER – Finding Last Backup Time for All Database – Last Full, Differential and Log Backup.

Well, in technology, there is always space for improvement and we should always be ready to learn and progress. SQL Expert TheSQLGuru came up with further optimized script which also lists all the details which earlier blog post listed, and the script is very quick to execute. Here is the script.

SELECT d.name AS 'DATABASE_Name',
MAX(CASE WHEN bu.TYPE = 'D' THEN bu.LastBackupDate END) AS 'Full DB Backup Status',
MAX(CASE WHEN bu.TYPE = 'I' THEN bu.LastBackupDate END) AS 'Differential DB Backup Status',
MAX(CASE WHEN bu.TYPE = 'L' THEN bu.LastBackupDate END) AS 'Transaction DB Backup Status',
CASE d.recovery_model WHEN 1 THEN 'Full' WHEN 2 THEN 'Bulk Logged' WHEN 3 THEN 'Simple' END RecoveryModel
FROM MASTER.sys.databases d
LEFT OUTER JOIN (SELECT database_name, TYPE, MAX(backup_start_date) AS LastBackupDate
FROM msdb.dbo.backupset
GROUP BY database_name, TYPE) AS bu ON d.name = bu.database_name
GROUP BY d.Name, d.recovery_model

Thanks TheSQLGuru for excellent script, you win USD 20 Amazon Gift Card or INR 1000 Flipkart Gift Voucher. I have already sent you emails with details.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – Microsoft Whitepaper – Migrating Content Between Report Servers

A very common challenge developer and administrator face when they have to migrate content between SQL Server Reporting Server. This is because SQL Server Reporting Services (SSRS) currently doesn’t include a feature for migrating content items and settings from one Reporting Services report server to another report server. There are two different methods to do overcome this challenge. 1) Create RSS scripts that are used by rs.exe to migrate the content of Reporting Services, between reports servers of the same mode or different modes. 2) Run Reporting Services Migration Tool to migrate the content from a Native mode report server to a SharePoint mode report server.

The white paper discusses the Reporting Services Migration tool and various aspects of method of migration like the location of the tool, supportability of the tool, a list of contents migrated by the tool, migration steps and known issues with the tool. You can download the tool from here.

Click here to download and read the white paper

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – Microsoft Whitepaper – Idle Connection Resiliency

Developers can write data access application that enables ideal connection resiliency with the .NET framework. An idle connection is the one that is active but it’s not executing a command or waiting for data. It is very important to understand how idle connection is reconnecting back in the .NET framework with SQL Server. This white paper actually discusses the same in very simple works. The user has to connect either to a SQL Server 2014 or Microsoft Azure, SQL Database to enable idle connection resiliency.

Here is a very interesting example in the of the idle connection resiliency provided in the Overview section of the Whitepaper.

Let’s imagine that you are a roaming worker that needs to use an Access application to connect to SQL Server.  When you need to move from meeting to meeting, you normally close your notebook’s lid in order to move.  In working online, every time this happens, you may end up disconnected either because your notebooks sleep or due to blind wireless spots in your building.  To avoid the hassle of being disconnected, you may choose to avoid certain places (like elevators, for example) and walk with your notebook’s lid open. Now, imagine if you can close your lid and walk anywhere in your building (even take the elevator) and just arrive to your next meeting, open your lid and find your work there, waiting for you to continue.  To  address this and other scenarios when an idle connection drops, SQL Server introduced a new feature called Idle Connection Resiliency.

Well, that’s it. This white paper describes the internal working of the Idle Connection Resiliency. It further discusses about the Client’s idle connection, reconnect logic, Client session state handling and replay logic, Non-recoverable session states, and General Considerations.

Click here to read the white paper on Idle Connection Resiliency.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Drop All the Foreign Key Constraint in Database – Create All the Foreign Key Constraint in Database

Earlier I wrote a blog post about how to Disable and Enable all the Foreign Key Constraint in the Database. It is a very popular article. However, there are some scenarios when user needs to drop and recreate the foreign constraints. Here is a fantastic blog comment by SQL Expert Swastik Mishra. He has written a script which drops all the foreign key constraints and recreates them. He uses temporary tables to select the existing foreign keys and respective column name and table name. Then determine the primary key table and column name and accordingly drop and recreate them.

Here is a script by Swastik and it works great.

SET NOCOUNT ON
DECLARE
@table TABLE(
RowId INT PRIMARY KEY IDENTITY(1, 1),
ForeignKeyConstraintName NVARCHAR(200),
ForeignKeyConstraintTableSchema NVARCHAR(200),
ForeignKeyConstraintTableName NVARCHAR(200),
ForeignKeyConstraintColumnName NVARCHAR(200),
PrimaryKeyConstraintName NVARCHAR(200),
PrimaryKeyConstraintTableSchema NVARCHAR(200),
PrimaryKeyConstraintTableName NVARCHAR(200),
PrimaryKeyConstraintColumnName NVARCHAR(200)
)
INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
SELECT
U.CONSTRAINT_NAME,
U.TABLE_SCHEMA,
U.TABLE_NAME,
U.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE
C.CONSTRAINT_TYPE = 'FOREIGN KEY'
UPDATE @table SET
PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
FROM
@table T
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME
UPDATE @table SET
PrimaryKeyConstraintTableSchema = TABLE_SCHEMA,
PrimaryKeyConstraintTableName = TABLE_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME
UPDATE @table SET
PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME
--SELECT * FROM @table
--DROP CONSTRAINT:
SELECT
'
ALTER TABLE ['
+ ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
DROP CONSTRAINT '
+ ForeignKeyConstraintName + '

GO’
FROM
@table
–ADD CONSTRAINT:
SELECT

ALTER TABLE ['
+ ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
ADD CONSTRAINT ‘
+ ForeignKeyConstraintName + ‘ FOREIGN KEY(+ ForeignKeyConstraintColumnName + ‘) REFERENCES [' + PrimaryKeyConstraintTableSchema + '].[' + PrimaryKeyConstraintTableName + '](‘ + PrimaryKeyConstraintColumnName + ‘)

GO’
FROM
@table
GO

Thanks Swastik for a great script. Swastik, please send me email at my mail id and I will send USD 20 worth Amazon Gift Card or INR 1000 Flipkart Gift Voucher for your wonderful contribution. If you use any such script in your daily routine. Please send me email and I will be glad to post the same on blog with due credit.

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

SQL SERVER – Finding Jobs Shrinking Database Files – Notes from the Field #023

[Notes from Pinal]: Search on the internet about ‘Shrinking Database’, you will find plenty of advice why it is bad and it should not be done. If you do not believe me, just try it yourself. Even I have blogged about it before that it is an absolutely bad thing to do. However, there are very few blogs which talks about how to solve this ancient problem. The reason, I call it ancient problem is that there are quite a few organizations which are doing this for many years and they have many different places where they have included Shrinking Database code. There are quite a few places, where I see that people have created database jobs to shrink the database. I was talking to Tim about this and he comes up with a beautifully simple script where he demonstrated how to find out jobs which are shrinking database files.

Linchpin People are database coaches and wellness experts for a data driven world. In this 16th episode of the Notes from the Fields series database expert Tim Radney (partner at Linchpin People) explains a very simple script to find out jobs shrinking database files.


Often when analyzing a SQL Server Instance I come across jobs that are automating shrinking a database file.  Checking for a shrinking operation is important when analyzing a server for a number of reasons.  One is that anytime a data file or log file has to grow, transactions have to wait until the growth operation is complete thus causing a performance impact.  Shrinking a log file can contribute to high virtual log file counts and shrinking a data file will lead to fragmenting the database.

I check to see if any database maintenance plans exist and if so I check to see if auto shrink has been chosen, occasionally I find this is the case.  I also have a TSQL script that will search for any reference to the word ‘shrink’ in a job step.  I find custom jobs like this more often than auto shrink being used in a maintenance plan.  Often times the justification for someone configuring a job like this is due to drive space issues.

In cases where the shrink is to reclaim drive space it is most often due to not having a proper backup routine in place for the transaction logs or a process that keeps a transaction open for an extended period of time.  The transaction log will need to be large enough to handle your index maintenance, ETL processes and transactions that occur between log backups.   If you find that you are having an erratic large growth, then you will need to examine what transactions or processes are holding open an active transaction for such a long duration or the frequency of your log backups.

Below is the script I use to search for the word shrink in any tsql job step.

DECLARE @search VARCHAR(100)
SET @Search = 'shrink'
SELECT  A.[job_id],
B.[name],
[step_id],
[step_name],
[command],
[database_name]
FROM    [msdb].[dbo].[sysjobsteps] A
JOIN [msdb].dbo.sysjobs B ON A.job_id = B.[job_id]
WHERE   command LIKE '%' + @Search + '%'
ORDER BY [database_name],
B.[name],
[step_id]

If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.

Note: Tim has also written an excellent book on SQL Backup and Recovery, a must have for everyone.


Related Articles:

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