Login     Register

        Contact Us     Search

Time-Weighted Rate of Return

Oct 14

Written by: Charles Flock
10/14/2010 5:35 PM  RssIcon

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.

PRICE_CLOSE
109.8

 
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:

TWRR
0.002869557

 
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.

TWRR
-0.000910747

 
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:

TWRR
-0.032203477

 
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:

TWRR
-0.032203477

 
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.

RATE
-0.017642203

 
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:

TWRR
0.022591231

 
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:

TWRR
0.099870583

 
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:

RATE
-0.005182909

 
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)/+ 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)/+ 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:

TWRR
0.207639906

 
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:

IRR
0.2534238

 
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.

Tags:
Categories:
Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service