TWROR
Updated 2024-02-29 20:49:39.357000
Syntax
SELECT [westclintech].[wct].[TWROR] (
<@CF, float,>
,<@CFdate, datetime,>
,<@TType, nvarchar(4000),>)
Description
Use the aggregate function 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.
r=\prod_{t=1}^n\frac{MV_t-CFE_t}{MV_{t-1}+CFB_t}-1
WhereMV is the ending market value for the periodCFE is the net cash flow for the period to be subtracted from the ending market valueCFB is the net Cash flow for the period to be added to the beginning market value
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.
{"columns":[{"field":"TWROR","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"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.
{"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":"TWRR","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":"2012-01-31","date_end":"2012-02-14","TWRR":"0.234566666666667","Cumulative":"0.234566666666667"},{"date_start":"2012-02-14","date_end":"2012-02-29","TWRR":"-0.00846412987967127","Cumulative":"0.224117134054887"},{"date_start":"2012-02-29","date_end":"2012-03-15","TWRR":"-0.313062028000944","Cumulative":"-0.159107458443039"},{"date_start":"2012-03-15","date_end":"2012-03-31","TWRR":"0.000429591692926445","Cumulative":"-0.158746217992542"},{"date_start":"2012-03-31","date_end":"2012-04-15","TWRR":"0.0914925549731962","Cumulative":"-0.0817777600958162"},{"date_start":"2012-04-15","date_end":"2012-04-30","TWRR":"0.0010000042517182","Cumulative":"-0.0808595339518892"}]}
See Also
EMDIETZ - Enhanced Modified Dietz
GTWRR - Generalized time-weighted rate of return