Time-Weighted Rate of Return
Oct
14
Written by:
Charles Flock
10/14/2010 5:35 PM
We frequently get inquiries about whether or not XLeratorDB can perform a time weighted return calculation. The Modified Dietz function (MDIETZ and MDIETZ_q) is just such a calculation. However, we have come up with another technique using the RATE and FVSCHEDULE functions that performs a daily time-weighted return calculation that you may find useful.
Today I am going to discuss the time-weighted rate of return (TWRR) calculation. First, I will walk through an example, putting together all the pieces to do the calculation in SQL Server using XLeratorDB. Then, I will look at the mechanics of several other methods of calculating the rate of return.
For purposes of this example, I have put together some hypothetical gold trades, consisting of purchases and sales of the SPDR Gold ETF which trades under the symbol GLD. To record the purchase and sale transactions, I created a table called TRADES, which you can create using the following script.
CREATE TABLE [dbo].[TRADES](
[TRN] [bigint] NOT NULL,
[TDATE] [datetime] NOT NULL,
[SDATE] [datetime] NOT NULL,
[SYM] [nvarchar](10) NOT NULL,
[PRICE] [float] NOT NULL,
[SHRS] [float] NOT NULL,
[COMM] [float] NOT NULL,
[SETTAMT] [float] NOT NULL,
CONSTRAINT [PK_TRADES] PRIMARY KEY CLUSTERED
(
[TRN] ASC
))
I then inserted the following data into the TRADES table.
INSERT INTO TRADES VALUES (1,'01/04/2010','01/04/2010','GLD',109.31,91,16,9963.21)
INSERT INTO TRADES VALUES (2,'02/04/2010','02/04/2010','GLD',103.75,96,16,9976)
INSERT INTO TRADES VALUES (3,'03/10/2010','03/10/2010','GLD',108.02,185,16,19999.7)
INSERT INTO TRADES VALUES (4,'04/08/2010','04/08/2010','GLD',112.13,356,16,39934.28)
INSERT INTO TRADES VALUES (5,'05/19/2010','05/19/2010','GLD',116.08,430,16,49930.4)
INSERT INTO TRADES VALUES (6,'06/22/2010','06/22/2010','GLD',120.87,206,16,24915.22)
INSERT INTO TRADES VALUES (7,'07/26/2010','07/26/2010','GLD',115.3,108,16,12468.4)
INSERT INTO TRADES VALUES (8,'08/26/2010','08/26/2010','GLD',120.5,-700,16,-84334)
INSERT INTO TRADES VALUES (9,'09/30/2010','09/30/2010','GLD',126.61,-500,16,-63289)
As a practical matter, it is not possible to do a TWRR calculation without having a daily valuation for the portfolio. Since my portfolio consists entirely of trades in GLD, it is easy enough to calculate the daily portfolio using the end of day prices, which you can download quite easily from Yahoo finance. To store the data, I created another table called PRICES which you can create using the following script.
CREATE TABLE [dbo].[PRICES](
[SYM] [nvarchar](10) NOT NULL,
[TDATE] [datetime] NOT NULL,
[PRICE_OPEN] [float] NOT NULL,
[PRICE_HIGH] [float] NOT NULL,
[PRICE_LOW] [float] NOT NULL,
[PRICE_CLOSE] [float] NOT NULL,
[VOLUME] [float] NOT NULL,
[PRICE_CLOSE_ADJ] [float] NOT NULL,
CONSTRAINT [PK_PRICES] PRIMARY KEY CLUSTERED
(
[SYM] ASC,
[TDATE] ASC
))
I have made the primary key SYM and TDATE so that if I decide to add other investments in the future I can use the same data structure. I then went to Yahoo, downloaded a year’s worth of price and volume data into EXCEL, and then imported the spreadsheet into SQL Server.
The basic premise of TWRR is to measure the performance (of the portfolio) every time there is movement in the portfolio. Let’s look at the first trade.
SELECT convert(varchar, TDATE, 106) as TDATE
,PRICE
,SHRS
,COMM
,SETTAMT
FROM TRADES
WHERE TRN = 1
This produces the following result:
|
TDATE
|
PRICE
|
SHRS
|
COMM
|
SETTAMT
|
|
4-Jan-10
|
109.31
|
91
|
16
|
9963.21
|
On the 4th of January 2010 I bought 91 shares of GLD at a price of 109.31. I paid $9,963.21 for those shares, including commission. I can figure out the profit (or loss) for that day by looking at the closing price from Yahoo finance for January 4th.
SELECT PRICE_CLOSE
FROM PRICES
WHERE TDATE = '01/04/2010'
This produces the following result.
Thus, at the end of the day on the 4th, my long position in GLD was worth $9,991.80. Since I had paid $9,963.21 for it, I had a paper profit of $28.59. I can turn that into a percentage by dividing the profit into the cost:
.00287 = 28.59 / 9963.21
Alternatively, I could have used the following formula:
.00287 = EMV/BMV – 1
Where EMV is the ending market value and BMV is the beginning market value. Since the beginning market value is zero, I had to add the purchase transaction to the BMV. The formula then becomes:
.00287 = 9991.80/9963.21 – 1
There is also an XLeratorDB function, RATE, which I can use to do the same calculation. If I enter the following SQL:
SELECT wct.RATE(
1 --number of periods
,0 --periodic payment
,-9963.21 --beginning market value
,9991.80 --ending market value
,1 --pay_type
,NULL --guess
) as TWRR
I get the following result:
For purposes of the rate calculation, the beginning market value and the ending market value need to have opposite signs, so I have arbitrarily made the beginning market value negative.
On the next day, the 5th of January, there were no more trades, but the closing price is now 109.7. To calculate the return for the 5th, we simply use the EMV for the 4th as the BMV for the 5th and calculate the new EMV as 91 times 109.70. The following SQL will do that for us:
SELECT wct.RATE(
1 --number of periods
,0 --periodic payment
,-9991.80 --beginning market value
,91.0*109.7 --ending market value
,1 --pay_type
,NULL --guess
) as TWRR
This produces the following result.
We can continue to perform this calculation every day until the next trade date. To find the next trade date, we enter the following SQL:
SELECT convert(varchar, TDATE, 106) as TDATE
,PRICE
,SHRS
,COMM
,SETTAMT
FROM TRADES
WHERE TRN = 2
This produces the following result:
|
TDATE
|
PRICE
|
SHRS
|
COMM
|
SETTAMT
|
|
4-Feb-10
|
103.75
|
96
|
16
|
9976
|
Since the next trade is not until February, I am going to look at the performance in the month of January. In its simplest form, the calculation of return for the month of January is pretty straightforward, since there is only one transaction. I can calculate that with the following SQL:
SELECT wct.RATE(
1
,0
,-SETTAMT
,T.SHRS * P.PRICE_CLOSE
,1
,NULL
) as TWRR
FROM TRADES T
JOIN PRICES P ON P.TDATE = '01/29/2010'
AND T.TRN = 1
This produces the following result:
I lost a little over 3% for the month. This approach is fine, but if we wanted to do this calculation for many portfolios or accounts, it would be extremely time consuming. I came up with the following approach, which will calculate the BMV, Purchases, Sales, and EMV for every day that I have a GLD price. The EMV for one day will become the BMV for the next day. The SQL can be broken into to two distinct parts.
This first part calculates the EMV for each day using the PRICE_CLOSE from the PRICES table and a running subtotal of the shares (SHRS) from the TRADES table.
SELECT ROW_NUMBER() OVER (ORDER BY P.TDATE ASC) AS SEQ
,P.TDATE
,SUM(T.SHRS) AS QTY
,P.PRICE_CLOSE
,SUM(T.SHRS) * P.PRICE_CLOSE AS EMV
FROM PRICES P
JOIN TRADES T
ON P.SYM = T.SYM
AND T.TDATE <= P.TDATE
GROUP BY P.TDATE, P.PRICE_CLOSE
Here are the first few rows that are returned.
|
SEQ
|
TDATE
|
QTY
|
PRICE_CLOSE
|
EMV
|
|
1
|
1/4/2010
|
91
|
109.8
|
9991.8
|
|
2
|
1/5/2010
|
91
|
109.7
|
9982.7
|
|
3
|
1/6/2010
|
91
|
111.51
|
10147.41
|
|
4
|
1/7/2010
|
91
|
110.82
|
10084.62
|
|
5
|
1/8/2010
|
91
|
111.37
|
10134.67
|
|
6
|
1/11/2010
|
91
|
112.85
|
10269.35
|
|
7
|
1/12/2010
|
91
|
110.49
|
10054.59
|
|
8
|
1/13/2010
|
91
|
111.54
|
10150.14
|
|
9
|
1/14/2010
|
91
|
112.03
|
10194.73
|
|
10
|
1/15/2010
|
91
|
110.86
|
10088.26
|
The following SQL will show you the rows where the QTY changed, so I don’t have to paste the entire resultant table. This is only here for illustrative purposes and is not necessary for the calculation of TWRR.
SELECT ROW_NUMBER() OVER (ORDER BY P.TDATE ASC) AS SEQ
,P.TDATE
,SUM(T.SHRS) AS QTY
,P.PRICE_CLOSE
,SUM(T.SHRS) * P.PRICE_CLOSE AS EMV
INTO #o
FROM PRICES P
JOIN TRADES T
ON P.SYM = T.SYM
AND T.TDATE <= P.TDATE
GROUP BY P.TDATE, P.PRICE_CLOSE
SELECT *
FROM #o
WHERE SEQ = 1
UNION ALL
SELECT a.*
FROM #o a
JOIN #o b
ON a.seq = b.seq + 1
AND a.qty <> b.qty
This produces the following result:
|
SEQ
|
TDATE
|
QTY
|
PRICE_CLOSE
|
EMV
|
|
1
|
1/4/2010
|
91
|
109.8
|
9991.8
|
|
23
|
2/4/2010
|
187
|
104.37
|
19517.19
|
|
46
|
3/10/2010
|
372
|
108.47
|
40350.84
|
|
66
|
4/8/2010
|
728
|
112.65
|
82009.2
|
|
95
|
5/19/2010
|
1158
|
116.63
|
135057.5
|
|
118
|
6/22/2010
|
1364
|
121.45
|
165657.8
|
|
141
|
7/26/2010
|
1472
|
115.52
|
170045.4
|
|
164
|
8/26/2010
|
772
|
120.96
|
93381.12
|
|
188
|
9/30/2010
|
272
|
127.91
|
34791.52
|
By putting the ending market values into a temporary table (#o) and sequentially numbering them by date, it is now possible to produce a resultant table containing the beginning market value, purchase, sales, and ending market value for every day.
SELECT ROW_NUMBER() OVER (ORDER BY P.TDATE ASC) AS SEQ
,P.TDATE
,SUM(T.SHRS) AS QTY
,P.PRICE_CLOSE
,SUM(T.SHRS) * P.PRICE_CLOSE AS EMV
INTO #o
FROM PRICES P
JOIN TRADES T
ON P.SYM = T.SYM
AND T.TDATE <= P.TDATE
GROUP BY P.TDATE, P.PRICE_CLOSE
SELECT a.TDATE
,0 as BMV
,T.SETTAMT as PURCH
,0 AS SALES
,a.EMV
,a.PRICE_CLOSE
FROM #o a
JOIN TRADES T
ON A.SEQ = 1
AND A.TDATE = T.TDATE
UNION ALL
SELECT a.TDATE
,b.EMV as BMV
,ISNULL(T.SETTAMT, 0) as PURCH
,ISNULL(-U.SETTAMT, 0) as PURCH
,a.EMV
,a.PRICE_CLOSE
FROM #o a
JOIN #o b
ON a.seq = b.seq + 1
LEFT OUTER JOIN TRADES T ON T.TDATE = a.TDATE AND T.SETTAMT > 0
LEFT OUTER JOIN TRADES U ON U.TDATE = a.TDATE AND U.SETTAMT < 0
Here are the first few rows returned by this statement.
|
TDATE
|
BMV
|
PURCH
|
SALES
|
EMV
|
PRICE_CLOSE
|
|
1/4/2010
|
0
|
9963.21
|
0
|
9991.8
|
109.8
|
|
1/5/2010
|
9991.8
|
0
|
0
|
9982.7
|
109.7
|
|
1/6/2010
|
9982.7
|
0
|
0
|
10147.41
|
111.51
|
|
1/7/2010
|
10147.41
|
0
|
0
|
10084.62
|
110.82
|
|
1/8/2010
|
10084.62
|
0
|
0
|
10134.67
|
111.37
|
|
1/11/2010
|
10134.67
|
0
|
0
|
10269.35
|
112.85
|
|
1/12/2010
|
10269.35
|
0
|
0
|
10054.59
|
110.49
|
|
1/13/2010
|
10054.59
|
0
|
0
|
10150.14
|
111.54
|
|
1/14/2010
|
10150.14
|
0
|
0
|
10194.73
|
112.03
|
Now it becomes a simple matter to calculate the rate of return for each day:
SELECT TDATE
,BMV
,PURCH
,SALES
,EMV
,PRICE_CLOSE
,wct.RATE(1,0,BMV+PURCH,-(SALES+EMV),1,NULL) as RATE
FROM (
SELECT a.TDATE
,0 as BMV
,T.SETTAMT as PURCH
,0 AS SALES
,a.EMV
,a.PRICE_CLOSE
FROM #o a
JOIN TRADES T
ON A.SEQ = 1
AND A.TDATE = T.TDATE
UNION ALL
SELECT a.TDATE
,b.EMV as BMV
,ISNULL(T.SETTAMT, 0) as PURCH
,ISNULL(-U.SETTAMT, 0) as PURCH
,a.EMV
,a.PRICE_CLOSE
FROM #o a
JOIN #o b
ON a.seq = b.seq + 1
LEFT OUTER JOIN TRADES T ON T.TDATE = a.TDATE AND T.SETTAMT > 0
LEFT OUTER JOIN TRADES U ON U.TDATE = a.TDATE AND U.SETTAMT < 0
) n
ORDER BY 1
This produces the following results for the month of January:
|
TDATE
|
BMV
|
PURCH
|
SALES
|
EMV
|
PRICE_CLOSE
|
RATE
|
|
1/4/2010
|
0
|
9963.21
|
0
|
9991.8
|
109.8
|
0.00287
|
|
1/5/2010
|
9991.8
|
0
|
0
|
9982.7
|
109.7
|
-0.00091
|
|
1/6/2010
|
9982.7
|
0
|
0
|
10147.41
|
111.51
|
0.0165
|
|
1/7/2010
|
10147.41
|
0
|
0
|
10084.62
|
110.82
|
-0.00619
|
|
1/8/2010
|
10084.62
|
0
|
0
|
10134.67
|
111.37
|
0.004963
|
|
1/11/2010
|
10134.67
|
0
|
0
|
10269.35
|
112.85
|
0.013289
|
|
1/12/2010
|
10269.35
|
0
|
0
|
10054.59
|
110.49
|
-0.02091
|
|
1/13/2010
|
10054.59
|
0
|
0
|
10150.14
|
111.54
|
0.009503
|
|
1/14/2010
|
10150.14
|
0
|
0
|
10194.73
|
112.03
|
0.004393
|
|
1/15/2010
|
10194.73
|
0
|
0
|
10088.26
|
110.86
|
-0.01044
|
|
1/19/2010
|
10088.26
|
0
|
0
|
10148.32
|
111.52
|
0.005953
|
|
1/20/2010
|
10148.32
|
0
|
0
|
9913.54
|
108.94
|
-0.02313
|
|
1/21/2010
|
9913.54
|
0
|
0
|
9770.67
|
107.37
|
-0.01441
|
|
1/22/2010
|
9770.67
|
0
|
0
|
9752.47
|
107.17
|
-0.00186
|
|
1/25/2010
|
9752.47
|
0
|
0
|
9780.68
|
107.48
|
0.002893
|
|
1/26/2010
|
9780.68
|
0
|
0
|
9787.96
|
107.56
|
0.000744
|
|
1/27/2010
|
9787.96
|
0
|
0
|
9694.23
|
106.53
|
-0.00958
|
|
1/28/2010
|
9694.23
|
0
|
0
|
9689.68
|
106.48
|
-0.00047
|
|
1/29/2010
|
9689.68
|
0
|
0
|
9642.36
|
105.96
|
-0.00488
|
However, what I really want is to calculate the return for the month of January, not just the return for every day in January. This is where the aggregate function FVSCHEDULE is used. We can make a simple modification to the above SQL, to get the January return.
SELECT wct.FVSCHEDULE(wct.RATE(1,0,BMV+PURCH,-(SALES+EMV),1,NULL)) - 1 as TWRR
FROM (
SELECT a.TDATE
,0 as BMV
,T.SETTAMT as PURCH
,0 AS SALES
,a.EMV
,a.PRICE_CLOSE
FROM #o a
JOIN TRADES T
ON A.SEQ = 1
AND A.TDATE = T.TDATE
UNION ALL
SELECT a.TDATE
,b.EMV as BMV
,ISNULL(T.SETTAMT, 0) as PURCH
,ISNULL(-U.SETTAMT, 0) as PURCH
,a.EMV
,a.PRICE_CLOSE
FROM #o a
JOIN #o b
ON a.seq = b.seq + 1
LEFT OUTER JOIN TRADES T ON T.TDATE = a.TDATE AND T.SETTAMT > 0
LEFT OUTER JOIN TRADES U ON U.TDATE = a.TDATE AND U.SETTAMT < 0
) n
WHERE TDATE < '02/01/2010'
This produces the following result:
This is exactly the result that we were expecting. The derived table, which contains all the components, makes it easy to look at the calculations.
In February, we have another purchase transaction. Let’s examine how that affects the calculation of TWRR. The new purchase transaction is dated the 4th of February. If we use the previous SQL and just change the WHERE clause to < '02/01/2010', we can see that the TWRR on the day before the purchase transaction is -0.007177406.
We then have the following row for the 4th of February:
|
TDATE
|
BMV
|
PURCH
|
SALES
|
EMV
|
PRICE_CLOSE
|
|
2/4/2010
|
9891.7
|
9976
|
0
|
19517.19
|
104.37
|
From this point forward, we need to calculate the TWRR including the impact of the new purchase transaction. There are two things that are going on. First, the price on the opening position of 91 shares at 108.70 has declined to 104.37, a loss of $394.03. Second, the 96 shares that I bought for $9,976.00 are worth $10,019.52, a gain of $43.52. The RATE calculation takes both of these facts into consideration:
SELECT wct.RATE(
1 --number of periods
,0 --periodic payment
,-(9891.7+9976) --beginning market value + purchases
,19517.19 --ending market value
,1 --pay_type
,NULL --guess
) as RATE
This returns the following result.
I can now calculate the TWRR for the first two months of 2010.
SELECT wct.FVSCHEDULE(wct.RATE(1,0,BMV+PURCH,-(SALES+EMV),1,NULL)) - 1 as TWRR
FROM (
SELECT a.TDATE
,0 as BMV
,T.SETTAMT as PURCH
,0 AS SALES
,a.EMV
,a.PRICE_CLOSE
FROM #o a
JOIN TRADES T
ON A.SEQ = 1
AND A.TDATE = T.TDATE
UNION ALL
SELECT a.TDATE
,b.EMV as BMV
,ISNULL(T.SETTAMT, 0) as PURCH
,ISNULL(-U.SETTAMT, 0) as PURCH
,a.EMV
,a.PRICE_CLOSE
FROM #o a
JOIN #o b
ON a.seq = b.seq + 1
LEFT OUTER JOIN TRADES T ON T.TDATE = a.TDATE AND T.SETTAMT > 0
LEFT OUTER JOIN TRADES U ON U.TDATE = a.TDATE AND U.SETTAMT < 0
) n
WHERE TDATE < '03/01/2010'
This produces the following result:
However, I am also interested in knowing the performance by month. Since FVSCHEDULE is an aggregate function, this requires only a minor change to the SQL.
SELECT MONTH(TDATE) AS [MONTH]
, wct.FVSCHEDULE(wct.RATE(1,0,BMV+PURCH,-(SALES+EMV),1,NULL)) - 1 as TWRR
FROM (
SELECT a.TDATE
,0 as BMV
,T.SETTAMT as PURCH
,0 AS SALES
,a.EMV
,a.PRICE_CLOSE
FROM #o a
JOIN TRADES T
ON A.SEQ = 1
AND A.TDATE = T.TDATE
UNION ALL
SELECT a.TDATE
,b.EMV as BMV
,ISNULL(T.SETTAMT, 0) as PURCH
,ISNULL(-U.SETTAMT, 0) as PURCH
,a.EMV
,a.PRICE_CLOSE
FROM #o a
JOIN #o b
ON a.seq = b.seq + 1
LEFT OUTER JOIN TRADES T ON T.TDATE = a.TDATE AND T.SETTAMT > 0
LEFT OUTER JOIN TRADES U ON U.TDATE = a.TDATE AND U.SETTAMT < 0
) n
WHERE TDATE < '03/01/2010'
GROUP BY MONTH(TDATE)
This produces the following result:
|
MONTH
|
TWRR
|
|
1
|
-0.032203
|
|
2
|
0.056618
|
The way that I interpret the result is that the portfolio had a time weighted return of just less than -3% in January and a little over 5.6% in February, with a year-to-date time weighted return of just over 2.25%.
Before I move on to demonstrating how sales affect the calculation, I will calculate the TWRR for each month and year-to-date through the end of July (since the first sale transaction in August). For the TWRR by month:
SELECT MONTH(TDATE) AS [MONTH]
, wct.FVSCHEDULE(wct.RATE(1,0,BMV+PURCH,-(SALES+EMV),1,NULL)) - 1 as TWRR
FROM (
SELECT a.TDATE
,0 as BMV
,T.SETTAMT as PURCH
,0 AS SALES
,a.EMV
,a.PRICE_CLOSE
FROM #o a
JOIN TRADES T
ON A.SEQ = 1
AND A.TDATE = T.TDATE
UNION ALL
SELECT a.TDATE
,b.EMV as BMV
,ISNULL(T.SETTAMT, 0) as PURCH
,ISNULL(-U.SETTAMT, 0) as PURCH
,a.EMV
,a.PRICE_CLOSE
FROM #o a
JOIN #o b
ON a.seq = b.seq + 1
LEFT OUTER JOIN TRADES T ON T.TDATE = a.TDATE AND T.SETTAMT > 0
LEFT OUTER JOIN TRADES U ON U.TDATE = a.TDATE AND U.SETTAMT < 0
) n
WHERE TDATE < '08/01/2010'
GROUP BY MONTH(TDATE)
This produces the following result:
|
MONTH
|
TWRR
|
|
1
|
-0.032203
|
|
2
|
0.056618
|
|
3
|
0.002949
|
|
4
|
0.060286
|
|
5
|
0.041429
|
|
6
|
0.022838
|
|
7
|
-0.05049
|
And for the year-to-date calculation:
SELECT wct.FVSCHEDULE(wct.RATE(1,0,BMV+PURCH,-(SALES+EMV),1,NULL)) - 1 as TWRR
FROM (
SELECT a.TDATE
,0 as BMV
,T.SETTAMT as PURCH
,0 AS SALES
,a.EMV
,a.PRICE_CLOSE
FROM #o a
JOIN TRADES T
ON A.SEQ = 1
AND A.TDATE = T.TDATE
UNION ALL
SELECT a.TDATE
,b.EMV as BMV
,ISNULL(T.SETTAMT, 0) as PURCH
,ISNULL(-U.SETTAMT, 0) as PURCH
,a.EMV
,a.PRICE_CLOSE
FROM #o a
JOIN #o b
ON a.seq = b.seq + 1
LEFT OUTER JOIN TRADES T ON T.TDATE = a.TDATE AND T.SETTAMT > 0
LEFT OUTER JOIN TRADES U ON U.TDATE = a.TDATE AND U.SETTAMT < 0
) n
WHERE TDATE < '08/01/2010'
Producing the following result:
On August 26th, I sold 700 shares of GLD and received net proceeds of $84,334. This shows up in the derived table as:
|
TDATE
|
BMV
|
PURCH
|
SALES
|
EMV
|
PRICE_CLOSE
|
|
8/26/2010
|
178641.9
|
0
|
84334
|
93381.12
|
120.96
|
This still fits very nicely into the RATE calculation:
SELECT wct.RATE(
1 --number of periods
,0 --periodic payment
,-(178641.0) --beginning market value + purchases
,84334+93381.12 --ending market value + sales
,1 --pay_type
,NULL --guess
)
Producing the following result:
This is now the fully developed model and we can calculate the TWRR for each month, each quarter, or year-to-date. To calculate the TWRR for each month:
SELECT MONTH(TDATE) AS [MONTH]
,wct.FVSCHEDULE(wct.RATE(1,0,BMV+PURCH,-(SALES+EMV),1,NULL)) - 1 as TWRR
FROM (
SELECT a.TDATE
,0 as BMV
,T.SETTAMT as PURCH
,0 AS SALES
,a.EMV
,a.PRICE_CLOSE
FROM #o a
JOIN TRADES T
ON A.SEQ = 1
AND A.TDATE = T.TDATE
UNION ALL
SELECT a.TDATE
,b.EMV as BMV
,ISNULL(T.SETTAMT, 0) as PURCH
,ISNULL(-U.SETTAMT, 0) as PURCH
,a.EMV
,a.PRICE_CLOSE
FROM #o a
JOIN #o b
ON a.seq = b.seq + 1
LEFT OUTER JOIN TRADES T ON T.TDATE = a.TDATE AND T.SETTAMT > 0
LEFT OUTER JOIN TRADES U ON U.TDATE = a.TDATE AND U.SETTAMT < 0
) n
WHERE TDATE < '10/01/2010'
GROUP BY MONTH(TDATE)
This produces the following result:
|
MONTH
|
TWRR
|
|
1
|
-0.032203
|
|
2
|
0.056618
|
|
3
|
0.002949
|
|
4
|
0.060286
|
|
5
|
0.041429
|
|
6
|
0.022838
|
|
7
|
-0.05049
|
|
8
|
0.055055
|
|
9
|
0.040689
|
To calculate the TWRR for each quarter:
SELECT (MONTH(TDATE)-1)/3 + 1 AS [QUARTER]
,wct.FVSCHEDULE(wct.RATE(1,0,BMV+PURCH,-(SALES+EMV),1,NULL)) - 1 as TWRR
FROM (
SELECT a.TDATE
,0 as BMV
,T.SETTAMT as PURCH
,0 AS SALES
,a.EMV
,a.PRICE_CLOSE
FROM #o a
JOIN TRADES T
ON A.SEQ = 1
AND A.TDATE = T.TDATE
UNION ALL
SELECT a.TDATE
,b.EMV as BMV
,ISNULL(T.SETTAMT, 0) as PURCH
,ISNULL(-U.SETTAMT, 0) as PURCH
,a.EMV
,a.PRICE_CLOSE
FROM #o a
JOIN #o b
ON a.seq = b.seq + 1
LEFT OUTER JOIN TRADES T ON T.TDATE = a.TDATE AND T.SETTAMT > 0
LEFT OUTER JOIN TRADES U ON U.TDATE = a.TDATE AND U.SETTAMT < 0
) n
WHERE TDATE < '10/01/2010'
GROUP BY (MONTH(TDATE)-1)/3 + 1
ORDER BY 1
This produces the following result:
|
QUARTER
|
TWRR
|
|
1
|
0.025606
|
|
2
|
0.12943
|
|
3
|
0.042551
|
This SQL will produce the TWRR through the end of September.
SELECT wct.FVSCHEDULE(wct.RATE(1,0,BMV+PURCH,-(SALES+EMV),1,NULL)) - 1 as TWRR
FROM (
SELECT a.TDATE
,0 as BMV
,T.SETTAMT as PURCH
,0 AS SALES
,a.EMV
,a.PRICE_CLOSE
FROM #o a
JOIN TRADES T
ON A.SEQ = 1
AND A.TDATE = T.TDATE
UNION ALL
SELECT a.TDATE
,b.EMV as BMV
,ISNULL(T.SETTAMT, 0) as PURCH
,ISNULL(-U.SETTAMT, 0) as PURCH
,a.EMV
,a.PRICE_CLOSE
FROM #o a
JOIN #o b
ON a.seq = b.seq + 1
LEFT OUTER JOIN TRADES T ON T.TDATE = a.TDATE AND T.SETTAMT > 0
LEFT OUTER JOIN TRADES U ON U.TDATE = a.TDATE AND U.SETTAMT < 0
) n
WHERE TDATE < '10/01/2010'
This produces the following result:
Now that I have shown you how to calculate TWRR in SQL Server using the RATE and FVSCHEDULE functions, I am going to discuss what the time-weighted rate of return is actually measuring.
Having calculated that the TWRR through the end of September is 20.764%, does that mean that my investment in gold (GLD) has had a 20% return?
Of course, the answer to that depends upon what you mean by return. If we are talking about return on investment, the answer is clearly no.
Here’s one way to calculates the ROI.
SELECT Purchases
,Sales
,EMV
,(SALES + EMV)/PURCHASES - 1 as ROI
FROM (
SELECT SUM(
CASE
WHEN SETTAMT > 0 THEN SETTAMT
ELSE 0
END) as Purchases
,-SUM(
CASE
WHEN SETTAMT > 0 THEN 0
ELSE SETTAMT
END) as Sales
,SUM(SHRS) * P.PRICE_CLOSE as EMV
FROM TRADES T, PRICES P
WHERE P.TDATE =(SELECT MAX(TDATE) FROM PRICES WHERE TDATE < '10/01/10')
GROUP BY P.TDATE, P.PRICE_CLOSE
) n
This produces the following result:
|
Purchases
|
Sales
|
EMV
|
ROI
|
|
167187.21
|
147623
|
34791.52
|
0.091079
|
So, in terms of dollars invested and gains realized on sales and the market value of the remaining position, it looks like my ROI is just a little over 9.1% as of the end of September. We can break this down even finer, by looking at the profit on the positions that have been closed out, taking a FIFO approach.
SELECT ABS(PROCEEDS) as [Sales]
,COGS - SUM(CASE
WHEN SHRS > INVENTORY THEN INVENTORY
ELSE SHRS
END / SHRS * SETTAMT) AS [Cost of Sales]
,ABS(PROCEEDS) -(COGS - SUM(CASE
WHEN SHRS > INVENTORY THEN INVENTORY
ELSE SHRS
END / SHRS * SETTAMT)) As [Realized Profit]
,ABS(PROCEEDS)/(COGS - SUM(CASE
WHEN SHRS > INVENTORY THEN INVENTORY
ELSE SHRS
END / SHRS * SETTAMT)) - 1 as ROI
FROM (
SELECT TOP 100 PERCENT A.TRN
,A.SYM
,A.TDATE
,A.SHRS
,C.RUNNINGTOTAL + n.SALES as INVENTORY
,A.SETTAMT
,n.SALES
,n.PURCHASES
,n.PROCEEDS
,n.COGS
FROM (
SELECT SUM(CASE WHEN SHRS < 0 THEN SHRS ELSE 0 END) as SALES
,SUM(CASE WHEN SHRS < 0 THEN SETTAMT ELSE 0 END) as PROCEEDS
,SUM(CASE WHEN SHRS > 0 THEN SHRS ELSE 0 END) as PURCHASES
,SUM(CASE WHEN SHRS > 0 THEN SETTAMT ELSE 0 END) as COGS
FROM TRADES
) n,
TRADES A
CROSS APPLY(
SELECT SUM(B.SHRS) as runningtotal
FROM TRADES B
WHERE A.SYM = B.SYM
AND B.TDATE <= A.TDATE
) C
WHERE A.SHRS > 0
ORDER BY 1,2) n
WHERE INVENTORY > 0
GROUP BY SALES, PURCHASES, PROCEEDS, COGS
This produces the following result:
|
Sales
|
Cost of Sales
|
Realized Profit
|
ROI
|
|
147623
|
134883.4
|
12739.61
|
0.094449
|
So, we have made 9.45% on the closed positions. And we can calculate the unrealized gain on the open positions as:
SELECT SUM(CASE
WHEN SHRS > INVENTORY THEN INVENTORY
ELSE SHRS
END) AS [Position]
,SUM(CASE
WHEN SHRS > INVENTORY THEN INVENTORY
ELSE SHRS
END / SHRS * SETTAMT) AS [Position at Cost]
,P.PRICE_CLOSE *( SUM(CASE
WHEN SHRS > INVENTORY THEN INVENTORY
ELSE SHRS
END)) AS [Market Value]
,(P.PRICE_CLOSE *( SUM(CASE
WHEN SHRS > INVENTORY THEN INVENTORY
ELSE SHRS
END)))/(SUM(CASE
WHEN SHRS > INVENTORY THEN INVENTORY
ELSE SHRS
END / SHRS * SETTAMT)) - 1 as ROI
FROM (
SELECT TOP 100 PERCENT A.TRN
,A.SYM
,A.TDATE
,A.SHRS
,C.RUNNINGTOTAL + n.SALES as INVENTORY
,A.SETTAMT
,n.SALES
,n.PURCHASES
,n.PROCEEDS
,n.COGS
FROM (
SELECT SUM(CASE WHEN SHRS < 0 THEN SHRS ELSE 0 END) as SALES
,SUM(CASE WHEN SHRS < 0 THEN SETTAMT ELSE 0 END) as PROCEEDS
,SUM(CASE WHEN SHRS > 0 THEN SHRS ELSE 0 END) as PURCHASES
,SUM(CASE WHEN SHRS > 0 THEN SETTAMT ELSE 0 END) as COGS
FROM TRADES
) n,
TRADES A
CROSS APPLY(
SELECT SUM(B.SHRS) as runningtotal
FROM TRADES B
WHERE A.SYM = B.SYM
AND B.TDATE <= A.TDATE
) C
WHERE A.SHRS > 0
ORDER BY 1,2) n, PRICES P
WHERE INVENTORY > 0
AND P.TDATE = '09/30/2010'
GROUP BY SALES, PURCHASES, PROCEEDS, COGS, P.PRICE_CLOSE
This produces the following result:
|
Position
|
Position at Cost
|
Market Value
|
ROI
|
|
272
|
32303.82
|
34791.52
|
0.07701
|
It’s pretty clear that the ROI on the GLD portfolio is very different from the time-weighted rate of return. This is because they are measuring two very different things. ROI is a very simple form of money-weighted return (let’s call that MWRR). MWRR judges the performances of a portfolio while TWRR judges the performance of a portfolio manager (which, in this example, was me). The whole idea behind the TWRR calculation is that it eliminates or reduces the impact of the cash flows (because managers don’t always control the flows).
Let’s look at some of the other measures of return. The Modified Dietz method is also a TWRR calculation which assumes a constant rate of return on the portfolio during the period and weights each external cash flows by the amount of time that is it held in the portfolio. Here’s the modified Dietz calculation for the first nine months of the year.
SELECT wct.MDIETZ(
'TRADES' --cash flow table name
,'TDATE' --cash flow dates column name
,'SETTAMT' --cash flows column name
,'' --grouped column name
,NULL --grouped column value
,n.StartDate --start date
,n.EndDate --end date
,n.BMV --beginning market value
,N.EMV --ending market value
) as [Modified Dietz]
FROM (
SELECT MIN(T.TDATE) as StartDate
,Cast('9/30/2010' as datetime) as EndDate
,0 as BMV
,SUM(T.SHRS) * P.PRICE_CLOSE AS EMV
FROM TRADES T, PRICES P
WHERE P.TDATE = '09/30/2010'
GROUP BY P.PRICE_CLOSE
) n
This returns the following result:
|
Modified Dietz
|
|
0.29251079
|
The modifed Dietz at 29.25% is 8.486 percentage points higher than the TWRR of 20.764%, which is arguably more precise. Still, a big difference in performance measurement.
Another measure of return, the internal rate of return, which is a money-weighted return calculation, takes the cash flows into consideration. The XLeratorDB XIRR_q function takes the cash flow amounts and dates into consideration when calculating the return.
SELECT wct.XIRR_q(
'SELECT -SETTAMT
,TDATE
FROM TRADES
UNION ALL
SELECT SUM(T.SHRS) * P.PRICE_CLOSE, P.TDATE
FROM PRICES P, TRADES T
WHERE P.TDATE = ' + CHAR(39) + '09/30/2010' + CHAR(39) + '
GROUP BY P.TDATE, P.PRICE_CLOSE'
,NULL) AS IRR
This returns the following result:
This gives us four different ways of measuring the performance from the same set of data. Two of these are time-weighted rates of return: the modified Dietz method, and the daily TWRR using the RATE and the FVSCHEDULE function. The other two are money-weighted rates of return: calculating the ROI and using the XIRR_q function.
Whether you are calculating the performance of a portfolio manager or calculating the returns on your own portfolio, XLeratorDB makes it very simple.