Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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)

Loop

Range("B8").Value = RetRate

End Sub

Can any body help ?

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

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

Related Topics:

- to loop or not to loop
- program using while loop and for loop
- clearing cells value without deleting the formula I've made in cell from excel formula , HOW?
- formula help but not vba please
- Array Formula
- Ctrl+Shift+Arrow Fails To Select Cells When Used in Formula Bar
- Three Leveled Formula
- VBA or array formula?
- VBA code to add values in two cells which have formulas
- combining formula
- How to add several conditions for formula "SUMIF"
- VBA Formula prob
- Getting a formula to copy from inside VBA
- Populate Formula(Function) in rows
- searching within formulas
- Formula using range name in VBA code
- R1C1 Formula
- Paste Formula help
- Copy down formula with vba
- Stuck on copying formula from 1 sheet to another
- Named formula/Windows dialog boxes
- sum formula
- Manually creating a row outline with no formula anywhere
- Blank Formula bar
- Disabling Formulas, Not calculation