This page has been archived and commenting is disabled.

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

 


- advertisements -

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
Tue, 02/12/2013 - 12:51 | Link to Comment hedgeless_horseman
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

Tue, 02/12/2013 - 12:59 | Link to Comment notbot
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...)

Tue, 02/12/2013 - 13:01 | Link to Comment nope-1004
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.

 

Tue, 02/12/2013 - 13:06 | Link to Comment LawsofPhysics
LawsofPhysics's picture

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

Tue, 02/12/2013 - 13:15 | Link to Comment goldfish1
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

Tue, 02/12/2013 - 13:26 | Link to Comment fourchan
fourchan's picture

there are no errors.

Tue, 02/12/2013 - 13:31 | Link to Comment Ness.
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

 


Tue, 02/12/2013 - 13:49 | Link to Comment waterwitch
waterwitch's picture

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

Tue, 02/12/2013 - 22:47 | Link to Comment NaN
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.

 

Tue, 02/12/2013 - 14:37 | Link to Comment Biosci
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.

Tue, 02/12/2013 - 17:13 | Link to Comment RockyRacoon
RockyRacoon's picture

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

Tue, 02/12/2013 - 13:25 | Link to Comment earleflorida
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 :(

Tue, 02/12/2013 - 14:21 | Link to Comment insanelysane
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.

Tue, 02/12/2013 - 15:02 | Link to Comment Dr Benway
Dr Benway's picture

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

Absolutely incredible.

Tue, 02/12/2013 - 14:33 | Link to Comment LibertarianX
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

 

Tue, 02/12/2013 - 13:17 | Link to Comment Larry Dallas
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?

Tue, 02/12/2013 - 13:56 | Link to Comment hedgeless_horseman
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

Tue, 02/12/2013 - 14:29 | Link to Comment tradewithdave
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.

Tue, 02/12/2013 - 14:58 | Link to Comment tbone654
tbone654's picture

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

Tue, 02/12/2013 - 12:50 | Link to Comment Quinvarius
Quinvarius's picture

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

Tue, 02/12/2013 - 13:24 | Link to Comment machineh
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!

Tue, 02/12/2013 - 12:52 | Link to Comment Cognitive Dissonance
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.>

Tue, 02/12/2013 - 12:57 | Link to Comment Lohn Jocke
Lohn Jocke's picture

Fracking Cylons...

Tue, 02/12/2013 - 16:40 | Link to Comment Panafrican Funk...
Panafrican Funktron Robot's picture

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

Tue, 02/12/2013 - 12:51 | Link to Comment francis_sawyer
francis_sawyer's picture

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

Tue, 02/12/2013 - 13:52 | Link to Comment waterwitch
waterwitch's picture

Just get Timmah to do them for you.

Wed, 02/13/2013 - 09:45 | Link to Comment Titus
Titus's picture

+1000

Tue, 02/12/2013 - 12:53 | Link to Comment gmak
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.

Tue, 02/12/2013 - 12:58 | Link to Comment adr
adr's picture

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

Tue, 02/12/2013 - 13:09 | Link to Comment Cognitive Dissonance
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.

Tue, 02/12/2013 - 13:28 | Link to Comment machineh
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).

Wed, 02/13/2013 - 09:46 | Link to Comment Titus
Titus's picture

Bing-fucking-o. 

Tue, 02/12/2013 - 13:05 | Link to Comment hooligan2009
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)!

Tue, 02/12/2013 - 12:57 | Link to Comment adr
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?

Tue, 02/12/2013 - 13:28 | Link to Comment riphowardkatz
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...

 

Tue, 02/12/2013 - 12:58 | Link to Comment williambanzai7
williambanzai7's picture

Is this positive contagion?

Tue, 02/12/2013 - 12:58 | Link to Comment LawsofPhysics
LawsofPhysics's picture

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

Tue, 02/12/2013 - 13:00 | Link to Comment Praetorian Guard
Praetorian Guard's picture

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

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

Tue, 02/12/2013 - 13:09 | Link to Comment hooligan2009
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! :) )

 

Tue, 02/12/2013 - 13:26 | Link to Comment Sweet Pea
Sweet Pea's picture

hyperinflation?

Tue, 02/12/2013 - 13:29 | Link to Comment spellbound
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.

Tue, 02/12/2013 - 13:02 | Link to Comment Sweet Pea
Sweet Pea's picture

well they sure fucked up Word.  FTW!

Tue, 02/12/2013 - 13:03 | Link to Comment Sweet Pea
Sweet Pea's picture

oh poop, wrong place

