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.
