DIY Stress Test 2: Final Spreadsheet

[To get a sense of where and how we found the data, check out the previous entry in the DIY Stress Test.]

The Rortybomb DIY Stress Test.

So without further ado, the Google doc Rortybomb DIY Stress Test.

Directions

Type in the U3 average you expect will happen in 2010 in the top yellow box. Now ideally you type in the U3 that only has a 10% chance of happening, not what you actually expect to happen, because of the good risk management practices discussed in the previous entry. The spreadsheet will extrapolate losses based on the two data points in the Fed’s document.

If you are feeling bold, type in your own estimates of losses next to it and the spreadsheet will use those instead. So if you think the 10.3 numbers are really good, but you expect housing and credit cards to do worse than expected, type 10.3 into U3 and then some numbers into the rightmost yellow row of boxes for housing and credit cards and see how the banks would have done.

Next look at the results below. You can see the results of the stress test that came out (the “adverse numbers” one) below along with your very own. It also has a total at the bottom representing the total amount of new capital the financial sector has to raise, and a chart showing the released stress test versus your own.

I’m leaving this as a google chart. Please don’t eat it. You can click “File -> Export -> .xls” to play around with it in excel. Do use xls format, as there are multiple sheets that talk to each other (if you have any problem, tell me and I’ll fix it, but it should work in excel). Also you can click on the upper left corner of the image to publish your results to the web, and hotlink to that.

And by the way, I said back when that the U3 adverse numbers should be like 12-13%. Here’s what the chart looks like with 12.5%:

How would the market have reacted if the Fed said that the banking sector needed $390bn instead of $76bn?

FAQ

Linear interpolation, aka drawing a line, is nonsense here.

Yes. It assumes the losses between 8% and 10% U3 are the same as the losses between 10% and 12%. But notice that this biases against banks losing money, as the growth in losses are going to be positively, not negatively correlated with each other.

In addition, I simply add the increase in loss % to each individual firm's loss %. Again I believe this biases against individual banks losing money, because the errors won’t be identical here – this will tend to underestimate the bad parts of each portfolio.

There are variables other than Unemployment used in the Stress Tests.

True, but I only have two pieces of data here. If you feel that there will be green shoots in, say, Commerical Real Estate that won’t be reflected in the U3 number, go ahead and lower the U3 number a bit. Or just see the projected CRE numbers and adjust them downwards. I’d love to get into all kinds of crazy modeling here for each macro variable, but we go to war with the army we’ve got.

I would have done it this way…..

Please leave critiques in the comments or contact me directly. I think I covered most of the bases with this though. I’m a little uncertain about how to calculate the buffer capital stock remaining for those banks that didn’t need cash infusions in the stress test, but I think I went with the approach that was most generous to the banks. And “other” category doesn’t have % loss in the original numbers per bank, so I treat that as a zero intercept. At the end, these turned out to be a rounding error though.

These numbers make no sense at low unemployment, they are all zero.

Yes. Just like unemployment, there is some “natural level” of losses that we aren’t going to be able to get at with the data at hand. I wouldn’t trust this for U3 under the baseline, 8.8%.

Wow, it really seems if you go out a bit all the losses are with a few specific banks, and maybe we should look into breaking them up before they become even more of a rotting albatross on our economy’s neck.

Yup. Sure looks that way. I was actually surprised – I assume turning up the numbers a bit would cause everything to start leaking red ink. Instead it seems that if there is an additional slight downturn in the economy, we know the firms that will have all the problems. They are the ones that are too big to fail. Funny that. But I’d be curious as to your interpretations!

Comments

[J. Crighton:]

Handy, and depressing.

Good work though.

Jun 11 12:40 PM

[Jasper M:]

I wouldn't say "depressing" - model shows plenty of fairly strong banks, even at the 'bloated' end of the size curve. Shows problems concentrated in few, worthy candidates for break-up, or just plain extinction. Encourages the others, you know.

Jun 11 12:58 PM

[TradingHelp...:]

Another genius on Seeking Alpha. I get more depressed, acutely aware of my own intellectual inadequacies, every time I re-visit this site.

Jun 11 01:09 PM

[speeddaimon:]

I do wish you hadn't made the spreadsheet shareable, and only made it available for download. When I was in taking a look at it, there was another 8 people in there changing things, canceling each other's changes, etc. Made it very difficult. As a comment on the whole though, awesome work. Its nice to at least have a rough estimate (even if we are using the banks' own numbers) of what to expect when/if things get worse.

Jun 11 01:10 PM

[RiskReturnO...:]

Credit losses needs to be run by vintages to be meaningful; also, loan performance (especially secured by real estate) varies dramatically by location / state, as well as underwriting standards.

My stress tests suggest Texas banks are in the best shape, for three factors: 1) unemployment rate much lower than national average; 2) real estate bubble never really happened, so not much downside risk; 3) rising oil prices supports fast recovery in Texas economy.

