Calculating XIRR for multiple portfolios and multiple time periods in SQL Server
May
13
Written by:
Charles Flock
5/13/2015 3:27 PM
Calculating internal rates of return for many accounts or portfolios across many different time horizons is a laborious and error-prone task in a spreadsheet and the calculations are easily broken when new cash flows are added. In this article we look at how doing this calculation in SQL Server with just a few lines of SQL lets you calculate internal rates of return for millions of cash flows across all of your accounts or portfolios across multiple time horizons in just a few seconds.
In this article we take a hands-on look at some simple things that you can do in SQL Server using XLeratorDB to simplify performance reporting based on internal rates of return. To do that we need some data. The following SQL populates a temporary table, #mv, with the ending market values for 8 different portfolios for each quarter from 2010-12-31 to 2015-03-31 as well as the ending market value as of 2015-04-24. Some of the portfolios had their initial investments occur after 2010-12-31, so there is no entry in the market value table and some portfolios were closed before 2015-04-24. As a matter of convention, the ending market values are recorded as positive values.
--Create ending market values as of 2015-04-24 and for the
--end of each quarter back to 2010-12-31
SELECT port, cast(dt as datetime) as dt, cast(mv as money) as mv
INTO #mv
FROM (VALUES
('AAA','2015-04-24',3546169.74),('BBB','2015-04-24',3176936.32),('CCC','2015-04-24',4585545.55),('DDD','2015-04-24',4809068.79),('EEE','2015-04-24',4909217.65)
,('AAA','2015-03-31',3473231.87),('BBB','2015-03-31',3117700.02),('CCC','2015-03-31',4650654.72),('DDD','2015-03-31',4724036.14),('EEE','2015-03-31',4958805.71)
,('AAA','2014-12-31',3529707.19),('BBB','2014-12-31',3041658.56),('CCC','2014-12-31',4550542.78),('DDD','2014-12-31',4608815.75),('EEE','2014-12-31',5039436.7)
,('AAA','2014-09-30',3457107.92),('BBB','2014-09-30',3053874.06),('CCC','2014-09-30',4591869.61),('DDD','2014-09-30',4698079.26),('EEE','2014-09-30',5034402.3)
,('AAA','2014-06-30',3534875.17),('BBB','2014-06-30',3038680.66),('CCC','2014-06-30',4484247.67),('DDD','2014-06-30',4759958.72),('EEE','2014-06-30',5029372.93)
,('AAA','2014-03-31',3506820.61),('BBB','2014-03-31',2996726.49),('CCC','2014-03-31',4461937.98),('DDD','2014-03-31',4698873.37),('EEE','2014-03-31',5100783.9)
,('AAA','2013-12-31',3542243.04),('BBB','2013-12-31',3061007.65),('CCC','2013-12-31',4361620.7),('DDD','2013-12-31',4643155.5),('EEE','2013-12-31',4995870.62)
,('AAA','2013-09-30',3549341.72),('BBB','2013-09-30',3079484.56),('CCC','2013-09-30',4437050.56),('DDD','2013-09-30',4671182.6),('EEE','2013-09-30',4897912.37)
,('AAA','2013-06-30',3503792.42),('BBB','2013-06-30',3107451.62),('CCC','2013-06-30',4450401.77),('DDD','2013-06-30',4615793.08),('EEE','2013-06-30',4907727.83)
,('AAA','2013-03-31',3514335.43),('BBB','2013-03-31',3177353.39),('CCC','2013-03-31',4481774.19),('DDD','2013-03-31',4734146.75),('EEE','2013-03-31',5007885.54)
,('AAA','2012-12-31',3571479.1),('BBB','2012-12-31',3099856.97),('CCC','2012-12-31',4596691.48),('DDD','2012-12-31',4705911.28),('EEE','2012-12-31',4914509.85)
,('AAA','2012-09-30',3636944.09),('BBB','2012-09-30',3121708.93),('CCC','2012-09-30',4488956.52),('DDD','2012-09-30',4654709.48),('EEE','2012-09-30',4959142.13)
,('AAA','2012-06-30',3604503.56),('BBB','2012-06-30',3072548.16),('CCC','2012-06-30',4475529.93),('DDD','2012-06-30',4599515.3),('EEE','2012-06-30',4984062.44)
,('AAA','2012-03-31',3666839.84),('BBB','2012-03-31',3100452.23),('CCC','2012-03-31',4471058.87),('DDD','2012-03-31',4655379.86),('EEE','2012-03-31',4964205.62)
,('AAA','2011-12-31',3692688.66),('BBB','2011-12-31',3176692.86),('CCC','2011-12-31',4475534.4),('DDD','2011-12-31',4564097.9),('EEE','2011-12-31',4979143.05)
,('AAA','2011-09-30',3685318.02),('BBB','2011-09-30',3129746.66),('CCC','2011-09-30',4444423.44),('DDD','2011-09-30',4483396.76),('EEE','2011-09-30',4999139.61)
,('AAA','2011-06-30',3752869.67),('BBB','2011-06-30',3068379.08),('CCC','2011-06-30',4409150.24),('DDD','2011-06-30',4412792.09),('EEE','2011-06-30',4891526.04)
,('AAA','2011-03-31',3833370.45),('BBB','2011-03-31',3127807.42),('CCC','2011-03-31',4382853.12),('DDD','2011-03-31',4475448.37),('EEE','2011-03-31',4876895.35)
,('AAA','2010-12-31',3931662),('BBB','2010-12-31',3175439),('CCC','2010-12-31',4280130),('DDD','2010-12-31',4431137),('EEE','2010-12-31',4804823)
,('FFF','2011-09-30',1464578.72),('GGG','2014-12-31',2091476.79)
,('FFF','2011-12-31',1550076.45),('GGG','2015-03-31',2102056.46)
,('FFF','2012-03-31',1561232.48),('GGG','2015-04-24',2180357.96)
,('FFF','2012-06-30',1670569.33),('HHH','2012-06-30',3314175.68)
,('FFF','2012-09-30',1743008.37),('HHH','2012-09-30',3277245.69)
,('FFF','2012-12-31',1717127.36),('HHH','2012-12-31',3234338.08)
,('FFF','2013-03-31',1884871.78),('HHH','2013-03-31',3198409.56)
,('FFF','2013-06-30',1796611.87),('HHH','2013-06-30',3162490.36)
,('FFF','2013-09-30',1745072.09),('HHH','2013-09-30',3129125.9)
,('FFF','2013-12-31',1811542.29),('HHH','2011-09-30',3451121.68)
,('FFF','2014-03-31',1794138.18),('HHH','2011-12-31',3410438.24)
,('FFF','2014-06-30',1740592.68),('HHH','2012-03-31',3356028.26)
,('FFF','2014-09-30',1684900.39)
,('FFF','2014-12-31',1849758.32)
,('FFF','2015-03-31',1704707.49)
,('FFF','2015-04-24',1721553.51)
)n(port,dt,mv)
The following SQL populates a temporary table, #cf, with cash flows for each of the portfolios in the #mv table.
--Create a bunch of cash flows
SELECT port, cast(dt as datetime) as dt, cast(cf as money) as cf
INTO #cf
FROM (VALUES
('AAA','2015-04-10',-37703.81),('BBB','2015-04-10',-31071.97),('CCC','2015-04-10',-18647.66),('DDD','2015-04-10',-19545.71),('EEE','2015-04-10',-31454.7)
,('AAA','2015-01-10',-36820.12),('BBB','2015-01-10',-31803.45),('CCC','2015-01-10',-18317.94),('DDD','2015-01-10',-19181.26),('EEE','2015-01-10',-32129.41)
,('AAA','2014-10-10',-36347.6),('BBB','2014-10-10',-31551.04),('CCC','2014-10-10',-17923.62),('DDD','2014-10-10',-19673.09),('EEE','2014-10-10',-32355.9)
,('AAA','2014-07-10',-36603.83),('BBB','2014-07-10',-31901.96),('CCC','2014-07-10',-18289.41),('DDD','2014-07-10',-19614.25),('EEE','2014-07-10',-32485.85)
,('AAA','2014-04-10',-36313.32),('BBB','2014-04-10',-31368.69),('CCC','2014-04-10',-18271.14),('DDD','2014-04-10',-19732.64),('EEE','2014-04-10',-32880.41)
,('AAA','2014-01-10',-36025.12),('BBB','2014-01-10',-32041.56),('CCC','2014-01-10',-18701.26),('DDD','2014-01-10',-19654.03),('EEE','2014-01-10',-33246.12)
,('AAA','2013-10-10',-36573.73),('BBB','2013-10-10',-32105.77),('CCC','2013-10-10',-18986.06),('DDD','2013-10-10',-19872.62),('EEE','2013-10-10',-32562.31)
,('AAA','2013-07-10',-36831.55),('BBB','2013-07-10',-32234.71),('CCC','2013-07-10',-18986.06),('DDD','2013-07-10',-19852.77),('EEE','2013-07-10',-33260.79)
,('AAA','2013-04-10',-36794.75),('BBB','2013-04-10',-32692.41),('CCC','2013-04-10',-18559.19),('DDD','2013-04-10',-19406.42),('EEE','2013-04-10',-33161.3)
,('AAA','2013-01-10',-37507.4),('BBB','2013-01-10',-32019.99),('CCC','2013-01-10',-18177.47),('DDD','2013-01-10',-18970.11),('EEE','2013-01-10',-33095.11)
,('AAA','2012-10-10',-36664.12),('BBB','2012-10-10',-32310.78),('CCC','2012-10-10',-17734.11),('DDD','2012-10-10',-19396.84),('EEE','2012-10-10',-32702.68)
,('AAA','2012-07-10',-35980.49),('BBB','2012-07-10',-32054.35),('CCC','2012-07-10',-17628.34),('DDD','2012-07-10',-19533.58),('EEE','2012-07-10',-32637.41)
,('AAA','2012-04-10',-35659.56),('BBB','2012-04-10',-32443.67),('CCC','2012-04-10',-17842.45),('DDD','2012-04-10',-19340.18),('EEE','2012-04-10',-32768.48)
,('AAA','2012-01-10',-35063.48),('BBB','2012-01-10',-33105.79),('CCC','2012-01-10',-17544.2),('DDD','2012-01-10',-19755.03),('EEE','2012-01-10',-33066.08)
,('AAA','2011-10-10',-35669.86),('BBB','2011-10-10',-33072.72),('CCC','2011-10-10',-17404.96),('DDD','2011-10-10',-19974.75),('EEE','2011-10-10',-32868.86)
,('AAA','2011-07-10',-35108.13),('BBB','2011-07-10',-32360.78),('CCC','2011-07-10',-17422.39),('DDD','2011-07-10',-20258.37),('EEE','2011-07-10',-32836.03)
,('AAA','2011-04-10',-36008.34),('BBB','2011-04-10',-32720.71),('CCC','2011-04-10',-17352.97),('DDD','2011-04-10',-19822.28),('EEE','2011-04-10',-33001.03)
,('AAA','2011-01-10',-35130.09),('BBB','2011-01-10',-32493.26),('CCC','2011-01-10',-17012.72),('DDD','2011-01-10',-19901.89),('EEE','2011-01-10',-32417.52)
,('FFF','2011-07-15',-1500000),('GGG','2014-12-15',-2000000)
,('FFF','2011-10-10',-5492.17),('GGG','2015-01-10',-7843.04)
,('FFF','2012-01-10',-5812.79),('GGG','2015-04-10',-7882.71)
,('FFF','2012-04-10',-5854.62),('HHH','2011-07-15',-3500000)
,('FFF','2012-07-10',-6264.63),('HHH','2011-10-10',-12941.71)
,('FFF','2012-10-10',-6536.28),('HHH','2012-01-10',-12789.14)
,('FFF','2013-01-10',-6439.23),('HHH','2012-04-10',-12585.11)
,('FFF','2013-04-10',-7068.27),('HHH','2012-07-10',-12428.16)
,('FFF','2013-07-10',-6737.29),('HHH','2012-10-10',-12289.67)
,('FFF','2013-10-10',-6544.02),('HHH','2013-01-10',-12128.77)
,('FFF','2014-01-10',-6793.28),('HHH','2013-04-10',-11994.04)
,('FFF','2014-04-10',-6728.02),('HHH','2013-07-10',-11859.34)
,('FFF','2014-07-10',-6527.22),('HHH','2013-10-10',-11734.22)
,('FFF','2014-10-10',-6318.38),('HHH','2013-10-17',3233599.15)
,('FFF','2015-01-10',-6936.59)
,('FFF','2015-04-10',-6392.65)
)n(port,dt,cf)
Let's look at how to calculate the Internal Rate of Return (IRR) for the AAA porfolio for the current year. The XLeratorDB XIRR function requires cash flows and dates to calculate the internal rate of return. We need to combine data from the market value table #mv and the cash flow table #cf to do this calculation. Since we are calculating the year-to-date IRR we want all the cash flows occurring after the previous year-end, the market value as at the previous year-end, and the market value as of the close of business 2014-04-24. Let's look at the following SQL.
--The cash flows
SELECT dt, cf FROM #cf WHERE dt > '2014-12-31' AND port = 'AAA'
UNION ALL
--The ending market value
SELECT dt, mv FROM #mv WHERE dt = '2015-04-24' AND port = 'AAA'
UNION ALL
--The beginning market value
SELECT dt, -mv FROM #mv WHERE dt = '2014-12-31' AND port = 'AAA'
ORDER BY 1
This produces the following cash flows to be used by the XIRR function. Note that the SQL flipped the sign on the beginning market value to create the initial cash flow
To calculate the XIRR we just pass those cash flows into the XLeratorDB XIRR function.
SELECT
wct.XIRR(cf,cfdate,NULL) as [XIRR from 2014-12-31]
FROM (
SELECT dt, cf FROM #cf WHERE dt > '2014-12-31' AND port = 'AAA'
UNION ALL
SELECT dt, mv FROM #mv WHERE dt = '2015-04-24' AND port = 'AAA'
UNION ALL
SELECT dt, -mv FROM #mv WHERE dt = '2014-12-31' AND port = 'AAA'
)n(cfdate,cf)
which produces the following result
Since XIRR is an aggregate function, with a small change in the SQL we can calculate the XIRR for all the portfolios.
SELECT
port
,wct.XIRR(cf,cfdate,NULL) as [XIRR from 2014-12-31]
FROM (
SELECT port, dt, cf FROM #cf WHERE dt > '2014-12-31'
UNION ALL
SELECT port, dt, mv FROM #mv WHERE dt = '2015-04-24'
UNION ALL
SELECT port, dt, -mv FROM #mv WHERE dt = '2014-12-31'
)n(port,cfdate,cf)
GROUP BY
port
This produces the following result.
Note that no row has been returned for portfolio HHH. This is because our SQL has not returned any rows from the HHH portfolio as no entries in the #mv or #cf table meet the selection criteria.
What if we wanted to know the XIRR since the beginning of 2014? By selecting the market value from 2013-12-31 and all the cash flows greater than 2013-12-31 we can calculate the internal rate of return from 2013-21-31 to 2015-04-24.
SELECT
port
,wct.XIRR(cf,cfdate,NULL) as [XIRR from 2013-12-31]
FROM (
SELECT port, dt, cf FROM #cf WHERE dt > '2013-12-31'
UNION ALL
SELECT port, dt, mv FROM #mv WHERE dt = '2015-04-24'
UNION ALL
SELECT port, dt, -mv FROM #mv WHERE dt = '2013-12-31'
)n(port,cfdate,cf)
GROUP BY
port
This produces the following result.
Again, portfolio HHH does not meet the selection criteria.
Similarly to calculate the cumulative return since 2012 or 2011 or 2010 requires adjusting the date parameter used in the SQL to reflect the desired time horizon.
This SQL calculates the different start dates that we will use to group together the XIRR results so that we can see the cumulative XIRR returns from year-end 2010, 2011, 2012, 2013, and 2014. We will put the results into #h.
In addition to getting the dates of the year-end market values for each portfolio this SQL also creates a row for any cash flows occuring before the first market value entry for a particular portfolio. When you set up a new portfolio, the first entry in #cf will be the cash flow entry recording the initial investment in the portfolio. Subsequent to that initial investment a row will be added to the #mv table.
SELECT *
INTO #h
FROM (
SELECT port, YEAR(dt) as yr, dt as eoy, 2015 - YEAR(dt) as horizon
FROM #mv
WHERE wct.EOMONTH(dt,0) = wct.CALCDATE(YEAR(dt),12,31) OR dt = '2015-04-24'
UNION
SELECT #cf.port, YEAR(MIN(#cf.dt)) - 1 as yr, wct.CALCDATE(YEAR(MIN(#cf.dt))-1,12,31) as eoy, 2015 -(YEAR(MIN(#cf.dt))-1) as horizon
FROM (SELECT port, MIN(dt) FROM #mv GROUP BY port)n(port,dt)
INNER JOIN #cf
ON #cf.port = n.port AND #cf.dt <= n.dt
GROUP BY #cf.port
)n
Using the data in #h, we can now calculate the cumulative return for each portfolio for each time horizon.
SELECT port, horizon, wct.XIRR(cf,dt,NULL) as XIRR
FROM (
SELECT h.horizon, c.dt, c.cf, c.port
FROM #h h
INNER JOIN #cf c
ON c.dt > h.eoy AND c.port = h.port
UNION ALL
SELECT h.horizon, m.dt, -m.mv, m.port
FROM #h h
INNER JOIN #mv m
ON m.dt = h.eoy AND m.port = h.port
UNION ALL
SELECT h.horizon, m.dt, m.mv,m.port
FROM #h h
INNER JOIN #mv m
ON m.port = h.port AND m.dt = '2015-04-24'
)n(horizon,dt,cf,port)
WHERE horizon > 0
GROUP BY port, horizon
This produces the following result.
If we modify our SQL to use a PIVOT we can create a very user-friendly tabular representation of the results.
SELECT port,
[1] as [YTD IRR],
[2] as [2-year IRR],
[3] as [3-year IRR],
[4] as [4-year IRR],
[5] as [5-year IRR]
FROM (
SELECT port, horizon, wct.XIRR(cf,dt,NULL) as XIRR
FROM (
SELECT h.horizon, c.dt, c.cf, c.port
FROM #h h
INNER JOIN #cf c
ON c.dt > h.eoy AND c.port = h.port
UNION ALL
SELECT h.horizon, m.dt, -m.mv, m.port
FROM #h h
INNER JOIN #mv m
ON m.dt = h.eoy AND m.port = h.port
UNION ALL
SELECT h.horizon, m.dt, m.mv,m.port
FROM #h h
INNER JOIN #mv m
ON m.port = h.port AND m.dt = '2015-04-24'
)n(horizon,dt,cf,port)
WHERE horizon > 0
GROUP BY port, horizon
)d
PIVOT(SUM(XIRR) FOR horizon in([1],[2],[3],[4],[5]))pvt
This produces the following result.
There are few interesting things to take note of in the resultant table. First, portfolio FFF is included in the 5-year column, even though the initial investment in the portfolio wasn’t made until 2011-07-15. We have made the choice to include portfolios that we were invested in, even if only for one day, in the results for that time horizon.
Second, portfolio GGG shows NULL as the result for 3-, 4-, and 5-year IRR. The initial investment in GGG occurred on 2014-12-15, therefore we have only included it in the results where the start date of the time period was greater than or equal to 2013-12-31. We could have included it all the other periods, but the 2-, 3-, 4-, and 5-year IRR would all have the same value providing no real informational value and, in my opinion, making the results somewhat confusing.
Third, portfolio HHH shows NULL as the result for YTD IRR and 2-year IRR. This is because the portfolio was closed out on 2013-10-17. We could have included it in the YTD IRR and the 2-year IRR but it would report the same result as the 3-year, again providing no incremental information value.
What if we wanted to the XIRR for each calendar year and not the cumulative XIRR? We can use the following SQL to calculate the XIRR value for each year: 2011; 2012; 2013; and 2014 as well Y-T-D 2015.
SELECT
yr
,CASE yr
WHEN 2015 THEN wct.CALCDATE(yr,04,24)
ELSE wct.CALCDATE(yr,12,31)
END as date_end
,wct.CALCDATE(yr-1,12,31) as date_start
INTO
#h2
FROM (VALUES (2011),(2012),(2013),(2014),(2015))n(yr)
SELECT port,[2015],[2014],[2013],[2012],[2011]
FROM (
SELECT yr, port, wct.XIRR(cf,cfdate,NULL) as XIRR
FROM (
SELECT #h2.yr, #cf.port, #cf.cf, #cf.dt
FROM #cf
INNER JOIN #h2
ON #cf.dt > #h2.date_start AND #cf.dt <= #h2.date_end
UNION ALL
SELECT #h2.yr, #mv.port, #mv.mv, #mv.dt
FROM #mv
INNER JOIN #h2
ON #mv.dt = #h2.date_end
UNION ALL
SELECT #h2.yr, #mv.port, -#mv.mv, #mv.dt
FROM #mv
INNER JOIN #h2
ON #mv.dt = #h2.date_start
)n(yr,port,cf,cfdate)
GROUP BY yr,port
)d
PIVOT(SUM(XIRR) FOR yr in([2011],[2012],[2013],[2014],[2015]))pvt
This produces the following result.
It is important to remember that internal rates of return cannot be aggregated, meaning that the internal rate of return across all portfolios is not simply the sum of the internal rate of return of each of the portfolios. However, if we just eliminate the GROUP BY clause in our SQL it is simple to get the internal rate of return for all portfolios. For cumulative returns:
SELECT
[1] as [YTD IRR],
[2] as [2-year IRR],
[3] as [3-year IRR],
[4] as [4-year IRR],
[5] as [5-year IRR]
FROM (
SELECT horizon, wct.XIRR(cf,dt,NULL) as XIRR
FROM (
SELECT h.horizon, c.dt, c.cf, c.port
FROM #h h
INNER JOIN #cf c
ON c.dt > h.eoy AND c.port = h.port
UNION ALL
SELECT h.horizon, m.dt, -m.mv, m.port
FROM #h h
INNER JOIN #mv m
ON m.dt = h.eoy AND m.port = h.port
UNION ALL
SELECT h.horizon, m.dt, m.mv,m.port
FROM #h h
INNER JOIN #mv m
ON m.port = h.port AND m.dt = '2015-04-24'
)n(horizon,dt,cf,port)
WHERE horizon > 0
GROUP BY horizon
)d
PIVOT(SUM(XIRR) FOR horizon in([1],[2],[3],[4],[5]))pvt
which produces the following result.
To calculate the returns for each year, use the following SQL.
SELECT [2015],[2014],[2013],[2012],[2011]
FROM (
SELECT yr, wct.XIRR(cf,cfdate,NULL) as XIRR
FROM (
SELECT #h2.yr, #cf.port, #cf.cf, #cf.dt
FROM #cf
INNER JOIN #h2
ON #cf.dt > #h2.date_start AND #cf.dt <= #h2.date_end
UNION ALL
SELECT #h2.yr, #mv.port, #mv.mv, #mv.dt
FROM #mv
INNER JOIN #h2
ON #mv.dt = #h2.date_end
UNION ALL
SELECT #h2.yr, #mv.port, -#mv.mv, #mv.dt
FROM #mv
INNER JOIN #h2
ON #mv.dt = #h2.date_start
)n(yr,port,cf,cfdate)
GROUP BY yr
)d
PIVOT(SUM(XIRR) FOR yr in([2011],[2012],[2013],[2014],[2015]))pvt
This produces the following result.
XIRR is one of the more than 250 financial functions (and 900+ total functions) contained in XLeratorDB. If you need to do sophisticated financial reporting on the data in your SQL Server database then try our 15-day free trial today.
XLeratorDB downloads in less than a minute and takes about a minute to install. You are just a few minutes away from completely changing the way you think about using SQL Server and analyzing the important information already contained in your SQL Server database.