ILAAP ALM LCR Reports Template Validator

7 mins read

This product is designed for banking professionals, consultants, implementation teams and model auditors 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 rely on reports generated by the ALM and risk functions of the bank.  The validator serves as a tool that can be used to full fill the annual model audit and validation recommendations under the Basel supervisory guidelines.

ILAAP ALM LCR Report Validator.
A summarized charts based dashboard supported by 11 tabs of exposed calculation and 10 report templates. ILAAP ALM LCR Report Validator.

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 data and parameters to be imported into the input sheet to generate the required templates.  Once data and parameters have been set and finalized, it can be used as a quick validation cross check on reports being generated.

Exposed tabs for importing data, setting report parameters, report templates and working calculations.

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 ALM, ILAAP and LCR reports are actually generated.

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

This product is designed 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)

  1. Price Sensitive Gap
  2. Liquidity Gap
  3. Rate Sensitive Gap
  4. Cost to Close
  5. Net Interest Income at Risk
  6. Earnings at Risk
  7. Fall in Market Value of Equity (MVE)
  8. 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:

  1. Liquidity Coverage Ratio (LCR)
  2. 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 – from the distribution across asset and liability categories that can be viewed for different residual maturity buckets,

ALM Reports-Distribution-Assets-validator
Figure 1: Distribution of assets Total bucket across categories
ALM Reports-Distribution-Assets-2-validator
Figure 2: Distribution of assets in the “Upto 1 Month” bucket across categories

To the distribution of assets and liabilities across residual maturity buckets,

ALM Reports-Distribution-DTM-validator
Figure 3: Distribution of liabilities across days to maturity (DTM) buckets

To the distribution of the liquidity gap across buckets.

ALM Reports-LiquidityGap-DTM-validator
Figure 4: Cumulative Liquidity Gap by DTM Bucket

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):

ALM Reports-MVE-validator
Figure 5: Fall in Market Value of equity from different confidence levels

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

  • 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


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.


The revised and upgraded edition of the ILAAP, ALM, LCR report template editor goes on sale on Tuesday 26th April 2016 for two weeks.

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 Report validator. A short review

a. Product History

A number of risk models on this site were put together 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 were no vendor sponsored validation models that could be used 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 new categories/ types are defined, the same would need to be incorporated in the working & reports by the user for their results to be visible. The user should proceed with caution as such changes impact the entire worksheet and the output/ results.
  • Where a drop down is present (in the data input tabs) it should be used. 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, others like Payment Frequency, Claim Type, Claim Security, Re pricing Frequency, Accounting class, Asset class, Include in Owners Equity (OE), Amortizing method should not be changed 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.
  • 365 day look back is used 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.
  • Tabs marked as “Working-…” should not be changed unless the user needs to make structural changes to the reports (new types, etc). 

3.  Data Capture and input

Data is captured 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.

The revaluation rates data input sheet

For the calculation of the Liquidity Coverage Ratio (LCR) and the Net Stable Funding Ration (NSFR) LCR Title & NSFR Title fields are to be manually entered by the user against each transaction in the INPUT BALANCE SHEET tab of the worksheet.

The title definition and drop down entry sheet.

It is suggested that the relevant title is first copied 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) and then pasted (paste special as value) 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.

Certain drop downs like Title, asset/ liability Type are user definable (in the Titles Definitions & Drop downs tab) while others like Payment Frequency, Claim Type, Claim Security, Re pricing Frequency, Accounting class, Asset class, Include in OE, Amortizing method should not be changed.

The above figure shows the area in the Titles Definition & Drop downs tab where existing transaction titles have been defined. New transaction titles may also be defined here by the user. If a new transaction title is defined, an appropriate transaction Type should be selected from a pre-defined drop down. The Type drop down may also be edited by the user as seen in the figure below.

Note: the reports have been prepared keeping the existing Types defined in mind. If new Types are defined, the same would need to be incorporated in the working & reports for their results to be visible. This should be a simple extension of the existing EXCEL spreadsheet but the user should proceed with caution as any change could impact the entire worksheet.

Other drop downs as mentioned above should not be changed. These are clearly marked in orange in the Titles Definitions & Drop downs tab.

Revaluation rates used in the Earnings at Risk, Fall in MVE & Balance Sheet Duration reports are also taken as entries by the user. These may be entered in the Revaluation Rates input sheet. Note that specified tenors have been mentioned for which rates need to be provided.

A look back period of 365 days has been used above as defined on the Report Parameters tab. For the purpose of this worksheet, look back days may be defined for a period less than or equal to 365 days and greater than 10 days (see Report Parameters below).

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 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 please drop us a line at jawwad at the rate financetrainingcourse dot com with your specific needs.