EMDIETZ
Updated: 31 October 2011
Use the EMDIETZ aggregate function to calculate the performance of an investment portfolio based on time-weighted cash flows.
Formula:
Where
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 [westclintech].[wct].[EMDIETZ] (
<@CF, float,>
,<@CFDate, datetime),>)
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.
Example
Let’s assume that we have the following account information.
|
Date
|
Description
|
Value
|
|
12/31/2009
|
Beginning 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
|
Ending Market 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.
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
This produces the following result
account performance
--------------- ----------------------
1 0.0584871328307772
2 0.0172725509214355
3 0.0486682012495889
See Also