### Master Bank & Insurance Modeling

Master bank accounting, valuation, M&A, and buyouts with 4 global case studies based on Shawbrook, KeyCorp / First Niagara, ANZ, and the Philippine Bank of Communications.

Learn moreIn this lesson, you’ll learn how to move from a commercial bank’s Balance Sheet to its Income Statement and Cash Flow Statement, including how to calculate Net Interest Income, regulatory capital, and key operating metrics and ratios. This lesson ties together and reviews everything in the first five lessons of this module.

**Table of Contents:**

**4:31:**Step 1: Project Loans, Deposits, and Key IEA/IBL**6:56:**Step 2: Project Charge-Offs, Recoveries, and Provisions**9:00:**Step 3: Assign Interest Rates & Calculate Interest Income / (Expense)**10:44:**Step 4: Link and Flesh Out the Income Statement**13:20:**Step 5: Link and Flesh Out the Cash Flow Statement**17:28:**Step 6: Calculate Regulatory Capital and Key Metrics/Ratios (Practice Exercise)**29:38:**Recap and Summary

Master bank accounting, valuation, M&A, and buyouts with 4 global case studies based on Shawbrook, KeyCorp / First Niagara, ANZ, and the Philippine Bank of Communications.

Learn moreWelcome to the next lesson in this introductory module on our overview of banks and financial institutions. In this lesson we are going to move from a commercial bank’s Balance Sheet to its Income Statement and Cash Flow Statement, and also look at some key metrics and ratios and regulatory capital for the bank.

So in essence we are going to start out with some of those key assumptions for the Balance Sheet. Then use those to derive the bank’s Income Statement and Cash Flow Statement, and then move from both of those and from the Balance Sheet itself to the regulatory capital, the Allowance for Loan Losses and other loan loss accounting numbers, and ultimately the key operational metrics and ratios for the bank.

In this lesson, you’re not really going to learn anything new, because it really just puts together everything we’ve learned up to this point. So if you want to review, this is a good lesson to go through, and it’s also good if you’ve been skipping around and you haven’t really been paying attention, or you just skipped over some of the lessons up to this point.

[01:02]

Now, I’m going to move fairly quickly so you’re not going to be able to grasp every last detail of what we do here, but it is a very, very good summary of the first four or five lessons in this module. We’re going to show you from the ground up how to link a commercial bank’s financial statements, starting with those key Balance Sheet assumptions.

Now, the basis for this lesson is my favorite diagram in this whole module, maybe this whole course, on the value creation process for a bank. So you know that at a basic level, a bank has loans and other interest earning assets and then deposits and other interest bearing liabilities.

You multiply by the interest rates on those, or the weighted average to get to the Interest Income, and interest expense, you net those against each other to get to the Net Interest Income, you factor in the Provision for Credit Losses, Non-Interest Income, and Non-Interest Expenses, you subtract Taxes to get to the Net Income, you divide by Equity to get to the Return on Equity.

[02:02]

And then you compare it to the Cost of Equity to see whether or not the bank is actually doing anything useful. And, of course, everything is backed up by regulatory capital.

So this is what the diagram looks like at a high level; the difference is that in this lesson we’re going to move into it at a much lower level. And you can see the model on screen right here. We have a bank’s Balance Sheet with the Assets side right here, with Liabilities & Equity right below it, and then Regulatory Capital below that.

Then you have the bank’s Income Statement, we also have the Loan Loss Reserve Calculations, and the bank’s Cash Flow Statement. We have some Off-Balance Sheet Assets for the bank as well, and then we have their Key Operating Metrics and Ratios over here. So we’re going to go through this step-by-step and you’re going to learn how to do everything here and how to think about this process.

Now the steps in this process are the following. First off, you have to start by projecting the loans, deposits, and the key interest-earning assets and interest-bearing liabilities.

[03:01]

Then you have to project Charge-Offs, Recoveries and the Provisions for Credit Losses so you can figure out what the Allowance for Loan Losses will be.

Then you can assign interest rates to everything and calculate the Interest Income, Interest Expense, and Net Interest Income.

