Five Spreadsheet Errors That Cost Millions, And What They Have in Common

It's tempting to read stories like these as being about incompetence. They aren't. Every one happened inside a serious organisation, in a file built by someone who knew what they were doing. What was missing wasn't skill. It was any mechanism that would have made the mistake visible while it still mattered.

1. JPMorgan's London Whale, and a copy-paste in the risk model

In 2012 JPMorgan Chase lost more than $6 billion on credit derivative positions. The bank's own task force report describes a new Value-at-Risk model that ran through a series of Excel spreadsheets, updated by manually copying and pasting data from one into the next. It also identified a formula dividing by a sum where it should have divided by an average, which understated volatility.

The model didn't cause the loss. What it did was make the position look less risky than it was, for long enough that the position kept growing. The report is unusually blunt about how that happened: manual steps, no automated checks, and not enough review of something that had quietly become load-bearing.

2. Reinhart-Rogoff, and a range that stopped five rows short

The 2010 paper Growth in a Time of Debt reported that growth slows sharply once public debt passes 90% of GDP. It was cited heavily in arguments for austerity across Europe and the United States.

In 2013 researchers at UMass Amherst got hold of the original spreadsheet and found an averaging formula covering rows 30 to 44 instead of 30 to 49, leaving five countries out. Alongside other methodological choices, correcting it moved the headline number substantially, and the dramatic cliff at 90% turned out to be a good deal less dramatic.

This is the most ordinary error on the list. A range that stops short of the data happens in every company, most weeks, and it's step 9 of the cleaning checklist for a reason. What was unusual here was how many decisions were resting on the output.

3. TransAlta, and a cut-and-paste in a bidding sheet

In 2003 the Canadian power generator TransAlta took a charge of roughly $24 million after what its chief executive described publicly as a cut-and-paste error in an Excel spreadsheet. The company had bid on more transmission hedging contracts, at higher prices, than it meant to.

It was literally a cut-and-paste error in an Excel spreadsheet that we did not detect when we did our final sorting and ranking of bids prior to submission.

Note the last part. There was a review before submission. It looked at the bids rather than at whether the spreadsheet producing them was internally consistent, and a misaligned paste produces numbers that look entirely plausible.

4. Fidelity's Magellan Fund, and one missing minus sign

In 1994 Fidelity's Magellan Fund told shareholders to expect a year-end distribution of around $4.32 a share. It was later withdrawn. An accountant transcribing a net realised capital loss of about $1.3 billion had left off the minus sign, so it went in as a gain. That's a swing of roughly $2.6 billion in the estimate, from one character.

5. Public Health England, and a file format with a row limit

In October 2020, Public Health England found that around 16,000 positive COVID-19 test results had never reached the national contact-tracing system. The pipeline used the legacy .xls format, which stops at 65,536 rows per sheet. Once the data went past that, the extra records were simply dropped.

The cost wasn't financial. Thousands of contacts of positive cases went untraced during a period when tracing quickly mattered enormously.

This one is the most instructive, because nothing was miscalculated. Every formula in that pipeline was correct. The system threw away input without saying so, and nobody was comparing how many rows went in against how many came out.

What all five have in common

Three things run through the whole list. The error lived in a manual step, a paste or a typed range or a transcription. The output looked completely normal, with no error value and nothing to prompt a second look. And nobody was reading the spreadsheet's internals, because spreadsheets show you results while hiding the reasoning that produced them.

How common is this, really?

More common than anyone would like. Reviews of the field-audit literature, including work by Ray Panko, report that when operational spreadsheets get inspected properly, errors turn up in most of them. Methods differ between studies and so do the exact numbers, but the direction has held for three decades: spreadsheets that have never been audited usually contain errors, and the people who built them are confident they don't.

That confidence gap is the real finding. In every case above, nobody thought they had a problem until somebody from outside looked.

Controls that are worth the effort

That last one is where AI has a genuine role in spreadsheet risk, and it isn't the role usually advertised. Reading a workbook back to you, mapping which cells feed a total, flagging hardcoded constants, spotting ranges that stop short, is mechanical work nobody has time for and nobody's ego is invested in. It would also have caught four of the five failures above. The seven-workflow guide has the prompts for it.