GTWRR
Updated 2024-02-23 15:58:12.783000
Syntax
SELECT [westclintech].[wct].[GTWRR] (
<@CF, float,>
, <@CFdate, datetime,>
, <@MV, bit,>
, <@CalcRule, int,>)
Description
Use the aggregate function GTWRR to calculate time-weighted rates of return. GTWRR supports three different methods for calculating time-weighted rates of return. You specify the formula you want to use in the @CalcRule variable. When @CalcRule = 0, the following formula is used.
r=\prod_{t=1}^n\frac{MV_t+W_t}{MV_{t-1}+D_t}-1
When @CalcRule = 1, the following formula is used.
r=\prod_{t=1}^n\frac{MV_t-CF_t}{MV_{t-1}}-1
When @CalcRule = 2, the following formula is used.
r=\prod_{t=1}^n\frac{MV_t}{MV_{t-1}+CF_t}-1
Where
r is the time the time weighted rate of return
t is the period for which the return is calculated
MV is the ending market value for the period
D is the amount that has been added to the portfolio during the period
W is the amount that has been subtracted from the portfolio during the period
CF is net cash flow for the period
Arguments
@CF
the cash flow amounts. @CF is an expression of type float or of a type that can be implicitly converted to float.
@MV
identifies the cash flow as being the (ending) Market Value ('True'). @MV is an expression of type bit or of a type that can be implicitly converted to bit.
@CFdate
the date on which the cash flow occurred. @CFDate is an expression of type datetime or of a type that can be implicitly converted to datetime.
@CalcRule
identifies the formula to be used in calculating the time-weighted rate of return. @CalcRule is an expression of type int or of a type that can be implicitly converted to int.
Return Type
float
Remarks
The GTWRR aggregate function requires a series of cash flows (@CF) and the dates on which those cash flows occurred (@CFDate) as input. As a result, the order of the cash flows is not important.
Dates on which the cash flow is zero, or on which there is no cash flow, do not have to be included.
The beginning market value for a period is the ending market value for the previous period.
Cash flows earlier than the minimum market value date are not included in the calculation.
Cash flows later than the maximum market value date are not included in the calculation.
If @CalcRule IS NULL then @CalcRule is set to 1.
GTWRR does not require a market value for each day that there is a cash movement. All cash flows will be grouped together where the cash flow date is greater than the date of the previous ending market value and less than or equal to the current market value.
For other time-weighted rate of return functions see TWRR and TWROR.
@CalcRule must be the same within a GROUP BY.
Examples
Let’s put some data into a table.
CREATE TABLE #t
(
tdate date,
tamt money,
mv bit
);
-- Populate the table
INSERT INTO #t
SELECT *
FROM
(
VALUES
('2011-12-31', 100000, 'True'),
('2012-01-10', 5500, NULL),
('2012-01-20', -5254, NULL),
('2012-01-31', 105000, 'True'),
('2012-02-10', 4556, NULL),
('2012-02-20', -9754, NULL),
('2012-02-29', 110250, 'True'),
('2012-03-10', 886, NULL),
('2012-03-10', -9525, NULL),
('2012-03-20', 9775, NULL),
('2012-03-20', -983, NULL),
('2012-03-31', 115762.5, 'True'),
('2012-04-10', -6734, NULL),
('2012-04-20', 3913, NULL),
('2012-04-30', 1284, NULL),
('2012-04-30', -6015, NULL),
('2012-04-30', 121550.63, 'True')
) n (tdate, tamt, mv);
In this example we will calculate the time-weighted rate of return using the formula (EMV – CF) / BMV).
SELECT wct.GTWRR(tamt, tdate, mv, 1) as [(EMV - CF) / BMV]
FROM #t;
This produces the following result.
{"columns":[{"field":"(EMV - CF) / BMV","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"(EMV - CF) / BMV":"0.346944952211272"}]}
In this example we will calculate the time-weighted rate of return for each period and cumulatively using the formula EMV / (BMV + CF).
SELECT date_start,
date_end,
wct.GTWRR(t1.tamt, t1.tdate, t1.mv, 2) as [EMV / (BMV + CF)],
wct.GTWRR(t2.tamt, t2.tdate, t2.mv, 2) as [Cumulative]
FROM
(
SELECT t1.tdate as date_start,
MIN(t2.tdate) as date_end
FROM #t t1
JOIN #t t2
ON t1.mv = 'True'
AND t2.mv = 'True'
AND t2.tdate > t1.tdate
GROUP BY t1.tdate
) d
JOIN #t t1
ON t1.tdate
between d.date_start and d.date_end
JOIN #t t2
ON t2.tdate
between '2011-12-31' and d.date_end
GROUP BY d.date_start,
d.date_end;
This produces the following result.
{"columns":[{"field":"date_start","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"date_end","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"EMV / (BMV + CF)","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Cumulative","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"date_start":"2011-12-31","date_end":"2012-01-31","EMV / (BMV + CF)":"0.0474233385870757","Cumulative":"0.0474233385870757"},{"date_start":"2012-01-31","date_end":"2012-02-29","EMV / (BMV + CF)":"0.104687280816016","Cumulative":"0.15707523976699"},{"date_start":"2012-02-29","date_end":"2012-03-31","EMV / (BMV + CF)":"0.0485448764979213","Cumulative":"0.213245314380281"},{"date_start":"2012-03-31","date_end":"2012-04-30","EMV / (BMV + CF)":"0.123279441459008","Cumulative":"0.362813519089841"}]}
In this example, we calculate the time-weighted rate of return for multiple accounts using data from a table that records cash movements across an account and a table that stores the market values for an account.
SELECT *
INTO #mv
FROM
(
VALUES
(1, '2011-12-31', 85012.82),
(1, '2012-01-31', 85862.95),
(1, '2012-02-29', 86721.58),
(1, '2012-03-31', 87588.79),
(2, '2011-12-31', 73767.7),
(2, '2012-01-31', 74505.38),
(2, '2012-02-29', 75250.43),
(2, '2012-03-31', 76002.94),
(3, '2011-12-31', 71494.33),
(3, '2012-01-31', 72209.27),
(3, '2012-02-29', 72931.37),
(3, '2012-03-31', 73660.68),
(4, '2011-12-31', 66259.82),
(4, '2012-01-31', 66922.42),
(4, '2012-02-29', 67591.64),
(4, '2012-03-31', 68267.56)
) n (accountno, date_mv, amt_mv);
SELECT *
INTO #trn
FROM
(
VALUES
(1, 2, '2012-01-14', -35.83),
(2, 4, '2012-02-02', -933.22),
(3, 2, '2012-03-14', 967.21),
(4, 2, '2012-02-26', 457),
(5, 3, '2012-01-12', -967.3),
(6, 4, '2012-03-25', 371.87),
(7, 2, '2012-01-01', -953.38),
(8, 2, '2012-02-29', 304.7),
(9, 4, '2012-03-06', 579.72),
(10, 3, '2012-03-08', -789.7),
(11, 4, '2012-01-07', 925.93),
(12, 4, '2012-01-04', 624.14),
(13, 4, '2012-03-23', 291.59),
(14, 2, '2012-03-08', 949.7),
(15, 1, '2012-01-18', 966.84),
(16, 4, '2012-03-09', -262.56),
(17, 3, '2012-01-14', 619.02),
(18, 4, '2012-03-08', -437.44),
(19, 4, '2012-02-18', -557.03),
(20, 2, '2012-01-07', -846.8),
(21, 1, '2012-03-02', -950.56),
(22, 4, '2012-02-12', -185.72),
(23, 2, '2012-03-08', -665.38),
(24, 1, '2012-01-11', -37.85),
(25, 4, '2012-01-16', -705.25),
(26, 4, '2012-02-04', 626.54),
(27, 3, '2012-02-11', 177.31),
(28, 4, '2012-01-20', -731.52),
(29, 3, '2012-01-03', 691.15),
(30, 3, '2012-03-19', -521.52),
(31, 1, '2012-01-14', 779.75),
(32, 4, '2012-01-30', -341.24),
(33, 3, '2012-03-12', 300.8),
(34, 2, '2012-02-05', -152.45),
(35, 1, '2012-03-08', 262.74),
(36, 2, '2012-01-18', -193.99),
(37, 2, '2012-03-30', 826.88),
(38, 2, '2012-01-03', 460.72),
(39, 2, '2012-02-26', 562.05),
(40, 1, '2012-02-10', -834.91),
(41, 3, '2012-01-21', -723.25),
(42, 4, '2012-03-12', -329.02),
(43, 3, '2012-01-30', -262.32),
(44, 3, '2012-03-15', 730.71),
(45, 4, '2012-01-04', -622.92),
(46, 3, '2012-01-09', -137.88),
(47, 1, '2012-02-13', 769.51),
(48, 2, '2012-03-31', 433.96),
(49, 3, '2012-01-16', 465.57)
) n (trno, accountno, date_trn, amt_trn);
SELECT accountno,
wct.GTWRR(cf, dt, mv, 1) as TWRR
FROM
(
SELECT accountno,
date_mv,
amt_mv,
'True'
FROM #mv
UNION ALL
SELECT accountno,
date_trn,
amt_trn,
NULL
FROM #trn
) n(accountno, dt, cf, mv)
GROUP BY accountno;
This produces the following result.
{"columns":[{"field":"accountno","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"TWRR","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"accountno":"1","TWRR":"0.018494435683666"},{"accountno":"2","TWRR":"0.00139321913891433"},{"accountno":"3","TWRR":"0.0361997183850629"},{"accountno":"4","TWRR":"0.056276223766047"}]}
Using the same tables from the previous example, we will calculate the time-weighted rate of return for each month and cumulatively.
;with mycte (accountno, dt, cf, mv)
as (SELECT accountno,
date_mv,
amt_mv,
'True'
FROM #mv
UNION ALL
SELECT accountno,
date_trn,
amt_trn,
NULL
FROM #trn)
SELECT d.accountno,
d.date_start,
d.date_end,
wct.GTWRR(t1.cf, t1.dt, t1.mv, 2) as TWRR_Month,
wct.GTWRR(t2.cf, t2.dt, t2.mv, 2) as TWRR_Cum
FROM
(
SELECT m1.accountno as accountno,
m1.date_mv as date_start,
MIN(m2.date_mv) as date_end
FROM #mv m1
JOIN #mv m2
ON m1.accountno = m2.accountno
AND m2.date_mv > m1.date_mv
GROUP BY m1.date_mv,
m1.accountno
) d
JOIN mycte t1
ON t1.accountno = d.accountno
AND t1.dt
between d.date_start and d.date_end
JOIN mycte t2
ON t2.accountno = d.accountno
AND t2.dt
between '2011-12-31' and d.date_end
GROUP BY d.accountno,
d.date_start,
d.date_end;
This produces the following result.
{"columns":[{"field":"accountno","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"ate_start","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"ate_end","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"TWRR_Month","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"TWRR_Cum","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"accountno":"1","ate_start":"2011-12-31","ate_end":"2012-01-31","TWRR_Month":"-0.00990076746774404","TWRR_Cum":"-0.00990076746774404"},{"accountno":"1","ate_start":"2012-01-31","ate_end":"2012-02-29","TWRR_Month":"0.0107698879513458","TWRR_Cum":"0.000762490327341769"},{"accountno":"1","ate_start":"2012-02-29","ate_end":"2012-03-31","TWRR_Month":"0.0180746488355272","TWRR_Cum":"0.0188509209077758"},{"accountno":"2","ate_start":"2011-12-31","ate_end":"2012-01-31","TWRR_Month":"0.0319530538202915","TWRR_Cum":"0.0319530538202915"},{"accountno":"2","ate_start":"2012-01-31","ate_end":"2012-02-29","TWRR_Month":"-0.00563251453420033","TWRR_Cum":"0.0261405632460365"},{"accountno":"2","ate_start":"2012-02-29","ate_end":"2012-03-31","TWRR_Month":"-0.0226311295375164","TWRR_Cum":"0.00291784323551547"},{"accountno":"3","ate_start":"2011-12-31","ate_end":"2012-01-31","TWRR_Month":"0.0144697926307811","TWRR_Cum":"0.0144697926307811"},{"accountno":"3","ate_start":"2012-01-31","ate_end":"2012-02-29","TWRR_Month":"0.00752611879163223","TWRR_Cum":"0.0221048128006431"},{"accountno":"3","ate_start":"2012-02-29","ate_end":"2012-03-31","TWRR_Month":"0.0138884644893176","TWRR_Cum":"0.0363002791975857"},{"accountno":"4","ate_start":"2011-12-31","ate_end":"2012-01-31","TWRR_Month":"0.0231384201797429","TWRR_Cum":"0.0231384201797429"},{"accountno":"4","ate_start":"2012-01-31","ate_end":"2012-02-29","TWRR_Month":"0.026090359645129","TWRR_Cum":"0.0498324695289816"},{"accountno":"4","ate_start":"2012-02-29","ate_end":"2012-03-31","TWRR_Month":"0.00681003689949899","TWRR_Cum":"0.0569818673847651"}]}
See Also
EMDIETZ - Enhanced Modified Dietz
LMDIETZ - Linked Modified Dietz
TWROR - Time-weighted rate of return with market value indicators