Re: Liquidity and Cash reserve requirements
Yes, you can add an extra row somewhere for reserve/liquidity requirements. We didn't add this explicitly because if the bank is even remotely close to this threshold, there's really no point in building a standalone run-rate model - they'd be considered distressed and the FDIC (in the USA) would ha... Yes, you can add an extra row somewhere for reserve/liquidity requirements. We didn't add this explicitly because if the bank is even remotely close to this threshold, there's really no point in building a standalone run-rate model - they'd be considered distressed and the FDIC (in the USA) would have already taken action. Read More

Re: Provisioning IFRS 9
Expected credit losses are an input to the model. We base on historical trend absent internal asset level details. Therefore, assuming management is following the guidance, as they ought to be, then business as usual for modeling.

Re: Zero coupon bond vs. non-interest bearing debt
Zero coupon bonds still have an interest rate. They are typically sold at a discount and using regular bond math to calculate interest expense (and rate) means they are interest bearing. Separately, there is no cash outflow for the interest on the zero coupon until fill payment at maturity.

Re: bank's credit costs calculation
Yes, you have the right idea. Just make sure to match time periods. Total Provisions is typically an Income Statement item and Total Loans is a Balance Sheet item.

Re: Return Column and Row Vales for the minimum value in the table
Hello, there are a couple of ways to approach this, depending on how inflexible (or flexible) your table is to begin with. For now, we have a solution that only works if you're 100% sure that the minimum value appears exactly once. Open a blank workbook and enter your sample data. The numbers ... Hello, there are a couple of ways to approach this, depending on how inflexible (or flexible) your table is to begin with.

For now, we have a solution that only works if you're 100% sure that the minimum value appears exactly once.

Open a blank workbook and enter your sample data. The numbers will be in a square from B2 to D4.

First, to identify the minimum value, we can easily use the MIN function: =MIN($B$2:$D$4). This gives us 2, which is correct. Let's enter this formula into H2 for now, as a placeholder.

Next, to identify its column number within the overall table, we can "calculate" it by going through every column and looking for a match. For the columns with no match, we will get a 0, and for the columns with a match, we will get a 1.

You can visualize this search by imagining the columns in our table as [2, 3, 4].

Then, imagine a separate table (matrix) of 0s and 1s, where everything is a 0, except a 1 for your matching search value (aka "TRUE"). The 1 will be in the far bottom-left corner in this case.

We can use SUMPRODUCT to combine these and correctly look for the 2 in [2, 3, 4]. In H3, enter the formula as such: =SUMPRODUCT(COLUMN($B$1:$D$4)*($B$2:$D$4=H2))

It can seem a bit confusing at first, but basically it's returning a 0 for all those 0s in the matrix where we don't have our match, then multiplying the 1 for our found search value by its corresponding column number from that [2, 3, 4]. Of course, 1 times any number will just return that number, which is what we want.

So this gives us 2, which is correct, because our minimum value exists in column B.

We can apply the same logic with ROW instead of COLUMN for the row. Let's enter this in H4:

=SUMPRODUCT(ROW($B$1:$D$4)*($B$1:$D$4=H2))

This gives us 4, which is correct, because our minimum value exists in row 4.

You could sanity check this by entering =ADDRESS(H4,H3) which gives us $B$4: the exact location of our minimum value.

Again, remember that this will completely break if your minimum value exists in more than one place in the table, because the SUMPRODUCT will be a very inflated number. To fix this, we could wrap everything in an IFERROR that would return something like "[multiple minimum values]", but at that point, it might be better to just write a very simple VBA macro to spit out all of their locations.

Please let us know if that helps. If the SUMPRODUCT is not clear, try entering its two parameters separately into a blank range to help see it visually, or come back for further discussion. Read More