Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server Modified Dietz for multiple periods


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:
XLeratorDB LMDIETZ 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
Syntax for XLeratorDB function LMDIETZ for SQL Server
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.
·         Available in XLeratorDB / financial 2008 only
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
·         MDIETZ - Modified Dietz

 



Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service