Then you can go back and flesh out the Income Statement and pull in everything from your calculations to get the Net Interest Income there and then to project all the other items.

Then you can move to the Cash Flow Statement and reflect all those Balance Sheet changes as well as the items that flow in from the Income Statement like net income for example. And then at the end, you tend to calculate regulatory capital and the key metrics and ratios.

Now, in real life you don’t follow this exact order because, for example, you sort of have to look at regulatory capital earlier on, because when you’re filling out the Cash Flow Statement, when you get to the dividends line item. You can’t just project some arbitrary number for dividends.

[04:02]

It has to make sense for the bank’s regulatory capital levels. So it’s not exactly this order in real life, but this is roughly what you are doing, even if you look at certain smaller components of one of these steps earlier on.

So let’s go into Excel now. I’m going to do the first part of this for you, and then the second part of it and much of the latter part of this exercise will actually be an exercise for you where you have to go and complete it yourself.

To start with on the Assets sides, projecting loans, deposits, and key interest earning assets & interest bearing liabilities. So in real life, the key point here is to figure out how much the loans you’re increasing by, we just set this to a $100 or a $100 million increase in this period. In reality, we would probably tie this to the GDP growth rate and the bank’s market share, but that’s what we’ve set up for now. And then based on that, and a new deposit number which we’d also probably tie to the loans in some way, we could project everything else on here.

[05:04]

Now in our simplified example, we’re not quite going to do it that way. Instead what I’m going to do is just keep all of these items constant for now. So starting from the top, Available for Sale Securities will be kept constant, Other Securities will be kept constant. These would normally be projected as a percent of loans or as a percent of deposits, but we don’t have our loans or deposits yet, so we’re just going to do that.

Now for the Gross Loans we take our Gross Loans figure, we add our Loan Additions, and then we also have to factor in charge-offs but we don’t have those yet so we’re going to leave that part alone.

We don’t have the Allowance for Loan Losses yet. Goodwill & Other Intangibles we’re going to leave alone, and then Other Assets we’ll also keep constant for now. On the Liabilities side, most of these we’re just going to keep constant, so Deposits, Senior Debts, Subordinated Notes, all of these really, we’re holding constant.

And again in real life you would probably link all or most of these to the Loans on the Assets side.

[06:02]

But we’re just not doing that so we get numbers that are a bit cleaner here. We can add up all of these.

Now Common Stockholders’ Equity, technically we shouldn’t even be projecting it, but we’ll do it anyway. Just take the beginning number and then like you would for a normal company, it’s the same concept here. You link in the Net Income to Common at the top of Cash Flow Statement, and then you factor in any Common Stock Issuances and then any Dividends to Common as well, because those are going to reduce this number.

For Preferred Stock we just take our old number and then we add the Preferred Stock Issuances, this might also be projected as a percent of Loans or Deposits. Then we can add this up. And then for Total Liabilities and Equity we can just add these two numbers together. And for the Balance check, we can go up and just take our Total Assets and subtract our Liabilities and Equity. It’s not going to balance right now because we’ve left so much of this blank up here.

[06:56]

So we’ve done what we can on the Balance Sheet for now. So we’re actually done with Part 1 here, with Step 1. Let’s move into Step 2 and look into the Charge-Offs, Recoveries, and Provisions.

So here the assumption is that we’re adding $100 in loans, and we expect that we will lose $5 on those loans. So 5% will go into default, will go bad, and we’ll never get the money back for them. We’ve started off a beginning Reserve Balance of $10 so we need to add $5 to it, and then during this period when we add the $5, we also charge off $5, but we recover $2. So maybe we collect collateral that’s worth that amount, or maybe some of these borrowers actually come back to us and miraculously pay us back.

Whatever the case, we’ve somehow gotten some amount of the payments that were owed on these loans we had assumed were just completely charged off.

So to calculate this, we just take the Gross Charge-Offs and the Recoveries, and that gets our Net Charge-Offs, which are $3, really negative 3, here.

[07:57]

