Modified Dietz Method

From Wikipedia, the free encyclopedia

The Modified Dietz Method is a calculation used to determine the performance of an investment portfolio based on time-weighted cash flow. [1]


In the absence of daily portfolio valuations, the modified Dietz method weights individual cash flows by the amount of time that those cash flows are held (or absent) from the portfolio. The result is an approximation of a time-weighted return.


The formula for modified Dietz is as follows:

R_{MDietz}= \cfrac{ EMV - BMV - CF}{ BMV+ \sum_{i=1}^n Wi \times CF_i}


where:
EMV = ending market value
BMV = beginning market value
CF = the net cash flow for the period (contributions to a portfolio are entered as positive cash flows while withdrawals are entered as negative cash flows)[[Category:]]
and \sum_{i=1}^n Wi \times {CF_i} = the sum of each cash flow, CFi, multiplied by its weight, Wi


The weight (Wi) is the proportion of the total number of days in the period that the cash flow CFi is in (or out) of the portfolio. Wi can be calculated as:


W_i = \frac{CD-D_i}{CD}

where:
CD = the number of calendar days during the return period being calculated
Di = The day in the return period on which the cash flow (CFi) occurred


Excel VBA Function for Modified Dietz Return:

Public Function MDIETZ(dStartValue As Double, dEndValue As Double, iPeriod As Integer, rCash As Range, rDays As Range) As Double

    'Jelle-Jeroen Lamkamp 10 Jan 2008
    Dim i As Integer: Dim Cash() As Double: Dim Days() As Integer
    Dim Cell As Range: Dim SumCash As Double: Dim TempSum As Double
    
    'Some error trapping
    If rCash.Cells.Count <> rDays.Cells.Count Then MDIETZ = CVErr(xlErrValue): Exit Function
    If Application.WorksheetFunction.Max(rDays) > iPeriod Then MDIETZ = CVErr(xlErrValue): Exit Function
    
    ReDim Cash(rCash.Cells.Count - 1)
    ReDim Days(rDays.Cells.Count - 1)
    
    i = 0
    For Each Cell In rCash
        Cash(i) = Cell.Value: i = i + 1
    Next Cell
    
    i = 0
    For Each Cell In rDays
        Days(i) = Cell.Value: i = i + 1
    Next Cell
    
    SumCash = Application.WorksheetFunction.Sum(rCash)

    TempSum = 0
    For i = 0 To (rCash.Cells.Count - 1)
            TempSum = TempSum + (((iPeriod - Days(i)) / iPeriod) * Cash(i))
    Next i
    
    MDIETZ = (dEndValue - dStartValue - SumCash) / (dStartValue + TempSum)

End Function
 This economics or finance-related article is a stub. You can help Wikipedia by expanding it.

[edit] Further Reading

Bruce J. Feibel. Investment Performance Measurement. New York: Wiley, 2003. ISBN 0471268496