Hi,

I need a little help with the NPV function if possible.

The help for the NPV function states that the first argument in the
formula is the rate, that's fine. The second argument is the initial
cost of investment one year from today and that the next argument is
the return from the first year. Is the first year in the same time
period as the year which includes the initial cost or is the first
year effectively the end of the second year assuming that the costs
were all incurred in the first year and the second year is when you
start making money (termed as first year in the help)???

For example what happens if I have this scenario:-

Year 1 - Cost £10000, return £5000
Year 2 - Return £10000
Year 3 - Return £10000
Year 4 - Return £10000

Many thanks in advance

Gary

On Thu, 9 Jul 2009 03:02:01 -0700 (PDT), Gazza wrote:

Hi,

I need a little help with the NPV function if possible.

The help for the NPV function states that the first argument in the
formula is the rate, that's fine. The second argument is the initial
cost of investment one year from today and that the next argument is
the return from the first year. Is the first year in the same time
period as the year which includes the initial cost or is the first
year effectively the end of the second year assuming that the costs
were all incurred in the first year and the second year is when you
start making money (termed as first year in the help)???

For example what happens if I have this scenario:-

Year 1 - Cost £10000, return £5000
Year 2 - Return £10000
Year 3 - Return £10000
Year 4 - Return £10000

Many thanks in advance

Gary


Perhaps reading the description, rather than looking at the example, would be
more illuminating.

----------------------------------
NPV(rate,value1,value2, ...)

Value1, value2, ... are 1 to 254 arguments representing the payments and
income.

The NPV investment begins one period before the date of the value1 cash flow
and ends with the last cash flow in the list. The NPV calculation is based on
future cash flows. If your first cash flow occurs at the beginning of the first
period, the first value must be added to the NPV result, not included in the
values arguments. For more information, see the examples below.
-----------------------------------

So set up your Values while thinking in terms of "cash flow" and "periods"
rather than "investment", "return", and "years".

If money is going "away" from you (i.e. into an account, or investment), enter
it as a negative value. If money is coming "towards" you (i.e. as a return on
your investment, rental income, interest being paid, etc) then enter the value
as a positive number.

Also note that if your first cash flow occurs at the BEGINNING of the first
period, you don't include it in the series but add it to the computed NPV.
--ron

"Gazza" wrote:
Is the first year in the same time
period as the year which includes the initial
cost or is the first year effectively the end
of the second year

Neither.

First, realize that the NPV function has no concept of dates. (XNPV does,
though.) So when the costs occur relative to the period depends on how you
structure the data.

So when using NPV, the only thing to realize is that the cash flows are
assumed to occur at the same relative point in each period, be it the
beginning, middle, end and anything in between.

It might help to look at the NPV formula under "Remarks" in the NPV help
page. If you are unfamiliar with the mathematical notation, it is
effectively:

NPV function = CF1/(1+r)^1 + CF2/(1+r)^2 + ....

The key is: the first cash flow parameter is discounted. In most uses of
NPV, the initial cash flow, dubbed CF0, should not be discounted. So the
actual NPV, as it might computed with an HP business calculator is:

NPV = CF0 + CF1/(1+r)^1 + CF2/(1+r)^2 + ....


assuming that the costs were all incurred in the
first year and the second year is when you
start making money (termed as first year in the help)???

But that is not what you wrote in your example. You wrote: "Year 1 - Cost
£10000, return £5000".

In order to use NPV effectively, you need to decide whether to combine the
Year1 return with the Year1 cost (i.e. -10000 + 5000 = -5000), which would
make sense if the return occurred close to the initial investment, or to
combine the Year1 return with the Year2 return (10000 + 5000 = 15000), which
would make sense if the return occurred close to the Year2 return.

Alternatively, shift your concept of years. Suppose you invested 10000 in
Jan 2005, received a return of 5000 in Dec 2005, and received all other
returns in Dec of subsequent years. In that case, for the purpose of using
NPV, I would treat the investment as if it occurred in Dec 2004. So the NPV
would be:

=Y1 + NPV(rate,Y2:Y5)

where Y1 is -10000 and Y2:Y5 is 5000, 10000, 10000 and 10000.

Alternatively, assign dates to each cash flow and use XNPV.


----- original message -----

"Gazza" wrote in message
...
Hi,

I need a little help with the NPV function if possible.

The help for the NPV function states that the first argument in the
formula is the rate, that's fine. The second argument is the initial
cost of investment one year from today and that the next argument is
the return from the first year. Is the first year in the same time
period as the year which includes the initial cost or is the first
year effectively the end of the second year assuming that the costs
were all incurred in the first year and the second year is when you
start making money (termed as first year in the help)???

For example what happens if I have this scenario:-

Year 1 - Cost £10000, return £5000
Year 2 - Return £10000
Year 3 - Return £10000
Year 4 - Return £10000

Many thanks in advance

Gary

On 9 July, 18:21, "JoeU2004" wrote:
"Gazza" wrote:
Is the first year in the same time
period as the year which includes the initial
cost or is the first year effectively the end
of the second year

Neither.

First, realize that the NPV function has no concept of dates. *(XNPV does,
though.) *So when the costs occur relative to the period depends on how you
structure the data.

So when using NPV, the only thing to realize is that the cash flows are
assumed to occur at the same relative point in each period, be it the
beginning, middle, end and anything in between.

It might help to look at the NPV formula under "Remarks" in the NPV help
page. *If you are unfamiliar with the mathematical notation, it is
effectively:

NPV function = CF1/(1+r)^1 + CF2/(1+r)^2 + ....

The key is: *the first cash flow parameter is discounted. *In most uses of
NPV, the initial cash flow, dubbed CF0, should not be discounted. *So the
actual NPV, as it might computed with an HP business calculator is:

NPV = CF0 + CF1/(1+r)^1 + CF2/(1+r)^2 + ....

assuming that the costs were all incurred in the
first year and the second year is when you
start making money (termed as first year in the help)???

But that is not what you wrote in your example. *You wrote: *"Year 1 - Cost
£10000, return £5000".

In order to use NPV effectively, you need to decide whether to combine the
Year1 return with the Year1 cost (i.e. -10000 + 5000 = -5000), which would
make sense if the return occurred close to the initial investment, or to
combine the Year1 return with the Year2 return (10000 + 5000 = 15000), which
would make sense if the return occurred close to the Year2 return.

Alternatively, shift your concept of years. *Suppose you invested 10000 in
Jan 2005, received a return of 5000 in Dec 2005, and received all other
returns in Dec of subsequent years. *In that case, for the purpose of using
NPV, I would treat the investment as if it occurred in Dec 2004. *So the NPV
would be:

=Y1 + NPV(rate,Y2:Y5)

where Y1 is -10000 and Y2:Y5 is 5000, 10000, 10000 and 10000.

Alternatively, assign dates to each cash flow and use XNPV.

----- original message -----

"Gazza" wrote in message

...
Hi,

I need a little help with the NPV function if possible.

The help for the NPV function states that the first argument in the
formula is the rate, that's fine. The second argument is the initial
cost of investment one year from today and that the next argument is
the return from the first year. Is the first year in the same time
period as the year which includes the initial cost or is the first
year effectively the end of the second year assuming that the costs
were all incurred in the first year and the second year is when you
start making money (termed as first year in the help)???

For example what happens if I have this scenario:-

Year 1 - Cost £10000, return £5000
Year 2 - Return £10000
Year 3 - Return £10000
Year 4 - Return £10000

Many thanks in advance

Gary

Thanks for all your help. Your replies have been very useful