Calculate NPV - Array must be flexible

[Helge's]

I have a database with different dataset. That is the input to a
worksheet that calculate NPV.

The array that I want to calculate NPV could look like this:

0
0
-200
50
50
50
-10
40
30
-20
-20
-20

The array for this dataset must be from -200 to 30. How could the
arrayargument in the NPV-function look like? I am thinking of using
OFFSET or some kind of Arrayfunction.

 

[RB Smissaert]

What is NPV and how is it calculated?

RBS

 

[Helge's]

What is NPV and how is it calculated?

RBS







– Vis sitert tekst –

NPV calculates the net present value of an investment with the
discount rate and several future payments and income: =NPV(rate,array)

 

[Fred Smith]

What determines that you want to start with the -200 value cell and end with
the 30 value cell? If, for example, you have other variables which say start
at the 3rd cell and end at the 10th cell, just use those to formulate the
range to feed to NPV.

Regards,
Fred.

 

[Helge's]

What determines that you want to start with the -200 value cell and end with
the 30 value cell? If, for example, you have other variables which say start
at the 3rd cell and end at the 10th cell, just use those to formulate the
range to feed to NPV.

Regards,
Fred.







– Vis sitert tekst –

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).

 

[Ron Rosenfeld]

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/(rng>0),rng))*ROW(rng))-
MATCH(TRUE,rng<>0,0)+1,1))
--ron

 

[Helge's]

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/(rng>0),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/(rng>0)?

 

[Ron Rosenfeld]

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/(rng>0)?

It worked properly here on the data set you provided.
How did you define rng?
Did you enter this as an array formula (i.e. did Excel place braces {...}
around the formula after you entered it)?

LOOKUP(2,1/(rng>0),rng) returns the last value in rng that contains a value
greater than 0.

rng=LOOKUP(2,1/(rng>0),rng) returns an array of TRUE and FALSE depending on
whether or not a value in rng matches the last positive number. You have to do
this because there is no guarantee that the last positive value will be unique.

(rng=LOOKUP(2,1/(rng>0),rng))*ROW(rng) an array of either 0's, or the row
numbers that contain that last positive value.

MAX((rng=LOOKUP(2,1/(rng>0),rng))*ROW(rng)) returns the highest numbered row
value that contains the last positive number.
--ron

 

[Ron Rosenfeld]

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/(rng>0)?

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/(rng>0),rng))*ROW(rng))-
ROW(rng)-MATCH(TRUE,rng<>0,0)+2,1))

--ron

 

[Helge's]

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/(rng>0),rng))*ROW(rng))-
ROW(rng)-MATCH(TRUE,rng<>0,0)+2,1))

--ron– Skjul sitert tekst –

– Vis sitert tekst –

Thank you very much. I modified the formula to this: =NPV($M$9;OFFSET
(rng;MATCH(TRUE;rng<>0;0)-1;0;MAX((rng=LOOKUP(2;1/(rng>0);rng))*(ROW
(rng)))-(MIN(ROW(rng)+1)))). It work. You know I have to translate the
formula to norwegian. We are also using semicolon instead of comma to
separate the arguments. Is amazing what an arrayformula can do. Thanks
again.

 

[Ron Rosenfeld]

Thank you very much. I modified the formula to this: =NPV($M$9;OFFSET
(rng;MATCH(TRUE;rng<>0;0)-1;0;MAX((rng=LOOKUP(2;1/(rng>0);rng))*(ROW
(rng)))-(MIN(ROW(rng)+1)))). It work. You know I have to translate the
formula to norwegian. We are also using semicolon instead of comma to
separate the arguments. Is amazing what an arrayformula can do. Thanks
again.

Glad you got it working for you.
--ron

 

[Ron Rosenfeld]

Thank you very much. I modified the formula to this: =NPV($M$9;OFFSET
(rng;MATCH(TRUE;rng<>0;0)-1;0;MAX((rng=LOOKUP(2;1/(rng>0);rng))*(ROW
(rng)))-(MIN(ROW(rng)+1)))). It work. You know I have to translate the
formula to norwegian. We are also using semicolon instead of comma to
separate the arguments. Is amazing what an arrayformula can do. Thanks
again.

There is a problem with the change you made with regard to how you calculate
the height parameter.

When you calculate the height parameter for the OFFSET function, your
modification allows the resultant array to extend beyond the original
definition of rng.

You can show this by the following:

Rang refers to $B$1:$B$12
Enter the following:


$B$1: 0
$B$2: 0
$B$3: 0
$B$4: 0
$B$5: 50
$B$6: 50
$B$7: -10
$B$8: 40
$B$9: 30
$B$10: -20
$B$11: -20
$B$12: 20

Given the above, I would expect that you would want to only consider values in
the range B5:B12

However, if, using your modification, you were to insert a value into either
B13 or B14, you will find it included in the calculation.

If you can guarantee that there will never be any entries below rung, your
formula will work; however, it is not particularly robust when you need to rely
on this sort of assumption.
--ron

 

[Ron Rosenfeld]

Rang refers to $B$1:$B$12

Darn spell checkers; that should read:

Rng refers to $B$1:$B$12
--ron

 

[Helge's]

Darn spell checkers; that should read:

Rng refers to $B$1:$B$12
--ron

Thank you for reminding me about this. I will certenly check the
formula and the model many times. I am reading me up about
arrayformulas now. It is a hidden resource in Excel. A bit difficult
to understand.