Introduction to Roll Rate Analysis
Roll rate analysis is used for solving various type of problems. Most common usage is loss forecasting and 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?
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. See their updated delinquency status below.
Some customers who had no due at the end of 12 MOB becomes 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 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).
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.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.
Suppose number of customers in 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 -
In MS Excel, you can useMMULT
function for matrix multiplication. Make sure you select the whole range wherein you want to apply the function and then useCTRL+SHIFT+ENTER
to enter it as an array formula. If you enter it correctly, you would find your formula wrap in curly braces{ }
.
Hi Deeepanshu,
ReplyDeleteThis is great article. Could you please share the data and code. It helps us a lot.
Kind Regards,
Ramesh
Hi Deepanshu,
ReplyDeleteThanks for a great article!
This gives a great understanding of the concept.
ReplyDeleteWhen you say "iteration 1, 2, etc" -- are you referring to the following months that we're trying to predict?
ReplyDeleteThanks Deepanshu,
ReplyDelete2 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!
This is largely helpful for Modeling in other domains like marketing, healthcare, etc..
DeleteFor Risk, we use 90 plus DPD as chosen by regulators for unsecured products like cards, personal loan
Please share the excel file to help us understanding the story.
ReplyDeleteDo you have the excel sheet?
DeleteIf yes please share it with us.
Thanks
We need an excel sheet example
ReplyDelete