MDIETZ
Updated 2024-02-23 21:27:34.803000
Syntax
SELECT [westclintech].[wct].[MDIETZ] (
<@CashFlow_TableName, nvarchar(4000),>
,<@CashFlowDates_ColumnName, nvarchar(4000),>
,<@CashFlows_ColumnName, nvarchar(4000),>
,<@GroupedColumnName, nvarchar(4000),>
,<@GroupedColumnValue, sql_variant,>
,<@StartDate, datetime,>
,<@EndDate, datetime,>
,<@BMV, float,>
,<@EMV, float,>)
Description
Use the scalar function MDIETZ 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 (sells/withdrawals less buys/contributions)CFi is the currency amount of cash flow *i*Ci is the number of calendar days into the period CFi occursCD is the number of calendar days in the period
## Arguments
### @CashFlowDates_ColumnName
the name, as text, of the column in the TABLE or VIEW containing the cash flow dates to be used in the modified Dietz calculation. Data in this column must be of the type datetime or of a type that implicitly converts to datetime.
### @GroupedColumnName
the name, as text, of the column in the TABLE or VIEW to group the results on.
### @EMV
the ending market value. The @EMV must evaluate to float.
### @EndDate
the date value to be used in modified Dietz calculation to determine the length of the period. The period is calculated as the difference between the @StartDate and the @EndDate. The column values must evaluate to datetime.
### @BMV
the beginning market value. The @BMV must evaluate to float.
### @CashFlows_ColumnName
the name, as text, of the column in the TABLE or VIEW containing the cash flow values to be used in the modified Dietz calculation. Data in this column must of the type float or of a type that implicitly converts to float.
### @GroupedColumnValue
the column value to do the grouping on.
### @StartDate
the date value to be used in modified Dietz calculation to determine the length of the period. The period is calculated as the difference between the @StartDate and the @EndDate. The @StartDate, generally, is the end date of the previous period. Thus, to calculate the modified Dietz for the first quarter of the current calendar year, the @StartDate would be set to December 31 of the previous year and the @EndDate would be set to March 31 of the current year. The column values must evaluate to datetime.
### @CashFlow_TableName
the name, as text, of the TABLE or VIEW which contains the cash flow values to be used in the modified Dietz calculation.
## Return Type
float
## Remarks
For accounts where the beginning market value and the ending market value are non-zero, the length of the period is the difference between the start date and the end date.
For accounts where the beginning market value is zero, the start date is adjusted to the date of the first cash flow between the start date and the end date.
For accounts where the ending market value is zero, the end date is adjusted to the date of the final cash flow between the start date and the end date.
For more complex queries, use the [MDIETZ_Q](./MDIETZ_Q) function.
If @EndDate is less than or equal to @StartDate, an error will be returned.
If the adjusted end date is less than the adjusted start date, a NULL will be returned.
If the Beginning Market Value is equal and opposite to the weighted average cash flows, a NULL will be returned.
## Examples
You should run the MDIETZ function against a TABLE or a VIEW. Let’s assume that we want to calculate the performance on an account that had the following transactions:
```table
{"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":"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":"Market Value","Value":"18,500"}]}
We can put these transactions into a temporary table and then invoke the MDIETZ function.
SELECT cast(N.trandate as datetime) as trandate,
N.trandescr,
N.tranamt
INTO #t
FROM
(
SELECT '12/31/2009',
'Market Value',
20000
UNION ALL
SELECT '01/06/2010',
'Deposit',
4000
UNION ALL
SELECT '01/28/2010',
'Deposit',
1750
UNION ALL
SELECT '02/03/2010',
'Withdrawal',
-3500
UNION ALL
SELECT '02/18/2010',
'Withdrawal',
-2500
UNION ALL
SELECT '03/01/2010',
'Deposit',
1250
UNION ALL
SELECT '03/15/2010',
'Withdrawal',
-3750
UNION ALL
SELECT '03/31/2010',
'Market Value',
18500
) N(trandate, trandescr, tranamt)
WHERE N.trandescr <> 'Market Value';
SELECT wct.MDIETZ('#t', 'trandate', 'tranamt', '', NULL, '12/31/2009', '03/31/2010',
20000, 18500);
This produces the following result.
{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"0.0584871328307772"}]}
Alternatively, we could have two tables and get the beginning market value and ending market value from one of the tables and the transactions from the other.
SELECT cast(N.trandate as datetime) as trandate,
N.trandescr,
N.tranamt
INTO #t
FROM
(
SELECT '12/31/2009',
'Market Value',
20000
UNION ALL
SELECT '01/06/2010',
'Deposit',
4000
UNION ALL
SELECT '01/28/2010',
'Deposit',
1750
UNION ALL
SELECT '02/03/2010',
'Withdrawal',
-3500
UNION ALL
SELECT '02/18/2010',
'Withdrawal',
-2500
UNION ALL
SELECT '03/01/2010',
'Deposit',
1250
UNION ALL
SELECT '03/15/2010',
'Withdrawal',
-3750
UNION ALL
SELECT '03/31/2010',
'Market Value',
18500
) N(trandate, trandescr, tranamt);
SELECT *
INTO #trx
FROM #t
WHERE trandescr <> 'Market Value';
SELECT wct.MDIETZ('#trx', 'trandate', 'tranamt', '', NULL, '12/31/2009', '03/31/2010',
A.tranamt, B.tranamt)
FROM #t A,
#t B
WHERE A.trandate = '12/31/2009'
AND A.trandescr = 'Market Value'
AND B.trandate = '03/31/2010'
AND B.trandescr = 'Market Value';
This produces the following result.
{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"0.0584871328307772"}]}
In this example, we have the 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','12/31/2009','Market Value',20000);
INSERT INTO TRX VALUES ('1','01/06/2010','Deposit',4000);
INSERT INTO TRX VALUES ('1','01/28/2010','Deposit',1750);
INSERT INTO TRX VALUES ('1','02/03/2010','Withdrawal',-3500);
INSERT INTO TRX VALUES ('1','02/18/2010','Withdrawal',-2500);
INSERT INTO TRX VALUES ('1','03/01/2010','Deposit',1250);
INSERT INTO TRX VALUES ('1','03/15/2010','Withdrawal',-3750);
INSERT INTO TRX VALUES ('1','03/31/2010','Market Value',18500);
INSERT INTO TRX VALUES ('2','12/31/2009','Market Value',0);
INSERT INTO TRX VALUES ('2','01/06/2010','Deposit',4000);
INSERT INTO TRX VALUES ('2','01/28/2010','Deposit',1750);
INSERT INTO TRX VALUES ('2','02/03/2010','Deposit',3500);
INSERT INTO TRX VALUES ('2','02/18/2010','Withdrawal',-2500);
INSERT INTO TRX VALUES ('2','03/01/2010','Deposit',1250);
INSERT INTO TRX VALUES ('2','03/15/2010','Withdrawal',-3750);
INSERT INTO TRX VALUES ('2','03/31/2010','Market Value',4356);
INSERT INTO TRX VALUES ('3','12/31/2009','Market Value',14500);
INSERT INTO TRX VALUES ('3','01/06/2010','Deposit',4000);
INSERT INTO TRX VALUES ('3','01/28/2010','Deposit',1750);
INSERT INTO TRX VALUES ('3','02/03/2010','Deposit',3500);
INSERT INTO TRX VALUES ('3','02/18/2010','Withdrawal',-2500);
INSERT INTO TRX VALUES ('3','03/01/2010','Deposit',1250);
INSERT INTO TRX VALUES ('3','03/15/2010','Withdrawal',-23500);
INSERT INTO TRX VALUES ('3','03/31/2010','Market Value',0);
We want to produce a result that shows the performance for each account.
SELECT *
INTO #t
FROM TRX
WHERE trandescr <> 'Market Value';
SELECT A.Account,
wct.MDIETZ('#t', 'trandate', 'tranamt', 'account', A.ACCOUNT, '12/31/2009',
'03/31/2010', A.tranamt, B.tranamt) as Performance
FROM TRX A,
TRX B
WHERE A.trandate = '12/31/2009'
AND A.trandescr = 'Market Value'
AND B.trandate = '03/31/2010'
AND B.trandescr = 'Market Value'
AND A.ACCOUNT = B.ACCOUNT;
This produces the following result.
{"columns":[{"field":"Account","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Performance","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Account":"1","Performance":"0.0584871328307772"},{"Account":"2","Performance":"0.0172725509214355"},{"Account":"3","Performance":"0.048668201249589"}]}
See Also
EMDIETZ - Enhanced Modified Dietz
GTWRR - Generalized time-weighted rate of return
LMDIETZ - Linked Modified Dietz
TWROR - Time-weighted rate of return with market value indicators