A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Programming
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Calculate NPV - Array must be flexible



 
 
Thread Tools Display Modes
  #1  
Old January 18th 09, 03:18 PM posted to microsoft.public.excel.programming
Helge's
external usenet poster
 
Posts: 16
Default Calculate NPV - Array must be flexible

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.
Ads
  #2  
Old January 18th 09, 09:53 PM posted to microsoft.public.excel.programming
RB Smissaert
external usenet poster
 
Posts: 2,452
Default Calculate NPV - Array must be flexible

What is NPV and how is it calculated?

RBS


"Helge's" > wrote in message
...
>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.


  #3  
Old January 18th 09, 10:16 PM posted to microsoft.public.excel.programming
Helge's
external usenet poster
 
Posts: 16
Default Calculate NPV - Array must be flexible

On 18 Jan, 22:53, "RB Smissaert" >
wrote:
> What is NPV and how is it calculated?
>
> RBS
>
> "Helge's" > wrote in message
>
> ...
>
>
>
> >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.– Skjul sitert tekst –

>
> – 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)
  #4  
Old January 19th 09, 05:13 AM posted to microsoft.public.excel.programming
Fred Smith[_4_]
external usenet poster
 
Posts: 2,389
Default Calculate NPV - Array must be flexible

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" > wrote in message
...
>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.


  #5  
Old January 19th 09, 09:02 AM posted to microsoft.public.excel.programming
Helge's
external usenet poster
 
Posts: 16
Default Calculate NPV - Array must be flexible

On 19 Jan, 06:13, "Fred Smith" > wrote:
> 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" > wrote in message
>
> ...
>
>
>
> >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.– Skjul sitert tekst –

>
> – 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).
  #6  
Old January 19th 09, 12:12 PM posted to microsoft.public.excel.programming
Ron Rosenfeld
external usenet poster
 
Posts: 5,651
Default Calculate NPV - Array must be flexible

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/(rng>0),rng))*ROW(rng))-
MATCH(TRUE,rng<>0,0)+1,1))
--ron
  #7  
Old January 19th 09, 07:58 PM posted to microsoft.public.excel.programming
Helge's
external usenet poster
 
Posts: 16
Default Calculate NPV - Array must be flexible

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/(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)?
  #8  
Old January 19th 09, 09:28 PM posted to microsoft.public.excel.programming
Ron Rosenfeld
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/(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)?


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
  #9  
Old January 20th 09, 02:34 AM posted to microsoft.public.excel.programming
Ron Rosenfeld
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/(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)?


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
  #10  
Old January 20th 09, 09:38 AM posted to microsoft.public.excel.programming
Helge's
external usenet poster
 
Posts: 16
Default Calculate NPV - Array must be flexible

On 20 Jan, 03:34, Ron Rosenfeld > wrote:
> 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/(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)?

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




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Why doesn't this array formula calculate properly using VBA? downwitch Excel Worksheet Functions 0 February 22nd 10 02:28 AM
Calculate the result of an array in vba Sam Excel Programming 1 August 11th 08 11:51 PM
How can I calculate the minimum value of a VBA array? DJMF Excel Programming 4 December 3rd 07 12:25 PM
Help with excel array - select, add and calculate result [email protected] Excel Worksheet Functions 0 July 12th 07 06:51 PM
Using known arrays to calculate an array of new values Brett[_9_] Excel Programming 8 September 19th 06 06:32 AM


All times are GMT +1. The time now is 06:22 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright ©2004-2014 ExcelBanter.
The comments are property of their posters.