I agree that there is something missing from the code that you pasted
in the message... it needs a 'next' tobe paired with the 'for'...

The first problem that I see is that you are testing the variable
RetRate in your 'do until' statement before the variable is
initialized or set to any value. The second problem is that
the 'Guess' you are sending the IRR function is the same each time
you call it. (Guess +.1) This structure guarantees that you will get
the same result during each pass through the loop.

But, looking at your code structure I think that your understanding
of the IRR function may be where the biggest problem lies. The
function returns a normalized floating number not a percent. So, if
your array of cash flows is within a 'normal range' you would expect
to have an internal rate of return under 200%. Your test is exiting
the loop  if the IRR is greater -1000%. I think what you meant to
test on is an IRR of -10%. So, most likely... your routine is
working but your testing limit is way too high. (also... even if
your array would yield a abs(IRR) greater than 1000% the numerical
approximation formula  used by Excel would probably fail and return
the wrong root.)

As an aside... your 'do loop' is trying to exit if it gets an IRR
above a certain value. If your aim here is to make sure that you are
returning an accurate result... this test is not a measure of
accuracy. A test of accuracy would be to put the rate returned by
the IRR function into the NPV function. A return value of zero means
that the IRR is good.

I've looked at your problem in a little more detail... and given your
array of cash flows, I don't believe that it is possible to use the
IRR function to calculate a correct Internal Rate of Return. In
other words... it will always give you an error no matter what your
initial guess is set to. The correct IRR for this data is minus
138.5064%. Even if you use -1.38 as the initial guess the function
throws an error.

Below is some code that I wrote to test my theory. This uses linear
interpolation for successive approximations of the interest rate and
as such it is probably not as effecient as the IRR algorithm built
into Excel. But, unlike the complex calculus that math geeks use to
solve problems of this type, this method is less likely to chase down
the wrong root of a polynomial and it is more forgiving of a first
guess that is out of the ballpark.:-)

If this is a problem that some professor gave you to solve... I guess
it proves he has a sense of humor.


In the following code, step through it and watch the value of 'i'
and 'e2' change. As 'e2' approaches zero... 'i' gets more and more
accurate.

Sub Macro2()

Dim Guess As Double
Dim retrate As Double
Dim Values(5) As Double

Dim x1 As Double
Dim x2 As Double
Dim e1 As Double
Dim e2 As Double
Dim i As Double
Dim e3 As Double
Guess = -10
Values(0) = -70000#
Values(1) = 12000#
Values(2) = 15000#
Values(3) = 0#
Values(4) = 0#
' retrate = IRR(Values(), -1.38)
i = 0.1
x1 = i
e1 = Values(0) / (1 + i) ^ 0 + _
Values(1) / (1 + i) ^ 1 + _
Values(2) / (1 + i) ^ 2 + _
Values(3) / (1 + i) ^ 3 + _
Values(4) / (1 + i) ^ 4
i = i * 1.1
x2 = i
e2 = Values(0) / (1 + i) ^ 0 + _
Values(1) / (1 + i) ^ 1 + _
Values(2) / (1 + i) ^ 2 + _
Values(3) / (1 + i) ^ 3 + _
Values(4) / (1 + i) ^ 4
Do While Abs(e2) > 0.0000001
i = (e1 * x2 - e2 * x1) / (e1 - e2)
e1 = e2
x1 = x2
x2 = i
e2 = Values(0) / (1 + i) ^ 0 + _
Values(1) / (1 + i) ^ 1 + _
Values(2) / (1 + i) ^ 2 + _
Values(3) / (1 + i) ^ 3 + _
Values(4) / (1 + i) ^ 4
Loop
' on exit, 'i' will contain the correct value of interest rate
' as a final test... use the NPV function to double check the vlaue
of 'i'
e3 = NPV(i, Values())
End Sub

I think that you haven't done a copy/paste from your code to your message.
In particular, then "Next" is missing from your For loop. This makes it
impossible to see how your code is supposed to work.

The problem is how you refer to your Values Array within your loop
statement. You need to refer to each bucket you created. Add a
counter...

Dim i as Integer



i = 0



Do Until RetRate >= -10
RetRate = IRR(Values(i), Guess + 0.1) 'This refers to each bucket as
Values(0), Values(1), etc.

i = i + 1
Loop