Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How wrong can you be with your NPV and MIRR functions in EXCel
What an extraordinarily rude response, Mr. Grove. Given the repeated
rudeness of your responses in the face of sincere dialogue from Alan, there's little hope your social manners could ever improve. "Harlan Grove" wrote: "Alan" wrote... .... The 'nature' of the calculations for an NPV (let's leave IRR for now), are really quite simple. .... It's the seemingly simple things that tend to cause the biggest problems because they come as such a surprise. Start with a simple interest rate like 3.0%. That seems to be a simple number. Two problems: first, 0.03 is an infinitely repeating *BINARY* fraction like 1/3 is an infinitely repeating decimal fraction; two, the interest rate isn't used directly, 1 plus the interest rate is. Each later age requires the successively higher integer power of 1+r, so even if the calculations were done in decimal you'd quickly arrive at a point at which you can't retain the lowest order digits. Time 1 1.03 Time 2 1.0609 Time 3 1.092727 Time 4 1.12550881 Time 5 1.1592740743 Time 6 1.194052296529 Time 7 1.22987386542487 Time 8 1.2667700813876161 Time 8 exceeds 15 decimal digits, so it can't and won't be representented except as truncated or rounded in Excel. Without wishing to be presumptious, I am guessing that you are referring to the level of precision that can be held in a single cell (15 significant figures if I recall correctly). Got it in one. If so, then I think that, in theory you are absolutely correct, but that in a commercial environment, we rarely have the precision of inputs / assumptions to warrant showing results to more than three or four significant figures. I often wish it were otherwise! You're confusing the imprecision of inputs/estimates of future cashflows with the precision of NPV calculations based on those cashflows. I too work in a commercial environment, but I don't glibly dismiss the benefits of greater precision of calculations just because the inputs are flaky. I employ standard sensitivity testing to check the reasonableness of results by varying the cashflows stochastically. If my discount rate is a true cost of capital estimate, it gives me a measure of uncertainty which I can translate into variation of a cashflow around its assumed mean. If I have misunderstood your post, please do clarify, as I am always keen to improve! Given the superficiality of comments such as "in a commercial environment, we rarely have the precision of inputs / assumptions to warrant showing results to more than three or four significant figures.", it's obvious you don't see the point of distinguishing between what you display and what you calculate. It's difficult to believe there's any hope you could improve. Estimates of future cashflows are only that, estimates. You have a point that there's no reason to estimate them to 9 significant digits. However, the NPV calculation is different. There's no good reason to round your discount factors. Note that if your nominal cashflows were displayed to only 3 significant digits and your discount factors rounded to only 4 decimal places, then the product of these two sets of numbers have 7 significant digits. If you then round these to 3 or 4 significant digits, you're effectively eliminating the possibility of any meaningful NPV result less than 1.0% or 0.1% of your largest individual cashflow. If you're going to do that, why bother with pseudo-NPV calculations at all? Just sum up the negative cashflows and calculate their dollar-weighted average payout date and sum up the positive cashflows and calculate their dollar-weighted average receipt date. Then calculate your pseudo-NPVs as CumPosCFs/(1+r)^Avg Receipt Date - CumNegCFs/(1+r)^Avg Payout Date The calculations are even simpler than your pseudo-NPV, and it's highly likely the two would give the same accept/reject results except for those cashflows in which sensitivity testing is necessary to decide for sure whether the project represented by the cashflows makes sense or not. |
#2
|
|||
|
|||
Obviously not whining students. Apparently you've failed to learn two very
basic lessons from this: 1) read the fine print; 2) take responsibility for your own intellectual laziness. Based on your current trajectory, your future prospects don't look bright. Harlan, is your mission in life to demonstrate the ultimate in rude behavior on this forum? You are worse than hopeless. "Harlan Grove" wrote: "Briana" wrote... It doesn't matter if the fact that the formula is incorrect is documented in the excel book or not. Who reads that? . . . Obviously not whining students. Apparently you've failed to learn two very basic lessons from this: 1) read the fine print; 2) take responsibility for your own intellectual laziness. Based on your current trajectory, your future prospects don't look bright. . . . People who don't know how to use excel. Finance classes all over the country care coming up with two different NPV numbers for their capital projects, one frmo their $29 trusty calculator of less than 1 pound in weight, and an incorrect answer from a $100 computer program. . . . True, but that's how *ALL* spreadsheets work. They ALL have technically incorrect NPV functions (as NPV was a term in wide-spread use in finance and economics prior to the advent of elecronic spreadsheets, and finance texts always have NPV calcs begin at time zero, one does have to admit that spreadsheets, including Excel, screwed this up). Most of them also treat 1900 as a leap year. Get used to it. Learn the shortcomings of your tools, and work around them as many, many others have already learned to do. . . . NPV should be changed to have an option for a year 0 cash outflow. If not, then Microsoft does not care about having accurate programs, and we should question every other formula also(Yes, MIRR also has the same problem.) You've almost discovered an obvious truth. Microsoft really doesn't care how accurate Excel is. It took over a decade of complaining to get decent continuous distribution and regression functions. There were no simple work-arounds for those as there are for NPV, so Microsoft had to fix them. As for adding options to existing functions, flip that around - you could write your own 'corrected' NPV function in VBA. You wouldn't be the first to write your own replacements for flawed built-in 'functionality'. I am a finance student, and for the last year, my classmates and I have argued with professors to accept two numbers for each NPV calculation. Meaning your grades have suffered because you haven't checked the specs for the software you've been using? Tough. If you had been doing engineering coursework and just assumed certain measurements were in mks units rather than cgs units because your textbook always used mks, and thus got wrong answers, your grade would also rightly suffer. Never assume you know how a piece of software works until you either read the manual or run some test calculations. Failure to do either deserves to be recognized as FAILURE. Can't we just correct it? No because fixing it for you would break existing workbooks used by others. Your ONLY choice is between learning how to use Excel (warts & all) or using something else (but you'll have the same problem with any other spreadsheet unless you modify some open source one to suit your needs). |
#3
|
|||
|
|||
opieandy wrote...
.... Harlan, is your mission in life to demonstrate the ultimate in rude behavior on this forum? You are worse than hopeless. .... This was no where near the ultimate in rude behavior, but my preference is proving I'm rude while yours appears to be proving you're stupid. This thread was from months ago. What kind of moron goes out of their way to dredge up discussions like this? Oh! Morons like you. |
#4
|
|||
|
|||
opieandy wrote...
What an extraordinarily rude response, Mr. Grove. Given the repeated rudeness of your responses in the face of sincere dialogue from Alan, there's little hope your social manners could ever improve. Nope, but apparently you're too ignorant to have heard of the saying "let sleeping dogs lie". Or maybe you're just too stupid to understand what it means. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|