Your spreadsheets may be disasters in the making.
Caulkins, Morrison, & Weidemann (2006)
Despite being staggeringly error prone, spreadsheets are a highly flexible programming environment.
Abreu, et al (2015)
Studies have shown that there is a high incidence of errors in spreadsheets.
Csernoch & Biro (2013)
Spreadsheets... pose a greater threat to your business than almost anything you can imagine.
Howard (2005)
Despite overwhelming and unanimous evidence... companies have continued to ignore spreadsheet error risks.
Panko (2014)
Spreadsheets are alarmingly error-prone to write.
Paine (2001)
The quality and reliability of spreadsheets is known to be poor.
Bishop & McDaid (2007)
Most executives do not really check or verify the accuracy or validity of [their] spreadsheets...
Teo & Tan (1999)
Even obvious, elementary errors in very simple, clearly documented spreadsheets are... difficult to find.
Galletta, et al (1993)
Spreadsheet errors have resulted in huge financial losses.
Abraham & Erwig (2007)
Spreadsheets are the most popular live programming environments, but they are also notoriously fault-prone.
Hermans & van der Storm (2015)
Most large spreadsheets have dozens or even hundreds of errors.
Panko & Ordway (2005)
It is now widely accepted that errors in spreadsheets are both common and potentially dangerous.
Nixon & O'Hara (2010)
1% of all formulas in operational spreadsheets are in error.
Powell, Baker, & Lawson (2009)
...few incidents of spreadsheet errors are made public and these are usually not revealed by choice.
Kruck & Sheetz (2001)
The untested spreadsheet is as dangerous and untrustworthy as an untested program.
Price (2006)
Spreadsheets can be viewed as a highly flexible programming environment for end users.
Abreu, et al (2015)
It is irrational to expect large error-free spreadsheets.
Panko (2013)
Every study, without exception, has found error rates much higher than organizations would wish to tolerate.
Panko (1999)
Research on spreadsheet errors is substantial, compelling, and unanimous.
Panko (2015)
Overconfidence is one of the most substantial causes of spreadsheet errors.
Sakal, et al (2015)
Untested spreadsheets are riddled with errors.
Miller (2005)
Programmers exhibit unwarranted confidence in the correctness of their spreadsheets.
Krishna, et al (2001)
Spreadsheets are often hard, if not impossible, to understand.
Mireault & Gresham (2015)
60% of large companies feel 'Spreadsheet Hell' describes their reliance on spreadsheets.
Murphy (2007)
The issue is not whether there is an error but how many errors there are and how serious they are.
Panko (2007)
Spreadsheets are easy to use and very hard to check.
Chen & Chan (2000)
Spreadsheets are more fault-prone than other software.
Kulesz & Ostberg (2013)
Errors in spreadsheets... result in incorrect decisions being made and significant losses incurred.
Beaman, et al (2005)
Spreadsheets are notoriously error-prone.
Cunha, et al (2011)
The results given by spreadsheets are often just wrong.
Sajaniemi (1998)
The software that end users are creating... is riddled with errors.
Burnett & Myers (2014)
Never assume a spreadsheet is right, even your own.
Raffensperger (2001)
Spreadsheet shortcomings can significantly hamper an organization's business operation.
Reschenhofer & Matthes (2015)
Developing an error-free spreadsheet has been a problem since the beginning of end-user computing.
Mireault (2015)
Spreadsheet development must embrace extensive testing in order to be taken seriously as a profession.
Bock (2016)
Spreadsheets are dangerous to their authors and others.
Durusau & Hunting (2015)
Spreadsheets are commonly used and commonly flawed.
Caulkins, Morrison, & Weidemann (2008)
Errors in spreadsheets are as ubiquitous as spreadsheets themselves.
Colbenz (2005)
Spreadsheets contain errors at an alarmingly high rate.
Abraham, et al (2005)
Spreadsheets are extraordinarily and unacceptably prone to error.
Dunn (2010)
Spreadsheet errors... a great, often unrecognised, risk to corporate decision making & financial integrity.
Chadwick (2002)
Every study that has looked for errors has found them... in considerable abundance.
Panko & Halverson (1996)
94% of the 88 spreadsheets audited in 7 studies have contained errors.
Panko (2008)
Spreadsheet errors are pervasive, stubborn, ubiquitous and complex.
Irons (2003)
A lot of decisions are being made on the basis of some bad numbers.
Ross (1996)
People tend to believe their spreadsheets are more accurate than they really are.
Caulkins, Morrison, & Weidemann (2006)
Spreadsheets have a notoriously high number of faults.
Rust, et al (2006)
A significant proportion of spreadsheets have severe quality problems.
Ayalew (2007)
Spreadsheet errors are still the rule rather than the exception.
Nixon & O'Hara (2010)

Pitfalls of Excel's NPV function

Incorrect use of Excel's NPV function is a common source of spreadsheet errors. This article discusses several pitfalls and suggests ways to avoid them.

Summary of key points:
  • Excel's NPV function is a common source of spreadsheet errors.
  • Even simple spreadsheets often contain material errors.
  • When using the NPV function, errors that commonly occur include: referencing the wrong cells, constant jamming, inadequate documentation, inconsistent assumptions about cash flows being real/nominal and before/after tax, misinterpretation of an ambiguous model specification, and incorrect cash flow timing.
  • Finding and correcting errors requires inspecting every cell for errors to ensure that assumptions are consistent, functions are used correctly, good practice is followed, and the spreadsheet appropriately represents the situation being modelled.

