Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server time-weighted rate of return function


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.

formula for GTWRR time-weighted rate of return frunction for SQL Server 
 
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
syntax for XLeratorDB function GTWRR for SQL server
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.
·         Available in XLeratorDB / financial 2008 only
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
·         MDIETZ - Modified Dietz

 



Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service