Logo

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

LMDIETZ - Linked Modified Dietz

MDIETZ - Modified Dietz

TWRR - Calculate time-weighted rate of return.