What is Mintos?

Mintos.com is a P2P lending platform. One can invest in loans offered by various loan organizers from various countries. Most loans are short-term and personal loans from FSU countries such as Georgia, Armenia, Russia, but also Spain or Indonesia etc. Many loans are very short term (1 month) but there’s also some with longer terms (several years). The majority of loans are denominated in Euro but the share of loans in other currencies is increasing. Typical interest rates for Euro-loans is 11 to 13%.

More than 13 million loans were issued on Mintos as of mid 2019. The loan amount offered is over 3 billion EUR (Euro loans only).

Mintos grants access to the full loan book of all loans issued since inception. For this head to Statistics and look for the link Download Loan Book (might require having an account and being logged in). One gets a ZIP with (as of today) 27 individual Excel sheets. Unzipped it’s abozut 1 Gb.

Let’s have a look.

Sidenote on data analytics

While Mintos hands out the data in several Excel sheets, Excel is not recommended for analysis. The maximum number of rows in Microsoft Excel is 1 048 576.

For using it in R, I recommend first converting and saving the sheets to csv’s. These csv’s can be loaded and row-bind. I use tidyR tibbles and dplyr but for performance reasons one could prefer to use data.table.

library(tidyverse)
library(readxl)

# converting xlsx to csv
files <- list.files("data/xlsx")

for (i in 1:length(files)) {
  print(i)
  data <- read_excel(paste0("data/", files[i]))
  data %>% write_csv(paste0("data/", str_replace(files[i], ".xlsx", ".csv")))
  print("done")
}
rm(data)

# read and bind together
csvfiles <- list.files("data/csv")

data <- tibble()
for (i in 1:length(csvfiles)) {
  print(i)
  data <- bind_rows(data, read_csv(paste0("data/csv/", csvfiles[i])))
  print("done")
}

# check the size
object.size(data) %>% format(units = "auto")

# save for later use
data %>% saveRDS("data/data.RDS")

# save in two RDS (both <100Mb because of Github's restiction on file size)
nrows <- nrow(data)
half  <- round(nrows/2)

data[1:half,]         %>% saveRDS("data/data1.RDS")
data[(half+1):nrows,] %>% saveRDS("data/data2.RDS")

Yet better would be to use Apache Spark, e.g. with sparklyr. I tried it and it works fine, but at the end I chose to use my usual workflow with dplyr anyway (instead of dplyr commands with sparklyr).

library(sparklyr)

for (i in 1:length(csvfiles)) {
  print(i)
  sparklyr::spark_read_csv(sc, 
                           name = paste0("data", i),
                           path = paste0("data/csv/", csvfiles[i]))
  assign(paste0("sparkdata", i), tbl(sc, paste0("data", i)))
  print("done")
}

data <- sdf_bind_rows(sparkdata1, sparkdata2, sparkdata3, sparkdata4, sparkdata5, sparkdata6, sparkdata7, sparkdata8, sparkdata9, sparkdata10,
                      sparkdata11, sparkdata12, sparkdata13, sparkdata14, sparkdata15, sparkdata16, sparkdata17, sparkdata18, sparkdata19, sparkdata20,
                      sparkdata21, sparkdata22, sparkdata23, sparkdata24, sparkdata25, sparkdata26, sparkdata27)

object.size(data) # should be tiny

A medium spec workstation is recommended (used here 16 Gb RAM + 16 Gb Swap, i7-4790 CPU @ 3.60GHz × 4). My laptop was not suited for the job.

Stylized facts

First, let’s look at the available columns.

library(tidyverse)
library(lubridate)
library(magrittr)

data <- bind_rows(readRDS("data/data1.RDS"), readRDS("data/data2.RDS"))

data %>% glimpse()

