Calculating Internal Rates of Return in SQL Server for multiple periods and multiple holdings
May
19
Written by:
Charles Flock
5/19/2011 6:37 PM
In the article we will look at how we can use the XLeratorDB XIRR function to calculate the internal rate of return for individual securities in a portfolio as well as for all the securities in a portfolio, across multiple time horizons.
A few weeks ago an XLeratorDB user asked for help in constructing a query to analyze the returns on his portfolio by year, using the XIRR function. He was looking for a presentation which would show the internal rate of return by investment over a period of years and in total. The result would be a table that looks like this:
In this article I will explain how you can do this for yourself as well as explain what the numbers in this table mean. In the examples that follow I am using the XIRR function in from XLeratorDB/financial 2008 running in SQL Server 2008.
Let’s create some temporary tables to store information about the transactions that we are going to use in this example. We will have one table, called #ps, where we store information about purchase and sale transactions and another table, called #div, where we store information about dividends.
CREATE TABLE #ps(
sym nvarchar(10),
tdate datetime,
qty float,
amt float
)
CREATE TABLE #div(
sym nvarchar(10),
tdate datetime,
amt float
)
We then insert the following data, showing our purchases and dividends received for IBM, into these two tables.
INSERT INTO #ps VALUES ('IBM','2006-Jul-10',1400,-102998)
INSERT INTO #div VALUES ('IBM','2006-Aug-08',420)
INSERT INTO #div VALUES ('IBM','2006-Nov-08',420)
INSERT INTO #div VALUES ('IBM','2007-Feb-07',420)
INSERT INTO #ps VALUES ('IBM','2007-Apr-01',600,-61800)
INSERT INTO #div VALUES ('IBM','2007-May-08',800)
INSERT INTO #div VALUES ('IBM','2007-Aug-08',1600)
INSERT INTO #div VALUES ('IBM','2007-Nov-07',1600)
INSERT INTO #div VALUES ('IBM','2008-Feb-06',800)
INSERT INTO #div VALUES ('IBM','2008-May-07',2000)
INSERT INTO #div VALUES ('IBM','2008-Aug-06',1000)
INSERT INTO #div VALUES ('IBM','2008-Nov-06',1000)
INSERT INTO #div VALUES ('IBM','2009-Feb-06',1000)
INSERT INTO #div VALUES ('IBM','2009-May-06',1100)
INSERT INTO #div VALUES ('IBM','2009-Aug-06',1100)
INSERT INTO #div VALUES ('IBM','2009-Nov-06',1100)
INSERT INTO #div VALUES ('IBM','2010-Feb-08',1100)
INSERT INTO #div VALUES ('IBM','2010-May-06',1300)
INSERT INTO #div VALUES ('IBM','2010-Aug-06',1300)
INSERT INTO #div VALUES ('IBM','2010-Nov-08',1300)
INSERT INTO #div VALUES ('IBM','2011-Feb-08',1300)
INSERT INTO #div VALUES ('IBM','2011-May-06',1300)
Purchase transactions show a negative cash flow, sale transactions show a positive cash flow, and dividends received show as a positive cash flow. If we run the following SQL, we can see the cash flows.
SELECT sym
,tdate
,amt
FROM #ps
UNION ALL
SELECT sym
,tdate
,amt
FROM #div
ORDER BY 1, 2
The produces the following result.
sym tdate amt
---------- ----------------------- ----------------------
IBM 2006-07-10 00:00:00.000 -102998
IBM 2006-08-08 00:00:00.000 420
IBM 2006-11-08 00:00:00.000 420
IBM 2007-02-07 00:00:00.000 420
IBM 2007-04-01 00:00:00.000 -61800
IBM 2007-05-08 00:00:00.000 800
IBM 2007-08-08 00:00:00.000 1600
IBM 2007-11-07 00:00:00.000 1600
IBM 2008-02-06 00:00:00.000 800
IBM 2008-05-07 00:00:00.000 2000
IBM 2008-08-06 00:00:00.000 1000
IBM 2008-11-06 00:00:00.000 1000
IBM 2009-02-06 00:00:00.000 1000
IBM 2009-05-06 00:00:00.000 1100
IBM 2009-08-06 00:00:00.000 1100
IBM 2009-11-06 00:00:00.000 1100
IBM 2010-02-08 00:00:00.000 1100
IBM 2010-05-06 00:00:00.000 1300
IBM 2010-08-06 00:00:00.000 1300
IBM 2010-11-08 00:00:00.000 1300
IBM 2011-02-08 00:00:00.000 1300
IBM 2011-05-06 00:00:00.000 1300
This is not enough information to accurately calculate the internal rate of return, however. In fact, if we invoke the XIRR function with just this data, we get a hugely negative return, which, we will see in a few paragraphs, is not the correct result.
SELECT wct.XIRR(cfamt,cfdate,NULL) as IRR
FROM (
SELECT sym
,tdate
,amt
FROM #ps
UNION ALL
SELECT sym
,tdate
,amt
FROM #div
) #x(sym, cfdate, cfamt)
This produces the following result.
IRR
----------------------
-0.495802793329428
Calculating a meaningful internal rate of return requires that we know the ending market value and that we treat the ending market value as a positive cash flow, assuming that we are long the securities. If we are short the securities, then the ending market value would be treated as a negative cash flow. In this example, we can calculate the ending market value as the quantity of securities that we own, multiplied by the closing price, which in this case is 168.86. I have highlighted the changes to the previous SQL to include this value
SELECT wct.XIRR(cfamt,cfdate,NULL) as IRR
FROM (
SELECT sym
,tdate
,amt
FROM #ps
UNION ALL
SELECT sym
,tdate
,amt
FROM #div
UNION ALL
SELECT sym
,'2011-May-06'
,SUM(qty) * 168.86
FROM #ps
GROUP by sym
) #x(sym, cfdate, cfamt)
This produces the following result.
IRR
----------------------
0.193226838339943
The way to interpret this result is that our investment in IBM has returned 19.32% per annum from 2006-Jul-07 to 2011-May-16.
What we really want to do, however, is calculate the internal rate of return as at the end of each year since we made our initial purchase, through to the current date. This requires that we know what the market value was at the end of each year.
We will create a temporary table called #emv to hold the ending market value for each position we have as at the end of the year and as of 2011-May-16.
CREATE TABLE #emv(
sym nvarchar(10),
tdate datetime,
mv float
)
Then, we just insert the ending market values into the temporary table.
INSERT INTO #emv VALUES ('IBM','2006-Dec-29',136010)
INSERT INTO #emv VALUES ('IBM','2007-Dec-31',216200)
INSERT INTO #emv VALUES ('IBM','2008-Dec-31',168320)
INSERT INTO #emv VALUES ('IBM','2009-Dec-31',261800)
INSERT INTO #emv VALUES ('IBM','2010-Dec-31',293520)
INSERT INTO #emv VALUES ('IBM','2011-May-16',337720)
At this point we have all the pieces in place to calculate the internal rate of return as at the end of each year for the investment in IBM. I am going to use a common table expression (CTE) to do this, as this should simplify the SQL.
;WITH mycte as (
SELECT sym
,tdate as cfdate
,YEAR(tdate) as yr
,mv as cfamt
FROM #emv
UNION ALL
--This gets all the cash flows from purchases & sales
SELECT #ps.sym
,#ps.tdate
,YEAR(#emv.tdate)
,#ps.amt
FROM #emv, #ps
WHERE #ps.sym = #emv.sym
AND #ps.tdate < = #emv.tdate
UNION ALL
--This gets all the cash flows from dividends
SELECT #div.sym
,#div.tdate
,YEAR(#emv.tdate)
,#div.amt
FROM #emv, #div
WHERE #div.sym = #emv.sym
AND #div.tdate < = #emv.tdate
) SELECT sym
,yr
,ROUND(wct.XIRR(cfamt,cfdate,NULL), 4) as IRR
FROM mycte
GROUP BY sym, yr
This produces the following result.
sym yr IRR
---------- ----------- ----------------------
IBM 2006 0.8319
IBM 2007 0.2779
IBM 2008 0.0376
IBM 2009 0.18
IBM 2010 0.1711
IBM 2011 0.1932
What is this telling us? It tells us that from the first trade on 2006-Jul-10 to the end of 2006, the internal rate of return was 83.19% per annum. From 2006-Jul-10 to the end of 2007, the rate was 27.79%. From 2006-Jul-10 to the end of 2008, the rate was 3.76%. It’s important to realize that this is the rate from inception to the end of each respective year.
Let’s add some more transactions and ending market values to the example.
INSERT INTO #ps VALUES ('XOM','2007-Feb-16',1500,-102990)
INSERT INTO #ps VALUES ('XOM','2008-Mar-18',500,-41010)
INSERT INTO #ps VALUES ('XOM','2009-Apr-19',750,-46327.5)
INSERT INTO #ps VALUES ('XOM','2010-May-20',-2000,120660)
INSERT INTO #div VALUES ('XOM','2011-May-11',352.5)
INSERT INTO #div VALUES ('XOM','2011-Feb-08',330)
INSERT INTO #div VALUES ('XOM','2010-Nov-09',330)
INSERT INTO #div VALUES ('XOM','2010-Aug-11',330)
INSERT INTO #div VALUES ('XOM','2010-May-11',1210)
INSERT INTO #div VALUES ('XOM','2010-Feb-08',1155)
INSERT INTO #div VALUES ('XOM','2009-Nov-09',1155)
INSERT INTO #div VALUES ('XOM','2009-Aug-11',1155)
INSERT INTO #div VALUES ('XOM','2009-May-11',1155)
INSERT INTO #div VALUES ('XOM','2009-Feb-06',800)
INSERT INTO #div VALUES ('XOM','2008-Nov-07',800)
INSERT INTO #div VALUES ('XOM','2008-Aug-11',800)
INSERT INTO #div VALUES ('XOM','2008-May-09',800)
INSERT INTO #div VALUES ('XOM','2008-Feb-07',525)
INSERT INTO #div VALUES ('XOM','2007-Nov-07',525)
INSERT INTO #div VALUES ('XOM','2007-Aug-09',525)
INSERT INTO #div VALUES ('XOM','2007-May-10',525)
INSERT INTO #emv VALUES ('XOM','2007-Dec-31',140535)
INSERT INTO #emv VALUES ('XOM','2008-Dec-31',159660)
INSERT INTO #emv VALUES ('XOM','2009-Dec-31',187522.5)
INSERT INTO #emv VALUES ('XOM','2010-Dec-31',54840)
INSERT INTO #emv VALUES ('XOM','2011-May-16',60180)
We don’t need to make any changes to our SQL. We just simply run the same SQL that calculated the IRR for IBM, and it will include the IBM data and the XOM data.
;WITH mycte as (
SELECT sym
,tdate as cfdate
,YEAR(tdate) as yr
,mv as cfamt
FROM #emv
UNION ALL
--This gets all the cash flows from purchases & sales
SELECT #ps.sym
,#ps.tdate
,YEAR(#emv.tdate)
,#ps.amt
FROM #emv, #ps
WHERE #ps.sym = #emv.sym
AND #ps.tdate < = #emv.tdate
UNION ALL
--This gets all the cash flows from dividends
SELECT #div.sym
,#div.tdate
,YEAR(#emv.tdate)
,#div.amt
FROM #emv, #div
WHERE #div.sym = #emv.sym
AND #div.tdate < = #emv.tdate
) SELECT sym
,yr
,ROUND(wct.XIRR(cfamt,cfdate,NULL), 4) as IRR
FROM mycte
GROUP BY sym, yr
This produces the following result.
sym yr IRR
---------- ----------- ----------------------
IBM 2006 0.8319
IBM 2007 0.2779
XOM 2007 0.4501
IBM 2008 0.0376
XOM 2008 0.0881
IBM 2009 0.18
XOM 2009 0.015
IBM 2010 0.1711
XOM 2010 -0.0062
IBM 2011 0.1932
XOM 2011 0.0057
It might be more interesting to look at the results in a format where each year is represented in a column, thus making it easier to visually compare the results among different symbols. We can simply use PIVOT to do this. I have highlighted the changes to the SQL below.
;WITH mycte as (
SELECT sym
,tdate as cfdate
,YEAR(tdate) as yr
,mv as cfamt
FROM #emv
UNION ALL
--This gets all the cash flows from purchases & sales
SELECT #ps.sym
,#ps.tdate
,YEAR(#emv.tdate)
,#ps.amt
FROM #emv, #ps
WHERE #ps.sym = #emv.sym
AND #ps.tdate < = #emv.tdate
UNION ALL
--This gets all the cash flows from dividends
SELECT #div.sym
,#div.tdate
,YEAR(#emv.tdate)
,#div.amt
FROM #emv, #div
WHERE #div.sym = #emv.sym
AND #div.tdate < = #emv.tdate
) SELECT *
FROM (
SELECT sym
,yr
,ROUND(wct.XIRR(cfamt,cfdate,NULL), 4) as IRR
FROM mycte
GROUP BY sym, yr
) p
PIVOT(MIN(IRR) FOR yr in([2006],[2007],[2008],[2009],[2010],[2011])) as d
ORDER BY 1
This produces the following result.
sym 2006 2007 2008 2009 2010 2011
---- -------- -------- -------- -------- -------- --------
IBM 0.8319 0.2779 0.0376 0.1800 0.1711 0.1932
XOM NULL 0.4501 0.0881 0.0150 -0.0062 0.0057
As we add more data to the transaction table, we don’t need to make any changes to the SQL.
INSERT INTO #ps VALUES ('AAPL','2007-Mar-16',1100,-98549)
INSERT INTO #ps VALUES ('AAPL','2008-Apr-18',550,-88572)
INSERT INTO #ps VALUES ('AAPL','2009-May-19',825,-105146.25)
INSERT INTO #ps VALUES ('AAPL','2010-Jun-21',-2000,540340)
INSERT INTO #ps VALUES ('JPM','2007-Mar-16',2100,-98763)
INSERT INTO #ps VALUES ('JPM','2008-Apr-18',1050,-48048)
INSERT INTO #ps VALUES ('JPM','2009-May-19',1575,-56400.75)
INSERT INTO #ps VALUES ('JPM','2010-Jun-21',-4000,155480)
INSERT INTO #div VALUES ('JPM','2011-Apr-04',181.25)
INSERT INTO #div VALUES ('JPM','2011-Jan-04',36.25)
INSERT INTO #div VALUES ('JPM','2010-Oct-04',36.25)
INSERT INTO #div VALUES ('JPM','2010-Jul-01',36.25)
INSERT INTO #div VALUES ('JPM','2010-Apr-01',236.25)
INSERT INTO #div VALUES ('JPM','2010-Jan-04',236.25)
INSERT INTO #div VALUES ('JPM','2009-Oct-02',236.25)
INSERT INTO #div VALUES ('JPM','2009-Jul-01',236.25)
INSERT INTO #div VALUES ('JPM','2009-Apr-02',157.5)
INSERT INTO #div VALUES ('JPM','2009-Jan-02',1197)
INSERT INTO #div VALUES ('JPM','2008-Oct-02',1197)
INSERT INTO #div VALUES ('JPM','2008-Jul-01',1197)
INSERT INTO #div VALUES ('JPM','2008-Apr-02',798)
INSERT INTO #div VALUES ('JPM','2008-Jan-02',798)
INSERT INTO #div VALUES ('JPM','2007-Oct-03',798)
INSERT INTO #div VALUES ('JPM','2007-Jul-03',798)
INSERT INTO #div VALUES ('JPM','2007-Apr-03',714)
INSERT INTO #ps VALUES ('GOOG','2007-Apr-19',250,-117912.5)
INSERT INTO #ps VALUES ('GOOG','2008-May-22',200,-109892)
INSERT INTO #ps VALUES ('GOOG','2009-Jun-19',250,-105022.5)
INSERT INTO #ps VALUES ('GOOG','2010-Jul-21',-700,310375)
INSERT INTO #emv VALUES ('AAPL','2007-Dec-31',217888)
INSERT INTO #emv VALUES ('AAPL','2008-Dec-31',140827.5)
INSERT INTO #emv VALUES ('AAPL','2009-Dec-31',521556.75)
INSERT INTO #emv VALUES ('AAPL','2010-Dec-31',153216)
INSERT INTO #emv VALUES ('AAPL','2011-May-16',158317.5)
INSERT INTO #emv VALUES ('GOOG','2007-Dec-31',172870)
INSERT INTO #emv VALUES ('GOOG','2008-Dec-31',138442.5)
INSERT INTO #emv VALUES ('GOOG','2009-Dec-31',433986)
INSERT INTO #emv VALUES ('GOOG','2010-Dec-31',0)
INSERT INTO #emv VALUES ('JPM','2007-Dec-31',91665)
INSERT INTO #emv VALUES ('JPM','2008-Dec-31',99319.5)
INSERT INTO #emv VALUES ('JPM','2009-Dec-31',196890.75)
INSERT INTO #emv VALUES ('JPM','2010-Dec-31',30754.5)
INSERT INTO #emv VALUES ('JPM','2011-May-16',31088)
We just run our select statement again and it produces the following result.
sym 2006 2007 2008 2009 2010 2011
---- -------- -------- -------- -------- -------- --------
AAPL NULL 1.7145 -0.2040 0.3763 0.4315 0.4214
GOOG NULL 0.7255 -0.3615 0.1669 -0.0313 NULL
IBM 0.8319 0.2779 0.0376 0.1800 0.1711 0.1932
JPM NULL -0.0615 -0.2152 0.0047 -0.0173 -0.0157
XOM NULL 0.4501 0.0881 0.0150 -0.0062 0.0057
Notice that the return for GOOG in 2011 is NULL. There is no #emv row for GOOG in 2011, as the position was closed out in 2010. If we had wanted to include GOOG, we could include a 2011 #emv row with a market value of zero.
What if we want to include a calculation of the rate of return across all of our investments? We can think of this as the total internal rate of return. All we need to do is remove the ‘sym’ column from our original SELECT. The following SQL calculates this total internal rate of return quite easily.
SELECT *
FROM (
SELECT yr
,ROUND(wct.XIRR(cfamt,cfdate,NULL), 4) as IRR
FROM mycte
GROUP BY yr
) p
PIVOT(MIN(IRR) FOR yr in([2006],[2007],[2008],[2009],[2010],[2011])) as d
This produces the following result.
2006 2007 2008 2009 2010 2011
------- ------- ------- ------- ------- -------
0.8319 0.5069 -0.1163 0.1707 0.1454 0.1953
Is there any way to include this result in our PIVOT query so that we can see that total internal rate of return along with the internal rate of return by symbol? The easiest way to is to simply combine the detail and the summary query using a UNION ALL, with a little adjustment (I added a space to the beginning of the sym column) to make the total come out as the last row. I have highlighted the changes to the SQL.
;WITH mycte as (
SELECT sym
,tdate as cfdate
,YEAR(tdate) as yr
,mv as cfamt
FROM #emv
UNION ALL
--This gets all the cash flows from purchases & sales
SELECT #ps.sym
,#ps.tdate
,YEAR(#emv.tdate)
,#ps.amt
FROM #emv, #ps
WHERE #ps.sym = #emv.sym
AND #ps.tdate < = #emv.tdate
UNION ALL
--This gets all the cash flows from dividends
SELECT #div.sym
,#div.tdate
,YEAR(#emv.tdate)
,#div.amt
FROM #emv, #div
WHERE #div.sym = #emv.sym
AND #div.tdate < = #emv.tdate
) SELECT *
FROM (
SELECT ' ' + sym as sym
,yr
,ROUND(wct.XIRR(cfamt,cfdate,NULL), 4) as IRR
FROM mycte
GROUP BY sym, yr
UNION ALL
SELECT 'TOTAL'
,yr
,ROUND(wct.XIRR(cfamt,cfdate,NULL), 4) as IRR
FROM mycte
GROUP BY yr
) p
PIVOT(MIN(IRR) FOR yr in([2006],[2007],[2008],[2009],[2010],[2011])) as d
ORDER BY 1
This produces the following result.
sym 2006 2007 2008 2009 2010 2011
------ -------- -------- -------- -------- -------- --------
AAPL NULL 1.7145 -0.2040 0.3763 0.4315 0.4214
GOOG NULL 0.7255 -0.3615 0.1669 -0.0313 NULL
IBM 0.8319 0.2779 0.0376 0.1800 0.1711 0.1932
JPM NULL -0.0615 -0.2152 0.0047 -0.0173 -0.0157
XOM NULL 0.4501 0.0881 0.0150 -0.0062 0.0057
TOTAL 0.8319 0.5069 -0.1163 0.1707 0.1454 0.1953
You should recognize this as the table that is presented at the beginning of this article. And, remember, this is showing the internal rate of return from the date of the first transaction through to the last of the year of the column. Thus, the total return in the column with the heading 2011 is the total return from the first transction on 2006-Jul-07 to the latest day in #emv table, 2011-May-16.
If we wanted to calculate the internal rate of return for each year, we need to provide the calculation with an initial cash flow for that year. It’s strightforward to make the opening cash flow for the year the ending market value for the previous year multipied by -1. In other words, we are treating the start of the year as a purchase transaction. To keep things simple, we all assume that this transaction settled on the last day of the previous year and we will be calculating the internal rate of return from year end to year year end.
We just need to make some slight modifications to our existing SQL and we can achieve that result.
;WITH mycte as (
SELECT sym
,tdate as cfdate
,YEAR(tdate) as yr
,mv as cfamt
FROM #emv
UNION ALL
--This gets all the cash flows from purchases & sales
SELECT #ps.sym
,#ps.tdate
,YEAR(#emv.tdate)
,#ps.amt
FROM #emv, #ps
WHERE #ps.sym = #emv.sym
AND #ps.tdate < = #emv.tdate
AND #ps.tdate > wct.CALCDATE(Year(#emv.tdate) - 1, 12, 31)
UNION ALL
--This gets all the cash flows from dividends
SELECT #div.sym
,#div.tdate
,YEAR(#emv.tdate)
,#div.amt
FROM #emv, #div
WHERE #div.sym = #emv.sym
AND #div.tdate < = #emv.tdate
AND #div.tdate > wct.CALCDATE(Year(#emv.tdate) - 1, 12, 31)
UNION ALL
--This gets the previous market value * -1 and includes it as a --cash flow as at the last date of the previous year
SELECT sym
,wct.CALCDATE(YEAR(tdate),12,31) as cfdate
,YEAR(tdate) + 1 as yr
,-mv as cfamt
FROM #emv
WHERE MONTH(tdate) = 12
) SELECT *
FROM (
SELECT ' ' + sym as sym
,yr
,ROUND(wct.XIRR(cfamt,cfdate,NULL), 4) as IRR
FROM mycte
GROUP BY sym, yr
UNION ALL
SELECT 'TOTAL'
,yr
,ROUND(wct.XIRR(cfamt,cfdate,NULL), 4) as IRR
FROM mycte
GROUP BY yr
) p
PIVOT(MIN(IRR) FOR yr in([2006],[2007],[2008],[2009],[2010],[2011])) as d
ORDER BY 1
This produces the following result.
sym 2006 2007 2008 2009 2010 2011
------ -------- -------- -------- -------- -------- --------
AAPL NULL 1.7145 -0.5752 1.4148 0.6392 0.0919
GOOG NULL 0.7255 -0.5750 1.0280 -0.4543 NULL
IBM 0.8319 0.1267 -0.2013 0.5881 0.1416 0.4893
JPM NULL -0.0615 -0.2913 0.3286 -0.0899 0.0490
XOM NULL 0.4501 -0.1101 -0.0746 -0.0819 0.3242
TOTAL 0.8319 0.4699 -0.3910 0.6809 0.0644 0.3236
It would be extremely difficult to do this analysis in EXCEL, especially in a portfolio or portfolios with lots of turnover. Without using VBA, I would actually think that it’s impossible. Doing it in SQL Server, however, is quite simple, and once you have your SQL in place, it is not sensitive to the data at all. So, whether you do 10 trades a year or 10,000 trades a year, there is no need to make any change to the calculations. You just record your transactions, and everything is automatically included in these queries. You can even automate the calculation of the ending market values. What could be easier?