GTWRR
Updated: 21 December 2012
Use 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.
When @CalcRule = 1, the following formula is used.
When @CalcRule = 2, the following formula is used.
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
Syntax
Arguments
@CF
the cash flow amounts. @CF is an expression of type float or of a type that can be implicitly converted to float.
@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.
@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.
@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.
(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.
date_start date_end EMV / (BMV + CF) Cumulative
---------- ---------- ---------------------- ----------------------
2011-12-31 2012-01-31 0.0474233385870757 0.0474233385870757
2012-01-31 2012-02-29 0.104687280816016 0.15707523976699
2012-02-29 2012-03-31 0.0485448764979213 0.213245314380281
2012-03-31 2012-04-30 0.123279441459008 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.
accountno TWRR
----------- ----------------------
1 0.018494435683666
2 0.00139321913891433
3 0.0361997183850629
4 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.
accountno date_start date_end TWRR_Month TWRR_Cum
----------- ---------- ---------- ---------------------- ----------------------
1 2011-12-31 2012-01-31 -0.00990076746774404 -0.00990076746774404
1 2012-01-31 2012-02-29 0.0107698879513458 0.000762490327341769
1 2012-02-29 2012-03-31 0.0180746488355272 0.0188509209077758
2 2011-12-31 2012-01-31 0.0319530538202915 0.0319530538202915
2 2012-01-31 2012-02-29 -0.00563251453420033 0.0261405632460365
2 2012-02-29 2012-03-31 -0.0226311295375164 0.00291784323551547
3 2011-12-31 2012-01-31 0.0144697926307811 0.0144697926307811
3 2012-01-31 2012-02-29 0.00752611879163223 0.0221048128006431
3 2012-02-29 2012-03-31 0.0138884644893176 0.0363002791975857
4 2011-12-31 2012-01-31 0.0231384201797429 0.0231384201797429
4 2012-01-31 2012-02-29 0.026090359645129 0.0498324695289816
4 2012-02-29 2012-03-31 0.00681003689949899 0.0569818673847651
See Also