Using Indirect to set a range

[Dkline]

I have to calculate a column of NPV. However only during the years an
employee is in retirement. So I need to "build" the range reference starting
at retirement to the last year of benefit based on the employee's age and
year of retirement.

Each year from the retirement year to the last benefit year needs to be
caclulated. The range will vary by start year and by the number of years to
the end. I'm trying to use the INDIRECT fuction to build that address. I now
feel I'm using the wrong function and I am in need of guidance.

I have to use NPV as some schedules of benefits will not be a level amount.

=NPV(0.0528,R22C5:R35C5)*(1+0.0528).

For this example, the values in the range R22C5:R35C5 is a level $33,000
which returns $337,828.

What I need to do is to be able to set the decreasing row numbers for the
starting cell and set the row number for the last cell.

How can I do this?

 

[Ron Coderre]

How about something like this:

With
D14100 containing years (2000 through 2086)
and
E14:E100 containing value to be used by the NPV function.
AND
A1: (the start year...eg 2008)
A2: (the number to use....eg 14)

Then, try this variation of your formula.
=NPV(0.0528,INDEX(E14:E100,MATCH(A1,D14100,0)):
INDEX(E14:E100,MATCH(A1,D14100,0)+A2-1))*(1+0.0528)

Using the above example and if each cell in E22:E35
contains 33,000....then that formula returns: 337,827.80 (rounded)

Note....if you really are using R1C1 notation....
D14100....is R14C4:R100C4
E14:E100....is R14C5:R100C5
A1..............is R1C1
A2..............is R2C1
and...the formula would be:
=NPV(0.0528,INDEX(R14C5:R100C5,MATCH(R1C1,R14C4:R100C4,0)):
INDEX(R14C5:R100C5,MATCH(R1C1,R14C4:R100C4,0)+R2C1-1))*(1+0.0528)

Is that something you can work with?
Post back if you have more questions.

Regards,

Ron
Microsoft MVP - Excel

 

[Dkline]

I've been playing with it and it works just fine.

I'm trying to fit it into the original spreadsheet which I inherited.

 

[Dkline]

I'm having trouble getting to to work when an inflation factor is applied to
the stream of income. Taking the original $33,000 and inflate it annually by
3%. The final inflated value is nowhere near what it should be.

The inflated value in the last year should be 49,915. What I am getting is
33,990 (which is the original amount * (1 + 0.03).

If it would be helpful, I can email my soreadsheet.

Years Years Income PV Years of Value
PreRet PostRet Stream NPV Inflated Inflated Inflation Needed
1 15 0 0 0
2 14 0 0 0
3 13 0 0 0
4 12 0 0 0
5 11 0 0 0
6 10 0 0 0
7 9 0 0 0
8 8 0 0 0
9 7 0 0 0
10 6 0 0 0
11 5 0 0 0
12 4 0 0 0
13 3 0 0 0
14 2 0 0 0
15 1 0 0 0
16 0 14 33000 337,828 33,990 414,346 1 414,346
17 0 13 33000 320,923 35,010 388,776 2 400,439
18 0 12 33000 303,125 36,060 362,639 3 384,724
19 0 11 33000 284,388 37,142 335,924 4 367,073
20 0 10 33000 264,661 38,256 308,618 5 347,352
21 0 9 33000 243,893 39,404 280,707 6 325,416
22 0 8 33000 222,028 40,586 252,178 7 301,114
23 0 7 33000 199,008 41,803 223,018 8 274,284
24 0 6 33000 174,774 43,058 193,212 9 244,755
25 0 5 33000 149,259 44,349 162,747 10 212,347
26 0 4 33000 122,398 45,680 131,607 11 176,869
27 0 3 33000 94,118 47,050 99,778 12 138,116
28 0 2 33000 64,345 48,462 67,244 13 95,874
29 0 1 33000 33,000 49,915 33,990 14 49,915