How wrong can you be with your NPV and MIRR functions in EXCel

[jwbosu]

Did you know that the NPV and MIRR functions are different from the accepted
calculations as found in finance textbooks. The NPV function discounts the
first cash flow, the initial investment that is negative when it should not.
This is so wrong it is pitifull. What I want to know is how many business
decisions have been made over the years using this calculations. Who is
liable? MS should be.

The MIRR while it is not wrong is not the way we define the MIRR today. The
MIRR calculated the FV of the cash flow stream assuming the reinvestment
rate is the same as teh financing rate and find the PV of the terminal cash
flow less the initial investment. See Brigham Houston Concise edition.

 

[Alan]

Did you know that the NPV and MIRR functions are different from the
accepted calculations as found in finance textbooks. The NPV
function discounts the first cash flow, the initial investment that
is negative when it should not. This is so wrong it is pitifull.
What I want to know is how many business decisions have been made
over the years using this calculations. Who is liable? MS should
be.

The MIRR while it is not wrong is not the way we define the MIRR
today. The MIRR calculated the FV of the cash flow stream assuming
the reinvestment rate is the same as teh financing rate and find the
PV of the terminal cash flow less the initial investment. See
Brigham Houston Concise edition.

Hi,

Nothing new here - it is all well documented in the excel manual as
you discovered, so I don't see how anyone can complain that they
didn't know (unless they didn't read the manual in which case that is
a choice they made for themselves).

However, you are correct that the way that excel does these calcs is
not the way that many people do them.

Personally I would *never* use the built in NPV / IRR / MIRR
functions. I always build up those calculations using simple formulae
one step at a time so that the reader of the worksheet can see exactly
what was done, follow the calcs, and make up their own mind whether
they are happy with the approach chosen.

I always suggest to my team that this is the best approach with any
model.

HTH

Alan.

 

[JE McGimpsey]

First - I don't know who "you" is in your subject. This is a
peer-to-peer forum. If you want to contact Microsoft, send an email to
(e-mail address removed).

Nobody is "liable". This is an old issue. Whether or not you agree with
the implementation of the functions, they are fully documented, and have
been for at least 20 years. For example:

Help ("NPV Function"):

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.

So your assumption that the first cash flow was the initial (time 0)
investment is wrong.

You must never *assume* that you know what you're doing. XL, 123, and
probably Visicalc, have been calculating NPV this way "forever". It
won't change (because that would break previous models).

Of course, MS certainly isn't liable if you or anyone else fail to RTFM.

Likewise, MIRR is documented in Help. I would *hope* that the function
wouldn't change to suit "the way we define the MIRR today" (though, not
being a financial expert, I'd want to see more than one reference cited
so that I knew who "we" was). Again, think of all the models that would
break if the function started calculating differently. *That* would
involve some liability.


You might want to look at one of the myriad discussions in the archives.
For instance:

http://groups.mgoogle.com/groups?threadm=OBLOS#QODHA.2256@TK2MSFTNGP1
1.phx.gbl

 

[Harlan Grove]

Personally I would *never* use the built in NPV / IRR / MIRR
functions. I always build up those calculations using simple formulae
one step at a time so that the reader of the worksheet can see exactly
what was done, follow the calcs, and make up their own mind whether
they are happy with the approach chosen.

....

That's a BIG MISTAKE. Given the nature of the calculations involved in NPV
and IRR, built-in functions which could (and almost certainly do) make use
of extended precision available from storing intermediate results in 80-bit
FPU registers rather than only 64-bit Doubles in cells would have a decided
advantage. NPV at least will almost always retain greater precision and
accuracy than any multiple cell approach.

As for the IRRs, they're far more efficient than any rolled-your-own
alternatives.

As for NPV being inconsistent with finance texts, blame Lotus Development
Corp. 123 got it wrong from the beginning, but one done it couldn't be
easily undone. Excel adopted 123's bug because compatibility with 123 was
more important than compatibility with finance texts. [Note though that
there wer quite a few other things Microsoft chose to differently in Excel
than LDC chose to do them in 123, so Microsoft wasn't consistent in making
123 compatibility the universal goal.]

The standard approach to using these NPV functions hasn't changed from
123R1.

=NPV(d,CF)*(1+d)

gives the financial textbook NPV.

 

[Alan]

...

