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

Tags:
Categories:

## Search Blogs

 KeywordsPhrase

## Blog Archives

Archive
 < September 2024 >
SunMonTueWedThuFriSat
25262728293031
1234567
891011121314
15161718192021
22232425262728
293012345
Monthly
Go