The Annual Reserve Balance is just the beginning balance plus these Net Charge-Offs really minus the charge-offs and plus the recoveries, and then we add our Provisions right here, so get to $12. Now, we have to go back and then flesh this out on the Balance Sheet.

So let’s take what we have here, the old number, the loan additions, and then we can factor in the Net Charge-Offs right here. And then for the Allowance for Loan Losses, let’s go over here and just link to our Ending Reserve Balance right there, so we have that. We can sum this up.

So we’re moving closer now and that’s actually about all we have to do on this. Really the hard part of this is figuring out how to project these items in the first place which we’re not really doing here. Generally, as I said in the preceding lessons, these are going to be linked to the historical loan balance, and the percentages of these items as percentages of that loan balance. It might be linked to what pure companies are doing or something else like that.

[08:59]

So let’s actually move into Step 3 now and look at the Interest Rates and the Interest Income and Expense on all these items. This part is actually fairly easy, because we already have all our interest rates up here, and then down here for the Liabilities and Equity. To make things a bit easier I’m just going to use Beginning Balances here, so let’s take our Beginning Cash, we don’t what our Ending Cash is quite yet. Let’s take this and then multiply by the Interest Rate right here and then copy this down so we have that.

And then we have for Goodwill & Other Intangibles and then Other Assets here at the bottom. So we have that. And then we can sum up everything here.

[09:55]

And so we have that.

Notice, of course, how the ones that have 0% Interest Rate also have a 0% Risk Weight, which makes sense because if they’re not generating interest, chances are there’s no probably no risk or very little risk associated with them.

Now, on the Liabilities & Equity side we can do pretty much the same and take the Beginning Balance times the Interest Rate in each case, copy this down, and then do the same thing for Preferred Stock down here and so we have that. And then since we’re at the bottom we might as well just sum up everything above to make sure our numbers are adding up. So we’ve done that.

So we have all these figures for the Interest Income and Interest Expense, and at this point we can actually go over to Step 4 and link and flesh out the Income Statement.

[10:54]

So for the Interest Income, this is coming directly from the Balance Sheet. Let’s take our summed up Interest Income down here 111. For the Interest Expense, this one’s a little bit different because we don’t want to count the Preferred Stock Interest Expense, at least not yet, because this is not really Interest Expense. This is actually a Dividend. It’s a Preferred Stock Dividend and it’s going to affect Net Income and get us Net Income to Common, but it should not be in this calculation for now.

So let’s just sum up everything right here. You have that, and let’s flip the sign as well. We can then sum up the Net Interest Income to get that, Net Fee & Commission Income and Non-Interest Expense are already filled in.

If we did not have these, we would have to project them ourselves based on the underlying metrics for these business divisions. We don’t know what those are, so we’re just leaving in what we have for now. Provision for Credit Losses is just going to be linked to our number over here for our Loan Loss Reserve Calculation math.

[11:57]

And again, we don’t have the actual Net Charge-Offs here, just the expectation of future of losses which of course can be adjusted upward if there’s some large unexpected loss that the bank did not provision for. So then, Pre-Tax Income, let’s just take our Net Interest Income, our Fee & Commission Income, Provision for Credit Losses, and then Non-Interest Expense. So we have that.

And then Pre-Tax Income we’ll multiply by the Tax Rate which, in case you forgot, we have up here. So Tax Rate, and then Net Income. And then for the Preferred Stock Dividend, remember we have this calculating on our Balance Sheet analysis below. So we can just go in and this is not really Interest Expense for the Preferred Stock. It’s a Preferred Stock Dividend, so we can just link to that directly. And then we get to our Net Income to Common below all of that. So Net Income minus that Preferred Stock Dividend. And that’s really about all we have to do for the Income Statement.

In real life, yes, it gets more complicated. There are more categories, but that’s the basic principle.

[13:02]

Everything gets an interest rate, you figure out the Interest Income, and interest expense for those. And then you link them and show it on the Income Statement and you factor in these other items like the Provisions for Credit Losses that are coming from other schedules.

Let’s move to Step 5, Linking and Fleshing Out the Cash Flow Statement.