## Observations: 13,314,969
## Variables: 19
## $ Id                      <chr> "8210540-02", "9657926-03", "13425646-01…
## $ `Issue Date`            <dttm> 2019-04-14, 2019-04-04, 2019-04-10, 201…
## $ `Closing Date`          <dttm> 2019-04-28, 2019-05-04, 2019-05-10, 201…
## $ `Listing Date`          <dttm> 2019-04-14 19:26:03, 2019-04-14 19:26:0…
## $ Country                 <chr> "Ukraine", "Russian Federation", "Russia…
## $ `Loan Originator`       <chr> "Dinero", "Dozarplati", "Dozarplati", "D…
## $ `Mintos Rating`         <chr> "C+", "B-", "B-", "B-", "B-", "B-", "B-"…
## $ `Loan Type`             <chr> "Short-Term Loan", "Short-Term Loan", "S…
## $ `Loan Rate Percent`     <dbl> 14.0, 17.5, 17.5, 17.5, 17.5, 17.5, 17.5…
## $ Term                    <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…
## $ Collateral              <chr> "No", "No", "No", "No", "No", "No", "No"…
## $ `Initial LTV`           <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
## $ LTV                     <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
## $ `Loan Status`           <chr> "Finished prematurely", "Finished premat…
## $ `Buyback reason`        <chr> "Agreement prolongation", "Agreement pro…
## $ `Initial Loan Amount`   <dbl> 77.58, 8000.00, 4000.00, 10000.00, 1000.…
## $ `Remaining Loan Amount` <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
## $ Currency                <chr> "EUR", "RUB", "RUB", "RUB", "RUB", "RUB"…
## $ Buyback                 <chr> "Yes", "Yes", "Yes", "Yes", "Yes", "Yes"…

• Id: Loan ID, can be looked up in https://www.mintos.com/en/<Id> e.g. https://www.mintos.com/en/18218910-01
• Listing Date: in general loans are listed some time after issue
• Loan Originator: company handling the loan
• Mintos Rating: A, A-, B+, …, D. Can be changed, hence we do not like to include them in the analysis
• LTV: loan to value, aka leverage (equity / (debt + equity))
• Buyback (guarantee)

One loan at random:

set.seed(2)

data %>% 
  sample_n(1) %>% 
  glimpse()

## Observations: 1
## Variables: 19
## $ Id                      <chr> "2089899-06"
## $ `Issue Date`            <dttm> 2018-03-28
## $ `Closing Date`          <dttm> 2018-04-27
## $ `Listing Date`          <dttm> 2018-03-31 19:32:03
## $ Country                 <chr> "Latvia"
## $ `Loan Originator`       <chr> "Bino"
## $ `Mintos Rating`         <chr> "B-"
## $ `Loan Type`             <chr> "Short-Term Loan"
## $ `Loan Rate Percent`     <dbl> 13.1
## $ Term                    <dbl> 1
## $ Collateral              <chr> "No"
## $ `Initial LTV`           <dbl> 0
## $ LTV                     <dbl> 0
## $ `Loan Status`           <chr> "Finished prematurely"
## $ `Buyback reason`        <chr> "Agreement prolongation"
## $ `Initial Loan Amount`   <dbl> 420.86
## $ `Remaining Loan Amount` <dbl> 0
## $ Currency                <chr> "EUR"
## $ Buyback                 <chr> "Yes"

Due to the number of observations we resort to samples of 1 000 to 10 000 for some calculations.

# library(ggcorrplot)
library(GGally)

topcountries <- data %>%
  group_by(Country) %>%
  count() %>%
  ungroup() %>%
  arrange(-n) %>%
  top_n(3) %>%
  pull(Country)

toporigin <- data %>%
  group_by(`Loan Originator`) %>%
  count() %>%
  ungroup() %>%
  arrange(-n) %>%
  top_n(3) %>%
  pull(`Loan Originator`)

set.seed(15)

