Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server time-weighted rate of return


TWROR

Updated: 21 December 2012


Use TWROR to calculate time-weighted rates of return, allowing you to specify which cash flows are used in the numerator of the calculation and which cash flows are used in the denominator. The TWROR calculation is.

formala for TWROR time weighted rate of return function for SQL Server 
 
Where
                MV is the ending market value for the period
                CFE is the net cash flow for the period to be subtracted from the ending market value
                CFB is the net Cash flow for the period to be added to the beginning market value
Syntax
XLeratorDB syntax for TWROR function 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.
 
@TType
identifies the cash flow as being the Market value ('M'), an amount to be subtracted from the ending market value ('E') or an amount to be added to the beginning market value ('B' or NULL).
Return Type
float
Remarks
·         The TWROR 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.
·         TWROR 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 GTWRR and TWRR.
·         Available in XLeratorDB / financial 2008 only
Examples
Given the following cash flow information, calculate the time-weighted rate of return.
CREATE TABLE #t(
      tdate       date,
      tamt        money,
      ttype       varchar(2)
      )
 
INSERT INTO #t
SELECT *
FROM (VALUES
('2012-01-31',-100000,'M'),                    
('2012-02-14',-111111,'M'),
('2012-02-14',10000,''),           
('2012-02-29',-110666.31,'M'),
('2012-02-29',-500,'B'),                 
('2012-03-15',-89759.65,'M'),
('2012-03-15',-20000,''),    
('2012-03-31',-90298.21,'M'),
('2012-03-31',-500,'E'),
('2012-04-15',-94079.6,'M'),
('2012-04-15',500,'B'),
('2012-04-15',11000,''),
('2012-04-15',-8000,''),
('2012-04-15',660,'E'),
('2012-04-30',-94173.68,'M')                   
)n(tdate,tamt,ttype)
 
 
SELECT wct.TWROR(tamt,tdate,ttype) as TWROR
FROM #t
This produces the following result.
                 TWROR
----------------------
   -0.0808595339518893
 
Using the same data we can calculate the time-weighted rate of return for each period and cumulatively.
SELECT date_start
,date_end
,wct.TWROR(t1.tamt,t1.tdate,t1.ttype) as [TWRR]
,wct.TWROR(t2.tamt,t2.tdate,t2.ttype) as [Cumulative]
FROM (
      SELECT t1.tdate as date_start
      ,MIN(t2.tdate) as date_end
      FROM #t t1
      JOIN #t t2
      ON t1.ttype = 'M'
      AND t2.ttype = 'M'
      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 '2012-01-31' and d.date_end
GROUP BY d.date_start, d.date_end
This produces the following result.
date_start date_end                     TWRR             Cumulative
---------- ---------- ---------------------- ----------------------
2012-01-31 2012-02-14      0.234566666666667      0.234566666666667
2012-02-14 2012-02-29   -0.00846412987967127      0.224117134054887
2012-02-29 2012-03-15     -0.313062028000944     -0.159107458443039
2012-03-15 2012-03-31   0.000429591692926445     -0.158746217992542
2012-03-31 2012-04-15     0.0914925549731962    -0.0817777600958162
2012-04-15 2012-04-30     0.0010000042517182    -0.0808595339518892
 
 

 

See Also
·         MDIETZ - Modified Dietz

 



Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service