Variable Range for NPV

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?

Try the EDATE function. If A1 is the beginning date and B1 is the
number of years.
=EDATE(A1,B1*12)

On Aug 26, 4:14 pm, Spiky wrote:
Try the EDATE function. If A1 is the beginning date and B1 is the
number of years.
=EDATE(A1,B1*12)

Forgot: you need the Analysis toolpak if not in Excel 2007.

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?

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?

I don't know how to make it work as I am working with years as in the policy
years of a life insurance policy. So my starting point is somewere after the
first year and the end point is some "X" number of years after the starting
point.

"Spiky" wrote:

On Aug 26, 4:14 pm, Spiky wrote:
Try the EDATE function. If A1 is the beginning date and B1 is the
number of years.
=EDATE(A1,B1*12)

Forgot: you need the Analysis toolpak if not in Excel 2007.

I got it worked out.

=NPV(PV_Liab_Rate,INDIRECT("R"&RowRetYrStart&"C5:R "&RowRetYrEnd&"C5",FALSE))*(1+PV_Liab_Rate)

Thanls for your help.

"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?

I got it worked out.

=NPV(PV_Liab_Rate,INDIRECT("R"&RowRetYrStart&"C5:R "&RowRetYrEnd&"C5",FALSE))*(1+PV_Liab_Rate)

Thanls for your help.

"Spiky" wrote:

On Aug 26, 4:14 pm, Spiky wrote:
Try the EDATE function. If A1 is the beginning date and B1 is the
number of years.
=EDATE(A1,B1*12)

Forgot: you need the Analysis toolpak if not in Excel 2007.