Need Excel solution - mnthy rtrns using "Modified Dietz"

[npappous]

I need a Excel solution to cazluclating monthly returns using "Modifed
Dietz". Has anyone done this?

 

[Shane Devenshire]

Hi,

You can do that in the spreadsheet but its probably easier with a custom VBA
function:

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

Note: I do not take credit for this code.