To the poster XL -- -- a numerical analysis question for UDF

[Jay Petrulis]

Dear XL,

From this thread (among others)...

http://mrexcel.com/board/viewtopic.p...8550&forum=2&5

Since you feel it is your duty to berate others on this board whom you obviously feel are beneath you, I have a question that has been quite challenging for me, but will undoubtedly only require a moment of your time.

Pre-amble:
The investment performance measurement technique recommended by AIMR is called the BAI Iterative method. The rate, r, is found by the following algorithm

Vo(1+r) + Sum(Cd*(1+r)^((n-d)/n) = Ve

Vo = opening value
Ve = ending value
n = number of days in the measurement period
d = day cashflow occurred
Cd = Cashflow amount on day d

This cannot be solved analytically (as you already know, of course).

Problem: The following UDF works for almost all situations -- that is, I can get r to converge to a value nearly every time. My request *to you* is to correct the flaws when this starts to diverge. I am unable at present to "anneal" a sequence which begins to diverge.

(By anneal, I mean that once the algorithm starts to diverge, I need to "kick it" back into a converging series.)

Helpful hints:
1. I cannot get this to converge if the "real" r is -.9 or below. Above that, it works as I want, so concentrate on the problem range.

2. I do not handle the data correctly whenever
a) The values shift over the zero (+ beg / - end market values or vice versa)
b) The cashflows cause an interim value to cross the zero.

I would wish you good luck on this task, but I am sure you will not need it.

Function BAI_ITERATION(BeginMktValue As Double, EndMktValue As Double, _
BeginDate As Date, EndDate As Date, Optional Flows, Optional FlowDates, _
Optional FlowWeighting, Optional Annualized As Boolean, _
Optional Period As Byte) As Double

Dim PeriodDays As Double, FlowFactor As Double
Dim FlowCalc(), x As Long, z As Long, FlowCount As Long
Dim Sum_of_Flows As Double, Sum_of_Wtd_Flows As Double
Dim Modified_Dietz As Double, Dietz_Denominator As Double
Dim Calculated_EMV As Double, Sum_of_ROR_Flows As Double
Dim BAI_Rate As Double
Dim lngCount As Long
Dim CalcFactor As Double
Dim FlowCell As Range, FlowDateCount As Long
Dim FlowDateLoad(), FlowLoad()
Dim FlowMatchCount As Long
Dim Calculated_EMV_Base As Double

If BeginDate >= EndDate Then
    BAI_ITERATION = CVErr(xlErrValue)
    Exit Function
End If

If IsMissing(Period) Then Period = 0
If Period <> 0 Then Period = 1

If IsMissing(FlowWeighting) Then FlowWeighting = 0
Select Case FlowWeighting
    Case Is = 1
        FlowFactor = 1 'Beginning of Day
    Case Is = 2
        FlowFactor = 0.5 'Mid day
    Case Else
        FlowFactor = 0 'End of day (default)
End Select
PeriodDays = Period + EndDate - BeginDate
' change to 1 + EndDate - BeginDate for begin-of-current-period
' to end-of-current-period calculations

If IsMissing(Flows) Then
    BAI_ITERATION = (EndMktValue - BeginMktValue) / BeginMktValue
    Exit Function
Else
    ' count the number of flows
    If TypeName(Flows) = "Range" Then
        FlowCount = Flows.Count
    Else   'If TypeName(Flows) = "Variant()" Then
        For z = LBound(Flows) To UBound(Flows)
            FlowCount = FlowCount + 1
        Next z
    End If
    ReDim FlowLoad(1 To FlowCount)
    For z = 1 To FlowCount
        FlowLoad(z) = Flows(z)
    Next z
        
    ' make sure the flow dates are legitimate
    If TypeName(FlowDates) = "Range" Then
        FlowDateCount = FlowDates.Count
    Else
        For z = LBound(FlowDates) To UBound(FlowDates)
            FlowDateCount = FlowDateCount + 1
        Next z
    End If
    ReDim FlowDateLoad(1 To FlowDateCount)
        For z = 1 To FlowDateCount
            If Len(FlowDates(z)) Then
                FlowDateLoad(z) = FlowDates(z)
            Else
                FlowDateLoad(z) = BeginDate
                FlowLoad(z) = 0
            End If
        Next z
    
    FlowMatchCount = WorksheetFunction.Min(FlowDateCount, FlowCount)
    ReDim FlowCalc(1 To FlowMatchCount, 1 To 5)
    
    For x = 1 To FlowMatchCount
        If IsMissing(FlowLoad(x)) Then FlowLoad(x) = 0
        FlowCalc(x, 1) = FlowLoad(x) ' Flow Amount
        Sum_of_Flows = Sum_of_Flows + FlowLoad(x)
        
        FlowCalc(x, 2) = FlowDateLoad(x) 'Flow Date
        
        FlowCalc(x, 3) = WorksheetFunction.Min(PeriodDays, _
        WorksheetFunction.Max(0, Period + FlowDateLoad(x) - BeginDate)) 'Day of Flow
        ' change to 1 + FlowDates(x) - BeginDate for begin-of-period
        ' to end-of-period calculations
        
        FlowCalc(x, 4) = FlowCalc(x, 1) * _
        WorksheetFunction.Min(PeriodDays, _
        (PeriodDays + FlowFactor - FlowCalc(x, 3))) / PeriodDays ' Weighted flow
        Sum_of_Wtd_Flows = Sum_of_Wtd_Flows + FlowCalc(x, 4)
        
        FlowCalc(x, 5) = (PeriodDays + FlowFactor - FlowCalc(x, 3)) / PeriodDays 'ROR exponent
    Next x
