Trap an IRR iteration error

[Sharon]

Hi all,

I would like to trap the #NUM error that happens only occasionally with the IRR function. I decided
against added more to the formula since I couldn't figure out a simple way to do it (without
enclosing each IRR function with an ISERROR function)

The function I wrote is below but now it returns the #VALUE error. Is there a simple way to do this
or can my function be fixed?

***************************
the formula:

=IF(Period=1,IRR($O$46:$O$47,-0.9),
IF(Period=3,IRR($O$46:$O$49,-0.9),IF(Period=4,IRR($O$46:$O$50,-0.9),
IF(Period=5,IRR($O$46:$O$51,-0.9),"N/A"))))

the function:

Public Function InternalRR(Netflows As Range, drate As Double) As Variant
' return the result from Excel's IRR function trapping the #NUM error


Application.Volatile True

If WorksheetFunction.IsError(WorksheetFunction.IRR(Netflows, drate)) Then
InternalRR = "N/A"
Else
InternalRR = WorksheetFunction.IRR(Netflows, drate)
End If

End Function

*****************

Thanks,

Sharon

 

[Niek Otten]

Hi Sharon,

Maybe a typo, but the way you state it you seem to mix up IRR and
InternalRR; what happens if you use InternalRR from your worksheetformulas?

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Sharon]

Hi Sharon,

Maybe a typo, but the way you state it you seem to mix up IRR and
InternalRR; what happens if you use InternalRR from your worksheetformulas?

Hi Niek,

The formula I showed on my first post was the original formula that would produce the #NUM error
when it couldn't produce a valid result. The function was my attempt to trap the error and display
a "N/A" instead.

If I used my InternalRR function the formula would be:

=IF(Period=1,InternalRR($O$46:$O$47,-0.9),
IF(Period=3,InternalRR($O$46:$O$49,-0.9),IF(Period=4,InternalRR($O$46:$O$50,-0.9),
IF(Period=5,InternalRR($O$46:$O$51,-0.9),"N/A"))))

When I use my function I get a #VALUE error???

Sharon

 

[Harlan Grove]

I would like to trap the #NUM error that happens only occasionally with the
IRR function. I decided against added more to the formula since I couldn't
figure out a simple way to do it (without enclosing each IRR function with an
ISERROR function)

Using a udf to avoid an error trap in a formula isn't particularly efficient,
especially when you call the IRR function twice in the udf. Usually starting
with 2nd argument -0.9 is sufficient to avoid the #NUM! error. An alternative
would be =IRR(flows,SUMIF(flows,">0")/-SUMIF(flows,"<0")-1).

Anyway, if you want a udf to trap errors, generic is better.


Function trap(v As Variant, Optional r As String) As Variant
If IsError(v) Then trap = r Else trap = v
End Function


and use it as =trap(IRR(whatever,whateverelse),"N/A"). As for your specific
formula, consider

=IF(OR(Period={1,3,4,5}),IRR(OFFSET($O$46,0,0,1+Period,1),-0.9),"N/A")

or

=IF(AND(OR(Period={1,3,4,5}),ISNUMBER(IRR(OFFSET($O$46,0,0,1+Period,1),-0.9))),
IRR(OFFSET($O$46,0,0,1+Period,1),-0.9),"N/A")

 

[Sharon]

...



Using a udf to avoid an error trap in a formula isn't particularly efficient,
especially when you call the IRR function twice in the udf. Usually starting
with 2nd argument -0.9 is sufficient to avoid the #NUM! error. An alternative
would be =IRR(flows,SUMIF(flows,">0")/-SUMIF(flows,"<0")-1).

Anyway, if you want a udf to trap errors, generic is better.


Function trap(v As Variant, Optional r As String) As Variant
If IsError(v) Then trap = r Else trap = v
End Function


and use it as =trap(IRR(whatever,whateverelse),"N/A"). As for your specific
formula, consider

=IF(OR(Period={1,3,4,5}),IRR(OFFSET($O$46,0,0,1+Period,1),-0.9),"N/A")

or

=IF(AND(OR(Period={1,3,4,5}),ISNUMBER(IRR(OFFSET($O$46,0,0,1+Period,1),-0.9))),
IRR(OFFSET($O$46,0,0,1+Period,1),-0.9),"N/A")

Thank you for your insight, Harlan.

The formula:
=IF(AND(OR(Period={1,3,4,5}),ISNUMBER(IRR(OFFSET($O$46,0,0,1+Period,1),-0.9))),
IRR(OFFSET($O$46,0,0,1+Period,1),-0.9),"N/A")

is just what I needed to both check for errors and provide a concise formula without all of the IF
functions I was using...I have adapted it to several other formulas....thank you, again!

Sharon