Roll Rate Analysis

Deepanshu Bhalla 9 Comments

In this article, we will cover what roll rate analysis is and how it is used in credit risk domain.

Introduction to Roll Rate Analysis

Roll rate analysis is used for solving various types of problems. Most common usage is loss forecasting. It is also used to determine the definition of 'bad' customers (defaulters).

Most common definition of 'bad' customer is customer delinquent for 90 days or more. In simple words, if payment has been due 90 days or more, it is considered as 'bad'. It includes if it is partially or fully charged-off. Roll rate analysis helps to answer the question with quantitative reasoning - "Should we use 60 days or 90 days or 120 days or higher delinquency to identify 'bad' customers?".

Roll rate is the proportion of customers who will be 'better', 'worse' or 'remain same' with time in terms of delinquency. For example, Will customers who have no due today become delinquent in future? If yes, what percentage of customers will be in each delinquency buckets : 0-29 DPD, 30-59 DPD, 60-89 DPD, 90 or more DPD?

roll rate analysis
How it works?

As shown in the table above, there are 26000 customers who have no payment due (let's call it 'clean' customers) at the time when they completed 12 months on Book (MoB) since loan origination date. Similarly we calculate count of customers in other buckets.


Status at the end of 12 MOB
+-------------+-------+
| No Due      | 26000 |
| 0-29 DPD    | 12060 |
| 30-59 DPD   |  6030 |
| 60-89 DPD   |  3330 |
| 90-119 DPD  |  2130 |
| 120-149 DPD |   810 |
| 150-180 DPD |   759 |
+-------------+-------+

Next step is to check their status after 18 months on Book. Let's start with 'clean' customers. Below is the updated delinquency status of customers who had no due at the end of 12 MOB became delinquent at the end of 18 MOB.


+-------------+----------+
| No Due      |    22000 |
| 0-29 DPD    |     1670 |
| 30-59 DPD   |     1000 |
| 60-89 DPD   |      670 |
| 90-119 DPD  |      340 |
| 120-149 DPD |      220 |
| 150-180 DPD |      100 |
+-------------+----------+

We can see most of them remain 'clean' customers and only a few of them moved to other delinquency buckets. It is important to understand this in terms of percentage.

Next step : Calculate percentage of customers rolled backward (moved to lower delinquency bucket) or forward (moved to higher delinquency bucket)

6.4% of 'No Due' customers moved to 0-29 DPD bucket. 6.4% is calculated by dividing 'number of customers in 0-29 DPD at end of 18 MoB' by 'total number of No Due customers at end of 12 MoB'.

Roll Backward and Forward

Roll Backward against 30-59 DPD is 80% which is calculated by adding lower delinquency than 30-59 DPD (No Due and 0-29 DPD). In simple words, 80% debtors turned to less risky buckets.

Roll Forward against 30-59 DPD is 9% which is calculated by summing higher delinquency than 30-59 DPD (60-89 DPD, 90-119 DPD, 120-149 DPD, 150-180 DPD).

How to choose bad definition

As per Basel II Accord, definition of 'bad' is debtor unlikely to repay the debt which is 90 days past due. Individual regulators can change this to 180 for certain products. As explained above, we can also use analytics method to define bad customers using roll rate.

If we use 150+ DPD, only 8% of the debtors would be likely to move to lower delinquency buckets. It is lowest as compared to other buckets. It means a very few customers would roll back to lower buckets if we select 150+ DPD as definition of bad customers. We also need to ensure enough cases of bad customers should be acquired.

Loss Forecasting

By using roll rate analysis and markov chain, we can forecast credit loss at portfolio level. Let's under Markov Chain.

Markov Chain

Markov Chain is a random process where the next state is dependent on the previous state. Real world example is prediction of next word in mobile keyword. Algorithm uses thousands or millions of sentences as input and convert sentences into words. Then check how many times one word that follow this word. For example, probability of word "How" as a next word of "Hello" is 75%. Other possible next words : 25% - Who, 40% - brother; 10% - neighbor; 2% - adele.

Next step is to choose the next word randomly. In this example, previous state is the selected word and next state is the possible next words with their count.

In credit risk, previous state refers to account performance in the previous month and next state is account performance in the next month.

Transition or Migration Matrix

Migration or transition of delinquent accounts from one period to another period. We use probability scores which is % of customers in different delinquency buckets from one time to another time period. For example, 89% of customers stay 'clean' (no due) in the next month and 11% of them moved to 0-29 DPD next month. The matrix shown below is called transition matrix. It is the same matrix which we learnt in the previous section.

loss forecasting

Let's assume the numbers in the table below represent the number of customers in different delinquency buckets in the previous month.


+-------+-------+
| Clean | 20000 |
| DPD30 |   750 |
| DPD60 |   250 |
| DPD90 |     0 |
+-------+-------+

Matrix Multiplication : Multiply transition matrix with the matrix of no. of customers delinquent in the previous month. To understand the calculation, follow the screenshot below -

Calculations of Roll Rate Analysis
In MS Excel, you can use MMULT function for matrix multiplication. Make sure you select the whole range wherein you want to apply the function and then use CTRL+SHIFT+ENTER to enter it as an array formula. If you enter it correctly, you would find your formula wrap in curly braces { }.
Limitation of Roll Rate Analysis for Forecasting

It does not consider macroeconomic factors like fall in industrial production, high unemployment rate etc in forecasting. It is mainly used for short term forecasting.

Related Posts
Spread the Word!
Share
About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

Post Comment 9 Responses to "Roll Rate Analysis"
  1. Hi Deeepanshu,

    This is great article. Could you please share the data and code. It helps us a lot.

    Kind Regards,
    Ramesh

    ReplyDelete
  2. Hi Deepanshu,

    Thanks for a great article!

    ReplyDelete
  3. This gives a great understanding of the concept.

    ReplyDelete
  4. When you say "iteration 1, 2, etc" -- are you referring to the following months that we're trying to predict?

    ReplyDelete
  5. Thanks Deepanshu,

    2 questions:

    1. In case of roll rate analysis, how do we determine which bucket to be the defining bucket for default recognition?

    2. If basel II defines the definition, why do we perform roll rate analysis?

    Thanks in advance!

    ReplyDelete
    Replies
    1. This is largely helpful for Modeling in other domains like marketing, healthcare, etc..
      For Risk, we use 90 plus DPD as chosen by regulators for unsecured products like cards, personal loan

      Delete
  6. Please share the excel file to help us understanding the story.

    ReplyDelete
    Replies
    1. Do you have the excel sheet?
      If yes please share it with us.
      Thanks

      Delete
  7. We need an excel sheet example

    ReplyDelete
Next → ← Prev