So for this, I think the names here are pretty straightforward, but let’s go through and do this. Once we’re done we also need to go back and link the cash on the Balance Sheet to the bottom of the Cash Flow Statement, and look for anything else that is not properly linked right now.

So as a starting point we’re always going to use Net Income to Common in most of our models anyway on the site. Even when companies in other countries present their Cash Flow Statements differently and you use the direct method, or start with pre-tax income, we generally think it’s easiest and best to start with net-income.

[14:00]

For the Provision for Credit Losses, we need to go up, and take this and then reverse our Non-Cash charge right here. Then for Changes in Operating Assets & Liabilities, we’re only going to list the addition to Gross Loans. Why? Because remember, the only thing affects the Net Loans number in the Balance Sheet are, #1, the Provision for Credit Losses, and #2, the Additions to Gross Loans.

We’ve already factored the Provision for Credit Losses, so all that we really have to do at this point is factor in the Additions to Gross Loans. And we can go up and take that number from right there.

Now for Changes in Other Securities, let’s go and get our Beginning Balance and subtract our Ending Balance. We’re on the Assets side so if this increases we want it to be a negative. And then Changes in Other Assets, go over here and then subtract the ending one. Changes in Deposits, this is considered an operational item for a lot of banks, so we take our Ending Balance and then subtract our Beginning Balance right here.

[15:00]

And then we can sum these up to get our Total Cash Flow from Operations. Now, the Sales and Purchases of AFS Securities and Intangible Assets are going to be $0 or very minimal in most cases. But I’ve set it up like this, because now we can hard code a number for at least one of these. So I’m going to set the Purchase of Intangible Assets to $0. AFS Securities we can link to our beginning number, and then just subtract our ending number right here. So we have that. And it comes out to $0, which is pretty common for this one.

For Cash Flow from Financing, let’s go and take our Ending Senior Debt, and subtract our Beginning Senior Debt, and then we can apply that same formula really to everything else here. For the Preferred Issuances, this one’s going to be a hard-coded number, the same for Common Stock Issuances, and same for Dividends to Common.

[15:57]

Now with all these what you do, we already have the Common Stockholders’ Equity linked to those dividends. But for the others, if we didn’t have them linked, we’d need to go and actually flesh that out and do it. So for the Goodwill & Other Intangible Assets still on the Balance Sheet, we take the old number, and then we simply subtract whatever we have right here. So if this is a negative, meaning it’s a purchase, then our figure here goes up because we purchased new Intangible Assets. So that’s just a quick example of how that works.

For Cash, let’s take our beginning number and then factor in the Net Change in Cash at the bottom of the Cash Flow Statement which we actually haven’t calculated yet. But we can go back down and do that. So negative $72.4. To put everything together we can actually sum up Total Assets now and see what this comes out to.

[17:00]

And then we can sum up Total Liabilities & Equity and see that this is also correct. Common Stockholders’ Equity is linked to the correct items here. You can go in and test it yourself if you want to see exactly how this works. But for the most part, our jobs on the Balance Sheet here are done.

So we have this in place, and this should be a good review of what these items mean and how they link together for now.

The next step, really the last one here, is to calculate Regulatory Capital and then the Key Metrics and Ratios for commercial banks.

So let’s move back into Excel for this part of it, and previously we just completed the Balance Sheet, the Income Statement and the Cash Flow Statement, and linking some of those items.

For this part to really, to really finish this off you have to calculate the Regulatory Capital, and then you also have to go over here and calculate the Key Operating Metrics and Ratios for this bank.

[17:58]

So this part I am actually going to make your exercise. Go through it and make all these calculations yourself. For the Regulatory Capital, you’re going to have to do for the beginning point in time and the ending point in time, because there are two points in time listed on the Balance Sheet, so you can go through it and do it for all that.

Calculations are laid out here pretty well. And then you can calculate the Assets and the Ratios, and some of the other Balance Sheet metrics at the bottom.

