Asset and Liability (ALM) Excel Model

Description
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.