PV & NPV discrepancy? -showing different results for same entries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Calculating the Present Value of $120,000, $175,000
$195,000 @5% (as per exercise) gives: $441,464.20
as per detailed example below.

Year1 Year2 Year3
$120,000 $175,000 $195,000
PV @ 5% ($114,285.71) ($158,730.16) ($168,448.33)

=PV(5%,1,,120000)=PV(5%,2,,175000)=PV(5%,3,,195000)

(=sum of row)=$441,464.20

On the other hand, the NPV formula with the following
parameters:

=NPV(5%,0,120000,175000,195000) returns a value of
$420,442.10

Why is there a difference - should they not be the same?
 
Milan Frankl,

You wrote...
Year1 Year2 Year3
$120,000 $175,000 $195,000
PV @ 5% ($114,285.71) ($158,730.16) ($168,448.33)
Click to expand...

Okay, makes sense.

=PV(5%,1,,120000)+PV(5%,2,,175000)+PV(5%,3,,195000)
(I changed this from equal signs to plus signs, which I think is hwat you
had intended.)

Same as above.
=NPV(5%,0,120000,175000,195000) returns a value of
$420,442.10
Click to expand...

Not the same as above.

You should write....

=NPV(5%,120000,175000,195000)
= 420,442.10

Why are you adding the 0 when you don' t above?

Your cash flows are not: 0, 120,000, 175,000, 195,000?

I think you are confusing a single cash flow immediately as opposed to
year-end. That, I believe, is where the root of your concerns are.

In any event, for your symmetry, just drop your 0 so that it matches your
scenario above.

Just a word to the wise and on a completely separate topic. You should be
careful about including your real e-mail address in these newsgroups.
Spambots scour through these posts looking for addresses. You can expect
your welcoming messages soon, unfortunately.

Hope this helps.

Best regards,
Kevin
 
Hi milan.frankl!

The Excel (and other spreadsheet) NPV function, contrary to standard
definitions (e.g. that of Appraisal Institute of America), assumes
that the first cash flow is received at the end of the first period.

Your PV calculations assume this.
Your NPV calculation is "saying" that 0 is received after 1 year and
120000 after 2 years...

See Help on NPV and you'll see the assumption is referred to.

The standard approaches of correcting for the problem are:

=NPV(Rate,RangeExcludingOrigin)+Origin
Or:
=NPV(Rate,RangeIncludingOrigin)*(1+Rate)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Norman,

<<contrary to standard definitions (e.g. that of Appraisal Institute of
America), >>

I didn't realize that anyone, let alone Apppraisal Institute of America,
claimed to have the standard for NPV.

This is in jest, and no need for a reply.

Best regards,
Kevin
 
Hi Kevin!

I know you're jesting but..

With most terms used in any profession it's essential that
professionals use the same definitions for the same terms. In the case
of the NPV term it's probably the US Accounting professional body that
led the way in the US. But in Appraisal generally I think it was the
UK Royal Institution of Chartered Surveyors because they were very
early in issuing practice standards. But now, nearly all appraisal
professional institutions subscribe to the International Valuation
Standards Committee and their practice standards use the standard
definition.

I use US as an example that most readers would relate to. But don't
you just hate it when it's assumed everyone is in the US <vbg>

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
...
...
With most terms used in any profession it's essential that
professionals use the same definitions for the same terms. In the case
of the NPV term it's probably the US Accounting professional body that
led the way in the US. . . .
Click to expand...

Unlikely. It's much more likely the concept was originally defined and the term
originally coined by financial economists, as it's a foundation concept in the
mathematical development in the Theory of the Firm. For instance, the term was
used by John Maynard Keynes (only long-dead economist I searched for).

http://www.blancmange.net/tmh/books/keynes5c.shtml
 
Hi Norman,

Good to hear from you!

