A simple way to calculate ECL for IFRS 9

Azquo can greatly simplify ECL (Expected Credit Loss) calculations for IRFS 9.

 

ECL calculation for a single loan, or portfolio of loans is an ideal job for a spreadsheet.   Any loan can be expressed as a relatively small number of parameters – the loan amount, interest rate, monthly, quarterly, yearly repayments, probability of default by period, loss given default, probability of early repayment, etc.   These parameters can be used to create a formula which will give the EAD (Exposure at Default) for each period of the loan or loan portfolio, and, from that the EL (Expected Loss) for any month of the loan.   The ECL is simply the sum of the ELs for current and future months.

The problem with using a spreadsheet to do ECL calculations for IFRS 9 usually is that, with multiple loans starting in different periods, and with different characteristics, the spreadsheet formulae to summarise the data start to become very complicated indeed, and the ability to check the results becomes very limited.

Azquo is ideally suited to solve this problem.

We can set up a spreadsheet that handles loans starting in any particular month.   By replicating the formulae down the sheet we can handle thousands of loans in one spreadsheet, then save away the EL figures by the calendar month to our database.   Altogether, we can handle millions of loans. Because of the way our sets work we do not need to specify any formulae for adding the EL figures, but only specify the range of loan dates and repayment periods which we wish to summarise to create the ECL figure.    In short, two formulae, which can be audited and adjusted easily, form the basis for the entire ECL calculations.    A list of loans can be uploaded and processed in minutes to create fully auditable ECL figures with no further effort.

Testing different scenarios, and inserting actuals

Because Azquo stores the data away from the spreadsheet, the same database can hold more than one scenario, and reports can be produced comparing the outcomes.  And, of course, the actual figures can also be inserted to see how the models compare with reality.

For more information, contact us now at info@azquo.com

 

 

 

 

Here’s a sample of the upload sheet (this could be a text file)

Loan List Upload
A sample list of loans and parameters

This is the Excel sheet that does the calculations:

…and here’s a sample output that can be obtained immediately afterwards

ECL Summary sheet
Output after cycling through the calculations

…a breakdown of the ECL for February 18:

ECL Breakdown
A breakdown of the ECL for one month

 

Audit ECL figures
Auditing any figure