Based on my adverse scenario, two Texas banks are very undervalued: TCBI (serving the upscale market); and MCBI (serving the mass market).

MCBI is trading only at 30% of book value, so more room for price appreciation. But, be careful with buying all at once, since this is a small cap name. Management owns a lot of the stock, so liquidity is an issue, but for long term investors (e.g., those willing to hold for 2-3 years), you could be looking at a 3-5x return on investment, even with the stock trading at 50% of 2006 peak level.

Jun 11 01:59 PM

[asceptic:]

Can someone please try to do a stress test on the Fed. My guess is that they will come out worst of the banks by far.

Jun 11 03:51 PM

[Jeff Nielson:]

This is a very illuminating exercise. The problem is that even with well-meaning individuals like yourself, the entire "stress-test" exercise is flawed since variables are analyzed INDEPENDENTLY - and the DYNAMICS of the combined effect of multiple adverse variables is missing.

Given that the idea of the exercise was to paint a FALSE picture of the health of the U.S. financial sector, we can be certain that the Fed's INTERNAL modeling deliberately avoided incorporating such dynamics into their own "analysis".

Throw a couple of zeros onto the end of those numbers you came up with, and we'll probably start getting close to what lies ahead.

Jun 11 05:13 PM

[Roger Knights:]

The govt. may have had a relatively pedestrian ulterior motive in concocting them that hasn't yet been pointed out (AFAIK): CYA. If a new financial crisis is coming, the gov't doesn't want to be in the position of not having a quick response to the accusation, "Why didn't you anticipate this and prevent it?"

Having asked the banks to stress-test themselves, the gov't has now put them "on record" as misleading the public in the event that we have an h-shaped recovery. (I.e., in case we hit a second waterfall, as I anticipate.) The gov't's response can then be, "Don't blame us--we tried to get to the bottom of the situation, but the banks wouldn't come clean."

Jun 11 06:36 PM

[Roger Knights:]

PS: It's because the green-gang isn't thinking outside the box about a worser-case scenario that they are oblivious to the next waterfall. (There could be a fortune made in puts on the banks in a few months.)

Jun 11 06:39 PM

[Rollerball:]

I'm with you MH, but, in the words of Dirty Harry, "... a man's got to know his limitations ..." That said, I was depressed BEFORE I got here. So, if you leave your ego at the door, how marvelous it is to learn from the more erudite.

Jun 11 06:57 PM

[Moon Kil Woong:]

A often forgotten fact is TARP was not just to help banks not go bankrupt but was also to help support the economy by getting the banks to lend TARP money which they didn't do, don't want to do, and would rather return the money than do. Even should they get the capital to be fully solvent don't they have a moral duty to rapay the American people by actually implementing the policy that TARP was meant to do, make the banks healthy enough that they could lend.

You know TARP was not just written to keep bankers employed and make Paulson rich even though many people are concluding that this was its intention. It was designed to help get the US economy back on its feet. Sadly, rather than the banks helping they have been having fun contracting the money multiplier even further. They are probably saying to themselves, "Why should we help, if the Fed wants to expand money supply do it on their own balance sheet." Unfortunately, perhaps they aren't aware that all that monetary expansion also mysteriously goes to the banks that sit on that money as well."

Personallty, all those who pay TARP back should be publically shamed for a derelection of duty to the taxpayers who lent it to them in the first place. Second, I commend the author on bringing to light the fact that it appears they are a lot less healthy than they tout when they go on their fundraising expeditions. The first question I would ask is, "Why are you still using off balance sheet accounting if you are so healthy and why are you still asking the Fed to backstop your debt?" The follow on one would be, "If you are so healthy why are you still hiding from disclosing your derivatives positions from investors?"

Jun 11 10:36 PM

[DannyBoy153:]

"Beware of geeks bearing formulas" - Warren Buffett

Jun 11 10:46 PM

[PDT:]

Very nice. Thanks again.

Jun 12 09:41 AM

[bbro:]

Use of the employment rate as the primary driver is extremely simplistic...
no understanding of loan loss reserves,pre-provision earnings,etc
that counteract loan losses......Do you know the answer to this question? What was the worst two year commercial bank loan loss rate? Answer: The assumption in the recent bank stress test.

Jun 14 10:57 AM

[greedcanbgood:]

Moon - You're kidding, right?

So the banks lend money, get chewed out in front of the likes of Barney Frank for making the wrong loans to the wrong people. Then the government gives them more money to lend, which they do. This time however, they tighten up underwriting and become more prudent and YOU think they should be "shamed"? What a crock. I have news for you, we can't lend our way out of this.

One thing I do agree with you however is the derrivatives positions - that's the REAL 800lb. gorilla.

Jun 16 10:08 AM