My thoughts behind my original message are that there are probably millions
of new business graduates (undergrad and grad) from universities over all
the globe each year. Then you have all the various accountants, CFPs and
the rest of the potpourri of financial designations. Probably the vast
majority of them have used and continue to use Excel as an important arrow
in their vast quiver of financial tools. And if I am not mistaken, Lotus
1-2-3 was similar in format. (I don't recall 1-2-3, but according to
http://www.investit.ca/Resources/Screens/ArticleNPV Formula.htm, Lotus
appears to function in the same manner.) Perhaps Lotus 1-2-3, Quattro ?,
and Excel have (re)created the standard by which the NPV formula should be
used in spreadsheets?

With regard to standards, I am all for standards where they make sense. I am
very glad we have the ISO, and all the other various standard boards.
Without standards, our lives would be so much more difficult. I just hope
that the various standard bodies can soon agree on the best DVD format:
http://www.wired.com/news/business/0,1367,61415,00.html?tw=wn_story_top5 .

But, I don't know if any body is responsible for NPV. Accountants led the
way in the US? Maybe, but it seems to be more of a tool used by internal
planning processes and analyses than rigourous accounting. (Yes, I know
there are various accounting issues that do rely on NPV analyses--for
example, the oil industry accounting makes extensive use of NPV. But you
should first properly plan before you measure and account?) To me it is
just a formula by which you calculate the net present value of a cash flow
stream. I don't know if you would want to create a standard by which
spreadsheets must accept arguments?

I recall there had been some early copyright issues amongst the various
spreadsheets and they had to rearrange the manner by which they accepted
vaious arguments/menus to get around copyright issues. My memory is very
foggy here, so I could easiy be missing the mark. But might this have
something to do with NPV? (I honestly don't know the answer.)


I am not an American; however, I have long gotten over the assumption you
reference above.<vbg> I am sure we both have many good friends in the U.S.

I think we've beaten this issue into submission. <grin>

On a separate note, I enjoy reading your posts and learning from you.

Best regards,
Kevin
 
Hi Harlan!

Probably right. All the Accounting bodies etc. have done is mandated
the use of the definition by their members.

Actually Keynes is the original source of the discounting version of
the formula that Excel uses for financial functions PV etc.

Something like:

The value of an investment is equal to the discounted value of the
income plus the discounted terminal value. Excel re-expresses this by
putting all three elements on one side of the equation leaving the
result to equal zero.

I'd dearly love a precise reference to that quote.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Kevin!

See also Harlan's response and mine as to the source of the NPV
definition.

As far as Excel is concerned, the definition got stuffed up because
they wanted to be compatible with the (then) all conquering Lotus
1-2-3. Lotus copied from VisiCalc, I believe.

It's probably too late to correct this now unless and until Microsoft
grab the bull by the horns (or whatever) and produce a non backwards
compatible spreadsheet. There's a lot of historic anomalies and
problems within Excel that can, realistically, only be corrected by
abandoning backwards compatibility. Funnily enough Microsoft re-wrote
many statistical functions for Excel 2003 and accepted that they would
produce different answers than earlier versions. (pity they made stuff
ups doing it but we have hopes for a patch / service release soon.)

True about lots of friends all over the World. That's why I try to
ensure things are not always expressed in US terms.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
. . . And if I am not mistaken, Lotus
1-2-3 was similar in format. (I don't recall 1-2-3, but according to
http://www.investit.ca/Resources/Screens/ArticleNPV Formula.htm, Lotus
appears to function in the same manner.) Perhaps Lotus 1-2-3, Quattro ?,
and Excel have (re)created the standard by which the NPV formula should be
used in spreadsheets?
Click to expand...
...

FWIW, VisiCalc screwed up its NPV calculation, and all its successors have
followed its lead for the sake of backward compatibility (i.e., to be able to
poach each other's customers).
 
Norman and Harlan,

I hope we fully answered Milan's question <wry grin>.

It was fun reading everyone's responses.

Best regards,
Kevin
 
Hi Kevin!

Re: "I hope we fully answered Milan's question <wry grin>."

In passing! But where there's a departure from the usual definitions
and where there's a difference between spreadsheet and financial
calculator answers, it pays to go into the background.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top