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.
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
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.
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