data %>% 
  select(Rate = `Loan Rate Percent`, Term, LTV0 = `Initial LTV`, Amount1000 =`Initial Loan Amount`, Currency, `Listing Date`, Buyback,
         Country, Origin = `Loan Originator`, Collateral) %>% 
  mutate(Currency = recode_factor(Currency, "EUR" = "EUR", .default = "Other"),
         Buyback = as.factor(Buyback),
         Collateral = as.factor(Collateral),
         Country = fct_other(Country, keep = topcountries, other_level = "Other"),
         Origin = fct_other(Origin, keep = toporigin, other_level = "Other"),
         ListingYear = year(`Listing Date`)) %>% 
  select(-`Listing Date`) %>% 
  select(-Country, -Origin) %>% 
  sample_n(10000) %>% 
  filter(!is.na(ListingYear)) -> quants_fct

mintoscolor <- "#78c8c7"

quants_fct %>% 
  # drop_na() %>% 
  mutate(ListYr = as.factor(ListingYear),
         Amount1000 = Amount1000/1000) %>%
  select(-ListingYear) %>% 
  ggpairs(
    lower = list(
      continuous = wrap("smooth_loess", alpha = 0.5, color = mintoscolor),
      combo = wrap("box_no_facet", alpha = 0.5, fill = mintoscolor), #"facetdensity",
      discrete = wrap("ratio", alpha = 0.5, fill = mintoscolor)
      # mapping = aes(color = ListYr)
      ),
    upper = list(
      combo = wrap("facetdensity", color = mintoscolor),
      discrete = wrap("ratio", alpha = 0.5, fill = mintoscolor)
    ),
    diag = list(
      continuous = wrap("densityDiag", alpha = 0.5),
      discrete = "barDiag",
      mapping = aes(color = ListYr)
    ),
  switch = "y",
  title = "Correlation of Mintos.com loan characteristics") -> cp

cp

# library(ggbeeswarm)

set.seed(12)

data %>% 
  filter(Currency == "EUR", !is.na(`Listing Date`)) %>% 
  mutate(ListYr = factor(year(`Listing Date`))) -> d

d %>% sample_n(30000) %>% 
  ggplot(aes(x = ListYr, y = `Loan Rate Percent`)) +
  geom_boxplot() +
  # geom_beeswarm(data = (d %>% sample_n(1000)), alpha = 0.1, color = mintoscolor)
  geom_jitter(data = (d %>% sample_n(1000)), alpha = 0.2, color = mintoscolor) +
  scale_y_continuous(breaks = seq(0,25,1)) +
  labs(title = "Distribution of interest rate of newly listed loans") +
  theme_bw()

topn <- 5

topcurrencies <- data %>%
  group_by(Currency) %>%
  count() %>%
  ungroup() %>%
  arrange(-n) %>%
  top_n(topn) %>%
  pull(Currency)

topcountries <- data %>%
  group_by(Country) %>%
  count() %>%
  ungroup() %>%
  arrange(-n) %>%
  top_n(topn) %>%
  pull(Country)

toporigin <- data %>%
  group_by(`Loan Originator`) %>%
  count() %>%
  ungroup() %>%
  arrange(-n) %>%
  top_n(topn) %>%
  pull(`Loan Originator`)

data %>% 
   mutate(Currency = fct_other(Currency, keep = topcurrencies, other_level = "Other"),
         # Buyback = as.factor(Buyback),
         # Collateral = as.factor(Collateral),
         Country = fct_other(Country, keep = topcountries, other_level = "Other"),
         Origin = fct_other(`Loan Originator`, keep = toporigin, other_level = "Other"),
         ListingYear = year(`Listing Date`)) %>% 
    select(Rate = `Loan Rate Percent`, Country, Origin, Currency) %>% 
  sample_n(3000) %>% 
  drop_na() %>% 
    ggpairs(
    lower = list(
      continuous = wrap("smooth_loess", alpha = 0.5, color = mintoscolor),
      combo = wrap("box_no_facet", alpha = 0.5, fill = mintoscolor), #"facetdensity",
      discrete = wrap("ratio", alpha = 0.5, fill = mintoscolor)
      # mapping = aes(color = ListYr)
      ),
    upper = list(
      combo = wrap("facetdensity", color = mintoscolor),
      discrete = wrap("ratio", alpha = 0.5, fill = mintoscolor)
    ),
    diag = list(
      continuous = wrap("densityDiag", alpha = 0.5),
      discrete = "barDiag"
    ),
  switch = "y",
  title = "Correlation of Mintos.com loan characteristics") -> cp