End If

Dietz_Denominator = BeginMktValue + Sum_of_Wtd_Flows

If Dietz_Denominator = 0 Then Dietz_Denominator = 0.000001
Modified_Dietz = (EndMktValue - BeginMktValue - Sum_of_Flows) / Dietz_Denominator
BAI_Rate = Modified_Dietz
    
    For x = 1 To FlowMatchCount
        Sum_of_ROR_Flows = Sum_of_ROR_Flows + _
        (FlowCalc(x, 1) * ((1 + BAI_Rate) ^ FlowCalc(x, 5)))
    Next x
    Calculated_EMV = BeginMktValue * (1 + BAI_Rate) + Sum_of_ROR_Flows
    Calculated_EMV_Base = Calculated_EMV
    On Error Resume Next
    Do Until Abs(Calculated_EMV - EndMktValue) <= 0.00000000001
        lngCount = lngCount + 1
        If lngCount > 100000 Then
            If Abs(Calculated_EMV - EndMktValue) < Abs(Calculated_EMV_Base - EndMktValue) Then
                BAI_ITERATION = BAI_Rate
            Else
                BAI_ITERATION = Modified_Dietz
            End If
            Exit Function
        End If
    CalcFactor = WorksheetFunction.Min(0.05, Abs((Calculated_EMV - EndMktValue)) / _
    WorksheetFunction.Max(Abs(Calculated_EMV), Abs(EndMktValue)))
    If Calculated_EMV < EndMktValue Then
        BAI_Rate = BAI_Rate + Abs(BAI_Rate * CalcFactor)
    Else
        BAI_Rate = BAI_Rate - (BAI_Rate * CalcFactor * Sgn(BAI_Rate))
    End If
            Sum_of_ROR_Flows = 0
            For x = 1 To FlowMatchCount
                Sum_of_ROR_Flows = Sum_of_ROR_Flows + _
                (FlowCalc(x, 1) * ((1 + BAI_Rate) ^ FlowCalc(x, 5)))
            Next x
            Calculated_EMV = BeginMktValue * (1 + BAI_Rate) + Sum_of_ROR_Flows
    Loop
    If Annualized Then
        BAI_ITERATION = (1 + BAI_Rate) ^ (365 / PeriodDays) - 1
    Else
        BAI_ITERATION = BAI_Rate ' default = false
    End If
End Function

For more details, search for the answer on the internet. Search google.com for BAI Iteration and Excel and you will see a few posts by me explaining it in more detail.

Idiots like me haven't had any numerical analysis courses and don't know any programming languages other than VBA for Excel, so I am unable to read, let alone translate, any C or Fortran routine which may help. I am sure the great XL can do it while sleeping.

Thanks for your *PROMPT* resolution to this problem.

Regards,
Jay

P.S. For all others, any help greatly appreciated. My new hero, XL, should come through on this one, but if you need any explanation or details, please let me know.

(Sarcasm throughout fully intended)



[ This Message was edited by: Jay Petrulis on 2002-05-16 16:09 ]

[dimrod]

I think you're being a bit hard on XL.
I'm sure he would be first in line to praise those greater than he.
It's probably just that he can't find anyone.

[ This Message was edited by: dimrod on 2002-05-16 16:44 ]

[James]

Hi Jay,

Kudos to you my friend! It's all about helping but some people just don't get it.

With that said, i will be more than happy to help you with your code problem as soon as i
get my own figuered out. I'm trying to sum
in Cell C1 what i have in A1 and B1.

Regards,
James

[dimrod]

On 2002-05-16 17:01, James wrote:
Hi Jay,

Kudos to you my friend! It's all about helping but some people just don't get it.

With that said, i will be more than happy to help you with your code problem as soon as i
get my own figuered out. I'm trying to sum
in Cell C1 what i have in A1 and B1.

Regards,
James

James

Mail me your workbook.

No promises, but I might be able to help.

Dimrod

[James]

Hi Dimrod

Thanks for offering your assistance, but i would feel better about myself if i could figure this out on my own.

Regards,
James

[dsnbld]

James- Good luck :^)

[Nimrod]

I've read the posting of XL's that you refer to ! And I feel that children throwing tamtrums are best left alone until they learn to play with others. XL... maybe you should take a "time out" until you can come back and apologize to Jack.

[dimrod]

James

Let me know how you get on.

Would be good if you could post your solution when you get there - so that everyone can benefit.

dimrod

[James]

Dimrod,

I always try to help and share all i know, (which could fill half a thimble) BUT if i figure this one out i'll probably keep it to myself.

Regards,
James