# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  NVL type function

## Jonathan

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 "N/A".

Right now I have the formula ...

=IF(ISERR(IRR(OFFSET(G10,0,BeginOffset,1,NumOfPeriods),0.01)*12),"N/A",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,

Jonathan

----------


## Domenic

Will this do?

First, define the following...

Insert > Name > Define

Name:  BigNum

Refers to:

=9.99999999999999E+307

Click Ok

Secondly, custom format the cell as follows...

Format > Cells > Number > Custom > Type:

[=0]"N/A"

Then, try the following formula...

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

Hope this helps!

In article <1147361492.826447.218430@g10g2000cwb.googlegroups.com>,
"Jonathan" <jtushman@gmail.com> wrote:

> 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 "N/A".
>
> Right now I have the formula ...
>
> =IF(ISERR(IRR(OFFSET(G10,0,BeginOffset,1,NumOfPeriods),0.01)*12),"N/A",IRR(OFF
> SET(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,
>
> Jonathan

----------


## Jonathan

Interesting approach, thanks.

-- jt

----------


## Harlan Grove

Domenic wrote...
....
>=LOOKUP(BigNum,CHOOSE({1,2},0,
>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,NumOfPeriods),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.

----------


## Tushar Mehta

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 <1147361492.826447.218430@g10g2000cwb.googlegroups.com>,
jtushman@gmail.com says...
> 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 "N/A".
>
> Right now I have the formula ...
>
> =IF(ISERR(IRR(OFFSET(G10,0,BeginOffset,1,NumOfPeriods),0.01)*12),"N/A",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,
>
> Jonathan
>
>

----------


## Jonathan

Thanks guys!

----------

