 ## 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.
[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,
(
[TRN] ASC
))
I then inserted the following data into the TRADES table.

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,
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
) n,
CROSS APPLY(
SELECT SUM(B.SHRS) as runningtotal
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
) n,
CROSS APPLY(
SELECT SUM(B.SHRS) as runningtotal
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(
,'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
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
UNION ALL
SELECT SUM(T.SHRS) * P.PRICE_CLOSE, P.TDATE
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:
Location: Blogs The WestClinTech Blog

## Search Blogs

 KeywordsPhrase

## Blog Archives

Archive
 < June 2023 >
SunMonTueWedThuFriSat
28293031123
45678910
11121314151617
18192021222324
2526272829301
2345678
Monthly
Go

### Support  Copyright 2008-2023 Westclintech LLC         Privacy Policy        Terms of Service