Tue, 02/12/2013 - 13:03 | Link to Comment Rusty Shorts
Tue, 02/12/2013 - 13:04 | Link to Comment suteibu
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.

Tue, 02/12/2013 - 13:09 | Link to Comment DrDre
DrDre's picture

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

Tue, 02/12/2013 - 13:10 | Link to Comment A Lunatic
A Lunatic's picture

Bullshit........

Tue, 02/12/2013 - 13:11 | Link to Comment The Master
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.   

Tue, 02/12/2013 - 13:18 | Link to Comment hedgeless_horseman
hedgeless_horseman's picture

 

 

One of my best friends is a JPM analyst slave.

If you were my best friend, and I was in that situation?

Tue, 02/12/2013 - 13:26 | Link to Comment Dr. No
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.

Tue, 02/12/2013 - 13:28 | Link to Comment Pairadimes
Pairadimes's picture

Well, at least it didn't hurt bonuses. After all, what have they to do with performance?

Tue, 02/12/2013 - 13:33 | Link to Comment i_call_you_my_base
i_call_you_my_base's picture

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.

Tue, 02/12/2013 - 13:49 | Link to Comment AndrewJackson
AndrewJackson's picture

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.

Tue, 02/12/2013 - 13:41 | Link to Comment philosophers bone
philosophers bone's picture

Dear Valued JPM Shareholder,

We have recently discovered that our dividends paid since 2008 were made at a time when we were insolvent, which our lawyers advise is a breach of corporate law and unfortunately invalid.

Please send us a cheque in the amount of the dividends improperly paid to you, failing which we will exercise our right to sell securities in your account and apply the proceeds to us (me).

Faithfully yours,

JD.

Tue, 02/12/2013 - 13:46 | Link to Comment AndrewJackson
AndrewJackson's picture

Start throwing enough excel models at a problem and eventually it will disappear and be forgotten. Mezmorize them by bs.

Tue, 02/12/2013 - 13:56 | Link to Comment buzzsaw99
buzzsaw99's picture

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

Tue, 02/12/2013 - 14:01 | Link to Comment BrigstockBoy
BrigstockBoy's picture

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

Tue, 02/12/2013 - 14:04 | Link to Comment steve from virginia
steve from virginia's picture

 

 

 

Somehow I doubt JPM is using off-the-shelf Microsoft products for its hedging ... it can afford bespoke software and there was a glitch in it.

 

Software can be fine and background assumptions change: LTCM and Russian bonds, for instance.

Tue, 02/12/2013 - 14:07 | Link to Comment porfiriobryantdq
porfiriobryantdq's picture

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.

Tue, 02/12/2013 - 14:22 | Link to Comment buzzsaw99
buzzsaw99's picture

The increase in CIO average VaR was due to changes in the synthetic credit portfolio held by CIO as part of its...

looks like dog shit, good thing we don't step in it: http://www.youtube.com/watch?v=eY7ZX6ngOSs

Tue, 02/12/2013 - 14:29 | Link to Comment Joshua Falken
Joshua Falken's picture

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.

Tue, 02/12/2013 - 14:57 | Link to Comment writingsonthewall
writingsonthewall's picture

It might interest ZH readers to know that jpm used to own riskmetrics - an internal division which was 'spun off' for $$$'s for JM executirves.

 

Now it seems they rely on excel - rather than the industry leading software - riskmetrics.

 

Oh the irony of the short sightedness of those execs who chased short term gain and produced a ticking time bomb.

 

So there are no innocents in this - they deserved to fuck up because they were greedy.

Tue, 02/12/2013 - 15:16 | Link to Comment toys for tits
toys for tits's picture

 

 

EXCEL-lent

 

Tue, 02/12/2013 - 15:17 | Link to Comment Miss Expectations
Miss Expectations's picture

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

 

 

Tue, 02/12/2013 - 15:27 | Link to Comment Dewey Cheatum Howe
Dewey Cheatum Howe's picture

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.

Tue, 02/12/2013 - 17:19 | Link to Comment MeelionDollerBogus
MeelionDollerBogus's picture

LIBERTARIANS

Tue, 02/12/2013 - 21:14 | Link to Comment newworldorder
newworldorder's picture

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.

Thu, 02/28/2013 - 01:18 | Link to Comment resurger
resurger's picture

Let's see, no VaR backtesting?

btw, if they have reported this big VaR for years that should refrained them from dumping Customers deposits money into the IG9 Super Senior "Safe" Tranche..

fuck JPM

Do NOT follow this link or you will be banned from the site!