Cash flow financial modeling 101 for legacy commercial banking

So you are a well-rounded cash flow financial modeler. You have fair experience in modeling non-financial ventures. You are calm and confident. You victoriously solved any modeling task you faced before. The positive feedback you get from your clients only reinforces this stance.

But life’s full of surprises. And here’s today’s piece of news: your next project is modeling a commercial bank. Yikes! You don’t have FIG experience, but that does not seem to bother your client. Your first draft is expected in two weeks. The deadline is fast approaching.

No worries, you say to yourself. I have a lot of experience to build upon. I probably start from projecting sales… Wait, a bank does not have sales? OK, it has income, then income it is. Then… what is RWA? Risk-weighted assets? Weighted how? Based on Basel recommendations? What are those? And yeah, my client also wants to see LCR and NSFR ratios (not a slightest idea what it is supposed to mean) calculated in the model and the corresponding met at all times. Damn, what do these mean? OK now, there’re probably good tutorials written for just such an occasion… Hey, what do you mean there aren’t? Hell, what a freaking mess!

That’s exactly the situation I found myself in back in the day. I was not the best one to take the project, but I was the only one. And I had to break through the lack of my understanding the hard way.

Surprisingly, today the situation does not look much better. Sure, there are decent materials on commercial banking (how there could not be?). And yet almost none of them are designed for or by modeling community — at least none that I’m aware of. Yes, you have banking financial models, and books on regulation, and materials on valuation of financial companies. But if you need concise and up to the point quick start tutorial on banking for modelers — good luck finding it.

That’s what I undertook to fix. I put together a terse tutorial article to help non-FIG cash flow modelers quickly grasp the essentials of modeling commercial banks. I’ve encountered a lot of bumps along my way to do it at the time and trust me I don’t wish you the same.

So here’s the deal. To get started, you need to get yourself acquainted with the three pillars of banking essentials: business model, regulation, and modeling aspects. That’s what the article below will give you. As I will make clear, regulation plays a crucial role in banking modeling, so I pay a due attention to it — in both academic and practical terms. We’ll cover Basel III in appropriate detail here.

But if you think at this point, is it just another article on Basel regulation, then you are knocking on the wrong door. The real power of this tutorial is its practical orientation — we do everything in Excel! For starters, I will illustrate the calculation of key Basel III metrics and ratios (RWA, CAR, LR, LCR, NSFR). Then we’ll move on to the heart of the tutorial: step-by-step building of a full-fledged commercial banking model meeting essential regulatory requirements. That’s right: all underlying Excel files are part of the article. Theory and practice, all fused into a hands-on, ready-to-use quick-start guide.

As they used to say on TV, But wait! There’s more!. Besides bit-by-bit development of Excel model complying with Basel III requirements, I introduce you to cash flow valuation of banks. Just like any other aspect of banking, its valuation is somewhat peculiar. But don’t worry, I’ll get you covered.

After completing this tutorial, you’ll have a strong foundation in modeling commercial banks. You’ll be well equipped to build on it in any way as you project calls for.

Welcome to the journey! Hope you’ll enjoy it.