Calculating the Time-Weighted Rate of Return in SQL Server
Nov
25
Written by:
Charles Flock
11/25/2011 1:51 PM
With the release of the TWRR multi-input aggregate function, XLeratorDB users can now calculate the time-weighted rate of return directly in a T-SQL statement.
In October of 2010, we wrote a blog about calculating time-weighted rates of return using a combination of functions in the XLeratorDB function library. Some of our users got back to us and asked us to provide a function that was similar to the multi-input aggregate XIRR. Today we will look at how the TWRR function works and compare it to the XIRR function, which calculates the internal rate of return for irregular cash flows, and the modified Dietz function, EMDIETZ, which is a money-weighted rate of return calculation.
Let’s start with a simple example. We have a portfolio that at the end of December 2011 was worth 100,000 and that at the end of January 2012 was worth 101,233.33. Assume that there was no other activity in the account. Compute the XIRR, TWRR, and EMDIETZ values.
SELECT 'TWRR' as FN, wct.TWRR(c,d,mv) as [RETURN]
FROM (VALUES
('2011-12-31', -100000, 'True'),
('2012-01-31', -101233.33, 'True')
) n(d,c, mv)
UNION ALL
SELECT 'MDIETZ' as FN, wct.EMDIETZ(c,d) as [RETURN]
FROM (VALUES
('2011-12-31', 100000),
('2012-01-31', -101233.33)
) n(d,c)
UNION ALL
SELECT 'XIRR' as FN, wct.XIRR(c,d,NULL) as [RETURN]
FROM (VALUES
('2011-12-31', -100000),
('2012-01-31', 101233.33)
) n(d,c)
This produces the following result.
FN RETURN
------ ----------------------
TWRR 0.0123333000000001
MDIETZ 0.0123333
XIRR 0.155261203635414
Notice that the TWRR and MDIETZ calculations return the same result. But, the XIRR result is markedly different. Why is that? The XIRR calculation has been annualized. In other words, the XIRR calculation has taken the result for the month of January and multiplied it out for a full year, whereas the EMDIETZ and TWRR calculations reflect the return for the month of January. We could turn the XIRR calculation into a monthly calculation by calculating the fraction of the year in the period we are evaluating. In other words, (1+XIRR)31/365-1.
SELECT 'XIRR' as FN
,POWER(1+wct.XIRR(c,d,NULL), wct.YEARFRAC(min(d),max(d),1))-1 as [RETURN]
FROM (VALUES
('2011-12-31', -100000),
('2012-01-31', 101233.33)
) n(d,c)
Now the results look like this.
FN RETURN
------ ----------------------
TWRR 0.0123333000000001
MDIETZ 0.0123333
XIRR 0.0123333000000001
Also notice that even though the function signatures for each of these three functions are similar, they have different requirements about the sign on the cash flows.
In the XIRR function, we can interpret the signs as indicating that we invested 100,000 on the 31-Dec-11 and on 31-Jan-12 we received 101,233.33. The fact of the matter is that if we reversed the signs on the cash flows in the XIRR calculation, we would get exactly the same result. In other word, XIRR(c, d) = XIRR(-c, d). But, XIRR will only calculate a result if there is at least one positive cash flow and one negative cash flow. You can read more about the XIRR function here.
In the EMDIETZ function, the beginning market value has a positive value and the ending market value has a negative value. You can read more about the EMDIETZ function here.
In the TWRR function, the ending market value for 31-Dec-11 and the ending market value for 31-Jan-12 are both negative and we have set the market value bit to True, identifying these cash flows as market values. You can read more about the TWRR function here.
Assume that 75,000 was withdrawn from the account on 10-Jan-2012 and that the value of the account at the end of that day was 25,590.84 and the value in the account at the end of January was 25,308.33. How would that affect the different calculations?
SELECT 'TWRR' as FN, wct.TWRR(c,d,mv) as [RETURN]
FROM (VALUES
('2011-12-31', -100000, 'True'),
('2012-01-10', -75000, 'False'),
('2012-01-10', -25590.84, 'True'),
('2012-01-31', -25308.33, 'True')
) n(d,c, mv)
UNION ALL
SELECT 'MDIETZ' as FN, wct.EMDIETZ(c,d) as [RETURN]
FROM (VALUES
('2011-12-31', 100000),
('2012-01-31', -25308.33),
('2012-01-10', -75000)
) n(d,c)
UNION ALL
SELECT 'XIRR' as FN
,POWER(1+wct.XIRR(c,d,NULL), wct.YEARFRAC(min(d),max(d),1))-1 as [RETURN]
FROM (VALUES
('2011-12-31', -100000),
('2012-01-31', 25308.33),
('2012-01-10', 75000)
) n(d,c)
This produces the following result.
FN RETURN
------ ----------------------
TWRR -0.00519632270875037
MDIETZ 0.00626769180327872
XIRR 0.0062611794906311
That’s pretty interesting. The modified Dietz and internal rate of return calculations return relatively similar numbers, but the time-weighted rate of return is quite different, even to the point of having a different sign. How did it come up with that result?
We can break the time weighted return calculation into two pieces. The first piece is from 31-Dec-11 to 10-Jan-12.
SELECT 'TWRR' as FN, wct.TWRR(c,d,mv) as [RETURN]
FROM (VALUES
('2011-12-31', -100000, 'True'),
('2012-01-10', -75000, 'False'),
('2012-01-10', -25590.84, 'True')
) n(d,c, mv)
UNION ALL
SELECT 'TWRR' as FN, wct.TWRR(c,d,mv) as [RETURN]
FROM (VALUES
('2012-01-10', -25590.84, 'True'),
('2012-01-31', -25308.33, 'True')
) n(d,c, mv)
This produces the following result.
FN RETURN
---- ----------------------
TWRR 0.00590840000000004
TWRR -0.0110394969450006
From 31-Dec-11 to 10-Jan-12 the time-weighted rate of return was approximately 0.591%. From 10-Jan-12 to 31-Jan-12, it was -1.104%. The time-weighted rate of return for the entire period is the product of these two returns, calculated as (1+TWRR0)*(1+TWRR1)-1. The TWRR function automatically takes care of this, but you can enter the following SQL pure for illustrative purposes.
SELECT wct.FVSCHEDULE(r) - 1 as TWRR
FROM (
SELECT wct.TWRR(c,d,mv) as r
FROM (VALUES
('2011-12-31', -100000, 'True'),
('2012-01-10', -75000, 'False'),
('2012-01-10', -25590.84, 'True')
) n(d,c, mv)
UNION ALL
SELECT wct.TWRR(c,d,mv) r
FROM (VALUES
('2012-01-10', -25590.84, 'True'),
('2012-01-31', -25308.33, 'True')
) n(d,c, mv)
) t
This produces the following result, which is identical to the value produced by the TWRR function at the beginning of this example.
TWRR
----------------------
-0.00519632270875037
Let’s add a purchase transaction to this data. On 21-Jan-12 there is purchase transaction in the amount of 55,000. The ending market value on 21-Jan-12 is 79,932.76 and the ending market value on 31-Jan-12 is 80,432.29
SELECT 'TWRR' as FN, wct.TWRR(c,d,mv) as [RETURN]
FROM (VALUES
('2011-12-31', -100000, 'True'),
('2012-01-10', -75000, 'False'),
('2012-01-10', -25590.84, 'True'),
('2012-01-21', 55000, 'False'),
('2012-01-21', -79932.76, 'True'),
('2012-01-31', -80432.29, 'True')
) n(d,c, mv)
UNION ALL
SELECT 'MDIETZ' as FN, wct.EMDIETZ(c,d) as [RETURN]
FROM (VALUES
('2011-12-31', 100000),
('2012-01-31', -80432.29),
('2012-01-21', 55000),
('2012-01-10', -75000)
) n(d,c)
UNION ALL
SELECT 'XIRR' as FN, POWER(1+wct.XIRR(c,d,NULL),wct.YEARFRAC(min(d),max(d),1))-1 as [RETURN]
FROM (VALUES
('2011-12-31', -100000),
('2012-01-31', 80432.29),
('2012-01-10', 75000),
('2012-01-21', -55000)
) n(d,c)
This produces the following result.
FN RETURN
------ ----------------------
TWRR 0.00392943096555398
MDIETZ 0.00645830843373484
XIRR 0.00645694834069688
As in the previous example, the modified Dietz and internal rate of return numbers are relatively similar, but the time-weighted rate of return is not. Let’s look at the components of the TWRR calculation.
SELECT max(d) as end_date, wct.TWRR(c,d,mv) as r
FROM (VALUES
('2011-12-31', -100000, 'True'),
('2012-01-10', -75000, 'False'),
('2012-01-10', -25590.84, 'True')
) n(d,c, mv)
UNION ALL
SELECT max(d), wct.TWRR(c,d,mv)
FROM (VALUES
('2012-01-10', -25590.84, 'True'),
('2012-01-21', 55000, 'False'),
('2012-01-21', -79932.76, 'True')
) n(d,c, mv)
UNION ALL
SELECT max(d), wct.TWRR(c,d,mv)
FROM (VALUES
('2012-01-21', -79932.76, 'True'),
('2012-01-31', -80432.29, 'True')
) n(d,c, mv)
This produces the following result.
end_date r
---------- ----------------------
2012-01-10 0.00590840000000004
2012-01-21 -0.00816569227966857
2012-01-31 0.00624937760187438
Once again, for illustrative purpose, we can use the FVSCHEDULE function to verify the TWRR calculation.
SELECT wct.FVSCHEDULE(r) - 1 as TWRR
FROM (
SELECT max(d) as end_date, wct.TWRR(c,d,mv) as r
FROM (VALUES
('2011-12-31', -100000, 'True'),
('2012-01-10', -75000, 'False'),
('2012-01-10', -25590.84, 'True')
) n(d,c, mv)
UNION ALL
SELECT max(d), wct.TWRR(c,d,mv)
FROM (VALUES
('2012-01-10', -25590.84, 'True'),
('2012-01-21', 55000, 'False'),
('2012-01-21', -79932.76, 'True')
) n(d,c, mv)
UNION ALL
SELECT max(d), wct.TWRR(c,d,mv)
FROM (VALUES
('2012-01-21', -79932.76, 'True'),
('2012-01-31', -80432.29, 'True')
) n(d,c, mv)
) t
This produces the following result, matching the value returned by the TWRR function at the beginning of this example.
TWRR
----------------------
0.00392943096555398
As you can see, the TWRR function greatly simplifies the time-weighted rate of return calculation, but it requires a market valuation for each day that there is a cash flow, whereas the XIRR and EMDIETZ function only require the initial market value and the final market value.
For this example, we will create 2 temporary tables, one which will contain transaction data, and the other which will contain market values for a hypothetical portfolio. We will calculate the XIRR, modified Dietz and time-weighted rate of return for each.
SET NOCOUNT ON
/*Create a table to store market values*/
CREATE TABLE #mv(
tdate datetime,
m_value money
)
/*Create a table to store tranasctions*/
CREATE TABLE #t(
tdate datetime,
tranamt money
)
/*Insert market values*/
INSERT INTO #mv VALUES ('2011-12-31',15000)
INSERT INTO #mv VALUES ('2012-01-03',14560.89)
INSERT INTO #mv VALUES ('2012-01-04',15214.95)
INSERT INTO #mv VALUES ('2012-01-05',15850.9)
INSERT INTO #mv VALUES ('2012-01-06',15297.7)
INSERT INTO #mv VALUES ('2012-01-09',14483.07)
INSERT INTO #mv VALUES ('2012-01-10',14353.55)
INSERT INTO #mv VALUES ('2012-01-11',14489.23)
INSERT INTO #mv VALUES ('2012-01-12',14292.21)
INSERT INTO #mv VALUES ('2012-01-13',14355.17)
INSERT INTO #mv VALUES ('2012-01-17',13907.07)
INSERT INTO #mv VALUES ('2012-01-18',14293)
INSERT INTO #mv VALUES ('2012-01-19',14242.08)
INSERT INTO #mv VALUES ('2012-01-20',13484.64)
INSERT INTO #mv VALUES ('2012-01-23',13329.02)
INSERT INTO #mv VALUES ('2012-01-24',13558.11)
INSERT INTO #mv VALUES ('2012-01-25',14191.74)
INSERT INTO #mv VALUES ('2012-01-26',13900.61)
INSERT INTO #mv VALUES ('2012-01-27',13989.31)
INSERT INTO #mv VALUES ('2012-01-30',13751.09)
INSERT INTO #mv VALUES ('2012-01-31',14049.4)
/*Insert transactions*/
INSERT INTO #t VALUES ('2012-01-03',349.98)
INSERT INTO #t VALUES ('2012-01-04',881.34)
INSERT INTO #t VALUES ('2012-01-05',701.1)
INSERT INTO #t VALUES ('2012-01-06',254.89)
INSERT INTO #t VALUES ('2012-01-09',127.18)
INSERT INTO #t VALUES ('2012-01-10',347.76)
INSERT INTO #t VALUES ('2012-01-11',397.93)
INSERT INTO #t VALUES ('2012-01-12',585.66)
INSERT INTO #t VALUES ('2012-01-13',883.77)
INSERT INTO #t VALUES ('2012-01-17',324.37)
INSERT INTO #t VALUES ('2012-01-18',595)
INSERT INTO #t VALUES ('2012-01-19',355.16)
INSERT INTO #t VALUES ('2012-01-20',190.38)
INSERT INTO #t VALUES ('2012-01-23',282.08)
INSERT INTO #t VALUES ('2012-01-24',633.62)
INSERT INTO #t VALUES ('2012-01-25',709.19)
INSERT INTO #t VALUES ('2012-01-26',203.62)
INSERT INTO #t VALUES ('2012-01-27',807.66)
INSERT INTO #t VALUES ('2012-01-30',676.66)
INSERT INTO #t VALUES ('2012-01-31',444.96)
INSERT INTO #t VALUES ('2012-01-03',-836.58)
INSERT INTO #t VALUES ('2012-01-04',-240.47)
INSERT INTO #t VALUES ('2012-01-05',-125.38)
INSERT INTO #t VALUES ('2012-01-06',-905.4)
INSERT INTO #t VALUES ('2012-01-09',-979.8)
INSERT INTO #t VALUES ('2012-01-10',-513.36)
INSERT INTO #t VALUES ('2012-01-11',-363.94)
INSERT INTO #t VALUES ('2012-01-12',-888.05)
INSERT INTO #t VALUES ('2012-01-13',-853.19)
INSERT INTO #t VALUES ('2012-01-17',-784.62)
INSERT INTO #t VALUES ('2012-01-18',-218.9)
INSERT INTO #t VALUES ('2012-01-19',-458.64)
INSERT INTO #t VALUES ('2012-01-20',-971.49)
INSERT INTO #t VALUES ('2012-01-23',-520.49)
INSERT INTO #t VALUES ('2012-01-24',-445.15)
INSERT INTO #t VALUES ('2012-01-25',-193.02)
INSERT INTO #t VALUES ('2012-01-26',-525.02)
INSERT INTO #t VALUES ('2012-01-27',-749.59)
INSERT INTO #t VALUES ('2012-01-30',-976.77)
INSERT INTO #t VALUES ('2012-01-31',-168.48)
/*Calculate returns*/
SELECT 'XIRR' as FN, POWER(1+wct.XIRR(c,d,NULL),wct.YEARFRAC(min(d),max(d),1))-1 as [RETURN]
FROM (
SELECT tdate, tranamt from #t
UNION ALL
SELECT tdate, m_value from #mv
WHERE tdate =(SELECT MIN(tdate) from #mv)
UNION ALL
SELECT tdate, -m_value from #mv
WHERE tdate =(SELECT MAX(tdate) from #mv)
) n(d, c)
UNION ALL
SELECT 'MDIETZ' as FN, wct.EMDIETZ(c,d) as [RETURN]
FROM (
SELECT tdate, tranamt from #t
UNION ALL
SELECT tdate, m_value from #mv
WHERE tdate =(SELECT MIN(tdate) from #mv)
UNION ALL
SELECT tdate, -m_value from #mv
WHERE tdate =(SELECT MAX(tdate) from #mv)
) n(d, c)
UNION ALL
SELECT 'TWRR' as FN, wct.TWRR(c,d,mv) as [RETURN]
FROM (
SELECT tdate, tranamt, 'False' from #t
UNION ALL
SELECT tdate, -m_value, 'True' from #mv
) n(d, c, mv)
This produces the following result.
FN RETURN
------ ----------------------
XIRR 0.0731048888692927
MDIETZ 0.0731917538744161
TWRR 0.0707441314289605
All the rows in table #t were passed to each of the three functions. The XIRR and EMDIETZ functions only required the initial market value and final market value from the #mv table, though providing those values to the functions required the use of the MIN() and MAX() functions. The TWRR function consumed all the market values from the #mv table.
The results for the XIRR and EMDIETZ functions are, once again, very similar, diverging at the fifth decimal place, but that is only because the cash movements are relatively small and consistent. The result for the TWRR calculation diverges from the others at the third decimal place and reflects a somehat lower rate of return.
Which one should you be using to evaluate performance? That’s a complicated question. However, given the ease with which you can perform these calculations in SQL Server using XLeratorDB, you can provide both time-weighted and money-weighted rates of return with ease.