Asset and Liability (ALM) Excel Model
Originally published: 05/06/2018 15:24
Last version published: 22/08/2018 16:54
Publication number: ELQ-31439-3
View all versions & Certificate

Asset and Liability (ALM) Excel Model

ALM Model to compute Earnings at Risk and Equity at Risk for a TIER 3 bank or Credit Union

The model computes the Earnings at Risk , Equity at Risk, VAR of trading securities and Liquidity gap. Inputs to the Model are the market data, Chart of Accounts at the portfolio level, terms of the contracts at the portfolio level computed as balance weighted average. It makes two broad assumptions. It simplifies the world of amortization to P&I and Interest Only. Further, the interest rates are static after the third year up until which you can input the rates in the maket data sheet. Portofolio level information forms the basis of Chart of Accounts. All contractual terms are computed at this level using balance weighted average. Positions that are very large in isolation are required to be included as line items. Shocks provision is limited to 4, Base, and three other scenarios. Requires substantial code modification to expand for more scenarios. To circumvent that you could use the existing scenario slots to analyse more scenarios. Excel cells are numbered in the code as (row, column) with cell A2 as (2,1) and C3 as (3,3). While modifying the code to suit your bank, relative reference mode of cells are used. Report sheets should be redesigned to suit your bank. For all assistance, you may email me and let me know if you need help in customization.

This Best Practice includes
1 Excel Model + Video Walk-through

Acquire business license for $50.00

Add to cart

Add to bookmarks


Further information

Computes the first cut Earnings at Risk and Equity at Risk for a Bank

The tool is meant for inputs calculated at the balance weighted average of portfolio level of the Bank

No account level information

5.0 / 5 (3 votes)

please wait...