4 mins read
Building Deposits, Advances Maturity & Liquidity profiles for IAS 30& ICAAP reporting
As part of our work as risk managers, one routine and recurring activity is preparing the slide deck for the Asset Liability Management Committee (ALCO) meeting based on the IAS 30 accounting standard. Among other items the ALCO committee also looks at:
a) The interaction of interest rate changes, funding choices and product performance on balance sheet equity and net interest income of the bank.
b) The liquidity and maturity profile of the bank to anticipate any liquidity or funding related shocks.
The mandate of the ALM (ALCO) committee has increased with changes in the Basel II framework to accommodate liquidity and funding concentration concerns. Commonly known as Basel II extensions or the Basel III framework, the changes put a renewed focus on liquidity coverage ratio and funding concentration. To be fair both interest rate mismatch and liquidity profiling were already areas of focus under the original Pillar III Internal Capital Adequacy Assessment Process requirements.
A typical ALM (ALCO) committee discussion focuses on the following core elements
a) The interest and liquidity outlook for the markets in which the bank operates.
b) Upcoming changes that will impact or change the cost of funding of the bank or its liquidity profile.
c) The exposure of the bank to interest rate changes on account of the funding and investment choices it has made.
d) Examination of likely and the extreme case scenarios on both interest income and liquidity
e) Review of exposure and sensitivity limits to interest rate shock, interest rate mismatch and funding gap.
f) Review of suggested reaction and responses of the bank to a given scenario based on the interest and liquidity outlook discussed above.
Bank Deposit & Asset Maturity profile for ALM
None of the above analysis is possible without a detailed deposits and advances liquidity and maturity profile for the bank. While reasonable amount of detail is available for treasury and portfolio positions of the bank, it is generally a much harder task to generate the same level of detail for all advances and deposits.
Even if the required data is available given its size and magnitude most analysts end up making crude and rough approximations or alternatively only do the work once every few years and reuse the same analysis for reporting purposes. While this was acceptable behavior a few years ago the renewed focus on maturity and liquidity now requires us to do the profiling on a more frequent basis.
Most ALM (ALCO) mandates also include preparation and review of a Liquidity Contingency Plan (LCP). Once again the LCP requires a detailed maturity and liquidity profiling of deposits and advances book. While the LCP has many different components, a number of those components rely on the current snap shot of the bank’s balance sheet to determine the appropriate course of action. The maturity profile is that snapshot.
The challenge however is the availability of calculation engines and tools. High end ALM tools are expensive. Even if you have access, most tools require some amount of pre-processing on the data set before they are able to work with them. A acceptable short cut is to do the ground work in Excel and then import the summarized analysis in your ALM toolkit.
The primary objective of this course is to learn how to use available tools in Excel to do the work using a simple framework and the powerful Pivot Table functionality.
We use a sample case of a small midsize bank with about 7,500 – 8,000 lending accounts (borrowers) and 40,000 deposit accounts (lenders) with a portfolio of 5+ deposit and 5+ lending products.
Using this case and this associated data set we will walk through:
a) The process of preparing the bank data set for deposits and advances.
b) Completing the analysis required to prepare the liquidity and maturity profile of both advances and deposits of the bank.
c) Focus on five dimensions of analysis for Assets and Liabilities. The five dimensions are
- Size (For deposits and advances)
- Cost (rates for deposits and advances)
- Concentration (both by numbers and amounts for deposits and advances)
- Maturity (same bucketing used for deposits and advances)
d) Lay the foundation for linking this work with the ALM models and tools discussed separately in the ALM crash course.
When we are done with our work we will have:
a) A valid defensible basis for determining the distribution of assets and liabilities across the various maturity buckets for use by our ALM tool.
b) The outline of a slide deck reviewing the maturity and liquidity profile of advances and deposits that can be used for ALM (ALCO) committee meetings.
c) The staging area using Price and Reset Gaps for our ALM analysis.
Building Advances & Maturity profile – Target Audience
CFO, CRO’s, Members of the Risk, Treasury, FINCON, Liquidity and Regulatory reporting team members who don’t want to rely on technology resources to build their models. At the end of this course you will know where to get your data, how to pre-process it, how to use Excel Pivot Table and Charts to build your own desktop based million dollar ALM platform (not that we recommend it, we would rather you buy our ALM platforms).
Advance Maturity & Liquidity Profile – Methodology & Sample output
Here are some limited sample extracts from the Excel spread sheet that we will build in this session.
1. The Core Banking Dump
We start off with the detail account level dump from our core banking system and pre-process it. The fields in blue are from the core banking dump. The last 5 fields are added through a simple algorithm to help us with our later analysis using Excel Pivot Tables.
2. Step 2 – Pre-processing advances data.
When the Pre-processing cycle is complete we generate a Pivot Table that allows us to create a two dimension matrix that plots the size of a deposit against it scheduled contractual maturity in Months.
3. Step 3. Generating Presentation Graphs using Pivot Charts
Once the Table has been reconciled we go ahead and produce the sample output shown below.
Deposit Maturity Profile – Sample output
Deposit Size versus Maturity Profile
Deposit Cost versus Size
Advances Profile – Loan Size versus Maturity
Advances Profile – Segment versus Maturity
Advances Profile – Product versus Maturity
Related Posts
Liquidity Risk - Cost to Close Liquidity Gap
ICAAP: Stress Test: Liquidity Risk
ICAAP: Stress Test: Liquidity Risk
