Hi,

I am using the following equation as my Profitability Index:

{=IF(ISERROR(-NPV(Rate, IF(SIGN(CF)=1, CF, 0))/NPV(Rate, IF(SIGN(CF)=-1, CF,
0))),
"Not Applic.",
-NPV(Rate, IF(SIGN(CF)=1, CF, 0))/NPV(Rate, IF(SIGN(CF)=-1, CF, 0)))}

Rate=discount rate
CF=cash flow

I am simply taking NPV of positive values divided by NPV of negative values.
And if there is an error, a "Not Applic" note is displayed.

This is for a large project that spans more than one year during its
construction. So NPV of negative values is necessary.

Although the current equation works, is there a more elegant way of writing
this equation? Perhaps something that doesn't use formula arrays.

Thank you.

Best regards,
Kevin

Try ...

={-NPV(Rate,max(SIGN(CF),0))/NPV(Rate,(Min(SIGN(CF),0)}

I think using max and min is much more faster then the if statement.

Samo ... good luck


--
Samo
------------------------------------------------------------------------
Samo's Profile: http://www.excelforum.com/member.php...o&userid=34413
View this thread: http://www.excelforum.com/showthread...hreadid=541868

Try ...

={-NPV(Rate,max(SIGN(CF),0))/NPV(Rate,(Min(SIGN(CF),0)}

I think using max and min is much more faster then the if statement.

Samo ... good luck


Samo,

Thank you for your help.

Best regards,
Kevin