software

Is there a NVL type function for excel?

What I am trying to accomplish is a fomula that will give an IRR value
if exists, but if IRR produces an #DIV/0!, return quot;N/Aquot;.

Right now I have the formula ...

=IF(ISERR(IRR(OFFSET(G10,0,BeginOffset,1,NumOfPeri ods),0.01)*12),quot;N/Aquot;,IRR(OFFSET(G10,0,BeginOffset,1,NumOfPeriods),0. 01)*12)

Which works fine, but it would be nice if I did not have to copy the
IRR part of the equation twice.

Thanks,

JonathanWill this do?

First, define the following...

Insert gt; Name gt; Define

Name: BigNum

Refers to:

=9.99999999999999E 307

Click Ok

Secondly, custom format the cell as follows...

Format gt; Cells gt; Number gt; Custom gt; Type:

[=0]quot;N/Aquot;

Then, try the following formula...

=LOOKUP(BigNum,CHOOSE({1,2},0,IRR(OFFSET(G10,0,Beg inOffset,1,NumOfPeriods
),0.01)*12))

Hope this helps!

In article .comgt;,
quot;Jonathanquot; gt; wrote:

gt; Is there a NVL type function for excel?
gt;
gt; What I am trying to accomplish is a fomula that will give an IRR value
gt; if exists, but if IRR produces an #DIV/0!, return quot;N/Aquot;.
gt;
gt; Right now I have the formula ...
gt;
gt; =IF(ISERR(IRR(OFFSET(G10,0,BeginOffset,1,NumOfPeri ods),0.01)*12),quot;N/Aquot;,IRR(OFF
gt; SET(G10,0,BeginOffset,1,NumOfPeriods),0.01)*12)
gt;
gt; Which works fine, but it would be nice if I did not have to copy the
gt; IRR part of the equation twice.
gt;
gt; Thanks,
gt;
gt; Jonathan

Interesting approach, thanks.

-- jtDomenic wrote...
....
gt;=LOOKUP(BigNum,CHOOSE({1,2},0,
gt;IRR(OFFSET(G10,0,BeginOffset,1,NumOfPeriods),0.01 )*12))
....

If the #N/A error value would be acceptable, this could be shortened to

=LOOKUP(BigNum,IRR(OFFSET(G10,0,BeginOffset,1,NumO fPeriods),0.01)*12*{0;1})

Note: the effective annual interest rate derived by IRR from monthly
cashflows should be calculated as (1 IRR(..))^12-1 rather than as
IRR(..)*12. Since IRRs can get large, this does matter unless you
really do want the nominal annual interest rate compounded monthly.The *simplest* way to handle problems like this is to use an intermediate
cell to use the intermediate value. In your case that would be the result
of the IRR(). Suppose you put that in cell G12. Then, the 'final' formula
would be if(ISERR(G12),na(),G12).

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article .comgt;,says...
gt; Is there a NVL type function for excel?
gt;
gt; What I am trying to accomplish is a fomula that will give an IRR value
gt; if exists, but if IRR produces an #DIV/0!, return quot;N/Aquot;.
gt;
gt; Right now I have the formula ...
gt;
gt; =IF(ISERR(IRR(OFFSET(G10,0,BeginOffset,1,NumOfPeri ods),0.01)*12),quot;N/Aquot;,IRR(OFFSET(G10,0,BeginOffset,1,NumOfPeriods),0. 01)*12)
gt;
gt; Which works fine, but it would be nice if I did not have to copy the
gt; IRR part of the equation twice.
gt;
gt; Thanks,
gt;
gt; Jonathan
gt;
gt;

Thanks guys!