NVL type function

  • Thread starter Thread starter Jonathan
  • Start date Start date
J

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
 
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!
 
Domenic wrote...
....
=LOOKUP(BigNum,CHOOSE({1,2},0,
IRR(OFFSET(G10,0,BeginOffset,1,NumOfPeriods),0.01)*12))
Click to expand...
....

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.
 
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
 
Back
Top