Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server Modified Dietz function


MDIETZ
 
Updated: 5 Auguest 2010 

Use MDIETZ to calculate the performance of an investment portfolio based on time-weighted cash flows.
 
Formula:
Modified Dietz function
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] (
  <@CashFlow_TableName, nvarchar(4000),>
 ,<@CashFlowDates_ColumnName, nvarchar(4000),>
 ,<@CashFlows_ColumnName, nvarchar(4000),>
 ,<@GroupedColumnName, nvarchar(4000),>
 ,<@GroupedColumnValue, sql_variant,>
 ,<@StartDate, datetime,>
 ,<@EndDate, datetime,>
 ,<@BMV, float,>
 ,<@EMV, float,>)
Arguments
@CashFlow_TableName
the name, as text, of the TABLE or VIEW which contains the cash flow values to be used in the modified Dietz calculation.
@CashFlowDates_ColumnName
the name, as text, of the column in the TABLE or VIEW containing the cash flow dates to be used in the modified Dietz calculation. Data in this column must be of the type datetime or of a type that implicitly converts to datetime.
@CashFlows_ColumnName
the name, as text, of the column in the TABLE or VIEW containing the cash flow values to be used in the modified Dietz calculation. Data in this column must of the type float or of a type that implicitly converts to float.
@GroupedColumnName
the name, as text, of the column in the TABLE or VIEW to group the results on.
 
@GroupedColumnValue
             the column value to do the grouping on.
 
@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 @EMV 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.
·         For more complex queries, use the MDIETZ_q function.
·         If @EndDate is less than or equal to @StartDate, an error will be returned.
·         If the adjusted end date is less than the adjusted start date, a NULL will be returned.
·         If the Beginning Market Value is equal and opposite to the weighted average cash flows, a NULL will be returned.
Example
You should run the MDIETZ function against a TABLE or a VIEW. 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

 
We can put these transactions into a temporary table and then invoke the MDIETZ function.

SELECT cast(N.trandate as datetime) as trandate
,N.trandescr
,N.tranamt
INTO #t
FROM (SELECT '12/31/2009','Market Value',20000 UNION ALL
SELECT '01/06/2010','Deposit',4000 UNION ALL
SELECT '01/28/2010','Deposit',1750 UNION ALL
SELECT '02/03/2010','Withdrawal',-3500 UNION ALL
SELECT '02/18/2010','Withdrawal',-2500 UNION ALL
SELECT '03/01/2010','Deposit',1250 UNION ALL
SELECT '03/15/2010','Withdrawal',-3750 UNION ALL
SELECT '03/31/2010','Market Value',18500) N(trandate, trandescr, tranamt)
WHERE N.trandescr <> 'Market Value'
 
SELECT wct.MDIETZ(
      '#t'
      ,'trandate'
      ,'tranamt'
      ,''
      ,NULL
      ,'12/31/2009'
      ,'03/31/2010'
      ,20000
      ,18500)

This produces the following result.

----------------------
    0.0584871328307772
 
(1 row(s) affected)

Alternatively, we could have two tables and get the beginning market value and ending market value from one of the tables and the transactions from the other.

SELECT cast(N.trandate as datetime) as trandate
,N.trandescr
,N.tranamt
INTO #t
FROM (SELECT '12/31/2009','Market Value',20000 UNION ALL
SELECT '01/06/2010','Deposit',4000 UNION ALL
SELECT '01/28/2010','Deposit',1750 UNION ALL
SELECT '02/03/2010','Withdrawal',-3500 UNION ALL
SELECT '02/18/2010','Withdrawal',-2500 UNION ALL
SELECT '03/01/2010','Deposit',1250 UNION ALL
SELECT '03/15/2010','Withdrawal',-3750 UNION ALL
SELECT '03/31/2010','Market Value',18500) N(trandate, trandescr, tranamt)
 
SELECT *
INTO #trx
FROM #t
WHERE trandescr <> 'Market Value'
 
SELECT dbo.MDIETZ(
      '#trx'
      ,'trandate'
      ,'tranamt'
      ,''
      ,NULL
      ,'12/31/2009'
      ,'03/31/2010'
      ,A.tranamt
      ,B.tranamt)
FROM #t A, #t 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 example, we have the 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','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 *
INTO #t
FROM TRX
WHERE trandescr <> 'Market Value'
 
SELECT A.Account
,dbo.MDIETZ(
      '#t'
      ,'trandate'
      ,'tranamt'
      ,'account'
      ,A.ACCOUNT
      ,'12/31/2009'
      ,'03/31/2010'
      ,A.tranamt
      ,B.tranamt) as Performance
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'
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)

 

 

See Also

 



Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service