MDIETZ_q
Updated: 5 August 2010
Use MDIETZ_q to calculate the performance of an investment portfolio based on time-weighted cash flow.
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_q] (
<@CashFlows_RangeQuery, nvarchar(4000),>
,<@StartDate, datetime,>
,<@EndDate, datetime,>
,<@BMV, float,>
,<@EMV, float,>)
Arguments
@CashFlows_RangeQuery
the select statement, as text, which will return the cash flows and cash flow dates to be used in this function. The resultant table needs to have dates in the first column and amounts in the second column. The first column in the resultant table needs to be in the datetime format or in a format that implicitly converts to datetime. The second column needs to be in the float format or in a format that implicitly converts to float.
@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 @BMV 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.
· Unlike the MDIETZ function, the MDIETZ_q function may require that the @CashFlows_RangeQuery specify the date range in the WHERE clause. For accuracy, you need to specify that the dates in the resultant table are greater than @StartDate and less than or equal to @EndDate.
· For simpler queries, use the MDIETZ function.
· If @EndDate is less than or equal to @StartDate, an error will be returned.
· If the adjusted end date is equal to the adjusted start date, a NULL will be returned.
· If the Beginning Market Value is equal and opposite of the weighted average cash flows, a NULL will be returned.
Example
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
|
One way that we could do this, is to enter the data directly in function:
SELECT wct.MDIETZ_q(
'SELECT ''01/06/2010'',4000 UNION ALL
SELECT ''01/28/2010'',1750 UNION ALL
SELECT ''02/03/2010'',-3500 UNION ALL
SELECT ''02/18/2010'',-2500 UNION ALL
SELECT ''03/01/2010'',1250 UNION ALL
SELECT ''03/15/2010'',-3750'
,'12/31/2009'
,'03/31/2010'
,20000
,18500)
This produces the following result.
----------------------
0.0584871328307772
(1 row(s) affected)
This means that the performance for the first quarter of the year was 5.85%. Let’s put the same data in a table:
CREATE TABLE TRX (
trandate datetime,
trandescr varchar(50),
tranamt float)
INSERT INTO TRX VALUES ('12/31/2009','Market Value',20000)
INSERT INTO TRX VALUES ('01/06/2010','Deposit',4000)
INSERT INTO TRX VALUES ('01/28/2010','Deposit',1750)
INSERT INTO TRX VALUES ('02/03/2010','Withdrawal',-3500)
INSERT INTO TRX VALUES ('02/18/2010','Withdrawal',-2500)
INSERT INTO TRX VALUES ('03/01/2010','Deposit',1250)
INSERT INTO TRX VALUES ('03/15/2010','Withdrawal',-3750)
INSERT INTO TRX VALUES ('03/31/2010','Market Value',18500)
In this SELECT we have made sure that the dates are within the range specified by the start date and the end date.
SELECT wct.MDIETZ_q(
'SELECT trandate, tranamt
FROM TRX
WHERE trandescr <> ''Market Value''
AND trandate > ''12/31/2009''
AND trandate <= ''03/31/2010'''
,'12/31/2009'
,'03/31/2010'
,20000
,18500)
We could have also written the SELECT as
SELECT wct.MDIETZ_q(
'SELECT trandate, tranamt
FROM TRX
WHERE trandescr <> ''Market Value''
AND trandate > ''12/31/2009''
AND trandate <= ''03/31/2010'''
,'12/31/2009'
,'03/31/2010'
,A.tranamt
,B.tranamt)
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'
This produces the following result.
----------------------
0.0584871328307772
(1 row(s) affected)
In this final example, we have changed the definition of the TRX table to include 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 A.account
,wct.MDIETZ_q(
'SELECT trandate, tranamt
FROM TRX
WHERE trandescr <> ''Market Value''
AND trandate > ''12/31/2009''
AND trandate <= ''03/31/2010''
AND account = ''' + A.account + ''''
,'12/31/2009'
,'03/31/2010'
,A.tranamt
,B.tranamt) as [Performance]
FROM TRX A JOIN TRX B
ON 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)