That's a BIG MISTAKE. Given the nature of the calculations involved
in NPV and IRR, built-in functions which could (and almost certainly
do) make use of extended precision available from storing
intermediate results in 80-bit FPU registers rather than only 64-bit
Doubles in cells would have a decided advantage. NPV at least will
almost always retain greater precision and accuracy than any
multiple cell approach.

Hi Harlan,

I'm not up to speed with you on that one.

The 'nature' of the calculations for an NPV (let's leave IRR for now),
are really quite simple.

I usually lay out the calcs thus (ignoring other rows for formatting
etc):

Row 1: Period actual net cash lows in nominal dollars (usually
rounded to three or four significant figures depending on the client)

Row 2: Discount factor from the period in question to time zero
(usually rounded to four significant figures, but can vary)

Row 3: Present value (at time zero) of the nominal cash flows for
the peroid (usually rounded to three or four significant figures
depending on the client)

Row 4: Cumulative present value (to period)


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

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!

If I have misunderstood your post, please do clarify, as I am always
keen to improve!

Thanks,

Alan.

 

[Harlan Grove]

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.

 

[Alan]

Hi,

Perhaps an example might be more illuminating for me?

I'm not sure if this will post correctly, since I have attempted to
'line up' figures using tabs / spaces, but I'll give it a go.

I generated a simple table of numbers to represent the nominal in /
out flows across 11 discrete time points, being time zero to 10:

Time Point Cash Inflow / Outflow
0 -228000
1 -355000
2 53000
3 -276000
4 405000
5 497000
6 389000
7 287000
8 -321000
9 -480000
10 64000
11 -284000


I then applied a DF of 10% pa to arrive at the follow un-rounded /
rounded (to 4 s.f.) CDFs:

Time Point DF @ 10% DF @ 10% Rounded
0 1.00000000000 1
1 1.10000000000 1.1
2 1.21000000000 1.21
3 1.33100000000 1.331
4 1.46410000000 1.464
5 1.61051000000 1.611
6 1.77156100000 1.772
7 1.94871710000 1.949
8 2.14358881000 2.144
9 2.35794769100 2.358
10 2.59374246010 2.594
11 2.85311670611 2.853


This translates into the following un-rounded / rounded (to the
nearest thousand) PVs:

Time Point PV @ 10% PV @ 10% rounded
0 -228000.0000000000 -228000
1 -322727.2727272730 -323000
2 43801.6528925620 44000
3 -207362.8850488350 -207000
4 276620.4494228540 277000
5 308597.8975604000 309000
6 219580.3587909190 220000
7 147276.3799322130 147000
8 -149748.8690473240 -150000
9 -203566.8568187930 -204000
10 24674.7705234900 25000
11 -99540.2674527154 -100000


And finally, the following cumulative NPVs unrounded / rounded (to the
nearest thousand from above):

Time Point Cum PV @ 10% Cum PV @ 10% Rounded
0 -228000.000000000 -228000
1 -550727.272727273 -551000
2 -506925.619834711 -507000
3 -714288.504883546 -714000
4 -437668.055460693 -437000
5 -129070.157900293 -128000
6 90510.200890627 92000
7 237786.580822839 239000
8 88037.711775515 89000
9 -115529.145043278 -115000
10 -90854.374519788 -90000
11 -190394.641972503 -190000


For the avoidance of doubt, the following formula yields the same
result:

=NPV(10%,{-355000,53000,-276000,405000,497000,389000,287000,-321000,
-480000,64000,-284000})-228000

=-190394.641972503

Whilst I am sure that you are mathematically correct in all that you
state, my point of view is that from the perspective of a decision
maker, the final outcome difference between $190,394.641972503 and
$190,000 is not commercially relevant?


Despite your assumption to the contrary, I am most keen to understand
if I am making a mistake here, and welcome your (and others)
considered views.

Alan.

 

[Briana]

It doesn't matter if the fact that the formula is incorrect is documented in
the excel book or not. Who reads that? 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. 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.)
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. Can't
we just correct it?
Briana

 

[Myrna Larson]

No, they can't "just correct" it without, as JE says, breaking spreadsheets
developed by people who read the documentation, and write their formulas
accordingly.

 

[Harlan Grove]

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]

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.

 

[opieandy]

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

 

[Harlan Grove]

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.

 

[Harlan Grove]

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.