HOW A ROOKIE EXCEL ERROR LED JPMORGAN TO MISREPORT ITS VAR FOR YEARS / ZERO HEDGE

How A Rookie Excel Error Led JPMorgan To Misreport Its VaR For Years

Submitted by Tyler Durden

on 02/12/2013 11:47 -0500



Just under a year ago, when JPMorgan's London Whale trading fiasco was exposed as much more than just the proverbial "tempest in a teapot", Morgan watchers were left scratching their heads over another very curious development: the dramatic surge in the company's reported VaR, which as we showed last June nearly doubled, rising by some 93% year over year, a glaring contrast to what the other banks were reporting to be doing.



Visually:

Specifically we said that "in the 10-Q filing, the bank reported a VaR of $170 million for the three months ending March 31, 2012. This compared to a tiny $88 million for the previous year." JPM, which was desperate to cover up this modelling snafu, kept mum and shed as little light on the issue as possible. In its own words from the Q1 2012 10-Q filing: "the increase in average VaR was primarily driven by an increase in CIO VaR and a decrease in diversification benefit across the Firm." And furthermore: "CIO VaR averaged $129 million for the three months ended March 31, 2012, compared with $60 million for the comparable 2011 period. The increase in CIO average VaR was due to changes in the synthetic credit portfolio held by CIO as part of its management of structural and other risks arising from the Firm's on-going business activities." Keep the bolded sentence in mind, because as it turns out it is nothing but a euphemism for, drumroll, epic, amateur Excel error!


How do we know this? We know it courtesy of JPMorgan itself, which in the very last page of its JPM task force report had this to say on the topic of JPM's VaR:

... a decision was made to stop using the Basel II.5 model and not to rely on it for purposes of reporting CIO VaR in the Firm’s first-quarter Form 10-Q. Following that decision, further errors were discovered in the Basel II.5 model, including, most significantly, an operational error in the calculation of the relative changes in hazard rates and correlation estimates. Specifically, 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.... It also remains unclear when this error was introduced in the calculation.
 

In other words, the doubling in JPM's VaR was due to nothing but the discovery that for years, someone had been using a grossly incorrect formula in their excel, and as a result misreporting the entire firm VaR by a factor of nearly 50%! So much for the official JPM explanation in its 10-Q filing that somewhat conveniently missed to mention that, oops, we made a rookie, first year analyst error. As for how long this error was on the books, one can venture a guess: many years?



And if this glaringly amateur error was present in America's largest bank by assets, and one which proudly boasts a "fortress balance sheet", an error which just so happens feeds into countless other input cells driven by the firm's VaR calculation, leading to capital allocation, trading, and overall executive decisions many of which have a direct impact on the firm's exposure to $72 trillion in over the counter derivatives, what can one say about the thousands of other banks, which are not as closely "supervised" by the Federal Reserve as JPMorgan is (supposedly).


Or how about Europe's far more troubled banks?


Is there really any wonder why after reading humiliating reports like this one that nobody, and certainly not the banks themselves, trust any other banks, and why the Fed, contrary to false rumors of a recovery, is forced to inject some $85 billion in bank cash every month, most of it going to offshore banks as we previously reported?