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 2008Thanks Wiki!!
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
i = 0
For Each Cell In rDays
Days(i) = Cell.Value: i = i + 1
SumCash = Application.WorksheetFunction.Sum(rCash)
TempSum = 0
For i = 0 To (rCash.Cells.Count - 1)
TempSum = TempSum + (((iPeriod - Days(i)) / iPeriod) * Cash(i))
MDIETZ = (dEndValue - dStartValue - SumCash) / (dStartValue + TempSum)