Modeling Problems in Excel


Even though the whole "London Whale" trading loss at JP Morgan last year made for great theatre, we largely ignored it here on the blog and in discussions with our clients. There are two reasons for that. First of all, a proprietary trading loss is not something community banks have to deal with. And second, the size of the loss was pretty small compared to the uproar it caused. Don't get me wrong, as that many zeroes is painful for anyone to absorb. But, it helps to put in perspective what that looks like to a multi trillion dollar institution. For a typical $250 million community bank, the loss would equate to somewhere around $870,000. Painful? You bet. Will it put you out of business? No way.

However, some really interesting (and relevant to us) facts came out of the report from the internal investigation. James Kwak sums it up in a post at The Baseline Scenario called "The Importance of Excel." Here are a few of the highlights:

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

Wow. An elementary excel error blew up an exceptionally sophisticated model at one of the world's foremost financial institutions. And it wasn't found until over $8 billion was long gone. How many internal functions are you handling with spreadsheets? If you are like most banks, there are probably hundreds of different spreadsheets that are driving dozens of business decisions in your bank. These include major functions, like underwriting loans, reconciling accounts, production reports, and board packages. I have reviewed lots of these, and the majority of them major errors. It happens when there is that degree of manual input, often done by employees that do not have sufficient Excel skills. This is becoming a bigger focus even in small banks, as regulators are qualifying many of those spreadsheets as models (read this for an explanation) and applying the Model Risk guidance. Be careful with those spreadsheets. The mistakes might just be leading you to the exact opposite decision you should be making. This was one of the driving factors in the creation of our Drill Down Reports - automating the internal reporting process to remove the Excel mistakes. You can expect to continue having to upgrade many of these "models" and reports, thanks in part to our buddy the London Whale.