View Single Post
  #9   Report Post  
Posted to microsoft.public.excel.programming
Ron Rosenfeld Ron Rosenfeld is offline
external usenet poster
 
Posts: 5,651
Default Calculate NPV - Array must be flexible

On Mon, 19 Jan 2009 11:58:49 -0800 (PST), "Helge's"
wrote:

On 19 Jan, 13:12, Ron Rosenfeld wrote:
On Mon, 19 Jan 2009 01:02:02 -0800 (PST), "Helge's"
wrote:

It shall start the first place with a number (different from 0). Next
dataset might have a number in the 5th cell. The last cell (in this
case is 30) is the last cell with a postiv number. In this case it
continue with only negative values (-20).


The following, entered as an **array** formula (confirmed by holding down
<ctrl-shift while hitting <enter ) will generate NPV based on the values from
the first non-zero number to the last positive value.

Rate is either a % or a cell reference containing the interest rate you want to
use.

=NPV(Rate,OFFSET(rng,MATCH(TRUE,rng<0,0)-1,0,
MAX((rng=LOOKUP(2,1/(rng0),rng))*ROW(rng))-
MATCH(TRUE,rng<0,0)+1,1))
--ron


The formula looks very good, but it seems to not cut on the last
positive value. I do not understand the rng=LOOKUP. What are you
looking up. Why are you using 1/(rng0)?


OK, I see a problem with the formula when rng does not start in Row 1. The
following modification should take care of that -- also an array formula:


=NPV(5%,OFFSET(rng,MATCH(TRUE,rng<0,0)-1,0,
MAX((rng=LOOKUP(2,1/(rng0),rng))*ROW(rng))-
ROW(rng)-MATCH(TRUE,rng<0,0)+2,1))

--ron