IFRS 9 portfolio analytics with Python and Atoti

In this post I would like to present examples of data viz for IFRS 9 using the BI app built in the previous post with Python and Atoti: “Tutorial: How to Build an IFRS 9 BI app with Python and Atoti“.

Getting started

Please refer to the previous post for a guide on building a BI app for IFRS 9 analytics, or simply download and run this Jupyter Notebook

To view the dashboards in the newly built app, run session.url and launch the user interface.

In the example notebook referred above, you will find a few dashboards summarizing portfolio EAD, ECL and vintage matrix. We’ll explore them later in this post, but before let me start with a bit of explanation.

Aggregating loan-level data

If you remember from the previous post, we used Jupyter Notebook to launch an in-memory cube and a Tableau-like user interface using Atoti python module:

Then we have loaded loan-level PD, LGD and EAD and defined a formula for ECL. With that in place, we can slice-and-dice these metrics using drag and drop, and the measures will recalculate from the loan-level data every time we change the view:

What is the “Vintage matrix?” 

The term “Vintage” comes directly from the wine industry. For a long time, wine specialists have been evaluating their stock based on both the species of grapes and the year in which the grapes were harvested. Based on those parameters vinters will know if the wine is ready to drink or not and if it was meant to be drunk earlier. 

Instead of waiting for this worst case scenario wine specialists have created “Vintage tables” (Robert Parker and co) where we can find the information we need to make the decision to drink or not drink a wine we have in our cellar. The same type of analysis can be applied to loan portfolios and it has become a very popular model for managing credit risk for a number of reasons: 

• It is a useful tool for credit risk analysts to monitor the evolution of their loan book and  optimize their price on the retail market. 
• Users can apply stress tests on the matrix to assess the accuracy of forecasts by analyzing the number of default loans and their actual realization.
• The matrix can be applied in the process of estimating capital requirements.

In vintage analysis, the portfolio is broken down into “vintages” based on the origination month. This is because credit portfolios follow a certain lifecycle pattern with more loans defaulting in the first months of inception and stabilizing after a certain period. 

The following chart provides a visualization of cumulative default rates for two hypothetical credit portfolios – blue and orange. By displaying default rates along “months on books” the portfolios become comparable by select months. For example, based on the performance after 2 or 4 months on the books, the orange portfolio has a better credit quality than the blue one. 

Constructing a vintage matrix for the whole portfolio helps to evaluate the portfolio structure, find similar sub-portfolios, identify portfolios with better or poorer credit quality, and forecast performance of “younger” vintages based on available comparables. 

To build the matrix in the IFRS 9 Atoti app referred to above, you need to use the “Opening date” and “Months Since Inception” hierarchies. The first one allows you to break down/filter portfolio data by vintage, i.e. inception period. The second allows you to visualize the evolution along reporting dates. You can choose various measures for your vintage matrix, for example:

• % of contracts past due shows progressive percentage of defaulted contracts for a given vintage and a historical date. This measure can be used in conjunction with the attribute “PastDueDatesThreshold” – defining after how many days to consider a loan to be past due (30 by default) 
• ECL or EAD

You can also add your own measures easily in the Jupyter Notebook.

Here’s a screenshot of the percentage of defaulted contracts from the Atoti app. From this view, you can click “Drill-through” to see the list of contributing loans. 

Here’s an example dashboard sketched in Atoti. It shows  – from left to right – the cumulative write-offs by vintage, portfolio evolution broken down by Stage 1,2 and 3 and measured using ECL, and the vintage matrix. Using filters I’m changing the view quickly. Every time the dashboard is updated – all the underlying metrics are recalculated from the loan-level data. You can jump straight to the loan level data using Drill-through as shown:

Conclusion

The interactive visualizations we’ve discussed in this post rely heavily on the capabilities of the BI app to quickly re-aggregate the metrics, as we change the scope of data. 

We wanted to give the tool a stress test by loading millions of loans into it. You can read about this experiment in our next blog post: “IFRS9 Big Data: Performance test“.