Home 
Search 
Today's Posts 
#1




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 NPVfunction look like? I am thinking of using OFFSET or some kind of Arrayfunction. 
#2




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 NPVfunction look like? I am thinking of using OFFSET or some kind of Arrayfunction. 
#3




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




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 NPVfunction look like? I am thinking of using OFFSET or some kind of Arrayfunction. 
#5




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




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 <ctrlshift while hitting <enter ) will generate NPV based on the values from the first nonzero 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 
#7




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 <ctrlshift while hitting <enter ) will generate NPV based on the values from the first nonzero 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)? 
#8




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 <ctrlshift while hitting <enter ) will generate NPV based on the values from the first nonzero 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)? 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/(rng0),rng) returns the last value in rng that contains a value greater than 0. rng=LOOKUP(2,1/(rng0),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/(rng0),rng))*ROW(rng) an array of either 0's, or the row numbers that contain that last positive value. MAX((rng=LOOKUP(2,1/(rng0),rng))*ROW(rng)) returns the highest numbered row value that contains the last positive number. ron 
#9




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 <ctrlshift while hitting <enter ) will generate NPV based on the values from the first nonzero 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 
#10




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 <ctrlshift while hitting <enter ) will generate NPV based on the values from the first nonzero 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 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/(rng0);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. 
Reply 

Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Why doesn't this array formula calculate properly using VBA?  Excel Worksheet Functions  
Calculate the result of an array in vba  Excel Programming  
How can I calculate the minimum value of a VBA array?  Excel Programming  
Help with excel array  select, add and calculate result  Excel Worksheet Functions  
Using known arrays to calculate an array of new values  Excel Programming 