Once you’re done with that, go over and then calculate in just this one column, these ratios for Net Charge-Offs, Return on Equity, Common Equity, Tangible Assets, Assets, and so on and so forth and all of these.

Now if you don’t remember the formula or how to calculate some of these, go back and take a look at the Excel files in the preceding lessons. Look at the ones for the Balance Sheet, the Income Statement and Cash Flow Statement, Regulatory Capital, the Provision for Credit Losses and so on. And you can find the formulas and the explanations for all of these in the slides in the Excel files.

[18:57]

But pause this video right now, try it yourself, and then when you’re done, un-pause it, come back, and then we will walk through it together.

Okay, good. So to start with for Common Equity Tier 1, let’s go up and take our Common Stockholders’ Equity and then subtract Goodwill & Intangible Assets from the Assets side of our Balance Sheet. Preferred Stock, we can just link in from our Balance Sheet, and then we can just add these up to get to our Tier 1 Capital.

We don’t have any weird adjustments for Non-Controlling Interests or anything like that here. To simplify it, I just kept out all of that. With a real bank of course you might see it, but I felt it wouldn’t add that much here, so we just decided to leave it out.

Now for Tier 2 Capital, of course, we’re going to have Convertible Bonds because they’re hybrid instruments. And then Subordinated Notes, so we can add that. In real life, for both these, you might only have a portion that actually qualifies to be counted as Tier 2 Capital. But, again we’re simplifying it a little bit, and just assuming that the whole thing qualifies.

[20:02]

For the Allowance for Loan Losses, let’s go up and take our Allowance right here and then multiply by the percentage that qualifies to be included in Tier 2 Capital, so 50%. We have that, and then we can add up our Tier 2 Capital. Total Capital is just Tier 1 Capital plus Tier 2 Capital. And then for the Total Risk-Weighted Assets, so once again we’re going to the SUMPRODUCT function for this.

Let’s get our Assets over here. Since we are in the Beginning column here, we’re just going to be dealing with the Beginning Assets, and then the other one of course we’re going to be using the Ending Assets.

So let’s take our Beginning numbers over here, and then our Risk Weights over on the right most column on our Balance Sheet. We’re going to anchor the column part of the Risk Weights because we don’t want that to shift around when we copy this over.

[20:59]

So we have that. And then we need to risk weight our Gross Loans right here. Once again I’m going to take our Risk Weight and then I’m going to anchor the column part of that. We use another SUMPRODUCT, even though it’s a little bit silly because we just have two items for these next two, Goodwill & Other Intangibles and Other Assets.

And then we’ll anchor the column part of the Risk Weights there as well. So we have everything on our Balance Sheet. And then remember we also need to factor in Off-Balance Sheet Assets. So let’s go down here, and let’s take our balance, and then we’re going to actually anchor the whole thing there because we’re going to use the exact same area even as we copy this over. And then multiply by the conversion factor for all of those. So we have that.

For Total Tangible Assets, let’s just go up and take our Total Assets and then subtract Goodwill and Other Intangible Assets. We have that, and then we can start calculating some of these ratios.

[21:57]

For the Tangible Common Equity ratio, in this particular case, Common Equity Tier 1 is the same as Tangible Common Equity if you think about it.

That’s not necessarily true in real life, they’re often close, but usually there will be some further adjustments to Common Equity Tier 1, like what we saw with the non-controlling interest adjustment in the lesson on regulatory capital. But in this case I simplified it a little bit and so they’re the same number.

So we can just take this and divide by Total Tangible Assets. For Common Equity Tier 1, it’s the same numerator but we’re dividing them by Risk-Weighted Assets now. For Tier 1 Capital, it’s the Tier 1 number here divided by Risk-Weighted Assets. And then for Total Capital, Total Capital divided by Risk-Weighted Assets. And for the Leverage Ratio, it’s our Tier 1 Capital divided by Total Tangible Assets. So you have this.

And then moving down, Net Loans/Total Assets, we want to see how dependent the bank is on loans to generate Interest Income and other income from its’ assets. So we can just take these and divide them. Deposits/Total Liabilities and Equity. Similar concept, but on the other side of the Balance Sheet now.

