referencing cols in functions - NPV

[DesM]

I have an NPV calculation where I want to be able to change the numbe
of years for the NPV calculation by inputting different numbers into
single cell (instead of having to change the range in the NPV formul
each time).
I have the years 1-8 in range C1:J1 and the yearly returns in C2:J2. I
A2 I have =NPV(8%,C2:J2). I want to use a cell (say A5) referenc
instead on "J2" in this function.
So, if I enter 8 in A5 the NPV will sum the investment for 8 years
Can somebody help?
Des

 

[N Harkawat]

=NPV(8%,OFFSET(C2,0,0,1,A5))
will provide the NPV @ 8% for the range C2:J2 if A5=8

 

[cscorp]

Hi

Please, try this:


NPV(ir,OFFSET(C2,0,0,1,A5) )

Where:
ir: Is the interest rate, or a reference to the cell that contains the
interet rate.
c2: Initial reference for the initial year of the returns.
A5: Reference to the cell that contains the number of years to
consider.

Hope it helps!

Juan Carlos

 

[DesM]

Fantastic. I have spent hours trying to sort this out and overnight I
get two fantastic replies.
I have more books on Excel and none of them mention that you can do
this with offset.
Thank you both very much.
Des M