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: Stagewise and total averages for PD, LGD, interest rate, years to maturity, and days past default.
 
KEY INPUTS
 
Setup Assumptions including:
Name of company
Currency;
Reporting date
 
ECL Assumptions:
Risk / Credit Rating levels
Annual PD per risk level
Collateral / recovery assumptions
Stage definitions based on days past due, rate changes and defaults
 
Individual Loan Attributes including:
Loan ID
Borrower
Maturity date
Contract rate
Original and current risk level
Original and current Loan Amount
Collateral Value
Days past due
 
Individual Bond Attributes including:
Bond ID
Issuer
Maturity date
Effective Interest Rate
Original and current credit rating
Face value and latest carrying amount
Seniority
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. 
 
MODIFICATIONS & SUPPORT
 
If you require any be-spoke modifications or support, we are more than happy to assist. Please send us a message below or contact us on hello@useprojectify.com