Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server Modifed Dietz function


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.
·         Available in XLeratorDB / financial 2008 only
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
·         MDIETZ - Modified Dietz

 



Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service