LMDIETZ

Updated 2024-02-23 21:24:56.043000

Syntax

SELECT [westclintech].[wct].[LMDIETZ] (
 <@CF, float,>
,<@CFdate, datetime,>
, <@MV, bit,>)

Description

Use the aggregate function LMDIETZ aggregate function to calculate the linked Modified Dietz. The linked Modified Dietz function calculates the Modified Dietz for multiple periods as a method for calculating a time-weighted rate of return for each period.

Formula:

LMDIETZ=\left[\prod_{t=1}^n1+R_t\right]-1

Where

R_t=\frac{EMV_t-EMV_{t-1}-CF_t}{EMV_{t-1}+\Sigma_{i=1}^nW_i}

and

W_i=\frac{(CD-C_i)}{CD}*CF_i

WhereEMV is the Ending Market ValueBMV is the Beginning Market ValueCF is the net cash flow during the period (sells/withdrawals less buys/contributions)CFi is the currency amount of cash flow iCi is the number of calendar days into the period CFi occursCD is the number of calendar days in the period

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 an Ending Market Value ('True') or not ('False' or NULL). @MV is an expression of type bit or of a type that can be implicitly converted to bit.

Return Type

float

Remarks

The Ending Market Value for one period becomes the beginning market value for the next period.

If you specify multiple Ending Market Values for the same date, then the values are added together.

The period for weighting the cash flows is the number of days from the BMV to the EMV.

Zero and NULL cash flows are ignored.

Deposits to the account should be greater than zero.

Withdrawals from the account should be less than zero.

The ending market value of long positions is positive.

Examples

Let’s assume that we have the following account information.

CREATE TABLE #m

(

    date_tran date,

    amt_tran money,

    descr_tran nvarchar(10)

);

INSERT INTO #m

VALUES

('2012-06-28', 498987.32, 'MV');

INSERT INTO #m

VALUES

('2012-07-05', -993.58, 'Withdrawal');

INSERT INTO #m

VALUES

('2012-07-10', 1000.3, 'Deposit');

INSERT INTO #m

VALUES

('2012-07-16', -954.15, 'Withdrawal');

INSERT INTO #m

VALUES

('2012-07-20', 839.55, 'Deposit');

INSERT INTO #m

VALUES

('2012-07-25', 36124.27, 'Deposit');

INSERT INTO #m

VALUES

('2012-07-25', 493997.45, 'MV');

INSERT INTO #m

VALUES

('2012-07-31', 503977.19, 'MV');

INSERT INTO #m

VALUES

('2012-08-03', -930.18, 'Withdrawal');

INSERT INTO #m

VALUES

('2012-08-09', 828.23, 'Deposit');

INSERT INTO #m

VALUES

('2012-08-14', -938.37, 'Withdrawal');

INSERT INTO #m

VALUES

('2012-08-20', 1090.9, 'Deposit');

INSERT INTO #m

VALUES

('2012-08-24', -48246.35, 'Withdrawal');

INSERT INTO #m

VALUES

('2012-08-24', 498937.42, 'MV');

INSERT INTO #m

VALUES

('2012-08-29', 509016.96, 'MV');

INSERT INTO #m

VALUES

('2012-09-04', -922.09, 'Withdrawal');

INSERT INTO #m

VALUES

('2012-09-07', 1090.67, 'Deposit');

INSERT INTO #m

VALUES

('2012-09-13', -916.77, 'Withdrawal');

INSERT INTO #m

VALUES

('2012-09-18', 1044.57, 'Deposit');

INSERT INTO #m

VALUES

('2012-09-24', 35643.6, 'Deposit');

INSERT INTO #m

VALUES

('2012-09-24', 503926.79, 'MV');

INSERT INTO #m

VALUES

('2012-09-28', 514107.13, 'MV');

Calculate the Modified Dietz for the quarter.

SELECT wct.LMDIETZ(   amt_tran,

                      date_tran,

                      CASE descr_tran

                          WHEN 'MV' THEN

                              'True'

                          ELSE

                              'False'

                      END

                  ) as LMDIETZ

FROM #m;

This produces the following result.

{"columns":[{"field":"LMDIETZ","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"LMDIETZ":"-0.0272594273584181"}]}

If we wanted to calculate the Modified Dietz for each date for which we have a market value and cumulatively, we could use the following SQL.

SELECT date_start,

       date_end,

       wct.LMDIETZ(   m1.amt_tran,

                      m1.date_tran,

                      CASE m1.descr_tran

                          WHEN 'MV' THEN

                              'True'

                          ELSE

                              'False'

                      END

                  ) as [Modified Dietz],

       wct.LMDIETZ(   m2.amt_tran,

                      m2.date_tran,

                      CASE m2.descr_tran

                          WHEN 'MV' THEN

                              'True'

                          ELSE

                              'False'

                      END

                  ) as [Cumulative]

FROM

(

    SELECT m1.date_tran as date_start,

           MIN(m2.date_tran) as date_end

    FROM #m m1

        JOIN #m m2

            ON m1.descr_tran = 'MV'

               AND m2.descr_tran = 'MV'

               AND m2.date_tran > m1.date_tran

    GROUP BY m1.date_tran

) d

    JOIN #m m1

        ON m1.date_tran

           between d.date_start and d.date_end

    JOIN #m m2

        ON m2.date_tran

           between '2012-06-28' and d.date_end

GROUP BY d.date_start,

         d.date_end;