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

Tyler Durden's picture

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?

h/t manal

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
hedgeless_horseman's picture

 

 

Will you pay $2,000 to have a kid read 12 books of your selection?

Excel 2010 For Dummies, 2010
Greg Harvey, $ 13.70
Understanding Excel is a foundation skill of many 21st Century jobs, appears on most resumes, but actually resides in very few brains.
Reward: $ 150.00

notbot's picture

Wow. Speechless.  

How does a firm with derivatives 5x global GDP rely on an excel spreadsheet for VaR?

(Not that it really matters what you use for VaR, since it's a ridiculous metric, but still...)

nope-1004's picture

This corroborates what I've said all along:  Not only is JPIG insolvent, but they are also clearly incompetent.  Without the taxpayer teet, this pig would have been dead long ago.

Now go deposit your check with them..... lol.

 

LawsofPhysics's picture

Does anyone have any idea how many pension funds are parked with this pig?  would be useful information.

goldfish1's picture

I'm a lay person...what does VaR stand for?

 

VAR Value Assurance Review *

VAR Vulnerability Assessment Report *

VAR Value Added Reseller *

VAR Value At Risk

Ness.'s picture

Value at Risk is measured in three variables: the amount of potential loss, the probability of that amount of loss, and the time frame.

http://www.investopedia.com/terms/v/var.asp#ixzz2Khttef2U

 


waterwitch's picture

I'm a lay person too, but I infer it to be "Value at Risk"

NaN's picture

VaR is an embarassing measure to begin with. Some discussion of the criticism:

http://www.nakedcapitalism.com/2012/05/jp-morgan-loss-bomb-confirms-that-its-time-to-kill-var.html

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.

 

Biosci's picture

There's no reason to assume this pig is any worse than the other pigs.  Best to assume all pension funds are at the same risk.

RockyRacoon's picture

I just hope the same intern is not calculating the trajectory of the upcoming meteor event.  Things could get dicey!

earleflorida's picture

how to make a 10:1 spread with a $100 deposit times 106 = 1012 in the year of 366 days?

steralized Re-Rehypothecation using 'tinytimahexcel`turbo-spread sheets'... it's that simple :(

insanelysane's picture

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.

Dr Benway's picture

Yeah and that was the ONLY spreadsheet in the entire organization that calculated VAR?

Absolutely incredible.

LibertarianX's picture

Back Testing the VaR model should pick this up

What the fuck were they doing??

I thought they claimed to be good at this shit

 

Larry Dallas's picture

I may be wrong here, but wouldn't SarBox put the blame directly on Dimon as he had to personally guarantee results and accurate accounting?

hedgeless_horseman's picture

 

 

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

 

http://rense.com/general37/char.htm

tradewithdave's picture

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.

tbone654's picture

=if(vlookup(A1,B$1:D$1000,3,FALSE)="we're fucked", "lie", "dance")

Quinvarius's picture

I blame Steve Balmer.  When he finally quits, MSFT and JPM will both surge. 

machineh's picture

Ballmer would tell Dimon that JPM should be using MS Paint, not Excel.

After all, that's what the Fed does.

MOAR GREEN!

Cognitive Dissonance's picture

Plausible deniability bitches. When all else fails blame the fracking computer program and the data input.

<And never admit that garbage in produces garbage out because then you must explain why you were putting garbage in.>

Panafrican Funktron Robot's picture

It would strike me as reasonable for JPM to blame WDR for this.  

francis_sawyer's picture

Will this work for me when I go to pay my taxes?

gmak's picture

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.

adr's picture

That would be if it really was an error, instead of an intentional change to cook the books. 

Cognitive Dissonance's picture

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.

machineh's picture

Structurally, it suggests that VaR is just a number they generate for the annual report, not a tool actually used to manage risk (which was its purpose).

hooligan2009's picture

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)!

adr's picture

So if I enter $542k in the refund section of my 1040 and balme it on a TurboTax error I get to keep the money right?

Worked for Tim Geithner.

How much of the record corproae cash is nothing but a spreadsheet error?

riphowardkatz's picture

It already worked for this lady except she got 2 million, only problem was she lost the debit card and asked for a new one.
http://www.huffingtonpost.com/2012/06/11/krystle-marie-reyes_n_1586502.html

And states can do due diligence on pension fund investments...

 

williambanzai7's picture

Is this positive contagion?

LawsofPhysics's picture

Nothing changes until there are real consequences for bad behavior (intentional or not) at all levels of society - hedge accordingly.

Praetorian Guard's picture

Can someone explain what Karl Denninger meant by saying "it has started"????

http://market-ticker.org/akcs-www?post=217385

hooligan2009's picture

this?

http://market-ticker.org/akcs-www?blog=Market-Ticker

(and you will never get a spartacus badge with a name like that! :) )

 

spellbound's picture

@Praetorian Guard

I too was scratching my head about that comment. It sounded ominous and imminent. The comments below were referring to gasoline shortages I believe.

Sweet Pea's picture

well they sure fucked up Word.  FTW!

suteibu's picture

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.

DrDre's picture

When in doubt, blame the rookie analyst  ....

A Lunatic's picture

Bullshit........

The Master's picture

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.   

Dr. No's picture

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.