We have designed the ILAAP ALM LCR reports template validator product for banking professionals, consultants, implementation teams and model auditors. Those responsible for asset liability management and risk management model testing, validation and audits within banking and other deposit taking institutions. The tool is designed to validate report templates using small sample datasets from core banking applications.
You would benefit from the product if you use purchased software or legacy systems for ALM & Basel III report generation and analysis. Or rely on reports generated by the ALM and risk functions of the bank. Use the validator as a tool to full fill the annual model audit and validation recommendations under the Basel supervisory guidelines.
ILAAP, ALM, LCR and NSFR reports require fairly complex calculations. Rather than build a model again and again for each client or report, the ILAAP, ALM report validator requires the user to input data and parameters in the input sheet to generate the required templates. Once he sets and finalizes the data and parameters, they can use the sheet as a quick validation cross check on system-generated reports.
In addition to working as a validator by exposing the underlying calculations in the working tabs for each report the validator also serves as a great teaching tool for understanding how to prepare ALM, ILAAP and LCR reports.
Please note that this product is only a validating, educational and implementation support tool, not a functional reporting platform. It uses a number of simplifying assumptions and approximations to test if the ILAAP and ALM platform being tested is generating accurate reports.
About the product
We have designed this product to decode the black box that surrounds Asset Liability Management and Basel III Liquidity Framework Reporting. The ILAAP, ALM, LCR, NSFR report validator is an EXCEL spreadsheet that takes in the financial statement/ balance sheet data as input and then transparently shows the calculations and generates output for the following 10 reports:
Asset Liability Management (ALM)
- Price Sensitive Gap
- Liquidity Gap
- Rate Sensitive Gap
- Cost to Close
- Net Interest Income at Risk
- Earnings at Risk
- Fall in Market Value of Equity (MVE)
- Balance Sheet Duration
Basel III Liquidity Framework
Two reports that are key reforms of Basel III liquidity framework to promote a more resilient banking sector:
- Liquidity Coverage Ratio (LCR)
- Net Stable Funding Ratio (NSFR)
The EXCEL spreadsheet also includes a “Charts and Graphs” dashboard that gives an at-a-glance view of the liquidity profile of the entity. View the distribution across asset and liability categories for different residual maturity buckets,
To the distribution of assets and liabilities across residual maturity buckets,
To the distribution of the liquidity gap across buckets.
The EAR & MVE graphs further show the level of earnings and capital at risk as a result of interest rate shocks (as determined using a Value at Risk approach):
The transparency behind the transaction-wise calculations in the ILAAP, ALM, LCR, NSFR Report validator spreadsheet is intentional. In addition to being a validator it also lends itself to helping grasp the principles and fundamentals behind ALM and Basel III liquidity framework reporting.
Once users understand the calculations behind reports, they can extend the spreadsheet to cater to specific asset and liability portfolios and use these extended worksheets to validate reports generated by in-house and external vendor software.
Product Specifications for the ILAAP ALM LCR Report Validator
- Input:
- 500 rows of assets and 500 rows of liabilities
- Report parameter definition
- Asset & liability category dropdown definition
- 262 days revaluation rates for 21 tenors
- Working Tabulations :
- 10 working tabs with exposed calculations including an individual transaction calculator
- Output Reports :
- 11 reporting tabs consisting of the 10 reports & 1 charts and graphs dashboard
- Supporting document:
- ILAAP, ALM & Liquidity Report Validator – How to guide
Prerequisites
Familiarity with EXCEL, financial statements, local markets, banking industry and asset liability management. Data set for asset, liabilities at a transaction or pool level and interest and deposit rates for given maturity tenors and buckets.
Availability
The revised and upgraded edition of the ILAAP, ALM, LCR report template editor is now available on our store.
Special offer.
For the first two weeks of launch from 26th April to 10th May you can buy the ILAAP ALM LCR Report Validator for a special price of US$1,699. Off $1,300 from its full price of US$2,999. The offer comes with a 48 hour money back guarantee. If you are not happy with the product, we will be happy to do a full refund within 48 hours of your purchase.
If you are interested in the special offer drop me a line and I will be happy to share the special discount code.
ILAAP ALM LCR Report validator. A short review
a. Product History
We have put together a number of risk models on this site on customer request. The ALM Validator is no different. In 2012 a client reached out to us and asked if we had something he could use to validate reports coming out of his newly implemented ALM software. As is typical with large ticket item software purchases, there are no vendor-sponsored validation models that you can use to validate reports. That led to version 1.0. We are now on the third major release of our four year old Excel based report validation template. The most recent release includes support for a chart based dashboard, Liquidity Coverage Ratio (LCR) and Net Stable Funding Ratio (NSFR) reporting.
b. Product Limitations
- Reports are created around existing asset and liability definitions. If the user defines new categories/ types, incorporate the same in the working & reports for their results to be visible. The user should proceed with caution as such changes impact the entire worksheet and the output/ results.
- Use the drop down where it exists (in the data input tabs). Do not input a value in a cell where a drop down is present. Select a pre-defined value from the drop down.
- Certain drop downs are editable, however, do not change others like Payment Frequency, Claim Type, Claim Security, Re pricing Frequency, Accounting class, Asset class, Include in Owners Equity (OE), Amortizing method unless the user needs to change them to cater to their specific portfolios. In such instances please note that the accuracy of the results will most likely be affected as the existing work sheet have only been tested within the bounds of existing choices.
- Specific tenors are mentioned for the revaluation rates term structure and the validator only caters for these. The tenors are 7-day, 15-day, 30-day, 60-day, 90-day, 120-day, 180-day, 270-day, 1-year, 2-year, 3-year, 4-year, 5-year, 6-year, 7-year, 8-year, 9-year, 10-year, 15-year, 20-year and 30-year.
- We use a 365 day look back for the validator with 262 trading days. The validator will cater to an input of look back days less than or equal to 365 days/ 262 trading days only.
- Do not change tabs marked as “Working-…” unless the user needs to make structural changes to the reports (new types, etc).
3. Data Capture and input
Input Balance Sheet tab
Capture data in the INPUT BALANCE SHEET tab of the worksheet. Depending on the input field, the user needs to either enter data against the field or select an entry from a pre-defined drop down.
For the calculation of the Liquidity Coverage Ratio (LCR) and the Net Stable Funding Ration (NSFR), the user must manually enter the LCR Title & NSFR Title fields against each transaction in the INPUT BALANCE SHEET tab of the worksheet.
We suggest that you first copy the relevant title from the relevant report tab (from REPORT-LIQUIDITY COVERAGE RATIO or REPORT-NET STABLE FUNDING RATIO tabs under column Item as shown in the screen shot below). Then paste (paste special as value) the same against the relevant balance sheet transaction row in the given field.
This will ensure that the value for that transaction rolls up against the respective title the report tabs.
Title Definition & Dropdown tab
Certain drop downs like Title, asset/ liability Type are user definable (in the Titles Definitions & Drop downs tab). However, do not change other like Payment Frequency, Claim Type, Claim Security, Re pricing Frequency, Accounting class, Asset class, Include in OE, Amortizing method.
The above figure shows the area in the Titles Definition & Drop downs tab where existing transaction titles have been defined. The user may define new transaction titles here too. If you define a new transaction title, select an appropriate transaction Type from a pre-defined drop down. The Type drop down may also be edited by the user.
Note: the reports have been prepared keeping the existing Types defined in mind. If you define new Types, first incorporate them in the working & reports for their results to be visible. This is a simple extension of the existing EXCEL spreadsheet but the user should proceed with caution as any change could impact the entire worksheet.
Do not change other drop downs as mentioned above. We have marked these clearly in orange in the Titles Definitions & Drop downs tab.
Revaluation rates for the Earnings at Risk, Fall in MVE & Balance Sheet Duration reports are also entries by the user. You may enter these rates in the Revaluation Rates input sheet. Note that you need to provide rates for the specified tenors.
We use a look back period of 365 days, as defined on the Report Parameters tab. For this worksheet, you may define look back days for a period less than or equal to 365 days and greater than 10 days. See Report Parameters below.
Input – Report Parameters Tab
The user may also change the following on the “INPUT – Report Parameters” tab:
- Confidence Level
- Holding period
- Days to analyse (look back days)
- Term Structure for Price Sensitive Gap & Cost to Close (Rates & Shift (bps))
ILAAP ALM LCR reports model building and validation services
Our team also builds country and client specific models with country specific data for the US, United Kingdom and a number of markets in Western Europe and Far East. We also build up and populate ILAAP ALM report validator models for clients using their internal data extracts. If you are interested in exploring this service, drop us a line at jawwad at the rate financetrainingcourse dot com with your specific needs.