cp +
  theme_bw()

library(kableExtra)

data %>% 
  filter(`Closing Date` < (Sys.Date() - days(60))) %>% 
  mutate(Default = recode(`Mintos Rating`, "D" = "D", .default = "notD")) %>% 
  group_by(Default, ClYr = year(`Closing Date`)) %>% 
  count() %>% 
  spread(Default, n) %>% 
  ungroup() %>% 
  mutate(ClYr = as.character(ClYr)) %>%
  knitr::kable(format = "html", format.args = list(big.mark = "'")) %>% 
  kable_styling(bootstrap_options = c("striped", "hover", "condensed")) %>% 
  row_spec(0, bold = T)

data %>% 
  filter(`Closing Date` < (Sys.Date() - days(60))) %>% 
  # mutate(Default = recode(`Mintos Rating`, "D" = "D", .default = "notD")) %>% 
  group_by(`Loan Status`, ClYr = year(`Closing Date`)) %>% 
  count() %>% 
  spread(`Loan Status`, n) %>% 
  ungroup() %>% 
  mutate(ClYr = as.character(ClYr)) %>% 
  knitr::kable(format = "html", format.args = list(big.mark = "'")) %>% 
  kable_styling(bootstrap_options = c("striped", "hover", "condensed")) %>% 
  row_spec(0, bold = T)

data %>% 
  filter(Currency == "EUR",
         `Closing Date` < (Sys.Date() - days(60))) %>% 
  # sample_n(1) %>% glimpse()
  mutate(Lost95 = (`Remaining Loan Amount`/`Initial Loan Amount`)>=0.05) %>% 
  group_by(ClYr = year(`Closing Date`)) %>%
  summarise(Count = n(), CountLoss = sum(Lost95), 
            Amount = sum(`Initial Loan Amount`), 
            AmountLost = sum(`Remaining Loan Amount`)) %>% 
  mutate(lostPerc = (AmountLost/Amount)*100,
         lossPercCount = (CountLoss/Count)*100) %>% 
  ungroup() %>% 
  mutate(ClYr = as.character(ClYr)) %>%
  knitr::kable(format = "html", format.args = list(big.mark = "'"), digits = 2) %>% 
  kable_styling(bootstrap_options = c("striped", "hover", "condensed")) %>% 
  row_spec(0, bold = T)

data %>% 
  filter(Currency == "EUR",
         `Closing Date` < (Sys.Date() - days(60)),
         year(`Closing Date`) == 2019) %>% 
  # sample_n(1) %>% glimpse()
  mutate(Lost95 = (`Remaining Loan Amount`/`Initial Loan Amount`)>=0.05) %>% 
  group_by(`Loan Originator`) %>%
  summarise(Count = n(), CountLoss = sum(Lost95), 
            Amount = sum(`Initial Loan Amount`), 
            AmountLost = sum(`Remaining Loan Amount`)) %>% 
  mutate(lostPerc = (AmountLost/Amount)*100,
         lossPercCount = (CountLoss/Count)*100) %>% 
  ungroup() %>% 
  # mutate(ClYr = as.character(ClYr)) %>%
  
  knitr::kable(format = "html", format.args = list(big.mark = "'"), digits = 2) %>% 
  kable_styling(bootstrap_options = c("striped", "hover", "condensed")) %>% 
  row_spec(0, bold = T)

