IRR Calculation Question

[Les]

Hi all,
I am doing the calculation below and it works great, with the exception that
if the value i get out is too high it goes to "#NUM!". is there a way to
prevent this and rather put a text in "Value to High" ??

=IF(premises!F12="no",IRR('NPV calculation'!E121:L121),IRR(F122:L122))

Any help would be greatly appreciated.

 

[Fred Smith]

Two options:

1. Use another if statement to check for the error, as in:

=IF(premises!F12="no",if(iserror(irr(...),"Value too high",irr(...)) ...

2. Add a guess as the second parameter in IRR to help it get to the right
result.

Regards,
Fred.

 

[Les]

Hi Fred, i just cannot get this to work ??

I keep getting error in Formula

Can you help ??

 

[joeu2004]

Hi Fred, i just cannot get this to work ??
I keep getting error in Formula

First, I wonder if you understood Fred's suggestion and implemented it
correctly. To be more specific, you would have to write:

=IF(premises!F12="no",
if(iserror(IRR('NPV calculation'!E121:L121)), "Cannot compute IRR",
IRR('NPV calculation'!E121:L121)),
if(iserror(IRR(F122:L122)), "Cannot compute IRR", IRR(F122:L122)))

Whatamess, huh?!

Second, what makes you think IRR values because it is "too high"?

Is it possible that you mistakenly did not use opposite signs for inflows
and outflows in one of the ranges?

If both ranges are set up properly, the #NUM error only means that IRR could
not find a solution within 20(!) iterations starting with a guess of 10%.

That is why the "guess" argument exists. Unfortunately, I have not found a
good way to estimate a good "guess" in general.



----- remainder of posting -----