If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
PV & NPV discrepancy? -showing different results for same entries
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? |
#2
|
|||
|
|||
PV & NPV discrepancy? -showing different results for same entries
Milan Frankl,
You wrote... Year1 Year2 Year3 $120,000 $175,000 $195,000 PV @ 5% ($114,285.71) ($158,730.16) ($168,448.33) 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 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 wrote in message ... 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? |
#3
|
|||
|
|||
PV & NPV discrepancy? -showing different results for same entries
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 a =NPV(Rate,RangeExcludingOrigin)+Origin Or: =NPV(Rate,RangeIncludingOrigin)*(1+Rate) -- Regards Norman Harker MVP (Excel) Sydney, Australia Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. |
#4
|
|||
|
|||
PV & NPV discrepancy? -showing different results for same entries
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 |
#5
|
|||
|
|||
PV & NPV discrepancy? -showing different results for same entries
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 Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Kevin Stecyk" wrote in message ... 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 |
#6
|
|||
|
|||
PV & NPV discrepancy? -showing different results for same entries
"Norman Harker" wrote...
... 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. . . . 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 -- To top-post is human, to bottom-post and snip is sublime. |
#7
|
|||
|
|||
PV & NPV discrepancy? -showing different results for same entries
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/Scr...%20Formula.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...=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.) But don't you just hate it when it's assumed everyone is in the US vbg 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 "Norman Harker" wrote in message ... 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 |
#8
|
|||
|
|||
PV & NPV discrepancy? -showing different results for same entries
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 Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. |
#9
|
|||
|
|||
PV & NPV discrepancy? -showing different results for same entries
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 Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. |
#10
|
|||
|
|||
PV & NPV discrepancy? -showing different results for same entries
"Kevin Stecyk" wrote...
. . . 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/Scr...%20Formula.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? ... 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). -- To top-post is human, to bottom-post and snip is sublime. |
|
Thread Tools | |
Display Modes | |
|
|