Project evaluation using the NPV function

Many financial models use Excel's NPV function to calculate the Net Present Value of discounted cash flows. As an example, suppose we build a simple model to evaluate a project with the following specification:

Figure 1. Model 1
Model 1
Project evaluation using the NPV function. A positive NPV indicates that the project should proceed.
  • An immediate initial investment of $100m.
  • Cash flows of $30m per annum throughout each of the next five years.
  • A final year in which costs of $50m are incurred to complete the project (such as returning a site to greenfields condition).
  • Discount rate of 7%.

Model 1: Project evaluation NPV

Figure 1 shows Model 1, which models the project evaluation example. Note that the models described in this article are available to download.

The discount rate assumption is shown in B3. The cash flows are listed in B7:B13. The NPV of this project is calculated in B14, with a value of $12.5m. A positive NPV indicates that the project is economic, ie. the cash flows are more than sufficient to justify the initial investment, given our discount rate. Therefore we decide to proceed with the project.

Errors in Model 1

But hold that decision. When we inspect Model 1 we observe that there are several actual and potential errors:

  • Constant jamming. B14's formula is =NPV(0.1,Cashflow). Rather than referencing the discount rate in B3, the formula has a constant of 0.1 (a poor practice called "jamming"). Consequently the NPV result is wrong, since it uses an incorrect discount rate. Even if the formula contained the correct constant there would be a potential error, as a change in B3 would not be reflected in the NPV.
  • Named range. We could assume that the named range Cashflow, as used in B14, is correct. However, upon closer inspection we note that Cashflow is defined as B7:B12. That is, it omits the cash flow for the final year. Consequently, the NPV result is wrong.
  • Real or nominal cash flows. Is the discount rate real or nominal? Model 1 does not say. We must ensure that the discount rate is on the same basis as the cash flows. That is, if the cash flows grow at a rate that includes inflation, then they are nominal. If inflation has been removed from the cash flows, then they are real. Either basis could be used, depending on the model requirements. Whichever basis is used for the cash flows, the discount rate must be consistent.
  • Before or after tax. Similar to the real or nominal issue described above, the cash flows may be before or after tax. Again, the discount rate must be on the same basis as the cash flows.
  • Documentation. Both the discount rate and the cash flows lack documentation. They need to be documented to clearly state whether they are on a real/nominal and before/after tax basis. Other cells may also need documentation, to advise the spreadsheet's users and developers about what values are expected and how the model works.
  • Cash flow timing. Excel's NPV function assumes that cash flows occur at the end of each period. In our example, that assumption is incorrect, firstly because the initial investment occurs immediately, rather than being at the end of the first year, and secondly because the remaining cash flows occur throughout each year starting immediately. Therefore, the spreadsheet incorrectly models the timing of the cash flows.
  • Ambiguous specification. In the project specification there is some ambiguity about the timing of the costs in the final year. Do they occur at the start of the year, the end of the year, continuously throughout the year, or some other timing? Any ambiguity in the model specification must be clarified.

Model 2: Revised version that fixes the errors in Model 1

Figure 2. Model 2
Model 2
After correcting the errors in Model 1 the NPV is now negative, indicating that the project should not proceed.

Figure 2 shows Model 2, which has been revised to fix the errors in Model 1. In this revised version, we have:

  • Verified that the cash flows are on a real basis and after tax, which matches the discount rate assumption. These definitions are documented in cell comments.
  • Clarified the timing of the final year costs. ie. they are evenly spread throughout the year.
  • Added an assumption in B4, which is used to adjust the cash flow timing (as defined in its cell comment).
  • Changed the NPV calculation so that it refers to the discount rate assumption in B3.
  • Put the initial investment outside the NPV function so that it isn't discounted (ie. it occurs immediately).
  • Included the final year's costs in the calculation.
  • Discounted the cash flows using an NPV function, with a timing adjustment to approximate cash flows that occur throughout each year rather than at the end of each year.

The NPV is now -$7.2m, almost $20m less than Model 1. Most of this change is due to including the final year costs.

Adjusting the cash flow timing also makes a material difference (increasing the NPV, by bringing forward the cash flows by half a year).

A negative NPV indicates that the project is not economic. Therefore, we decide to not proceed with the project. This decision is the opposite of that indicated by Model 1.

Conclusion

To minimise errors when using Excel's NPV function:

  • Clarify any aspect of the model specification that is ambiguous.
  • Ensure that you understand the implicit assumptions made by Excel's functions, such as the NPV function assuming that cash flows occur at the end of each period.
  • Avoid putting constants in formulae.
  • Include appropriate documention so that the spreadsheet's users and developers are aware of the assumptions that are expected and how the model works.
  • Be careful with the timing of discounted cash flows, ensuring that the model represents the timing correctly. It is rare that the NPV function can be used without some adjustment. In many cases it may be better to discount the cash flows using formulae based on first principles, rather than using the NPV function, though such formulae can become complex.
  • Verify that named ranges reference all applicable cells.
  • Inspect every cell to check for errors. This is the only robust and reliable way to ensure that the spreadsheet is correct.

Downloads

Download the models described in this article:
NPV pitfalls

Comments

If you have any comments about this article, then please contact us.

Go to top