Maturity Profile of Loan Assets / Liabilities - an ALM Model

Banking and other financial institutions prepare statements of maturity profile of assets and liabilities as part of their Asset Liability Management (ALM) exercise - for regulatory and risk management purposes. This spreadsheet model demonstrates to show, how such statements can be prepared in Excel spreadsheet. The spreadsheet gives a summarized and meaningful picture of the organisation's asset / liability profile - by capturing the entire data on financial assets / liabilities with various maturities in a single statement - reflecting the maturity profile of the complete portfolio of assets or liabilities.

As the loan / asset data with maturity dates is entered in 'Data' sheet of this workbook, the 'Main' sheet instantly captures the cashflows from loan assets into various user defined time buckets. The assets can also be given quality indicators and the data in Main sheet summarizes the asset quality category under various maturities. The time buckets can be defined in 'Setup' sheet as per user requirements. The spreadsheet can also be used for creating Liabilities' maturity profile, in similar manner. No Macros/VBA been been used in this spreadsheet model - instead, it uses VLOOKUP, ARRAY, SUMPRODUCT functions of Excel to summarize the data. Download Maturity Profile Model.