Maturity Profile Course package available for sale 9th February 2012
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. 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 – Objectives
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 profiles – Course Options
1. Downloadable PDF file based walk through – Available 9th February 2012
2. Excel attachments – Available 12th February 2012
3. Dubai/Abu Dhabi / DIFC (UAE) Course registration – TBA (March 2012)
4. Bangkok (Thailand)/ Langkawi (Malaysia) Course registration – TBA (April 2012)
5. Online Video Course – Available 1st May 2012
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 Maturity by Maturity bucket
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
Course Instructor Profile
Jawwad Ahmed Farid is a Fellow Society of Actuaries (Chicago), a MBA from Columbia Business School (New York City) and a computer science graduate. During the last 20 years, he has worked as a consultant in North America, Pakistan and the United Kingdom with a number of blue chip clients.
Jawwad’s core areas of expertise include Asset/Risk Management, Investments, Product Development & the Financial Services Back Office. He blends a rare combination of risk management, information systems, international standards, business and product development skill set side by side with his actuarial expertise.
He is the author of three books on Entrepreneurship (Reboot), Commodity Markets (Understanding Commodities Risk) and Risk Management (Risk Application and Frameworks).
As an investment advisor Jawwad has advised a 3 billion US$ dollar life insurance fund in Pakistan on allocation and bid patterns for 10, 20 and 30 year bonds, a 30 million dollar Middle East fund on their ALM mismatch and fixed income strategy, a 10 million dollar fund on asset allocation and equity market timing. He has also worked with the securities regulator on assessing the state of the corporate bond market as well as issued valuation opinions on cross currency swaps, interest rate swaps, caps, floors, participating forwards and contingent liabilities for Exchange Guarantee Funds in the region.
Jawwad is an adjunct Faculty member at the SP Jain Campus in Dubai and Singapore where he teaches courses on Entrepreneurship, Risk Management, Derivative Products and Project Finance. As part of the Alchemy Training practice he has delivered over 125 courses in the last 9 years to treasury, risk, corporate and investment banking customers in Dubai, Abu Dhabi, Riyadh, Kula Lumpur, Singapore, Thailand, Karachi, Lahore and Islamabad. He has been a featured speaker on campus at Columbia Business School, University of Virginia’s Darden Business School, LUMS, IBA and CBM.
In addition to being a PASHA CEC member and Treasurer he has also served as a judge at the Asia Pacific ICT Awards in Macau, Singapore, Kula Lumpur and Jakarta as well as at PASHA ICT Award in 2006, 2007, 2008, 2009, 2010. He currently serves as member of the oversight board for the PASHA Social Innovation Fund.