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

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.

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?

The core problem with VaR is the assumption of gaussian distributions for rare events, but the statistical distribution of rare events is usually unknowable because there are not enough data points. In practical terms, that means systemic risk is ignored.

I agree with the "clearly incompetent" comment. Even if the formula is wrong, is there no one at JPM that knows if the calculated number is reasonable??? It was half of what it should have been and no one noticed. Incompetence or fraud, take your pick.

...wouldn't SarBox put the blame directly on Dimon as he had to personally guarantee results and accurate accounting?

The Rule of Law and personal responsibility for a fiduciary?

What the fuck are you thinking, Larry?

This is 21st Century America.

Relax and buy some moar GM and JPM stock.

Fascism...

"Corporate Power is Protected - The industrial and business aristocracy of a fascist nation often are the ones who put the government leaders into power, creating a mutually beneficial business/government relationship and power elite."

Indeed you were correct prior to the Citizens United v FEC decision. Now the corporation itself is the responsible "person". Tough to imprison so we will fine it, assuming "ripple effects" don't exceed the prosecutot's personal feelings of right and wrong. Think of it as "Rule of law lite". A government of men trying their best to "get it right" in the absence of law enforcement.

As any internal auditor can tell you - spreadsheets are rife with operational risk - Controls are only as good if people use them. If there is unrestricted access to the spreadsheet - anyone can modify it at any time -even in error when trying to see the impact f a small change.

If this s/s was designed properly, with project controls, then either there was insufficient verification and testing, or someone accidentally grabbed the wrong version when moving it into production, or someone made an uncontrolled change at some point for whatever reason.

From the note, it sounds like they are saying that there was insufficient testing and verification initiallyl to ensure that the s/s met the specifications of the designer.

Or..............the 'error' was introduced way back when the leadership decided they wanted to play with more risk without letting anyone know, so they planted the 'error' to provide a plausible excuse if the project shit the bed.

Or it was just a 'rookie' mistake.

Sorry folks. But I don't believe a single word out of these fracking thieves.

not a bad error considering that most VaR calcs are at the 99% or 95% confidence levels over the next month (expectation that 99% or 95% of outcomes will be better than that suggested by the VaR calc)!

Not shocked at all. They will do whatever they want and offer any excuse necessary without concern of accountability. This is not news. News will be the event that causes the whole thing to fall apart.

One of my best friends is a JPM analyst slave. It's not shocking that there would be an error of this kind given that these analysts work 18 hour days, 7 days a week toiling with mindless sensitivity analyses, pitchbooks, etc. With the churn in analyst pools there is also no guarantee of continuity or smoothness in reporting metrics. I'm sure this type of error is a pandemic in the finance industry.

No software validation? sheeze. I not allowed to even do a simple sum in excel during a design review without a validation of the worksheet. And these guys are publishing finacial reports? I guess it comes down to risk. No lives are at stake, only sheeples investment money. Therefore, dont bother validating.

I did some technology work for a few of the bigger banks and was shocked at how much they depend on Excel to make calculations instead of using real systems. They have entire systems just to manage their excel models. Totally pathetic technology.

I second this. Real systms, though, are much harder to fudge numbers. One has to wonder, if there is some alpha and omega cell, sittin somewhere on a secret excel sheet that will yield the jp morgue insolvent 10000x over.

VaR is what JPM says it is. Earnings are what JPM says they are. That is all you need to know.

As for how long this error was on the books, one can venture a guess: many years?

From bloomie:

The OCC, which oversees national banks, didn’t catch a January 2012 change in the way JPMorgan calculated so-called value-at-risk for the chief investment office, one of the people said. The revised model, which was later scrapped, cut the firm’s reported risk in half and ultimately exacerbated losses by underestimating their potential size as they began to mount, Chief Executive Officer Jamie Dimon told lawmakers in June...

This type of amateurish monitoring of critical risk factors isn't limited to JPM. It's pervasive at all financial institutions. And what's even more concerning is that the supposed crack regulators at the OCC and FDIC are not qualified to validate or correct these deficiencies. Guaranteed that JPM has OCC examiners on site and yet this goes on for years?! And people wonder how such a "sophisticated" system can break down. It's smoke and mirrors all the way around...

I'm not surprised. Many years ago I used an Excel sheet to keep track of and to collateralize the gov't accounts at First Chicago. I was the one who switched it to an actual application and it was shocking how under/over collateralized the various accounts were.

The VaR at JPM, GS and MS all went up as they sacked their prop desk traders and stuffed the empty chairs with twentysomething structured/packaged product sales traders and algo/HFT programers. This is mostly facilitation and arbitrage, not position risk, so profits and VaR numbers skyrocket all funded from ZIRP.

My favorite part is where Tyler approaches the jury box, his voice displaying his incredulity, and he asks:

"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?"

I am going to enjoy old west style justice unleashed on all these corrupt individuals when the institutions finally lose their last shreds of public credibility. The truth has a way of making or breaking credibility in the end.

When you get past all the "goblygook" the central issue is Bank Supervision. If a bank is not thoroughly examined by its regulator, then everything is suspect. It has been proven many times that our alleged Independent Regulators are at the very least inept at regulating.

Unless the Executive and Legislative Branches of government demand regulation, NOTHING IS GOING TO CHANGE. By their actions to date these 2 branches, appear to not want any financial regulation to occur.

There is no other way of looking at what has been happening since 2008.