Array issue

[James]

In an attempt to calculate a "simple" Internal Rate of Return based off a few
textboxes on a form, I can get the =IRR() function to work properly if and
only if each value in the array is sizably different. If they aren't
different, Access errors out with the message, "Run-time error '5'; Invalid
procedure call or argument" and highlights the last line of the code. I've
been able to replicate this error time and time again if the values 1 through
3 are the same, but if they are different, like I mentioned, it runs fine.

Here is the code:

----------------------------
Dim Fmt, RetRate, Msg, Count, IRRInital, IRRCashFlow, IRRCashFlowFinal
Static Values(4800) As Double ' Set up array.
IRRInitial = -19044.51
IRRCashFlow = 572.64
IRRCashFlowFinal = 100
'Guess = 0.1 ' Guess starts at 10 percent.
Fmt = "#0.00" ' Define percentage format.
'Initial value will always be negative
Values(0) = IRRInitial
' Positive cash flows reflecting income for four successive years.
Values(1) = IRRCashFlow
Values(2) = IRRCashFlow + 5000 'Extra normally not added
Values(3) = IRRCashFlow + 18000 'Extra normally not added
Values(4) = IRRCashFlowFinal

' Calculate internal rate--guess amount not required or needed.
Me.txtGENIRR = IRR(Values()) * 12
----------------------------

Two things should be noted; first, in the real world you will have the
initial number be negative, always, followed by a string of generally equal
and predictable cashflows (loan payments for example). The final cashflow
(or loan payment) may or may not be equal to the previous amounts. Second,
in Excel using the built in IRR function, you'd select a range of cells with
the same amounts as above (-19044.51, 572.64, 572.64, 572.64, 100.00) and it
works just fine.

In addition, I would not have specific amounts placed in the code.
Normally, they would be tied back to textboxes on the form to pull from, then
calculate, and output to a textbox on the same form. I put them here for an
example

As some added assistance, you can find more information about the IRR
function from this Access 2007 page on Microsoft's site--the place where I
obtained part of my
code...http://office.microsoft.com/en-us/access/HA012288611033.aspx.

If anyone can see what I'm doing wrong here if it is with the declaration of
the array or tell if it is a bug I'd greatly appreciate it!

Thanks!!

 

[Douglas J. Steele]

Since you've only got 5 values in the array, I'd think you should declare it
to be that size:

Static Values(5) As Double

 

[James]

Doug,

I allocated 4800 as that is the maximum number in the array that the
customer could ever need. Optimally, I'd like to stick a variable in there
and set it equal to the number of payments, however, it kept erroring out
when I did that. One would think that it could be done.

jb

 

[Klatuu]

Doug is 88 44/100% correct.
It actually depends on whether you are using an Option Base statement in
your code module.
If you do not have the Option Base statement or it is set at its default of
0, then it should be dimmed as 4. The elements would then be 0, 1, 2, 3, 4

If the Option Base is set at 1, dim it at 5 and the elements will be 1, 2,
3, 4, 5.

 

[Douglas J. Steele]

Dim lngSize As Long
Static Value() As Double

lngSize = 10
ReDim Value(lngSize)

 

[James]

Doug and Dave,

Thanks for the info on the variable for array size. I could have sworn that
I had done what you had stated but it worked this time. Any ideas, however,
on the rest of the array issue as to why the values in the array do work as
advertised?

jb

 

[James]

Doug and Dave,

I think I've got it figured out, however, I need one little piece of help
that I can't seem to find. First, the code I gave you as shown above will
cause the IRR function to fail because the cashflows have to at least zero
out the initial amount. Obviously in four cashflows of $572 isn't going to
cover $19000. Once that is covered then IRR will work fine.

The part I need assistance with is to assign the value of $572.31 (or
whatever the calculation ends up being from the form) to the array a number
of times through a loop. For instance it Me.Term = 42 payments then...

Values(0) = -19044.10
Values(1 - 41) = each need to equal $572.31
Values(42) = 100

so I would think that the code would look something like this

Dim Guess, Fmt, RetRate, Msg, LoopCount, IRRCashFlow, IRRCashFlowFinal
Dim IngSize As Long
Dim MidValues As Long
Static Values() As Double ' Set up array.
IngSize = Me.Term
MidValues = Me.Term - 1
ReDim Values(IngSize)
IRRCashFlow = Me.txtCIFinalBaseMonthlyPayment
IRRCashFlowFinal = Me.Residual - Me.SecurityDeposit
Guess = 0.1 ' Guess starts at 10 percent.
Fmt = "#0.00" ' Define percentage format.
Values(0) = (Me.CapitalizedCost * (-1)) + Me.AdminFee +
Me.txtCIFinalBaseMonthlyPayment _
+ Me.SecurityDeposit + Me.txtCIProRataTotal
' Positive cash flows reflecting income for x number of years
For LoopCount = 1 To MidValues
Values(MidValues) = IRRCashFlow
Next LoopCount
Values(IngSize) = IRRCashFlowFinal
' Calculate internal rate.
Me.txtGENIRR = IRR(Values(), Guess) * 12


Thank you very much for your help! It has saved a bottle of ibuprofen!

 

[Douglas J. Steele]

Other than your first line, that code looks fine. My objection to the first
line is that you're not defining a data type for any of the variables. I
would think it should be something like

Dim Guess As Double, IRRCashFlow As Double, IRRCashFlowFinal As Double
Dim LoopCount As Long
Dim Fmt As String

I don't see you using RetRate or Msg anywhere, so I'm not sure what they
should be. (Double and String respectively?)