MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Loop in formula

  Asked By: Bamah    Date: Nov 03    Category: MS Office    Views: 2309

The IRR formula sometimes need Guess value in order to get IRR value.
The Guess value is a closer value to IRR value.

My macro can't do loop. This macro should find the Guess value from -10
to 10 with interval 0.1 and stops when the IRR value is available.
If cell B2, B2 B3 B4 and B5 contains -70000, 12000, 15000, 0 and 0
respectively. The result of IRR value should be -44.35% in cell B8. In
order to get -44.35%, I should put the Guess value example is -10%.

Below is my macro

Sub Macro1()
Dim Guess, RetRate
Static Values(5) As Double
Guess = -10
For Gues = -10 To 10
Values(0) = Range("B2").Value
Values(1) = Range("B3").Value:
Values(2) = Range("B4").Value
Values(3) = Range("B5").Value:
Values(4) = Range("B6").Value

Do Until RetRate >= -10
RetRate = IRR(Values(), Guess + 0.1)

Range("B8").Value = RetRate

End Sub

Can any body help ?



4 Answers Found

Answer #1    Answered By: Freddie Evans     Answered On: Nov 03

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.

Answer #2    Answered By: Jonah Brown     Answered On: Nov 03

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

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
' 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

Answer #3    Answered By: Boyce Fischer     Answered On: Nov 03

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.

Answer #4    Answered By: Stacy Cunningham     Answered On: Nov 03

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

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

Didn't find what you were looking for? Find more on Loop in formula Or get search suggestion and latest updates.