EMDIETZ

Updated 2024-02-23 14:48:08.067000

Syntax

SELECT [westclintech].[wct].[EMDIETZ] (
   <@CF, float,>
 ,<@CFDate, datetime),>)

Description

Use the EMDIETZ aggregate function to calculate the performance of an investment portfolio based on time-weighted cash flows.

Formula:

R=\frac{EMV-BMV-CF}{BMV+\Sigma_{i=1}^nW_i}

Where

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

AndEMV is the Ending Market ValueBMV is the Beginning Market ValueCF is the net cash flow during the period (sales/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.

Return Type

float

Remarks

The Beginning Market Value (BMV) is calculated by the function. The BMV is the sum of the cash flows for the earliest date in the set of cash flows. For best results, there should only be one cash flow for the BMV.

The Ending Market Value is calculated by the function. Unlike the scalar versions of the modified Dietz calculation (MDIETZ and MDIETZ_q), EMDIETZ expects the ending market value to be negative.

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 and the Beginning Market Value should be greater than zero.

Withdrawals from the account and the Ending Market Value should be less than zero.

Examples

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

{"columns":[{"field":"Date","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell","width":75},{"field":"Description","width":179},{"field":"Value","width":69}],"rows":[{"Date":"12/31/2009","Description":"Beginning Market Value","Value":"20,000"},{"Date":"1/6/2010","Description":"Deposit","Value":"4,000"},{"Date":"1/28/2010","Description":"Deposit","Value":"1,750"},{"Date":"2/3/2010","Description":"Withdrawal","Value":"-3,500"},{"Date":"2/18/2010","Description":"Withdrawal","Value":"-2,500"},{"Date":"3/1/2010","Description":"Deposit","Value":"1,250"},{"Date":"3/15/2010","Description":"Withdrawal","Value":"-3,750"},{"Date":"3/31/2010","Description":"Ending Market Value","Value":"18,500"}]}

We can put these transactions into a temporary table and then invoke the EMDIETZ function.

SELECT cast(N.trandate as datetime) as trandate,

       N.trandescr,

       N.tranamt

INTO #t

FROM

(

    VALUES

        ('2009-12-31', 'Beginning Market Value', 20000),

        ('2010-01-06', 'Deposit', 4000),

        ('2010-01-28', 'Deposit', 1750),

        ('2010-02-03', 'Withdrawal', -3500),

        ('2010-02-18', 'Withdrawal', -2500),

        ('2010-03-01', 'Deposit', 1250),

        ('2010-03-15', 'Withdrawal', -3750),

        ('2010-03-31', 'Ending Market Value', -18500)

) n (trandate, trandescr, tranamt);

SELECT wct.EMDIETZ(tranamt, trandate) as [Modified Dietz]

FROM #t;

This produces the following result.

{"columns":[{"field":"Modified Dietz","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Modified Dietz":"0.0584871328307772"}]}

In this example, we have a #trx table which includes an account number and we have an account that is opened during the period and an account that is closed during the period.

CREATE TABLE #trx(
      account     varchar(15),
      trandate    datetime,
      trandescr   varchar(50),
      tranamt     float);
 
INSERT INTO #trx VALUES ('1','2009-12-31','Beginning Market Value',20000);
INSERT INTO #trx VALUES ('1','2010-01-06','Deposit',4000);
INSERT INTO #trx VALUES ('1','2010-01-28','Deposit',1750);
INSERT INTO #trx VALUES ('1','2010-02-03','Withdrawal',-3500);
INSERT INTO #trx VALUES ('1','2010-02-18','Withdrawal',-2500);
INSERT INTO #trx VALUES ('1','2010-03-01','Deposit',1250);
INSERT INTO #trx VALUES ('1','2010-03-15','Withdrawal',-3750);
INSERT INTO #trx VALUES ('1','2010-03-31','Ending Market Value',-18500);
INSERT INTO #trx VALUES ('2','2009-12-31','Beginning Market Value',0);
INSERT INTO #trx VALUES ('2','2010-01-06','Deposit',4000);
INSERT INTO #trx VALUES ('2','2010-01-28','Deposit',1750);
INSERT INTO #trx VALUES ('2','2010-02-03','Deposit',3500);
INSERT INTO #trx VALUES ('2','2010-02-18','Withdrawal',-2500);
INSERT INTO #trx VALUES ('2','2010-03-01','Deposit',1250);
INSERT INTO #trx VALUES ('2','2010-03-15','Withdrawal',-3750);
INSERT INTO #trx VALUES ('2','2010-03-31','Ending Market Value',-4356);
INSERT INTO #trx VALUES ('3','2009-12-31','Market Value',14500);
INSERT INTO #trx VALUES ('3','2010-01-06','Deposit',4000);
INSERT INTO #trx VALUES ('3','2010-01-28','Deposit',1750);
INSERT INTO #trx VALUES ('3','2010-02-03','Deposit',3500);
INSERT INTO #trx VALUES ('3','2010-02-18','Withdrawal',-2500);
INSERT INTO #trx VALUES ('3','2010-03-01','Deposit',1250);
INSERT INTO #trx VALUES ('3','2010-03-15','Withdrawal',-23500);
INSERT INTO #trx VALUES ('3','2010-03-31','Ending Market Value',0);

We want to produce a result that shows the performance for each account.

SELECT account
,wct.EMDIETZ(tranamt, trandate) as performance
FROM #trx
GROUP BY account;