For each time interval (t) in period containing n time intervals calculate the return (rt) at time (t) as the ending market value plus withdrawals from the account divided by the beginning market value plus deposits into the account. Then calculate the product of all the rt values and subtract 1.
The aggregate function TWRR takes 3 columns as input: the date of the cash flow, the amount of the cash flow, and a bit value identifying the cash flows as a market value or not. When the bit value is 0, cash flow amounts greater than zero are treated as deposits (D) and cash flow amounts less than zero are treated as withdrawals (W). When the bit value is 1, cash flow amounts less than zero are treated as ending market values and cash flow amounts greater than zero are treated as beginning market values.
Let’s look at the calculation of time-weighted rate-of-return when the market values are the beginning market values. We will assume that the customer opens an account on 01-Jun-2017 with a deposit of 150,000. We will have a beginning market value for each day (and obviously we will be missing one for the first day, because that’s that day that the account was opened) and there will be several other transactions across the account from 01-Jun-2017 to 01-Sep-2017.
We identify the market values by setting mv = 1. Since they are going to calculated as beginning market values they will be positive. Deposits into the account will also be positive, while withdrawals will be negative. Both deposits and withdrawals will have mv = 0.
We will put the data into temp table, #cf.
SELECT
*
INTO
#cf
FROM (VALUES
('2017-06-01',NULL,1),('2017-06-01',150000,0)
,('2017-06-02',149803.89,1)
,('2017-06-05',149617.04,1)
,('2017-06-06',149489.24,1)
,('2017-06-07',149284.93,1)
,('2017-06-08',149405.33,1)
,('2017-06-09',149447.78,1)
,('2017-06-12',149620.57,1),('2017-06-12',-75000,0)
,('2017-06-13',74679.62,1)
,('2017-06-14',74756.75,1)
,('2017-06-15',74747.43,1)
,('2017-06-16',74795.3,1)
,('2017-06-19',74914.4,1)
,('2017-06-20',74929.78,1)
,('2017-06-21',74969.78,1)
,('2017-06-22',74987.43,1),('2017-06-22',25000,0)
,('2017-06-23',99937.46,1)
,('2017-06-26',100045.24,1)
,('2017-06-27',100092.94,1)
,('2017-06-28',100119.24,1)
,('2017-06-29',100126.82,1)
,('2017-06-30',100345.15,1)
,('2017-07-03',100337.07,1),('2017-07-03',-15000,0)
,('2017-07-04',85353.88,1)
,('2017-07-05',85307.15,1)
,('2017-07-06',85383.39,1)
,('2017-07-07',85449.07,1)
,('2017-07-10',85442.12,1)
,('2017-07-11',85366.81,1)
,('2017-07-12',85427.62,1)
,('2017-07-13',85244.71,1),('2017-07-13',-82500,0)
,('2017-07-14',2740.96,1)
,('2017-07-17',2739.91,1)
,('2017-07-18',2742.3,1)
,('2017-07-19',2746.64,1)
,('2017-07-20',2749.79,1),('2017-07-20',75000,0)
,('2017-07-21',77752.69,1)
,('2017-07-24',77781.27,1)
,('2017-07-25',77811.4,1)
,('2017-07-26',77960.14,1)
,('2017-07-27',77838.42,1)
,('2017-07-28',77893.44,1),('2017-07-28',25000,0),('2017-07-28',-10000,0)
,('2017-07-31',92883.74,1)
,('2017-08-01',93038.94,1)
,('2017-08-02',93013.21,1)
,('2017-08-03',92921.64,1)
,('2017-08-04',92884.4,1)
,('2017-08-07',92791.55,1)
,('2017-08-08',92951.07,1)
,('2017-08-09',92928.92,1)
,('2017-08-10',92939.06,1)
,('2017-08-11',93034.94,1)
,('2017-08-14',93170.69,1)
,('2017-08-15',93172.78,1)
,('2017-08-16',93008.48,1)
,('2017-08-17',92932.19,1)
,('2017-08-18',92848.9,1)
,('2017-08-21',92786.68,1)
,('2017-08-22',92811.25,1)
,('2017-08-23',92909.75,1)
,('2017-08-24',93027.99,1)
,('2017-08-25',93145.59,1)
,('2017-08-28',93298.38,1)
,('2017-08-29',93431.38,1)
,('2017-08-30',93509.65,1)
,('2017-08-31',93496.32,1)
,('2017-09-01',93595.27,1)
)n(dt,amt,mv)
While this will put the #cf data into the 3 columns that I want for this example, it can return multiple rows for each date and I need to get to a single row for each date. This can easily be done using the SUM function.
SELECT
dt,
SUM(BMV) as BMV,
SUM(D) as D,
SUM(W) as W
FROM (
SELECT
dt,
amt as BMV,
0 as D,
0 as W
FROM
#cf
WHERE
mv = 1
UNION ALL
SELECT
dt,
0 as BMV,
amt as D,
0 as W
FROM
#cf
WHERE
mv = 0 and amt > 0
UNION ALL
SELECT
dt,
0 as BMV,
0 as D,
-amt as W
FROM
#cf
WHERE
mv = 0 and amt < 0
)n
GROUP BY
dt
This is what should be in #t.
dt BMV D W 1+r
---------- --------------------------------------- --------------------------------------- --------------------------------------- ----------------------
2017-06-01 0.00 150000.00 0.00 0.9986926
2017-06-02 149803.89 0.00 0.00 0.998752702616734
2017-06-05 149617.04 0.00 0.00 0.999145819219522
2017-06-06 149489.24 0.00 0.00 0.998633279559117
2017-06-07 149284.93 0.00 0.00 1.00080651141411
2017-06-08 149405.33 0.00 0.00 1.00028412640968
2017-06-09 149447.78 0.00 0.00 1.00115618980757
2017-06-12 149620.57 0.00 75000.00 1.0003946649849
2017-06-13 74679.62 0.00 0.00 1.00103281189701
2017-06-14 74756.75 0.00 0.00 0.99987532898367
2017-06-15 74747.43 0.00 0.00 1.00064042335636
2017-06-16 74795.30 0.00 0.00 1.00159234604313
2017-06-19 74914.40 0.00 0.00 1.00020530098352
2017-06-20 74929.78 0.00 0.00 1.00053383314351
2017-06-21 74969.78 0.00 0.00 1.0002354281952
2017-06-22 74987.43 25000.00 0.00 0.999500237179814
2017-06-23 99937.46 0.00 0.00 1.00107847447794
2017-06-26 100045.24 0.00 0.00 1.00047678430278
2017-06-27 100092.94 0.00 0.00 1.00026275579476
2017-06-28 100119.24 0.00 0.00 1.00007570972373
2017-06-29 100126.82 0.00 0.00 1.00218053464596
2017-06-30 100345.15 0.00 0.00 0.999919477921952
2017-07-03 100337.07 0.00 15000.00 1.0001675352888
2017-07-04 85353.88 0.00 0.00 0.99945251463671
2017-07-05 85307.15 0.00 0.00 1.00089371172287
2017-07-06 85383.39 0.00 0.00 1.00076923626481
2017-07-07 85449.07 0.00 0.00 0.999918665001269
2017-07-10 85442.12 0.00 0.00 0.999118584604408
2017-07-11 85366.81 0.00 0.00 1.00071233773407
2017-07-12 85427.62 0.00 0.00 0.997858889197662
2017-07-13 85244.71 0.00 82500.00 0.999956009000441
2017-07-14 2740.96 0.00 0.00 0.999616922538089
2017-07-17 2739.91 0.00 0.00 1.00087229142563
2017-07-18 2742.30 0.00 0.00 1.00158261313496
2017-07-19 2746.64 0.00 0.00 1.00114685579472
2017-07-20 2749.79 75000.00 0.00 1.00003729913611
2017-07-21 77752.69 0.00 0.00 1.00036757570703
2017-07-24 77781.27 0.00 0.00 1.00038736832145
2017-07-25 77811.40 0.00 0.00 1.00191154509493
2017-07-26 77960.14 0.00 0.00 0.998438689309691
2017-07-27 77838.42 0.00 0.00 1.00070684888003
2017-07-28 77893.44 25000.00 10000.00 0.999905727712087
2017-07-31 92883.74 0.00 0.00 1.00167090601649
2017-08-01 93038.94 0.00 0.00 0.99972344912786
2017-08-02 93013.21 0.00 0.00 0.999015516183131
2017-08-03 92921.64 0.00 0.00 0.999599232213293
2017-08-04 92884.40 0.00 0.00 0.999000370352826
2017-08-07 92791.55 0.00 0.00 1.00171912205368
2017-08-08 92951.07 0.00 0.00 0.999761702581799
2017-08-09 92928.92 0.00 0.00 1.00010911565528
2017-08-10 92939.06 0.00 0.00 1.00103164374591
2017-08-11 93034.94 0.00 0.00 1.00145912922607
2017-08-14 93170.69 0.00 0.00 1.00002243194721
2017-08-15 93172.78 0.00 0.00 0.998236609447523
2017-08-16 93008.48 0.00 0.00 0.999179752211841
2017-08-17 92932.19 0.00 0.00 0.999103755114347
2017-08-18 92848.90 0.00 0.00 0.999329878975411
2017-08-21 92786.68 0.00 0.00 1.0002648009391
2017-08-22 92811.25 0.00 0.00 1.0010612937548
2017-08-23 92909.75 0.00 0.00 1.0012726328507
2017-08-24 93027.99 0.00 0.00 1.00126413566498
2017-08-25 93145.59 0.00 0.00 1.00164033530734
2017-08-28 93298.38 0.00 0.00 1.00142553386243
2017-08-29 93431.38 0.00 0.00 1.00083772711053
2017-08-30 93509.65 0.00 0.00 0.99985744786768
2017-08-31 93496.32 0.00 0.00 1.00105833042413
2017-09-01 93595.27 0.00 0.00 1
Want to try this out for yourself? Download the 15-day trial here. Something that you would like to see added to the product? Send us a note at support@westclintech.com.
Looking for other ways to calculate time-weighted rate-of-return? Check out the GTWRR and TWROR functions. Looking for other financial calculations? Go the financial functions index where you will find more than 250 financial written specifically for SQL Server.
