IFRS 9 Expected Credit Loss (ECL) Calculation Model

Description
MODEL OVERVIEW
This Excel model is designed as a ready-to-use template for calculating Expected Credit Loss (ECL) under IFRS 9 for Loans and Bonds. It supports up to 1,000 loans and 1,000 bonds simultaneously, with fully configurable user assumptions and outputs structured for quick analysis and reporting.


The model has been developed with both practical usability and auditability in mind. It is structured around clear input tables, automated calculation engines, and dynamic dashboards. The modular setup ensures flexibility for different portfolios, while maintaining compliance with IFRS 9 requirements.


The model adheres to established best practices in financial modelling, with built-in instructions, validation checks, and error controls to ensure transparency, accuracy, and ease of use.


WHAT IS IFRS 9 ECL?
Expected Credit Loss (ECL) under IFRS 9 is the forward‑looking measure of credit risk that requires financial institutions and corporates to recognize potential losses on loans, bonds, and receivables before actual default occurs. It incorporates probability of default (PD), loss given default (LGD), and exposure at default (EAD), with classification into Stage 1, 2, or 3 depending on credit risk deterioration.


KEY OUTPUTS
- Loan / Bond-level results: ECL calculated for each loan and bond, with stage classification clearly flagged.
- Portfolio summaries: Total carrying amount and ECL split by stage (1/2/3) and by instrument type (loans vs bonds)
- Ratio of ECL to latest carrying amount
- Dashboard visuals: Pie charts showing stage distribution and risk / credit rating distribution for loans and bonds.
- Key averages: Stage-wise and total averages for PD, LGD, interest rate, years to maturity, and days past default.


KEY INPUTS
- Setup Assumptions including:
o Name of company
o Currency;
o Reporting date


- ECL Assumptions:
o Risk / Credit Rating levels
o Annual PD per risk level
o Collateral / recovery assumptions
o Stage definitions based on days past due, rate changes and defaults


- Individual Loan Attributes including:
o Loan ID
o Borrower
o Maturity date
o Contract rate
o Original and current risk level
o Original and current Loan Amount
o Collateral Value
o Days past due


- Individual Bond Attributes including:
o Bond ID
o Issuer
o Maturity date
o Effective Interest Rate
o Original and current credit rating
o Face value and latest carrying amount
o Seniority
o Days past due


KEY FEATURES
- Dual Portfolio Support: Separate structured inputs and calculations for loans and bonds (1,000 instruments each).
- User-defined Assumptions: Configure Probability of Default (PD), Loss Given Default (LGD), stage determination rules, collateral recoveries, seniority levels, and discount rates.
- User-defined Stage Classification: Flexible logic for Stage 1, 2, and 3 determination based on days past due, rating changes and defaults.
- ECL Calculation Engine: Automatically computes Stage 1 (12‑month) or Stage 2/3 (lifetime) ECL per instrument using IFRS 9 methodology.
- Dashboard Outputs: Ready-made summary and visualization layer including summary ECL outputs by stage averages by stage and charts showing Stage and rating breakdown for bonds and loans.
- Scalability: Built to handle 2,000 instruments efficiently with structured tables and formulas.
- Audit & Validation: Includes instructions, checks and input validations to help ensure input fields are populated accurately.
- Not Password Protected: The model is not password protected and can be modified as required following download.


SUPPORT / MODIFICATIONS
Our models are built with the user in mind: clear instructions, checks, and input validations make them easy to use without deep Excel or finance knowledge. If you have questions, we’re happy to assist. We also provide bespoke modifications to tailor the model to your business needs. To get in touch, email us at [email protected]. We welcome your feedback and encourage you to share reviews to help us improve.


ABOUT PROJECTIFY
We are financial modelling professionals with experience working in big 4 business modelling teams and strong experience supporting businesses with their financial modelling and decision support needs. Our aim is to provide robust and easy-to-use tools that follow good practice financial modelling guidelines and assist individuals and businesses with their financial projection and analysis requirements.