referencing cols in functions - NPV
 

I have an NPV calculation where I want to be able to change the number
of years for the NPV calculation by inputting different numbers into a
single cell (instead of having to change the range in the NPV formula
each time).
I have the years 1-8 in range C1:J1 and the yearly returns in C2:J2. In
A2 I have =NPV(8%,C2:J2). I want to use a cell (say A5) reference
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?
DesM


--
DesM
------------------------------------------------------------------------
DesM's Profile: http://www.excelforum.com/member.php...o&userid=24121
View this thread: http://www.excelforum.com/showthread...hreadid=377432

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


"DesM" wrote in message
...

I have an NPV calculation where I want to be able to change the number
of years for the NPV calculation by inputting different numbers into a
single cell (instead of having to change the range in the NPV formula
each time).
I have the years 1-8 in range C1:J1 and the yearly returns in C2:J2. In
A2 I have =NPV(8%,C2:J2). I want to use a cell (say A5) reference
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?
DesM


--
DesM
------------------------------------------------------------------------
DesM's Profile:
http://www.excelforum.com/member.php...o&userid=24121
View this thread: http://www.excelforum.com/showthread...hreadid=377432

Hi :)

Please, try this:


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

Whe
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


--
cscorp
------------------------------------------------------------------------
cscorp's Profile: http://www.excelforum.com/member.php...o&userid=24015
View this thread: http://www.excelforum.com/showthread...hreadid=377432

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


--
DesM
------------------------------------------------------------------------
DesM's Profile: http://www.excelforum.com/member.php...o&userid=24121
View this thread: http://www.excelforum.com/showthread...hreadid=377432