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.

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).

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.

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.