[23:04]

These are both very similar, and then finally Net Loans/Deposits. So how well can a bank cover its’ net loans with the deposits that it has on hand, and we have that. And at this stage, we can actually copy all these over now. So I’m just going to take them, press Shift and then Ctrl+R to copy all these over, and so we have that.

As you can see here, basically all these ratios go up by a fair amount. I shouldn’t say all of them, but at least all the regulatory capital ratios and the ones where the Tangible Common Equity go up. And that if you look at it, is really because our Common Stockholders’ Equity is going up by a fair amount, but our funding sources are staying roughly the same.

And then our Gross Loans are also going up by a fair amount, our Cash balance going down, but that doesn’t really matter because the cash itself is not going to factor into regulatory capital in that way.

[23:59]

It will factor into the Leverage Ratio, but nothing else really. So that is how this works.

Now let’s go over here and look at some of these other metrics and ratios. For some of these, you are going to be using averages, so you have to be a bit careful. For the Net Charge-Off Ratio, let’s take the number here and then divide by the average Gross Loan balance.

So let’s take our beginning Gross Loan and then our ending one right here, so we have that. And then for Net Charge-Offs/Reserves, we can just take our Net Charge-Offs and divide by the ending Reserve Balance to get this. And then for the Reserve Ratio, let’s just take our ending balance and then pair it with the ending Gross Loans balance.

We could also try to take some type of average here, but it wouldn’t really make sense, because the reserve from one period lines up with the Gross Loan number in that period. So we can take either the beginning one, or the ending one here, but it wouldn’t really make sense to try to average these as you do when you’re mixing Income Statement line items with Balance Sheet line items.

[24:59]

We have that. Now for these returns based metrics, these are mostly going to have Net Income to Common, or Net Income for the Numerator, and then the denominator is going to vary. So for Return on Common Equity, we want Net Income to Common here because we’re leaving out preferred stockholders. And then we can divide by the average Common Stockholders’ Equity.

Notice here how we’re not subtracting our Goodwill & Other Intangibles because that is the definition for how you get to Tangible Common Equity or Common Equity Tier 1. But Common Stockholders’ Equity, as shown on the Balance Sheet, is different and we’re not subtracting that out here.

So let’s get that, and then for the Return on Tangible Common Equity, this is the one where now we are actually going to be subtracting out Goodwill & Intangibles. So Net Income to Common, and then let’s take the average Intangible Common Equity ratio. Which again, here is the same as Common Equity Tier 1. So let’s just take the average of those.

[26:03]

Now, for the Return on Assets, here we’re actually going to be using Net Income, because we’re not subtracting anything out on the Assets side, or on the Liabilities and Equities side. We’re actually just including everything as-is.

So we’re not including a smaller subset of assets. It’s not as if we’re going in and subtracting out the portion of assets that correspond to preferred stock for example. So we are just going to take our Net Income and then divide by the average Total Assets right here.

And then for the Return on Tangible Assets, let’s take our Net Income and then go down and average our Total Tangible Assets. For the Net Interest Margin, we’re going to take our Net Interest Income and then we have to divide it by the average Interest-Earning Assets.

In this case we don’t call them out explicitly, but it’s pretty easy to tell that only four of these actually earn interest. So we’re just going to be dividing by the average for these four.

[26:59]

Available for Sale Securities, Other Securities, Gross Loans, and then Other Assets down here. So let’s go in and take all these, and so we have that. Now for the Average Interest Rate on Interest-Earning Assets, we can actually take almost the same formula and then just change the numerator because the same assets are still earning interest here. It’s just that in the numerator, we want the Interest Income now.

So let’s go up and take Interest Income instead. And then for the Average Interest on Interest-Bearing Liabilities, let’s take our Interest Expense and then divide by Deposits + Senior Debt + Subordinated Notes + Other Borrowings.

[28:01]

Convertible Bonds here do not have interest, so we’re not going to factor them in. And then Preferred Stock technically does have interest, but really the interest here is referring to the dividend on preferred stock and we’re not counting that within Net Interest Income on the Income Statement. So that one works a little bit differently.

