Here's one example below. If I shorten it up to only 10 years of benefts
instead of the 15 in the below example, the NPV is $675,902.38. The extra
wrinkle is that the "Income Stream" can be non-level which is why I need to
use the NPV rather then PV.

PV------- $836,668.75
NPV----- $836,668.75
PY "Income
"Stream" NPV
1 0 $200,291.85
2 0 $220,321.03
3 0 $242,353.13
4 0 $266,588.45
5 0 $293,247.29
6 0 $322,572.02
7 0 $354,829.22
8 0 $390,312.14
9 0 $429,343.36
10 0 $472,277.70
11 0 $519,505.46
12 0 $571,456.01
13 0 $628,601.61
14 0 $691,461.77
15 0 $760,607.95
16 100000 $836,668.75
17 100000 $810,335.62
18 100000 $781,369.18
19 100000 $749,506.10
20 100000 $714,456.71
21 100000 $675,902.38
22 100000 $633,492.62
23 100000 $586,841.88
24 100000 $535,526.07
25 100000 $479,078.68
26 100000 $416,986.54
27 100000 $348,685.20
28 100000 $273,553.72
29 100000 $190,909.09
30 100000 $100,000.00


"ShaneDevenshire" wrote:

Hi,

Please show us some sample data. INDIRECT might work or you may have to
take some other approach depending on what you data looks like. INDIRECT
would work if you had defined range names for each year for example and you
want just a given year.

--
Cheers,
Shane Devenshire


"Dkline" wrote:

I need to calculate an NPV that could start in any given year and end in a
later given year to provide a stream of income.

For example - I have a 30 years for this case. Income will start in year 16
and end in year 30. For a level $100,000 of income in the income period at
10% at beginning of year, I get $836,668.75. If it is only 10 years, the NPV
is $675,902.38.

How can I - by formula - adjust the start and end addresses for the NPV?

I've tried Address, Indirect, and a few others but I'm doing something wrong.

Is there a way to make this work by formula?