Can I set a range in a formula that based on a variable in another cell?

[TTB333]

I have a table of annual payments and I want to calculate the net present value of future payments (NPV) as of the “nth” year from now, where “n” is a number that I enter into another cell. I’m trying to calculate a payoff amount at some date in the future.

Let’s say I have 10 years of payments and I want to know what the NPV of all remaining payments will be at the end of year 3 with an 8% discount rate. The formula I’d use is as follows, with “‘{n}” representing the number I’ll enter in another cell:

=NPV(.08,$A${n}:$A$11)

I’d like the beginning of the range - A{n} - to be based off of whatever value I enter at B13 below.

Any ideas on how to do this?

 

[StephenCrump]

Welcome to the Forum!

Try: =NPV(0.08,INDEX(A1:A11,B13):A11)

If B13 is 3, say, this will give you the value of the payments in years 3 to 11 inclusive. NPV assumes these payments are made at the end of the year, i.e. the NPV value will be at the start of year 3.

If you want the value at the end of year 3, you'll need:

=NPV(0.08,INDEX(A1:A11,B13+1):A11)

Or, if you want to also include the payment at t=4, either:

=NPV(0.08,INDEX(A1:A11,B13+1):A11)+INDEX(A1:A11,B13) , or
=1.08*NPV(0.08,INDEX(A1:A11,B13):A11)