But these other four we will factor in and then let’s add up the other side here under Ending. And then for the Spread we can just take the Average Interest on the Interest-Earning Assets minus the Average Interest on the Interest-Bearing Liabilities, and so we have that.

And once again, the Net Interest margin does not quite match up with the Spread as you normally expect. For Net Interest Income/Total Revenue, let’s just take this and then we can take our Net Interest Income plus the Net Fee & Commission Income, and so we have that. The Overhead Ratio is just the Non-Interest Expenses divided by Net Interest Income plus Net Fee & Commission Income right here.

[29:03]

And then finally the Dividend Payout Ratio is just our Dividends to Common. I’m going to put a negative sign in front because these will appear as a negative on the Cash Flow Statement, and then divide by our Net Income to Common. This is $0 for right now because we have no dividends.

But those are the types of metrics you would calculate. We went over the meaning and importance of these in the earlier lessons and you’re going to be seeing them again and again throughout this course. But this is just an additional way for you to get some more practice with these, and with working with the financial statements and the other schedules that you’ll typically see for a commercial bank.

So to do a quick recap and summary here. When you link a bank’s financial statements, you always start with the Loans, Deposits, and the Key Interest-Earning Assets and Interest-Bearing Liabilities. So in this case, we started by looking at the Balance Sheet and keeping many of these items almost the same, except we did assume some loan additions here which did change things a little bit.

[29:59]

Then you have to look at the Charge-Offs, the Recoveries, and the Provisions for the Loans, and we did that over here, in this schedule for the Loan Loss Reserve calculation. Then you have to think about the interest rates on the Interest-Earning Assets and Interest-Bearing Liabilities and calculate Interest Income or Expense. Interest Income and Expense really, and then the Net Interest Income.

So we went down and did that for everything that’s relevant on both sides of the Balance Sheet. We got to the Interest Expense and then for the Assets we got to the Interest Income, we summed those up and netted them against each other to get to the Net Interest Income down here. And that’s how we got to the first part of our Income Statement.

We fleshed out the Income Statement by linking in the Provision for Credit Losses from the supporting schedule. Some of these other numbers were just hard-coded numbers that we don’t have any insight into for now. But we would actually project if it were a more complex model. And then, the usual item for Taxes, Preferred Stock Dividend, and Net Income to Common at the bottom.

[31:00]

Then we did the same thing for the Cash Flow Statement, and started with Net Income to Common, we added back non-cash charges, the main one being the Provision for Credit Losses. You’ll also see Depreciation, Amortization, and other things here.

We factored in changes to the Operational Assets and Liabilities which we defined as Gross Loans, Other Securities, Other Assets, and Deposits. Then we got to the cash flow from the Investing section which often has AFS Securities, Intangible Assets, CapEx as well. And then the Financing section, which is mostly about the liabilities and equities side of the Balance Sheet, and the different funding sources a bank has outside of customer deposits. We got to our Net Change in Cash, and then we went back and linked in everything properly on the Balance Sheet.

And then in the final part you got practice yourself with calculating regulatory capital. Down here, going from Common Equity Tier 1 to Tier 1 to Tier 2 to Total Capital, Risk-Weighted Assets and calculating many of these metrics and ratios.

[32:02]

And then you finished it off by calculating the Operational Metrics and Ratios over here. The Returns-based ones, the Net Interest margin, the Spread, metrics to track a bank’s profitability, how much in dividends it’s paying out, and so on.

So that’s it for this lesson. Coming up next, you’re going to get a three-statement interview question-style model for a commercial bank that will let you input different variables and assumptions, change them around and then see how the three financial statements change when you make it change to gross loans, or the provision for credit losses, or Interest Income, or anything else like that, that you want to modify. We’ll also be going over, after that, some common accounting interview questions for commercial banks.

Brian DeChesare is the Founder of Mergers & Inquisitions and Breaking Into Wall Street. In his spare time, he enjoys lifting weights, running, traveling, obsessively watching TV shows, and defeating Sauron.