A Programmer's Dream

And A Great Big Modified Dietz To You Too

Posted by Stephen Wrighton on 13 Sep 2008

I don't work well in the heat. Especially when it's a heat without airflow. Sure, the office was only 82 degrees F, but without a fan or anything, I was just flat-out miserable and all I wanted to do was curl up somewhere and gaze at a wall or something. Heck, I seriously considered going out to the truck, and getting my gym bag to trade my work clothes for my work-out clothes, all in an effort to keep my brain from overheating.

Or in Bender parlance, I was just shy of closing my eyes, and when I re-opened them stating "Insert Girder."

Now, why is this important? And more importantly, why am I bringing my issues with heat up on my programming blog? Well, outside of the fact that the heat thing happened at work, this heat thing slapped me down while I was trying to uncover the secrets of using four different algorithms.

Sure, they all calculate the same types of data, but they do it in highly divergent ways.

What algorithms you ask? Well, they're financial ones, specifically those related to calculating Investment Performance. The four in question are: Internal Rate of Return, Daily Valuation, Modified Dietz and Modified BAI (sometimes called Modified IRR).

Now, these things are evil. They're geometric algorithms, as they deal with compounding data sets over time, with sums and subtractions in addition to, and conjunction with the compounding data.

Don't believe me? Then look at this:
Or how about this:

Yes, those are two of the formulas I'm turning into a calculator. And doing it while my brain is frying like one of those proverbial eggs.

Hmm, now if only I had some bacon.

Alas, thanks to our good friends at Wikipedia, at least some of the Modified Dietz things have been dealt with. Their article provides this VB source code for calculating Modified Dietz:
'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)
Thanks Wiki!!

Tweet me @kidananubix if you like this post.