LMDIETZ
Updated: 21 December 2012
Use the LMDIETZ aggregate function to calculate the linked Modified Dietz. The linked Modified Dietz function calculates the Modified Dietz for multiple periods as a method for calculating a time-weighted rate of return for each period.
Formula:
Where
and
Where
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
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.
@MV
Identifies the cash flow as being an Ending Market Value ('True') or not ('False' or NULL). @MV is an expression of type bit or of a type that can be implicitly converted to bit.
Return Type
float
Remarks
· The Ending Market Value for one period becomes the beginning market value for the next period.
· If you specify multiple Ending Market Values for the same date, then the values are added together.
· 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 should be greater than zero.
· Withdrawals from the account should be less than zero.
· The ending market value of long positions is positive.
Example
Let’s assume that we have the following account information.
CREATE TABLE #m(
date_tran date,
amt_tran money,
descr_tran nvarchar(10)
)
INSERT INTO #m VALUES ('2012-06-28',498987.32,'MV')
INSERT INTO #m VALUES ('2012-07-05',-993.58,'Withdrawal')
INSERT INTO #m VALUES ('2012-07-10',1000.3,'Deposit')
INSERT INTO #m VALUES ('2012-07-16',-954.15,'Withdrawal')
INSERT INTO #m VALUES ('2012-07-20',839.55,'Deposit')
INSERT INTO #m VALUES ('2012-07-25',36124.27,'Deposit')
INSERT INTO #m VALUES ('2012-07-25',493997.45,'MV')
INSERT INTO #m VALUES ('2012-07-31',503977.19,'MV')
INSERT INTO #m VALUES ('2012-08-03',-930.18,'Withdrawal')
INSERT INTO #m VALUES ('2012-08-09',828.23,'Deposit')
INSERT INTO #m VALUES ('2012-08-14',-938.37,'Withdrawal')
INSERT INTO #m VALUES ('2012-08-20',1090.9,'Deposit')
INSERT INTO #m VALUES ('2012-08-24',-48246.35,'Withdrawal')
INSERT INTO #m VALUES ('2012-08-24',498937.42,'MV')
INSERT INTO #m VALUES ('2012-08-29',509016.96,'MV')
INSERT INTO #m VALUES ('2012-09-04',-922.09,'Withdrawal')
INSERT INTO #m VALUES ('2012-09-07',1090.67,'Deposit')
INSERT INTO #m VALUES ('2012-09-13',-916.77,'Withdrawal')
INSERT INTO #m VALUES ('2012-09-18',1044.57,'Deposit')
INSERT INTO #m VALUES ('2012-09-24',35643.6,'Deposit')
INSERT INTO #m VALUES ('2012-09-24',503926.79,'MV')
INSERT INTO #m VALUES ('2012-09-28',514107.13,'MV')
Calculate the Modfied Dietz for the quarter.
SELECT wct.LMDIETZ(amt_tran
,date_tran
,CASE descr_tran
WHEN 'MV' THEN 'True'
ELSE 'False'
END
) as LMDIETZ
FROM #m
This produces the following result.
LMDIETZ
----------------------
-0.0272594273584181
If we wanted to calculate the Modified Dietz for each date for which we have a market value and cumulatively, we could use the following SQL.
SELECT date_start
,date_end
,wct.LMDIETZ(m1.amt_tran
,m1.date_tran
,CASE m1.descr_tran
WHEN 'MV' THEN 'True'
ELSE 'False'
END
) as [Modified Dietz]
,wct.LMDIETZ(m2.amt_tran
,m2.date_tran
,CASE m2.descr_tran
WHEN 'MV' THEN 'True'
ELSE 'False'
END
) as [Cumulative]
FROM (
SELECT m1.date_tran as date_start
,MIN(m2.date_tran) as date_end
FROM #m m1
JOIN #m m2
ON m1.descr_tran = 'MV'
AND m2.descr_tran = 'MV'
AND m2.date_tran > m1.date_tran
GROUP BY m1.date_tran
) d
JOIN #m m1
ON m1.date_tran between d.date_start and d.date_end
JOIN #m m2
ON m2.date_tran between '2012-06-28' and d.date_end
GROUP BY d.date_start, d.date_end
This produces the following result.
date_start date_end Modified Dietz Cumulative
---------- ---------- ---------------------- ----------------------
2012-06-28 2012-07-25 -0.0822354637534843 -0.0822354637534843
2012-07-25 2012-07-31 0.0202020071156237 -0.0636947780617653
2012-07-31 2012-08-24 0.0857168243018607 0.0165623321400297
2012-08-24 2012-08-29 0.0202020125089035 0.0370989370900026
2012-08-29 2012-09-24 -0.0806292943843931 -0.0465216184143677
2012-09-24 2012-09-28 0.0202020218055887 -0.027259427358418
See Also