MDIETZ
Updated: 5 Auguest 2010
Use MDIETZ to calculate the performance of an investment portfolio based on time-weighted cash flows.
Formula:
And
EMV is the Ending Market Value
BMV is the Beginning Market Value
CF 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 occurs
CD is the number of calendar days in the period
Syntax
SELECT [wctFinancial].[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,>)
Arguments
@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.
@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.
@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.
@GroupedColumnName
the name, as text, of the column in the TABLE or VIEW to group the results on.
@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.
@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.
@EMV
the ending market value. The @EMV must evaluate to float.
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 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.
Example
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:
|
Date
|
Description
|
Value
|
|
12/31/2009
|
Market Value
|
20,000
|
|
1/6/2010
|
Deposit
|
4,000
|
|
1/28/2010
|
Deposit
|
1,750
|
|
2/3/2010
|
Withdrawal
|
-3,500
|
|
2/18/2010
|
Withdrawal
|
-2,500
|
|
3/1/2010
|
Deposit
|
1,250
|
|
3/15/2010
|
Withdrawal
|
-3,750
|
|
3/31/2010
|
Market 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.
----------------------
0.0584871328307772
(1 row(s) affected)
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 dbo.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.
----------------------
0.0584871328307772
(1 row(s) affected)
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
,dbo.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
Account Performance
--------------- ----------------------
1 0.0584871328307772
2 0.0172725509214355
3 0.048668201249589
(3 row(s) affected)
See Also