Rortybomb’s 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.

UPDATE: There’s a lot of crazy changing things that shouldn’t be changed in the google doc version. I suppose I was asking for that. I’m trying to keep up reverting the incorrect errors, but I’ve been requested to provide a version that is correct and uneditable so people can download something they know is working. Here is a lockdown version of the spreadsheet that is both correct and uneditable. Click “File -> Export -> .xls” to modify locally on your computer. You’ll have to recreate the graph if you want that.

The Rortybomb DIY Stress Test.

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



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?


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

Yes. It assume the increase in losses between 8% and 10% U3 is the same increase 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 firms 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 everyting 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!

I have some additional thoughts about where to go from here

This entry was posted in DIY Stress Test and tagged . Bookmark the permalink.

9 Responses to Rortybomb’s DIY Stress Test 2: Final Spreadsheet

  1. K. Williams says:

    Great work. But I’m confused by your conclusion. Have banks ever been required by regulators to have capital reserves sufficient to guard against a downturn that had a 10% chance of happening — which is what would be necessary for the government to have required the banks to raise $390 billion?

  2. Pingback: Thursday links: prominent investor seals of approval Abnormal Returns

  3. fredw says:

    Imagine using a worse case but U-6 instead of U-3 !

  4. Pingback: DIY Stress Test 3: A New Fed Stress Test and Size-Versus-Losses « Rortybomb

  5. Pingback: Run Your Own Stress Test « MoreNotesToSelf

  6. Pingback: Principal Writedowns and the Fake Stress Test « Rortybomb

  7. Pingback: The fake stress tests and the coming wave of second mortgage writedowns - Credit Writedowns

  8. Pingback: The fake stress tests « naked capitalism

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s