Packages:

> library(magrittr)
> library(dplyr)

Let’s redefine the plot() function:

> plot2 <- function(...) plot(c(0, 60), 0:1, type = "n", bty = "o",
+                             xlab = "day", xaxs = "i", yaxs = "i", ...)

Reading data:

> data <- "Fast Credit upto dec 2018.xlsx" %>%
+   readxl::read_excel() %>% 
+   select(1:4) %>% 
+   as.data.frame() %>% 
+   setNames(c("month", "day", "advance", "collection")) %>% 
+   mutate(day  = as.numeric(sub("th", "", day)),
+          rate = collection / advance) %>% 
+   na.exclude()

Let’s plot them:

> plot2(ylab = "rate")
> for(i in 1:12) with(filter(data, month == i), lines(day, rate))
> abline(h = .95, lty = 2)

Here is how we do:

• we assume that the increase of the rate with time is the same from month to month (we don’t have enough data any way to make another assumption);
• we assume that at a given day the rate follows a beta distribution (that is the same whatever the month);
• then we look for the probability corresonding to a .95 quantile at day 60;
• we use this probability for the other days and look for the corresponding quantiles;
• we smooth these quantiles as a function of time, by steps of 1 day:

> thresholds <- data %>%
+   group_by(day) %>%
+   summarize(mu  = mean(rate),
+             var = var(rate)) %>% 
+   ungroup() %>% 
+   mutate(alpha = ((1 - mu) / var - 1 / mu) * mu ^ 2,
+          beta  = alpha * (1 / mu - 1),
+          p     = qbeta(pbeta(.95, shape1 = alpha[day == 60],
+                                   shape2 = beta[day == 60]),
+                        shape1 = alpha, shape2 = beta)) %$% 
+   loess(p ~ day, span = .5) %>% 
+   predict(data.frame(day = 1:60)) %>% 
+   data.frame(day = 1:60, threshold = .)

The thresholds look like this:

> with(thresholds, {
+   plot2(ylab = "rate")
+   abline(v = 10 * 0:6, col = "grey")
+   abline(h = seq(0, 1, .2), col = "grey")
+   for(i in 1:12) with(filter(data, month == i), lines(day, rate, col = "grey"))
+   lines(day, threshold, col = "red")
+   abline(h = .95, lty = 2)
+ })

The threshold values for each day:

> thresholds
   day threshold
1    1        NA
2    2        NA
3    3        NA
4    4        NA
5    5 0.2042375
6    6 0.2480436
7    7 0.2892908
8    8 0.3280423
9    9 0.3643615
10  10 0.3983116
11  11 0.4296251
12  12 0.4581840
13  13 0.4842766
14  14 0.5081909
15  15 0.5302150
16  16 0.5489616
17  17 0.5638809
18  18 0.5765184
19  19 0.5884189
20  20 0.6011279
21  21 0.6127168
22  22 0.6214131
23  23 0.6289954
24  24 0.6372426
25  25 0.6479334
26  26 0.6604985
27  27 0.6732743
28  28 0.6863986
29  29 0.7000090
30  30 0.7142433
31  31 0.7302154
32  32 0.7481045
33  33 0.7666468
34  34 0.7845784
35  35 0.8006353
36  36 0.8161209
37  37 0.8324650
38  38 0.8485935
39  39 0.8634323
40  40 0.8759072
41  41 0.8856095
42  42 0.8933059
43  43 0.8996859
44  44 0.9054390
45  45 0.9112546
46  46 0.9165404
47  47 0.9205685
48  48 0.9238246
49  49 0.9267944
50  50 0.9299636
51  51 0.9331866
52  52 0.9360310
53  53 0.9385529
54  54 0.9408084
55  55 0.9428535
56  56 0.9446670
57  57 0.9461975
58  58 0.9474553
59  59 0.9484505
60  60 0.9491935