Textbox - IRR calculation with VBA

[James]

What I'm attempting to do: calculate IRR (internal rate of return) and
display the results in a textbox based on the input/calculations.

I'm not a seasoned coder, just picking this up new over the past couple of
weeks so my thoughts as to what should be right could be completely off.
Here is the code I came up with:

--------------------------------------
Private Sub CalcIRR()
'Declare counters and array (up to 240 payments + initial
negative value)
Dim CounterOne As Integer
Dim CounterTwo As Integer
Static Values(241) As Double

'Assign values to the Value Array
Values(0) = (Me.CapitalizedCost * (-1)) + Me.AdminFee +
Me.txtCIFinalBaseMonthlyPayment _
+ Me.SecurityDeposit + Me.txtCIProRataTotal
For CounterOne = 1 To (Me.Term - 1)
Values(Counter) = Me.txtCIFinalBaseMonthlyPayment
Next
Values(Me.Term) = Me.Residual - Me.SecurityDeposit

'Calculate the Internal Rate of Return
For CounterTwo = 0 To Me.Term
Me.txtGENIRR = IRR(Values()) * 12
Next

End Sub

--------------------------------------

Just for argument sake here are some test values:

Me.CapitalizedCost = 20000.00
Me.AdminFee = 100.00
Me.txtCIFinalBaseMonthlyPayment = 572.64
Me.txtCIProRataTotal = 283.22
Me.Term = 42
Me.Residual = 100.00
Me.SecurityDeposit = 0.00

Additional thoughts: It is known that the first value of the array has to be
negative, the rest have to be positive. Thus what should be Value(0) is the
sum of most of the variables (textboxes on a form) which ends up being
-19044.14. Values(1) through Values(41) are each 572.64, the amount of each
cashflow. The final cashflow, Value(Me.Term) which is the same as Value(42),
equals the residual minus the security deposit which would be 100.00.

In Excel, I can easily calculate this using the formula =IRR(H2:H74,)*12
which yields an answer of 12.66%, which I know is correct, however,
Access/VBA is causing me problems and it errors out.

If anyone could please help I'd be greatly appreciative. Searching here and
on the net has not yielded any solutions so far.

Thank you!

James

 

[June7 via AccessMonster.com]

One error is in the loop to fill array, you have Counter instead of
CounterOne. However, even after I corrected this, code errors on the IRR
function. I used the example from Access help which works with its example
values but not with yours. I tested your corrected code with a value of 650
instead of 572.64 and the code runs but answer no where near your expected.
Very baffling, sorry not more helpful but I am totally stumped.