data %>% 
  filter(Currency == "EUR",
         `Closing Date` < (Sys.Date() - days(60)),
         year(`Closing Date`) == 2019) %>% 
  # sample_n(1) %>% glimpse()
  mutate(Lost95 = (`Remaining Loan Amount`/`Initial Loan Amount`)>=0.05) %>% 
  group_by(Country) %>%
  summarise(Count = n(), CountLoss = sum(Lost95), 
            Amount = sum(`Initial Loan Amount`), 
            AmountLost = sum(`Remaining Loan Amount`)) %>% 
  mutate(lostPerc = (AmountLost/Amount)*100,
         lossPercCount = (CountLoss/Count)*100) %>% 
  ungroup() %>% 
  # mutate(ClYr = as.character(ClYr)) %>%
  
  knitr::kable(format = "html", format.args = list(big.mark = "'"), digits = 2) %>% 
  kable_styling(bootstrap_options = c("striped", "hover", "condensed")) %>% 
  row_spec(0, bold = T)

data %>% 
  filter(Currency != "EUR",
         `Closing Date` < (Sys.Date() - days(60))) %>% 
  # sample_n(1) %>% glimpse()
  mutate(Lost95 = (`Remaining Loan Amount`/`Initial Loan Amount`)>=0.05) %>% 
  group_by(ClYr = year(`Closing Date`)) %>%
  summarise(Count = n(), CountLoss = sum(Lost95), 
            Amount = sum(`Initial Loan Amount`), 
            AmountLost = sum(`Remaining Loan Amount`)) %>% 
  mutate(lostPerc = (AmountLost/Amount)*100,
         lossPercCount = (CountLoss/Count)*100) %>% 
  ungroup() %>% 
  mutate(ClYr = as.character(ClYr)) %>%
  knitr::kable(format = "html", format.args = list(big.mark = "'"), digits = 2) %>% 
  kable_styling(bootstrap_options = c("striped", "hover", "condensed")) %>% 
  row_spec(0, bold = T)

data %>% 
  filter(Currency != "EUR",
         `Closing Date` < (Sys.Date() - days(60)),
         year(`Closing Date`) == 2019) %>% 
  # sample_n(1) %>% glimpse()
  mutate(Lost95 = (`Remaining Loan Amount`/`Initial Loan Amount`)>=0.05) %>% 
  group_by(Currency) %>%
  summarise(Count = n(), CountLoss = sum(Lost95), 
            Amount = sum(`Initial Loan Amount`), 
            AmountLost = sum(`Remaining Loan Amount`)) %>% 
  mutate(lostPerc = (AmountLost/Amount)*100,
         lossPercCount = (CountLoss/Count)*100) %>% 
  ungroup() %>% 
  # mutate(ClYr = as.character(ClYr)) %>%
  knitr::kable(format = "html", format.args = list(big.mark = "'"), digits = 2) %>% 
  kable_styling(bootstrap_options = c("striped", "hover", "condensed")) %>% 
  row_spec(0, bold = T)

data %>% 
  filter(Currency != "EUR",
         `Closing Date` < (Sys.Date() - days(60)),
         year(`Closing Date`) == 2019) %>% 
  # sample_n(1) %>% glimpse()
  mutate(Lost95 = (`Remaining Loan Amount`/`Initial Loan Amount`)>=0.05) %>% 
  group_by(`Loan Originator`, Currency) %>%
  summarise(Count = n(), CountLoss = sum(Lost95), 
            Amount = sum(`Initial Loan Amount`), 
            AmountLost = sum(`Remaining Loan Amount`)) %>% 
  mutate(lostPerc = (AmountLost/Amount)*100,
         lossPercCount = (CountLoss/Count)*100) %>% 
  ungroup() %>% 
  # mutate(ClYr = as.character(ClYr)) %>%
  knitr::kable(format = "html", format.args = list(big.mark = "'"), digits = 2) %>% 
  kable_styling(bootstrap_options = c("striped", "hover", "condensed")) %>% 
  row_spec(0, bold = T)