# Trouble verifying roll rate model

I found this paper on roll rate analysis via a google search. I would post a link, but every page is stamped with "CONFIDENTIAL" at the bottom (humorous since it is easily found). In a nut-shell, roll rates are used to determine how a company’s portfolio is performing by following accounts/loans over a given time horizon. To find roll rates (at least how this paper explains it), do the following:

1. determine the delinquent statuses of all of your accounts at a certain point in time
2. after a specified period (6 months in this paper), determine the statuses of the entire portfolio again
3. finally determine the percentages of changes from one delinquent status to another

For example, let’s say at a given point in time you want to determine the roll rate of all of your current loans and let’s say you have 100 of them. 6 months later, 80 of those loans are still current, 15 are 30 days delinquent, and 5 paid off. This would translate to an 80% current-current roll rate, a 15% current-30 roll rate, and a 5% current-paid off role rate (this paper uses prepay for this delinquent status). It should be noted that the details of the portfolio (they are not even given in the paper) are immaterial as I am simply trying to verify their results. Below is the actual roll rates provided along with a few key statements that will help you interpret this matrix for a 6 month time span.

'''''''''|''Current''|''''30''''|''''60+'''|'''Default'''|'''Prepay
'Current |   90.4%   |   2.9%   |   2.3%   |     0.1%    |    4.3%
'  30    |   20.9%   |  16.4%   |  55.6%   |     6.4%    |    0.8%
'  60+   |    7.0%   |  10.3%   |  68.5%   |    13.9%    |    0.3%

- Key Observations:
− 90.4% of current loans were still current 6 months later; 4.3% paid in full during the period.
− Only 20.9% of 30 day loans were current 6 months later.
− Only 7.00% of 60+ day loans were current 6 months later.


Aside from the second row not summing to 1 (1.001), everything seems to be okay to this point. The first statement that is a bit mysterious is:

- As of six months later, the portfolio is approximately 3.1% 60+ days delinquent.


We are given no further information (i.e. we aren’t given how many loans/accounts were in each status from the outset, nor are we given how many there were 6 months later. We are simply given percentages). So, to verify any results and to make sense of their claims, I did a little algebra to determine how many loans were in each status at the beginning. This will give me the percentages of each delinquent status as well as allow me to carry out further analysis on this portfolio moving forward. Below is my setup along with all of my calculations:

x = # of Current accounts at time 0
y = # of 30 day delinquent accounts at time 0
z = # of 60+ day accounts at time 0


We need to find the number of each of these 6 months from now, so:

x_1 = # of Current accounts 6 months later
y_1 = # of 30 day delinquent accounts 6 months later
z_1 = # of 60+ day accounts 6 months later


Note: we are not concerned with the loans that “Defaulted” or “Prepaid” as they have left the portfolio.

x_1 = x(0.904) + y(.209) + z(.07)
y_1 = x(0.029) + y(.164) + z(.103)
z_1 = x(0.023) + y(.556) + z(.685)


Thus, we must now solve 0.031 = z_1 / (x_1 + y_1 + z_1). Below is my algebra with many steps left out (easily verified).

x(0.029636) + y(0.028799) + z(0.026598) = x(0.023) + y(.556) + z(.685)
x(0.006636) - y(0.527201) - z(0.658402) = 0


Now, since the numbers are arbitrary, I set x = 100,000

663.6 = y(0.527201) + z(0.658402)


This means that the limits on y and z are, 1258.73 (assuming z = 0) and 1007.89 respectively (assuming y = 0). Again, since the numbers are arbitrary, I let y = 800, which gives z = 367. Also, note that I am rounding up as it doesn't make since to have "part" of an account/loan.

x = 100000, y = 800, and z = 367 gives
2997 / (90593 + 3070 + 2997) = 0.03100559... approx = 3.1%


Now that we have a full set-up, we can move on to the claims I have issues with. They are:

- Assuming the 6 month experience repeats itself:
− In two years, 13.9% of the portfolio will be 60+ day delinquent.
− In three years, 17.9% of the portfolio will be 60+ day delinquent.
− In five years, 23.8% of the portfolio will be 60+ day delinquent.
− After a lifetime, 40.3% of the portfolio will be 60+ day delinquent.


I have written a function in R to carry out such an experiment.

RollRate60Analysis <- function(numC, num30, num60, mon6) {
## numC is the number of current accounts at time = 0
## num30 is the number of accounts that are 30 days delinquent at time = 0
## num60 is the number of accounts that are 60+ days delinquent at time = 0
## mon6 is the number of 6 month periods to carry out

mymat <- matrix(c(0.904,0.029,0.023,0.001,0.043,0.2085,0.164,0.556,
0.064,0.0075,0.07,0.103,0.685,0.139,0.003), nrow = 3, byrow = TRUE)
initMat <- matrix(c(numC, num30, num60, 0, 0), ncol = 5)
for (j in 1:mon6) {
tempMat <- mymat
for (i in 1:3) {tempMat[i, ] <- tempMat[i, ] * sum(initMat[,i])}
initMat <- ceiling(tempMat)
}
my60 <- sum(initMat[,3])
total <- sum(apply(initMat, 2, sum)[1:3])
list(initMat, 100*my60/total)
}


Here is where it gets a bit unclear. I interpret “in two years” as the time after the initial roll rate is determined and therefore I pass the number 5 for the mon6 variable. This yields RollRate60Analysis(100000, 800, 367, 5) = 12.60881. Even if they meant from the very beginning I get the wrong answer (i.e. RollRate60Analysis(100000, 800, 367, 4) = 11.00218). My results start to diverge even further as the time increases. For example, for three years later I get RollRate60Analysis(100000, 800, 367, 7) = 14.88264 and for 5 years later I get RollRate60Analysis(100000, 800, 367, 11) = 17.21863.

For the final claim, I obtain 27.16%. If you let mon6 get larger and larger, the percentages start to approach 27.16049%. Observe:

mon6 = 100, RollRate60Analysis(100000, 800, 367, 100) = 21.02804
mon6 = 200, RollRate60Analysis(100000, 800, 367, 200) = 27.16049
mon6 = 500, RollRate60Analysis(100000, 800, 367, 500) = 27.16049
mon6 = 1000, RollRate60Analysis(100000, 800, 367, 1000) = 27.16049
mon6 = 2000, RollRate60Analysis(100000, 800, 367, 2000) = 27.16049


This is far from 40.3%. Even if you start with absurd numbers at the beginning for numC, num30, and num60, you obtain 27.16049% (e.g. RollRate60Analysis(100000, 100000, 100000, 2000) = 27.16049). This makes sense as the roll rate matrix should determine everything at infinity, and not the number of a certain delinquent type at the outset.

Even if you alter the above logic and include Default as those loans that are 60+ days delinquent, I can't replicate their results. I use the same logic as above to obtain my initial state (I'll spare the algebraic details). Observe:

RollRateAnalysisAltered <- function(numC, num30, num60, mon6) {
mymat <- matrix(c(0.904,0.029,0.023,0.001,0.043,0.2085,0.164,0.556,
0.064,0.0075,0.07,0.103,0.685,0.139,0.003), nrow = 3, byrow = TRUE)
initMat <- matrix(c(numC, num30, num60, 0, 0), ncol = 5)
for (j in 1:mon6) {
tempMat <- mymat
for (i in 1:3) {tempMat[i, ] <- tempMat[i, ] * sum(initMat[,i])}
initMat <- ceiling(tempMat)
}
my60 <- sum(initMat[,3]) + sum(initMat[,4])  ## including 4th column i.e. "Default"
total <- sum(apply(initMat, 2, sum)[1:4])  ## including 4th column i.e. "Default"
100*my60/total
}

mon6 = 1, RollRateAnalysisAltered (100000, 600, 300, 1) = 3.127362
mon6 = 5, RollRateAnalysisAltered (100000, 600, 300, 200) = 14.25458
mon6 = 7, RollRateAnalysisAltered (100000, 600, 300, 500) = 16.87869
mon6 = 11, RollRateAnalysisAltered (100000, 600, 300, 1000) = 19.54957
mon6 = 200, RollRateAnalysisAltered (100000, 600, 300, 2000) = 32.18391
mon6 = 2000, RollRateAnalysisAltered (100000, 600, 300, 2000) = 32.18391
mon6 = 2000, RollRateAnalysisAltered (100000, 100000, 100000, 2000) = 32.18391


Question:
Is there an error in their analysis or is my line of reasoning completely off? Again, we aren't given that much information, but it seems as though we should be able to replicate their results. Thanks!