I’ve said it before, spreadsheets are great organizing tools and provide opportunities for both financial whizzes and ordinary folks to make splashy, expensive errors:

Microsoft Excel makes it easy for anyone to do the kind of number crunching once reserved for accountants and statisticians. But the world’s best-selling spreadsheet software has also contributed to the proliferation of bad math.

Close to 90% of spreadsheet documents contain errors, a 2008 analysis of multiple studies suggests. “Spreadsheets, even after careful development, contain errors in 1% or more of all formula cells,” writes Ray Panko, a professor of IT management at the University of Hawaii and an authority on bad spreadsheet practices. “In large spreadsheets with thousands of formulas, there will be dozens of undetected errors.”

Given that Microsoft says there are close to 1 billion Office users worldwide, “errors in spreadsheets are pandemic,” Panko says.

Such mistakes not only can lead to miscalculations in family budgets and distorted balance sheets at small businesses, but also might result in questionable rationales for global fiscal policy, as indicated by the case of a math error in a Harvard economics study. By failing to include certain spreadsheet cells in its calculations, the study by Harvard economists Carmen Reinhart and Kenneth Rogoff may have overstated the impact that debt burdens have on a nation’s economic growth.

There’s a reason I nominated Microsoft Excel as “The Most Dangerous Software on Earth“.

I’ve made this case in conversation several times — usually after having to forensically determine just why someone’s spreadsheet produced an unlikely answer — the greatest strength of spreadsheets is also their greatest weakness. Anyone who’s built a spreadsheet knows how easy it is to make a mistake, and how hard that mistake can be to detect after the fact. Spreadsheets are free-form: you can set up relationships on the fly, pull data from one place to plug into a different formula somewhere else. It’s literally empowering to gain that much control over your data without having to learn a full programming language.

But that flexibility and power comes at a cost: there’s no built-in error checking of your assumptions. Oh, it’ll alert you to practical problems like mis-matched data types or mechanical errors in your formula, but can’t tell you whether the operation you’re attempting makes sense. The program can’t read your mind and can’t sanity check your work.

Do a spreadsheet for your family budget and you’ll almost certainly make a minor error or two.

Make a set of inter-linked spreadsheets and you probably double the chances of error for each new spreadsheet in the set.

Make a set of inter-linked spreadsheets that require manual copy-and-paste updates and you exponentially increase the chances of error.

Then, make that manually updated set of spreadsheets have a real-world impact on vast amounts of money:

To give you and idea of how important this is here’s a great tale from James Kwak:

The issue is described in the appendix to JPMorgan’s internal investigative task force’s report. To summarize: JPMorgan’s Chief Investment Office needed a new value-at-risk (VaR) model for the synthetic credit portfolio (the one that blew up) and assigned a quantitative whiz (“a London-based quantitative expert, mathematician and model developer” who previously worked at a company that built analytical models) to create it. The new model “operated through a series of Excel spreadsheets, which had to be completed manually, by a process of copying and pasting data from one spreadsheet to another.” The internal Model Review Group identified this problem as well as a few others, but approved the model, while saying that it should be automated and another significant flaw should be fixed.** After the London Whale trade blew up, the Model Review Group discovered that the model had not been automated and found several other errors. Most spectacularly,

“After subtracting the old rate from the new rate, the spreadsheet divided by their sum instead of their average, as the modeler had intended. This error likely had the effect of muting volatility by a factor of two and of lowering the VaR . . .”

To translate that into the vernacular, the bank, JP Morgan, was running huge bets (tens of billions of dollars, what we might think of a golly gee gosh that’s a lot of money) in London. The way they were checking what they were doing was playing around in Excel. And not even in the Masters of the Universe style that we might hope, all integrated, automated and self-checking, but by cutting and pasting from one spreadsheet to another. And yes, they got one of the equations wrong as a result of which the bank lost several billion dollars (perhaps we might drop the gee here but it’s still golly gosh that’s a lot of money).

And it’s not just JP Morgan: every financial firm, every bank, every brokerage uses Excel (or another spreadsheet program). Multiply JP Morgan’s experiences by the number of companies to get a rough idea of how much is at risk from un-audited (possibly even un-audit-able) financial models running on spreadsheets.