Project 1: Lending Club’s data
JFdarre
September 30, 2015
Lending club:
For this project, we wish to present and explore the data provided by Lending Club.
Here is the website: Lending Club
Lending Club (LC) is a peer to peer online lending platform. It is the world’s largest marketplace connecting borrowers and investors, where consumers and small business owners lower the cost of their credit and enjoy a better experience than traditional bank lending, and investors earn attractive risk-adjusted returns.
How it works:
- Customers interested in a loan complete a simple application at LendingClub.com
- LC leverage online data and technology to quickly assess risk, determine a credit rating and assign appropriate interest rates.
- Qualified applicants receive offers in just minutes and can evaluate loan options with no impact to their credit score
- Investors ranging from individuals to institutions select loans in which to invest and can earn monthly returns
The entire process is online, using technology to lower the cost of credit and pass the savings back in the form of lower rates for borrowers and solid returns for investors.
Here is the link to more details about LC.
Goal of the project:
We will present and explore the data provided by LC at this address. This data was made available to us after the creation of an investor account.
The data consists in 4 files updated every quarter on the same day as the quaterly results of the compagny are released. They contain information on almost all the loans issued by LC. The only loans missing from these files are the few loans where LC was not authorized to release publicly the details of the transactions.
The information available for each loan consists of all the details of the loans at the time of their issuance as well as more information relative to the latest status of loan such as how much principal has been paid so far, how much interest, if the loan was fully paid or defaulted, or if the borrower is late on payments etc.
Downloading the data:
We downloaded the 4 csv files and loaded them in our environement. We then combined them into one table that we called LC and saved it under LC.Rdata:
setwd('/Users/jfdarre/Documents/NYCDS/Project1')
LCA = read.csv("LC2007-2012.csv", stringsAsFactors = FALSE, header = T, skip = 1)
LCB = read.csv("LC2012-2013.csv", stringsAsFactors = FALSE, header = T, skip = 1)
LCC = read.csv("LC2013-2014.csv", stringsAsFactors = FALSE, header = T, skip = 1)
LCD = read.csv("LC2014-2015.csv", stringsAsFactors = FALSE, header = T, skip = 1)
LCE = rbind(LCA, LCB, LCC, LCD)
LC = tbl_df(LCE)
save(LC, file = "LC.RData")
rm(list=ls())Preparing for the analysis:
The next step is to load all the librabies needed for our exploration of the data:
library(ggplot2)
library(dplyr)
library(reshape)
library(ggthemes)
library(RColorBrewer)
library(maps)
library(lattice)
library(plotly)Setting up some useful variables and loading loan data:
rm(list=ls())
setwd('/Users/jfdarre/Documents/NYCDS/Project1')
# Date of the latest update of the reports:
report_date = 201506
# Loading the data and storing the original data into LC0:
load("LC.RData")
LC0 = LCHere is a full description of every fields:
| LoanStatNew | Description |
|---|---|
| zip_code | The first 3 numbers of the zip code provided by the borrower in the loan application. |
| addr_state | The state provided by the borrower in the loan application |
| annual_inc | The annual income provided by the borrower during registration. |
| collection_recovery_fee | post charge off collection fee |
| collections_12_mths_ex_med | Number of collections in 12 months excluding medical collections |
| delinq_2yrs | The number of 30+ days past-due incidences of delinquency in the borrower’s credit file for the past 2 years |
| desc | Loan description provided by the borrower |
| dti | A ratio calculated using the borrower’s total monthly debt payments on the total debt obligations, excluding mortgage and the requested LC loan, divided by the borrower’s self-reported monthly income. |
| earliest_cr_line | The month the borrower’s earliest reported credit line was opened |
| emp_length | Employment length in years. Possible values are between 0 and 10 where 0 means less than one year and 10 means ten or more years. |
| emp_title | The job title supplied by the Borrower when applying for the loan. |
| fico_range_high | The upper boundary of range the borrower’s FICO belongs to. |
| fico_range_low | The lower boundary of range the borrower’s FICO belongs to. |
| funded_amnt | The total amount committed to that loan at that point in time. |
| funded_amnt_inv | The total amount committed by investors for that loan at that point in time. |
| grade | LC assigned loan grade |
| home_ownership | The home ownership status provided by the borrower during registration. Our values are: RENT, OWN, MORTGAGE, OTHER. |
| id | A unique LC assigned ID for the loan listing. |
| initial_list_status | The initial listing status of the loan. Possible values are – W, F |
| inq_last_6mths | The number of inquiries by creditors during the past 6 months. |
| installment | The monthly payment owed by the borrower if the loan originates. |
| int_rate | Interest Rate on the loan |
| is_inc_v | Indicates if income was verified by LC, not verified, or if the income source was verified |
| issue_d | The month which the loan was funded |
| last_credit_pull_d | The most recent month LC pulled credit for this loan |
| last_fico_range_high | The last upper boundary of range the borrower’s FICO belongs to pulled. |
| last_fico_range_low | The last lower boundary of range the borrower’s FICO belongs to pulled. |
| last_pymnt_amnt | Last total payment amount received |
| last_pymnt_d | Last month payment was received |
| loan_amnt | The listed amount of the loan applied for by the borrower. If at some point in time, the credit department reduces the loan amount, then it will be reflected in this value. |
| loan_status | Current status of the loan |
| member_id | A unique LC assigned Id for the borrower member. |
| mths_since_last_delinq | The number of months since the borrower’s last delinquency. |
| mths_since_last_major_derog | Months since most recent 90-day or worse rating |
| mths_since_last_record | The number of months since the last public record. |
| next_pymnt_d | Next scheduled payment date |
| open_acc | The number of open credit lines in the borrower’s credit file. |
| out_prncp | Remaining outstanding principal for total amount funded |
| out_prncp_inv | Remaining outstanding principal for portion of total amount funded by investors |
| policy_code | Publicly available policy_code=1, new products not publicly available policy_code=2 |
| pub_rec | Number of derogatory public records |
| purpose | A category provided by the borrower for the loan request. |
| pymnt_plan | Indicates if a payment plan has been put in place for the loan |
| recoveries | post charge off gross recovery |
| revol_bal | Total credit revolving balance |
| revol_util | Revolving line utilization rate, or the amount of credit the borrower is using relative to all available revolving credit. |
| sub_grade | LC assigned loan subgrade |
| term | The number of payments on the loan. Values are in months and can be either 36 or 60. |
| title | The loan title provided by the borrower |
| total_acc | The total number of credit lines currently in the borrower’s credit file |
| total_pymnt | Payments received to date for total amount funded |
| total_pymnt_inv | Payments received to date for portion of total amount funded by investors |
| total_rec_int | Interest received to date |
| total_rec_late_fee | Late fees received to date |
| total_rec_prncp | Principal received to date |
| url | URL for the LC page with listing data. |
Now let’s have a look at some examples to understand the fields:
# illustrative example
t(filter(LC0, id == 36805548))## [,1]
## id "36805548"
## member_id "39558264"
## loan_amnt "10400"
## funded_amnt "10400"
## funded_amnt_inv "10400"
## term " 36 months"
## int_rate " 6.99%"
## installment "321.08"
## grade "A"
## sub_grade "A3"
## emp_title "Truck Driver Delivery Personel"
## emp_length "8 years"
## home_ownership "MORTGAGE"
## annual_inc "58000"
## verification_status "not verified"
## issue_d "Dec-2014"
## loan_status "Current"
## pymnt_plan "n"
## url "https://www.lendingclub.com/browse/loanDetail.action?loan_id=36805548"
## desc ""
## purpose "credit_card"
## title "Credit card refinancing"
## zip_code "937xx"
## addr_state "CA"
## dti "14.92"
## delinq_2yrs "0"
## earliest_cr_line "Sep-1989"
## fico_range_low "710"
## fico_range_high "714"
## inq_last_6mths "2"
## mths_since_last_delinq "42"
## mths_since_last_record NA
## open_acc "17"
## pub_rec "0"
## revol_bal "6133"
## revol_util "31.6%"
## total_acc "36"
## initial_list_status "w"
## out_prncp "8544.32"
## out_prncp_inv "8544.32"
## total_pymnt "2237.46"
## total_pymnt_inv "2237.46"
## total_rec_prncp "1855.68"
## total_rec_int "381.78"
## total_rec_late_fee "0"
## recoveries "0"
## collection_recovery_fee "0"
## last_pymnt_d "Aug-2015"
## last_pymnt_amnt "321.08"
## next_pymnt_d "Sep-2015"
## last_credit_pull_d "Aug-2015"
## last_fico_range_high "679"
## last_fico_range_low "675"
## collections_12_mths_ex_med "0"
## mths_since_last_major_derog "59"
## policy_code "1"# looking for loans that were delinquent at some point in time, and looking at an example
filter(LC, recoveries != 0)## Source: local data frame [18,006 x 56]
##
## id member_id loan_amnt funded_amnt funded_amnt_inv term
## (chr) (int) (int) (int) (dbl) (chr)
## 1 1077430 1314167 2500 2500 2500 60 months
## 2 1071795 1306957 5600 5600 5600 60 months
## 3 1071570 1306721 5375 5375 5350 60 months
## 4 1064687 1298717 9000 9000 9000 36 months
## 5 1069057 1303503 10000 10000 10000 36 months
## 6 1069559 1304634 6000 6000 6000 36 months
## 7 1069657 1304764 5000 5000 5000 60 months
## 8 1069465 1304521 5000 5000 5000 36 months
## 9 1069248 1304123 15000 15000 15000 36 months
## 10 1069243 1304116 12000 12000 12000 36 months
## .. ... ... ... ... ... ...
## Variables not shown: int_rate (chr), installment (dbl), grade (chr),
## sub_grade (chr), emp_title (chr), emp_length (chr), home_ownership
## (chr), annual_inc (dbl), verification_status (chr), issue_d (chr),
## loan_status (chr), pymnt_plan (chr), url (chr), desc (chr), purpose
## (chr), title (chr), zip_code (chr), addr_state (chr), dti (dbl),
## delinq_2yrs (int), earliest_cr_line (chr), fico_range_low (int),
## fico_range_high (int), inq_last_6mths (int), mths_since_last_delinq
## (int), mths_since_last_record (int), open_acc (int), pub_rec (int),
## revol_bal (int), revol_util (chr), total_acc (int), initial_list_status
## (chr), out_prncp (dbl), out_prncp_inv (dbl), total_pymnt (dbl),
## total_pymnt_inv (dbl), total_rec_prncp (dbl), total_rec_int (dbl),
## total_rec_late_fee (dbl), recoveries (dbl), collection_recovery_fee
## (dbl), last_pymnt_d (chr), last_pymnt_amnt (dbl), next_pymnt_d (chr),
## last_credit_pull_d (chr), last_fico_range_high (int),
## last_fico_range_low (int), collections_12_mths_ex_med (int),
## mths_since_last_major_derog (int), policy_code (int)# looking for loans that were delinquent at some point in time, and looking at an example
t(filter(LC, id == 1069559))## [,1]
## id "1069559"
## member_id "1304634"
## loan_amnt "6000"
## funded_amnt "6000"
## funded_amnt_inv "6000"
## term " 36 months"
## int_rate " 11.71%"
## installment "198.46"
## grade "B"
## sub_grade "B3"
## emp_title "bmg-educational"
## emp_length "1 year"
## home_ownership "RENT"
## annual_inc "76000"
## verification_status "not verified"
## issue_d "Dec-2011"
## loan_status "Charged Off"
## pymnt_plan "n"
## url "https://www.lendingclub.com/browse/loanDetail.action?loan_id=1069559"
## desc ""
## purpose "major_purchase"
## title "cash"
## zip_code "900xx"
## addr_state "CA"
## dti "2.4"
## delinq_2yrs "0"
## earliest_cr_line "Jun-2001"
## fico_range_low "690"
## fico_range_high "694"
## inq_last_6mths "1"
## mths_since_last_delinq NA
## mths_since_last_record NA
## open_acc "7"
## pub_rec "0"
## revol_bal "5963"
## revol_util "29.7%"
## total_acc "7"
## initial_list_status "f"
## out_prncp "0"
## out_prncp_inv "0"
## total_pymnt "2050.14"
## total_pymnt_inv "2050.14"
## total_rec_prncp "1305.58"
## total_rec_int "475.25"
## total_rec_late_fee "0"
## recoveries "269.31"
## collection_recovery_fee "2.57"
## last_pymnt_d "Oct-2012"
## last_pymnt_amnt "198.46"
## next_pymnt_d ""
## last_credit_pull_d "Mar-2013"
## last_fico_range_high "499"
## last_fico_range_low "0"
## collections_12_mths_ex_med "0"
## mths_since_last_major_derog NA
## policy_code "1"Useful functions:
To facilitate our discovery and manipulation of the data we created some useful functions that will be used repetitively:
# used to create FICO bins to group fico scores
bin_name = function(x) {
low = 490 + (x - 1) * 30
high = low + 30
paste(low, high, sep = "-")
}
# used to generate summaries grouping by ... and showing how much loan amount is in each status
sumPerSatus = function(x, ...){
x %>% group_by(...) %>%
summarize(., charged = round(sum(loan_status_new == "Charged Off") / n() * 100, 2),
net_EL = round(sum((1 - total_rec_prncp / funded_amnt) * 100) / n(), 2),
avg_fico = round(mean(fico_range_high)),
avg_grade = sub_grade_vec[mean(LC_score)])
}
# used to generate summaries grouping by ... and showing how much loan amount and how many loans were issued by groups
sumAmnts = function(x, ...) {
x %>% group_by(., ...) %>%
summarise(., total_issued = prettyNum(round(sum(loan_amnt/1)),big.mark = ","),
n = prettyNum(round(n()),big.mark = ","))
}
# used to generate summaries grouping by ... and showing how much loan amount, principal out and recieved principle
# remove the "pretty numbers" to enable the use of the results in formula
sumAmnt = function(x, ...) {
x %>% group_by(., ...) %>%
summarise(., total_issued = round(sum(loan_amnt/1e6),1),
n = round(n()))
}
# used to generate summaries grouping by ... and showing usefull statics about each group
sumStats = function(x, ...) {
x %>% group_by(., ...) %>%
summarise(., median = prettyNum(round(median(loan_amnt/1)),big.mark = ","),
average = prettyNum(round(mean(loan_amnt/1)),big.mark = ","),
stdev = prettyNum(round(sd(loan_amnt/1)),big.mark = ","))
}
# used to generate summaries grouping by ... and showing usefull statics about each group
# remove the "pretty numbers" to enable the use of the results in formula
sumStat = function(x, ...) {
x %>% group_by(., ...) %>%
summarise(., median = round(median(loan_amnt/1)),
average = round(mean(loan_amnt/1)),
stdev = round(sd(loan_amnt/1)))
}
# calculating retrun on investment
roi = function(x, ...){
x %>% group_by(...) %>%
summarize(., roi = round(sum((total_pymnt / funded_amnt) * 100) / n(), 2))
}Modifying, cleaning the data and adding useful columns to the original data:
Now that we have prepared the grounds for our analysis we need to clean up the data and add some fields that will prove useful for our analysis.
First, we will clean the data by removing some corrupted loans. Note that for every entry that we remove, we check the sum of the loan amounts to judge how much of the data we are removing. In the end we will remove around USD 10 mm worth of loan which corresponds to 0.1% of the total. This is perfectly acceptable.
# removing policy_code == 2, i.e. "not public" and then removing the comlumn
sum(filter(LC0, policy_code != 1)$loan_amnt, na.rm = T)## [1] 0LC = filter(LC, policy_code == 1)
LC = select(LC, -policy_code)
# removing 28 records with a lot of missing data:
sum(filter(LC0, is.na(pub_rec))$loan_amnt, na.rm = T)## [1] 113200LC = filter(LC, !is.na(pub_rec))
# Filtering out the entries where last_fico_range_high = 0
sum(filter(LC0, last_fico_range_high == 0)$loan_amnt, na.rm = T)## [1] 1102375LC = filter(LC, last_fico_range_high != 0)
# Removing the loans without any entry for revol_util
sum(filter(LC0, revol_util == "")$loan_amnt, na.rm = T)## [1] 4582350LC = filter(LC, revol_util != "")
# Removing the loans with fico scores < 660 as they are very few of them, and LC changed their
# policy and does not issue loan for scores below 660
sum(filter(LC0, fico_range_high < 660)$loan_amnt, na.rm = T)## [1] 3531950LC = filter(LC, fico_range_high >= 660)
# Removing "Does not meet the credit policy. Status:" from:
# Does not meet the credit policy. Status:Charged Off
# Does not meet the credit policy. Status:Current
# Does not meet the credit policy. Status:Fully Paid
LC = mutate(LC, loan_status_new =
ifelse(grepl("Does not meet the credit policy. Status:", loan_status),
gsub("Does not meet the credit policy. Status:","",loan_status),
loan_status))Now we modify and add features to our remaining data set. These features will be key in our analysis. We are converting dates and characters to numeric to enable calculations as well as creating buckets to enable classifications:
# adding issue year, quarter
# adding FICO scores buckets
Months = c("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
LC = mutate(LC, issue_y = strtoi(substr(issue_d, 5, 9)),
issue_m = match(substr(issue_d, 1, 3),Months),
issue_ym = issue_y * 100 + issue_m,
issue_q = ceiling(issue_m / 3),
issue_yq = paste(issue_y, "-Q", issue_q, sep = ""),
n = 1)
# FICO buckets for future visualizations
LC = mutate(LC, FICO_buckets_Original = ceiling((fico_range_high - 490) / 30),
FICO_buckets_Last = ceiling((fico_range_high - 490) / 30),
FICO_bin_name_Original = sapply(FICO_buckets_Original, bin_name),
FICO_bin_name_Last = sapply(FICO_buckets_Last, bin_name))
# Add a feature "matured" for Loans that have or would have matured by report_date
LC = mutate(LC, matured = ifelse((issue_ym + ifelse(term == " 36 months", 300, 500)) > report_date, F, T))
# reduce the number of categories of purpose
LC = mutate(LC, purpose_new = ifelse(purpose == "credit_card" |
purpose == "debt_consolidation", "debt",
ifelse(purpose == "car" |
purpose == "major_purchase" |
purpose == "vacation" |
purpose == "wedding" |
purpose == "medical" |
purpose == "other", "purchase",
ifelse(purpose == "house" |
purpose == "home_improvement" |
purpose == "moving" |
purpose == "renewable_energy", "purchase", purpose))))
# reduce the number of categories of purpose
LC = mutate(LC, home = ifelse(home_ownership == "ANY" | home_ownership == "NONE", "OTHER", home_ownership))
# give LC grade numeric values
sub_grade_vec = unique(LC$sub_grade) %>% .[order(., decreasing = T)]
LC = mutate(LC, LC_score = match(sub_grade, sub_grade_vec))
# adding a feature credit_ym corresponding to how many years old is the credit history of a borrower:
LC = mutate(LC,
credit_ym = round(((floor(issue_ym/100)*100 +
((issue_ym - floor(issue_ym/100)*100)-1)/12*100)
- (strtoi(substr(earliest_cr_line, 5, 9)) * 100 +
(match(substr(earliest_cr_line, 1, 3),Months)-1)/12*100))/100,1))
# creating issue_y buckets:
LC = mutate(LC, issue_bucket = ifelse(issue_y <= 2012, "2007-2012", issue_y))
# delinq_2yrs buckets:
LC = mutate(LC, delinq_bucket = ifelse(delinq_2yrs >= 2, "2+", delinq_2yrs))
# inq_last_6mths buckets:
LC = mutate(LC, inq_bucket = ifelse(inq_last_6mths >= 7, "7+",
ifelse(inq_last_6mths >= 5, "5-6",
ifelse(inq_last_6mths >= 3, "3-4",
ifelse(inq_last_6mths >= 1, "1-2", 0)))))
# public record buckets:
LC = mutate(LC, rec_bucket = #ifelse(pub_rec >= 10, "10+",
#ifelse(pub_rec >= 7, "7-9",
#ifelse(pub_rec >= 4, "4-6",
ifelse(pub_rec >= 1, "1+", 0))#)))
# Annual income quantile buckets:
groupvec = quantile(LC$annual_inc, seq(0,1,0.1))
labels = c(0, prettyNum(groupvec[2:10], big.mark = ","), "+inf")
labels = paste(labels[1:10], labels[2:11], sep = "-")
LC = mutate(LC, annual_inc_bucket = cut(LC$annual_inc, breaks = groupvec, labels = factor(labels), include.lowest=TRUE))
# DTI quantile buckets:
groupvec = quantile(LC$dti, seq(0,1,0.1))
labels = c(0, prettyNum(groupvec[2:10], big.mark = ","), "+inf")
labels = paste(labels[1:10], labels[2:11], sep = "-")
LC = mutate(LC, dti_bucket = cut(LC$dti, breaks = groupvec, labels = factor(labels), include.lowest=TRUE))
# Revolving balance quantile buckets:
LC = mutate(LC, revol = as.numeric(gsub("%","",revol_util)))
groupvec = quantile(LC$revol, seq(0,1,0.1))
labels = c(0, prettyNum(groupvec[2:10], big.mark = ","), "+inf")
labels = paste(labels[1:10], labels[2:11], sep = "-")
LC = mutate(LC, revol_bucket = cut(LC$revol, breaks = groupvec, labels = factor(labels), include.lowest=TRUE))
# Revolving balance quantile buckets:
groupvec = quantile(LC$revol_bal, seq(0,1,0.1))
labels = c(0, prettyNum(groupvec[2:10], big.mark = ","), "+inf")
labels = paste(labels[1:10], labels[2:11], sep = "-")
LC = mutate(LC, revol_bal_bucket = cut(LC$revol_bal, breaks = groupvec, labels = factor(labels), include.lowest=TRUE))
# total accounts buckets:
groupvec = quantile(LC$total_acc, seq(0,1,0.1))
labels = c(0, prettyNum(groupvec[2:10], big.mark = ","), "+inf")
labels = paste(labels[1:10], labels[2:11], sep = "-")
LC = mutate(LC, total_acc_bucket = cut(LC$total_acc, breaks = groupvec, labels = factor(labels), include.lowest=TRUE))
# open accounts buckets:
groupvec = quantile(LC$open_acc, seq(0,1,0.1))
labels = c(0, prettyNum(groupvec[2:10], big.mark = ","), "+inf")
labels = paste(labels[1:10], labels[2:11], sep = "-")
LC = mutate(LC, open_acc_bucket = cut(LC$open_acc, breaks = groupvec, labels = factor(labels), include.lowest=TRUE))
# credit_y quantile buckets:
groupvec = quantile(LC$credit_ym, seq(0,1,0.1))
labels = c(0, prettyNum(groupvec[2:10], big.mark = ","), "+inf")
labels = paste(labels[1:10], labels[2:11], sep = "-")
LC = mutate(LC, credit_ym_bucket = cut(LC$credit_ym, breaks = groupvec, labels = factor(labels), include.lowest=TRUE))We also create a subset of our modified data. This subset will corespond to all the loans that have or would have matured by the time of the report. The loans with 3 years of maturity may have been issued all the way to Jun-2012 as we recall that our current reports are as of Jun-2015. Similarly, the loans with 5 years of maturity will be selected only if they were issued before Jun-2010.
This subset will be called LCmatured and will enable us to extract true default rates by comparing solely loans whose final status is know with certainty.
Note that we will have to over-write the quantile buckets that we just created on our dataset LC to adjust them to our new subset LCmatured.
# create sub table of only the matured loans:
LCmatured = filter(LC, matured == T)
# we'll re-do the quantile buckets for LCmatured to adjust them:
# Annual income quantile buckets:
groupvec = quantile(LCmatured$annual_inc, seq(0,1,0.1))
labels = c(0, prettyNum(groupvec[2:10], big.mark = ","), "+inf")
labels = paste(labels[1:10], labels[2:11], sep = "-")
LCmatured = mutate(LCmatured, annual_inc_bucket = cut(LCmatured$annual_inc, breaks = groupvec, labels = factor(labels), include.lowest=TRUE))
# DTI quantile buckets:
groupvec = quantile(LCmatured$dti, seq(0,1,0.1))
labels = c(0, prettyNum(groupvec[2:10], big.mark = ","), "+inf")
labels = paste(labels[1:10], labels[2:11], sep = "-")
LCmatured = mutate(LCmatured, dti_bucket = cut(LCmatured$dti, breaks = groupvec, labels = factor(labels), include.lowest=TRUE))
# Revolving balance utilization quantile buckets:
# First we need to mutate a field to convert them to numeric:
groupvec = quantile(LCmatured$revol, seq(0,1,0.1))
labels = c(0, prettyNum(groupvec[2:10], big.mark = ","), "+inf")
labels = paste(labels[1:10], labels[2:11], sep = "-")
LCmatured = mutate(LCmatured, revol_bucket = cut(LCmatured$revol, breaks = groupvec, labels = factor(labels), include.lowest=TRUE))
# Revolving balance quantile buckets:
groupvec = quantile(LCmatured$revol_bal, seq(0,1,0.1))
labels = c(0, prettyNum(groupvec[2:10], big.mark = ","), "+inf")
labels = paste(labels[1:10], labels[2:11], sep = "-")
LCmatured = mutate(LCmatured, revol_bal_bucket = cut(LCmatured$revol_bal, breaks = groupvec, labels = factor(labels), include.lowest=TRUE))
# total accounts buckets:
groupvec = quantile(LCmatured$total_acc, seq(0,1,0.1))
labels = c(0, prettyNum(groupvec[2:10], big.mark = ","), "+inf")
labels = paste(labels[1:10], labels[2:11], sep = "-")
LCmatured = mutate(LCmatured, total_acc_bucket = cut(LCmatured$total_acc, breaks = groupvec, labels = factor(labels), include.lowest=TRUE))
# open accounts buckets:
groupvec = quantile(LCmatured$open_acc, seq(0,1,0.1))
labels = c(0, prettyNum(groupvec[2:10], big.mark = ","), "+inf")
labels = paste(labels[1:10], labels[2:11], sep = "-")
LCmatured = mutate(LCmatured, open_acc_bucket = cut(LCmatured$open_acc, breaks = groupvec, labels = factor(labels), include.lowest=TRUE))
# credit_y quantile buckets:
groupvec = quantile(LCmatured$credit_ym, seq(0,1,0.1))
labels = c(0, prettyNum(groupvec[2:10], big.mark = ","), "+inf")
labels = paste(labels[1:10], labels[2:11], sep = "-")
LCmatured = mutate(LCmatured, credit_ym_bucket = cut(LCmatured$credit_ym, breaks = groupvec, labels = factor(labels), include.lowest=TRUE))First look at the data, exploratory research:
Looking at loan amounts types and loan amounts in each category
# Looking at loan amounts types and loan amounts in each category
# Smallest loan:
min(LC$loan_amnt)## [1] 500# Largest loan:
max(LC$loan_amnt)## [1] 35000# Total amount of loans in USD millon
prettyNum(round(sum(LC$loan_amnt/1e6), 1),big.mark = ",")## [1] "9,409.2"# Total amount of loans in USD millon using the original data:
prettyNum(round(sum(LC0$loan_amnt/1e6, na.rm = T),1), big.mark = ",")## [1] "9,418.4"Looking at the distribution of the loans relative to their latest status:
# Looking at total loan amnts per loan_status
# Report showing the amounts and number of loans in each category, here the latest status of the loans
sumAmnts(LC, loan_status_new)## Source: local data frame [7 x 3]
##
## loan_status_new total_issued n
## (chr) (chr) (chr)
## 1 Charged Off 481,653,700 33,793
## 2 Current 6,666,935,175 440,981
## 3 Default 2,414,500 160
## 4 Fully Paid 2,016,144,450 154,966
## 5 In Grace Period 83,747,125 5,218
## 6 Late (16-30 days) 31,062,300 2,043
## 7 Late (31-120 days) 127,221,050 8,259# Report showing some statistics for each category, here the latest status of the loans
sumStats(LC, loan_status_new)## Source: local data frame [7 x 4]
##
## loan_status_new median average stdev
## (chr) (chr) (chr) (chr)
## 1 Charged Off 12,000 14,253 8,362
## 2 Current 14,000 15,118 8,411
## 3 Default 13,750 15,091 9,075
## 4 Fully Paid 11,200 13,010 7,937
## 5 In Grace Period 15,000 16,050 8,543
## 6 Late (16-30 days) 13,750 15,204 8,516
## 7 Late (31-120 days) 14,000 15,404 8,524# Pie chart of the distribution of Loans across the different status:
sumAmnt(LC, loan_status_new) %>% merge(sumPerSatus(LC, loan_status_new)) %>%
plot_ly(type = "pie",
labels = loan_status_new,
values = total_issued,
hole = 0.5,
marker = list(colors = c("#f2f0f7","#dadaeb","#bcbddc","#9e9ac8","#807dba","#6a51a3","#4a1486"),
line = list(width = 1, color = "rgb(52, 110, 165)")),
sort = F,
direction = "counterclockwise",
rotation = 90,
textinfo = "label+percent",
textfont = list(size = 14),
text = paste("Default rates: ", charged),
textposition = "outside") %>%
layout(title = 'LOAN ISSUED GROUPED BY STATUS<br>(Hover for breakdown)',
height = 500, width = 1400, autosize = T,
legend = list(font = list(size = 16), x = 1, y = 1, traceorder = "normal"))## Success! Created a new plotly here -> https://plot.ly/~jfdarre/660Looking at Lending Club’s issuances over the years:
# Summary by issue year
# Report showing the amounts and number of loans in each category, here the year of issuance
sumAmnts(LC, issue_y)## Source: local data frame [9 x 3]
##
## issue_y total_issued n
## (int) (chr) (chr)
## 1 2007 3,717,025 409
## 2 2008 18,659,300 2,054
## 3 2009 51,823,950 5,263
## 4 2010 131,893,250 12,513
## 5 2011 261,580,825 21,706
## 6 2012 717,928,125 53,314
## 7 2013 1,980,602,200 134,656
## 8 2014 3,501,506,225 235,472
## 9 2015 2,741,467,400 180,033# Report showing some statistics for each category, here the year of issuance
sumStats(LC, issue_y)## Source: local data frame [9 x 4]
##
## issue_y median average stdev
## (int) (chr) (chr) (chr)
## 1 2007 6,800 9,088 6,644
## 2 2008 7,500 9,084 5,863
## 3 2009 8,800 9,847 5,991
## 4 2010 9,500 10,540 6,599
## 5 2011 10,000 12,051 8,169
## 6 2012 12,000 13,466 8,086
## 7 2013 13,000 14,709 8,098
## 8 2014 13,000 14,870 8,437
## 9 2015 14,000 15,228 8,480# Bar chart of LC's quaterly loan issuance
sumAmnt(LC, issue_yq) %>%
plot_ly(type = "bar",
x = issue_yq,
y = total_issued,
marker = list(color = total_issued,
colorscale = list(c(0, "rgb(183, 232, 161)"), list(1, "rgb(106, 168, 79)")),
line = list(width = 1, color = "rgb(255, 255, 255)"))
) %>%
layout(title = "QUATERLY LOAN ISSUANCE", bargap = 0,
yaxis = list(title = "TOTAL LOAN ISSUED IN MLN USD"),
xaxis = list(title = "YEAR OF ISSUE", range = c(7,32.5), dtick = 2))## Success! Created a new plotly here -> https://plot.ly/~jfdarre/662Looking at Lending Club’s issuances over the United States:
# Preparing our data for the plot:
sumStates = group_by(LC, addr_state) %>%
summarise(., total_issued = round(sum(loan_amnt/1e6),1), n = n()) %>%
merge(sumPerSatus(LC, addr_state)[,c(1,2)]) %>%
merge(data.frame(addr_state, state_name))
# adding the text for the hovering functionality:
sumStates$hover = with(sumStates, paste(state_name, '<br>',
"Total amount issued:", prettyNum(total_issued, big.mark = ","), "<br>",
"Number of issued loans:", prettyNum(n, big.mark = ","), "<br>",
"Default rates:", charged))
# because the range of values is to large, the map looks white aside of california and a few other states.
# To avoid this we limit the the values to a maximum, here 500.
sumStates$map_amnt = with(sumStates, pmin(total_issued, 500))
# options for the markers:
l <- list(color = toRGB("steelblue"), width = 1)
# options for the geo:
g = list(scope = 'usa', projection = list(type = 'albers usa'), showlakes = F, lakecolor = toRGB('white'))
# plot of the map of issuance made by lending club so far by state:
plot_ly(sumStates, z = map_amnt, text = hover, locations = addr_state, type = 'choropleth',
locationmode = 'USA-states', color = map_amnt, colors = 'Purples',
marker = list(line = l), colorbar = list(title = "Amount Issued <br>in millions USD")) %>%
layout(title = toupper('Total loan amount issued though LC<br>(Hover for breakdown)'), geo = g)## Success! Created a new plotly here -> https://plot.ly/~jfdarre/664Looking at different statistics and disributions of LC grades:
# Looking at grade statistics:
# Report showing the amounts and number of loans in each category, here LC grades
sumAmnts(LC, grade)## Source: local data frame [7 x 3]
##
## grade total_issued n
## (chr) (chr) (chr)
## 1 A 1,464,627,275 106,632
## 2 B 2,482,035,550 184,093
## 3 C 2,523,126,075 176,391
## 4 D 1,603,390,750 105,095
## 5 E 912,883,050 51,295
## 6 F 333,103,450 17,623
## 7 G 90,012,150 4,291# Same report as above but constrained to LCmatured for comparison:
sumAmnts(LCmatured, grade)## Source: local data frame [7 x 3]
##
## grade total_issued n
## (chr) (chr) (chr)
## 1 A 134,843,575 14,502
## 2 B 157,486,075 15,183
## 3 C 93,918,425 9,440
## 4 D 62,679,175 5,387
## 5 E 20,798,750 1,473
## 6 F 5,851,850 397
## 7 G 2,277,700 155# Report showing some statistics for each category, here LC grades
sumStats(LC, grade)## Source: local data frame [7 x 4]
##
## grade median average stdev
## (chr) (chr) (chr) (chr)
## 1 A 12,000 13,735 7,388
## 2 B 12,000 13,483 7,596
## 3 C 12,000 14,304 8,420
## 4 D 14,000 15,257 8,948
## 5 E 16,750 17,797 9,254
## 6 F 18,425 18,902 9,300
## 7 G 20,000 20,977 8,713# Same report as above but constrained to LCmatured for comparison:
sumStats(LCmatured, grade)## Source: local data frame [7 x 4]
##
## grade median average stdev
## (chr) (chr) (chr) (chr)
## 1 A 8,000 9,298 6,025
## 2 B 9,450 10,373 6,587
## 3 C 9,000 9,949 6,293
## 4 D 10,000 11,635 7,646
## 5 E 12,500 14,120 8,819
## 6 F 14,800 14,740 8,420
## 7 G 14,500 14,695 8,537# Report showing charge off rates, average LC scores and FICO scores for each category, here LC grades
sumPerSatus(LC, grade)## Source: local data frame [7 x 5]
##
## grade charged net_EL avg_fico avg_grade
## (chr) (dbl) (dbl) (dbl) (chr)
## 1 A 1.98 49.93 730 A4
## 2 B 4.11 50.05 702 B4
## 3 C 5.25 58.54 692 C3
## 4 D 7.27 60.85 686 D3
## 5 E 8.61 66.70 685 E3
## 6 F 12.60 65.55 682 F3
## 7 G 13.59 67.04 680 G3# Same report as above but constrained to LCmatured for comparison:
sumPerSatus(LCmatured, grade)## Source: local data frame [7 x 5]
##
## grade charged net_EL avg_fico avg_grade
## (chr) (dbl) (dbl) (dbl) (chr)
## 1 A 6.50 3.60 753 A4
## 2 B 12.03 7.05 713 B4
## 3 C 16.42 10.26 691 C3
## 4 D 20.07 12.77 681 D3
## 5 E 22.47 15.33 678 E3
## 6 F 30.48 20.86 677 F3
## 7 G 32.90 21.76 674 G3# Pie chart of the distribution of Loans across the different LC grades:
sumAmnt(LC, grade) %>% merge(sumPerSatus(LC, grade)) %>%
plot_ly(type = "pie",
labels = grade,
values = total_issued,
hole = 0.5,
marker = list(colors = c("#f2f0f7","#dadaeb","#bcbddc","#9e9ac8","#807dba","#6a51a3","#4a1486"),
line = list(width = 1, color = "rgb(52, 110, 165)")),
sort = F,
direction = "counterclockwise",
rotation = 120,
textinfo = "label+percent",
textfont = list(size = 14),
text = paste("Default rates: ", charged),
textposition = "outside") %>%
layout(title = 'LOAN ISSUED GROUPED BY GRADES<br>(Hover for breakdown)',
height = 731, width = 1274, autosize = T,
legend = list(font = list(size = 16), x = 0.88, y = 1, traceorder = "normal"))## Success! Created a new plotly here -> https://plot.ly/~jfdarre/666Looking at different statistics and disributions across the different FICO buckets:
# Now let's have a look at FICO scores:
# Report showing the amounts and number of loans in each category, here the FICO score buckets
sumAmnts(LC, FICO_bin_name_Original)## Source: local data frame [7 x 3]
##
## FICO_bin_name_Original total_issued n
## (chr) (chr) (chr)
## 1 640-670 1,413,717,625 111,559
## 2 670-700 4,120,303,050 289,106
## 3 700-730 2,506,786,975 157,429
## 4 730-760 900,005,000 55,966
## 5 760-790 325,995,650 21,772
## 6 790-820 121,222,575 8,267
## 7 820-850 21,147,425 1,321# Report showing some statistics for each category, here the FICO score buckets
sumStats(LC, FICO_bin_name_Original)## Source: local data frame [7 x 4]
##
## FICO_bin_name_Original median average stdev
## (chr) (chr) (chr) (chr)
## 1 640-670 11,000 12,672 7,492
## 2 670-700 12,000 14,252 8,153
## 3 700-730 15,000 15,923 8,682
## 4 730-760 15,000 16,081 8,868
## 5 760-790 13,500 14,973 8,699
## 6 790-820 12,575 14,663 8,775
## 7 820-850 15,000 16,009 9,305# Report showing charge off rates, average LC scores and FICO scores for each category, here the FICO score buckets
sumPerSatus(LC, FICO = FICO_bin_name_Original)## Source: local data frame [7 x 5]
##
## FICO charged net_EL avg_fico avg_grade
## (chr) (dbl) (dbl) (dbl) (chr)
## 1 640-670 5.86 62.02 666 D1
## 2 670-700 5.75 57.77 686 C4
## 3 700-730 4.69 53.93 714 C1
## 4 730-760 4.17 47.95 744 B3
## 5 760-790 3.19 42.29 775 B1
## 6 790-820 2.42 41.70 803 A5
## 7 820-850 1.51 49.25 831 A4# Same report as above but constrained to LCmatured for comparison:
sumPerSatus(LCmatured, FICO = FICO_bin_name_Original)## Source: local data frame [7 x 5]
##
## FICO charged net_EL avg_fico avg_grade
## (chr) (dbl) (dbl) (dbl) (chr)
## 1 640-670 21.19 13.47 667 D4
## 2 670-700 16.13 9.93 687 C3
## 3 700-730 12.19 7.27 716 B4
## 4 730-760 8.51 5.04 745 A5
## 5 760-790 5.63 3.24 774 A4
## 6 790-820 3.60 2.11 802 A4
## 7 820-850 1.92 0.64 828 A3# Pie chart of the distribution of Loans across the different FICO score buckets:
sumAmnt(LC, FICO_bin_name_Original) %>% merge(sumPerSatus(LC, FICO_bin_name_Original)) %>%
plot_ly(type = "pie",
labels = FICO_bin_name_Original,
values = total_issued,
hole = 0.5,
marker = list(colors = c("#f2f0f7","#dadaeb","#bcbddc","#9e9ac8","#807dba","#6a51a3","#4a1486"),
line = list(width = 1, color = "rgb(52, 110, 165)")),
sort = F,
direction = "counterclockwise",
rotation = 120,
textinfo = "label+percent",
textfont = list(size = 14),
text = paste("Default rates: ", charged),
textposition = "outside") %>%
layout(title = 'LOAN ISSUED GROUPED BY FICO SCORES<br>(Hover for breakdown)',
height = 731, width = 1274, autosize = T,
legend = list(font = list(size = 16), x = 0.88, y = 1, traceorder = "normal"))## Success! Created a new plotly here -> https://plot.ly/~jfdarre/668Second look at the data: Exploration of the relationship between FICO and LC scores:
FICO scores have been implemented to assess the credit worthiness of potential borrowers. A model that Lending Club could have used could have been to simply rely on the foundation of FICO’s scores. The majority of the mortgage indsutry relies on FICO scores to issue mortgages of 100s of thousands.
Hence our interest in looking into LC’s grade system and more specifically if they have an obvious linear relationship with FICO scores.
At first, it seems obvious that the LC grades and FICO scores are very correlated:
# plot 1 shows the average fico scores of the borrowers against their LC grade:
# as expected, we see that the lower your fico score the worst your LC grade is going to be
gradeVSfico = group_by(LC, grade) %>% summarise(., avg_fico = mean(fico_range_high))
qplot(grade, avg_fico, data = gradeVSfico, geom = "boxplot", color = grade) +
theme_economist() +
scale_fill_economist() +
xlab(toupper("LC Grades ranging from A to G")) +
ylab("FICO SCORES") +
ggtitle("AVERAGE FICO SCORE VS. LC GRADES")
But this graph already seems to imply otherwise. Indeed 20 of the 35 possible LC sub grades have average FICO scores within 10pts:
# plot 2 shows the average fico scores of the borrowers against their LC sub_grade:
# grades ranging from D1 to G5 (20 levels) have average fico scores ranging from 678 to 688!
sub_gradeVSfico = group_by(LC, sub_grade) %>% summarise(., avg_fico = mean(fico_range_high))
qplot(x = sub_grade, y = avg_fico, data = sub_gradeVSfico, group = 1, geom = "point", size = I(2), col = I("steelblue")) +
theme_economist() +
scale_fill_economist() +
geom_hline(y = 688, color = "red") +
geom_hline(y = 678, color = "red") +
xlab(toupper("LC SUB Grades ranging from A1 to G5")) +
ylab("FICO SCORES") +
ggtitle(toupper("Average FICO score vs. LC SUB Grades"))
Running the full distribution of FICO ratings per LC grade confirm our previous point. Grades E, F, G and arguably D and even C have very close distributions and medians:
# plot 3 shows the distribution of fico scores of the borrowers against their LC's grade:
# although there is a trend, we can see on this plot that some borrowers with very high FICO scores
# got really poor LC Grade
qplot(grade, fico_range_high, data = LC, geom = "boxplot", color = grade) +
theme_economist() +
scale_fill_economist() +
xlab(toupper("LC Grades ranging from A to G")) +
ylab("FICO SCORES") +
ggtitle(toupper("Box plot of the FICO score distribution for each LC Grades"))
Looking at the densities adds to our previous points too:
# plot 4 shows the densities of fico scores of the borrowers against their LC's grade:
# there seems to be a cutoff where if your FICO score is below the trigger then it will be impossible
# to get a LC grade of A, B or C.
# we can also see that there are no obvious differences between D, E, F and G and we'll need to explore further.
qplot(grade, fico_range_high, data = LC, geom = "violin", color = grade) +
theme_economist() +
scale_fill_economist() +
xlab(toupper("LC Grades ranging from A to G")) +
ylab("FICO SCORES") +
ggtitle(toupper("Box plot of the FICO score distribution for each LC Grades"))
It is very interesting to see how these densities have evolved over the years. We can see how Lending Club’s earlier model used to rely on FICO score heavily and over the course of the years, LC refined there credit models using the data that they started accumulating. It is particularly obvious when looking at the grade A. Its density changed from being condensed over a range FICO scores of 730 to 820 to a density that covers the entire FICO range with a mean that is lower than the minimum score they used to require to get an A rating:
# plot 5 is the same as plot 4 but "warpped" over the years.
# this plot shows how much LC's methodology has evolved over the years
# we see that in 2009 LC seems to have introduced a cutoff at 660 for the loans available to the public
# from 2010 to 2015 we can observe that the distributions are scretching more and more reflecting the fact
# that LC's model is relying less and less on FICO scores and probably more and more on their own data.
qplot(factor(issue_y), fico_range_high, data = filter(LC, grade != "G"), geom = "violin", color = grade) +
theme_economist() +
scale_fill_economist() +
xlab(toupper("LC Grades ranging from A to G")) +
ylab("FICO SCORES") +
ggtitle(toupper("Box plot of the FICO score distribution for each LC Grades")) +
facet_wrap( ~ grade, ncol = 2)
Third look at the data: Exploration and discovery of the predictive strength of each features:
In this section we will take each features given at the time of the origination of the debt and attempt to extract their relation to default rates. For this part of the analysis we will use the data set LCmatured that we recall contains only the loans that have matured or if defaulted, would have matured.
Home ownership:
Home ownership: Does home ownership have any relashionship with LC grades, FICO scores and Charge Off rates?
# Report showing the amounts and number of loans in each category, here the home ownership status of the borrower.
sumAmnts(LCmatured, home)## Source: local data frame [4 x 3]
##
## home total_issued n
## (chr) (chr) (chr)
## 1 MORTGAGE 216,735,425 19,179
## 2 OTHER 1,231,575 123
## 3 OWN 36,008,825 3,678
## 4 RENT 223,879,725 23,557# Report showing some statistics for each category, here the home ownership status of the borrower.
sumPerSatus(LCmatured, home)## Source: local data frame [4 x 5]
##
## home charged net_EL avg_fico avg_grade
## (chr) (dbl) (dbl) (dbl) (chr)
## 1 MORTGAGE 11.41 6.77 722 B4
## 2 OTHER 20.33 12.24 718 C3
## 3 OWN 13.10 7.86 719 B5
## 4 RENT 13.61 8.44 710 B5# Home ownership: here is a bubble graph of our 4 home ownership categories:
sumAmnt(LCmatured, home) %>% merge(sumPerSatus(LCmatured, home)) %>%
plot_ly(x = n,
y = charged,
size = total_issued,
text = paste(home, "<br>", "Total Issued: ", prettyNum(total_issued, big.mark = ",")),
mode = "markers",
marker = list(color = net_EL,
colorscale = list(c(0, "rgb(255, 255, 255)"), list(1, "rgb(65, 50, 103)")),
colorbar = list(title = "Net Expected Loss"),
cauto = F,
cmin = 5,
cmax = 13,
opacity = 1,
line = list(size = 2))
)%>%
layout(xaxis = list(title = toupper("Number of issued loans")),
yaxis = list(title = toupper("Default rates"), range = c(-2, 25)),
title = 'LOAN ISSUED GROUPED BY HOME OWNERSHIP<br>(Hover for breakdown)')## Success! Created a new plotly here -> https://plot.ly/~jfdarre/670# Home ownership: here is a pie chart of our 4 home ownership categories:
sumAmnt(LCmatured, home) %>% merge(sumPerSatus(LCmatured, home)) %>%
plot_ly(type = "pie",
labels = home,
values = total_issued,
hole = 0.5,
marker = list(colors = c("#f0f9e8", "#bae4bc", "#7bccc4", "#2b8cbe"),
line = list(width = 1, color = "rgb(52, 110, 165)")),
sort = F,
direction = "counterclockwise",
rotation = -45,
textinfo = "label+percent",
textfont = list(size = 14),
text = paste("Default rates: ", charged),
textposition = "outside") %>%
layout(title = 'LOAN ISSUED GROUPED BY HOME OWNERSHIP<br>(Hover for breakdown)',
height = 731, width = 1274, autosize = T,
legend = list(font = list(size = 16), x = 0.88, y = 1, traceorder = "normal"))## Success! Created a new plotly here -> https://plot.ly/~jfdarre/672Purpose:
Purpose: We want to see if there is any relations between LC Grades, FICO scores, Charge Off rates and purpose. There is a strong correlation between charge off rates and if the purpose is either educational or small business. These loans tend to be a lot riskier. The fico score does not reflect this while the LC score seems to partially capture that risk:
# Report showing the amounts and number of loans in each category, here the purpose of the borrower.
sumAmnts(LCmatured, purpose_new)## Source: local data frame [4 x 3]
##
## purpose_new total_issued n
## (chr) (chr) (chr)
## 1 debt 331,208,725 29,391
## 2 educational 2,590,450 385
## 3 purchase 119,601,350 14,848
## 4 small_business 24,455,025 1,913# Report showing some statistics for each category, here the purpose of the borrower.
sumPerSatus(LCmatured, purpose_new)## Source: local data frame [4 x 5]
##
## purpose_new charged net_EL avg_fico avg_grade
## (chr) (dbl) (dbl) (dbl) (chr)
## 1 debt 12.11 7.17 712 B5
## 2 educational 19.74 12.65 714 C2
## 3 purchase 12.36 7.76 723 B4
## 4 small_business 22.58 14.74 725 C2# Home ownership: here is a bubble graph of our 4 home ownership categories:
sumAmnt(LCmatured, purpose_new) %>% merge(sumPerSatus(LCmatured, purpose_new)) %>%
plot_ly(x = n,
y = charged,
size = total_issued,
text = paste(purpose_new, "<br>", "Total Issued: ", prettyNum(total_issued, big.mark = ",")),
mode = "markers",
marker = list(color = net_EL,
colorscale = list(c(0, "rgb(255, 255, 255)"), list(1, "rgb(65, 50, 103)")),
colorbar = list(title = "Net Expected Loss"),
cauto = F,
cmin = 5,
cmax = 13,
opacity = 1,
line = list(size = 2))
)%>%
layout(xaxis = list(title = toupper("Number of issued loans")),
yaxis = list(title = toupper("Default rates"), range = c(-2, 25)),
title = 'LOAN ISSUED GROUPED BY PURPOSE<br>(Hover for breakdown)')## Success! Created a new plotly here -> https://plot.ly/~jfdarre/674# Home ownership: here is a pie chart of our different purposes categories:
sumAmnt(LCmatured, purpose_new) %>% merge(sumPerSatus(LCmatured, purpose_new)) %>%
plot_ly(type = "pie",
labels = purpose_new,
values = total_issued,
hole = 0.5,
marker = list(colors = c("#f0f9e8", "#bae4bc", "#7bccc4", "#2b8cbe"),
line = list(width = 1, color = "rgb(52, 110, 165)")),
sort = F,
direction = "counterclockwise",
rotation = -45,
textinfo = "label+percent",
textfont = list(size = 14),
text = paste("Default rates: ", charged),
textposition = "outside") %>%
layout(title = 'LOAN ISSUED GROUPED BY PURPOSE<br>(Hover for breakdown)',
height = 731, width = 1274, autosize = T,
legend = list(font = list(size = 16), x = 0.88, y = 1, traceorder = "normal"))## Success! Created a new plotly here -> https://plot.ly/~jfdarre/676Revolvoving balance and employement length:
Revolving Balance, along with Employment length are actually the features with the least obvious link to default rates:
# Report showing the amounts and number of loans in each category, here the revolving balance of the borrower.
sumAmnts(LCmatured, revol_bal_bucket)## Source: local data frame [10 x 3]
##
## revol_bal_bucket total_issued n
## (fctr) (chr) (chr)
## 1 0-1,333 34,543,875 4,655
## 2 1,333-3,170.2 33,292,050 4,653
## 3 3,170.2-5,080 35,756,925 4,654
## 4 5,080-6,974.4 38,856,750 4,653
## 5 6,974.4-9,100 43,295,600 4,654
## 6 9,100-11,592.2 48,193,625 4,653
## 7 11,592.2-14,693.2 52,795,525 4,654
## 8 14,693.2-19,306 57,945,375 4,654
## 9 19,306-28,052.4 64,651,425 4,653
## 10 28,052.4-+inf 68,524,400 4,654# Report showing some statistics for each category, here the revolving balance of the borrower.
sumPerSatus(LCmatured, revol_bal_bucket)## Source: local data frame [10 x 5]
##
## revol_bal_bucket charged net_EL avg_fico avg_grade
## (fctr) (dbl) (dbl) (dbl) (chr)
## 1 0-1,333 12.93 8.19 734 B4
## 2 1,333-3,170.2 11.93 7.50 723 B4
## 3 3,170.2-5,080 12.59 7.66 717 B4
## 4 5,080-6,974.4 12.94 7.77 712 B5
## 5 6,974.4-9,100 13.62 7.93 711 B5
## 6 9,100-11,592.2 12.27 7.48 710 B5
## 7 11,592.2-14,693.2 13.04 7.59 710 B5
## 8 14,693.2-19,306 12.38 7.53 712 B5
## 9 19,306-28,052.4 12.06 7.25 712 C1
## 10 28,052.4-+inf 13.09 8.26 717 C1# Report showing the amounts and number of loans in each category, here the employement length of the borrower.
sumAmnts(LCmatured, emp_length)[c(12,1,2,4,5,6,7,8,9,10,11,3),]## Source: local data frame [12 x 3]
##
## emp_length total_issued n
## (chr) (chr) (chr)
## 1 n/a 11,244,875 1,397
## 2 < 1 year 49,119,700 5,379
## 3 1 year 37,644,850 3,936
## 4 2 years 50,175,450 5,216
## 5 3 years 45,226,075 4,531
## 6 4 years 40,879,325 4,000
## 7 5 years 39,388,800 3,829
## 8 6 years 29,429,025 2,787
## 9 7 years 23,283,975 2,149
## 10 8 years 18,897,175 1,748
## 11 9 years 16,522,100 1,481
## 12 10+ years 116,044,200 10,084# Report showing some statistics for each category, here the employement length of the borrower.
sumPerSatus(LCmatured, emp_length)[c(12,1,2,4,5,6,7,8,9,10,11,3),]## Source: local data frame [12 x 5]
##
## emp_length charged net_EL avg_fico avg_grade
## (chr) (dbl) (dbl) (dbl) (chr)
## 1 n/a 19.11 11.86 723 B3
## 2 < 1 year 12.73 8.05 715 B5
## 3 1 year 12.50 7.84 713 B5
## 4 2 years 12.25 7.65 713 B5
## 5 3 years 12.14 7.27 714 B5
## 6 4 years 12.35 7.43 713 B5
## 7 5 years 11.91 7.03 716 B5
## 8 6 years 12.56 7.88 715 B5
## 9 7 years 13.54 8.20 714 B5
## 10 8 years 12.64 7.85 717 B5
## 11 9 years 11.68 6.86 718 B5
## 12 10+ years 12.74 7.49 719 B5Number of delinquencies over the past 2 years prior the application for a loan:
Number of delinquencies during the past 2 years: This feature is somewhat linked to charge off rates but a vast majority of the borrowers actually have 0 delinquencies, which is not helpful distinguishing between them.
# Report showing the amounts and number of loans in each category, here the number of delinquencies of the borrower.
sumAmnts(LCmatured, delinq_bucket)## Source: local data frame [3 x 3]
##
## delinq_bucket total_issued n
## (chr) (chr) (chr)
## 1 0 431,368,925 41,730
## 2 1 35,914,375 3,665
## 3 2+ 10,572,250 1,142# Report showing some statistics for each category, here the number of delinquencies of the borrower.
sumPerSatus(LCmatured, delinq_bucket)## Source: local data frame [3 x 5]
##
## delinq_bucket charged net_EL avg_fico avg_grade
## (chr) (dbl) (dbl) (dbl) (chr)
## 1 0 12.45 7.56 718 B4
## 2 1 14.41 8.90 696 C3
## 3 2+ 15.85 9.64 686 C4Number of opened and total number of accounts:
Total number of accounts and Open accounts are not the most impactfull features.
Fully paying borrower tend to have slightly more accounts but too many accounts may be bad too. This is interesting because FICO considers that the more accounts. This is also confirmed in the numbers below.
# Report showing the amounts and number of loans in each category, here the total number of accounts of the borrower.
sumAmnts(LCmatured, total_acc_bucket)## Source: local data frame [10 x 3]
##
## total_acc_bucket total_issued n
## (fctr) (chr) (chr)
## 1 0-9 39,765,700 5,647
## 2 9-12 40,829,900 4,671
## 3 12-15 49,987,650 5,289
## 4 15-17 35,890,100 3,588
## 5 17-20 51,703,175 5,023
## 6 20-23 50,391,475 4,642
## 7 23-26 45,776,075 4,086
## 8 26-30 51,132,300 4,412
## 9 30-37 58,139,025 4,828
## 10 37-+inf 54,240,150 4,351# Report showing some statistics for each category, here the total number of accounts of the borrower.
sumPerSatus(LCmatured, total_acc_bucket)## Source: local data frame [10 x 5]
##
## total_acc_bucket charged net_EL avg_fico avg_grade
## (fctr) (dbl) (dbl) (dbl) (chr)
## 1 0-9 15.85 9.99 710 C2
## 2 9-12 13.32 8.11 711 B5
## 3 12-15 13.50 8.17 713 B5
## 4 15-17 12.85 7.42 714 B5
## 5 17-20 11.55 6.89 715 B4
## 6 20-23 11.05 6.67 718 B4
## 7 23-26 11.92 7.32 718 B4
## 8 26-30 12.56 7.48 719 B4
## 9 30-37 11.12 6.75 720 B4
## 10 37-+inf 12.41 7.80 721 B5# Report showing the amounts and number of loans in each category, here the number of opened accounts of the borrower.
sumAmnts(LCmatured, open_acc_bucket)## Source: local data frame [10 x 3]
##
## open_acc_bucket total_issued n
## (fctr) (chr) (chr)
## 1 0-4 35,433,925 4,804
## 2 4-6 72,536,450 8,018
## 3 6-7 47,380,850 4,697
## 4 7-8 48,221,325 4,655
## 5 8-9 47,336,725 4,457
## 6 9-10 41,332,225 3,786
## 7 10-11 37,208,050 3,331
## 8 11-13 57,797,575 5,048
## 9 13-15 36,561,525 3,201
## 10 15-+inf 54,046,900 4,540# Report showing some statistics for each category, here the number of opened accounts of the borrower.
sumPerSatus(LCmatured, open_acc_bucket)## Source: local data frame [10 x 5]
##
## open_acc_bucket charged net_EL avg_fico avg_grade
## (fctr) (dbl) (dbl) (dbl) (chr)
## 1 0-4 15.09 9.61 718 C2
## 2 4-6 12.10 7.43 718 B4
## 3 6-7 12.20 7.47 717 B4
## 4 7-8 12.91 7.71 716 B4
## 5 8-9 12.36 7.35 715 B4
## 6 9-10 12.78 7.67 715 B4
## 7 10-11 11.41 6.74 715 B5
## 8 11-13 12.52 7.63 714 B5
## 9 13-15 13.06 7.95 715 B5
## 10 15-+inf 12.53 7.54 713 C1DTI (Debt To Income ratio):
The DTI ratio: the lower the better, a DTI of 5 means your debts payment excluding mortgage, are only 5% of your gross income. DTI vs LC Grade, although having the expected trend, does not seem to show an strong dependency. DTI vs FICO score show a strong relationship for scores ranging from 700-850 but no correlation for scores < 700. But overall DTI has some impact on charge off probabilities.
# Report showing the amounts and number of loans in each category, here the DTI of the borrower.
sumAmnts(LCmatured, dti_bucket)## Source: local data frame [10 x 3]
##
## dti_bucket total_issued n
## (fctr) (chr) (chr)
## 1 0-4.32 42,671,250 4,662
## 2 4.32-7.24 45,736,300 4,651
## 3 7.24-9.56 48,358,550 4,663
## 4 9.56-11.684 48,692,050 4,639
## 5 11.684-13.69 49,009,375 4,655
## 6 13.69-15.68 49,961,650 4,658
## 7 15.68-17.8 49,722,425 4,656
## 8 17.8-20.1 48,587,825 4,663
## 9 20.1-22.67 47,762,925 4,646
## 10 22.67-+inf 47,353,200 4,644# Report showing some statistics for each category, here the DTI of the borrower.
sumPerSatus(LCmatured, dti_bucket)## Source: local data frame [10 x 5]
##
## dti_bucket charged net_EL avg_fico avg_grade
## (fctr) (dbl) (dbl) (dbl) (chr)
## 1 0-4.32 11.07 6.90 736 B4
## 2 4.32-7.24 10.47 6.12 723 B4
## 3 7.24-9.56 11.52 6.83 720 B4
## 4 9.56-11.684 11.73 7.35 716 B5
## 5 11.684-13.69 12.52 7.79 714 B5
## 6 13.69-15.68 12.64 7.66 712 B5
## 7 15.68-17.8 13.72 8.27 710 B5
## 8 17.8-20.1 13.68 8.18 709 B5
## 9 20.1-22.67 14.70 8.94 707 C1
## 10 22.67-+inf 14.79 9.14 711 B5# Boxplot of the distribution the DTI for each grade:
qplot(grade, dti, data = LC, geom = "boxplot", color = grade) +
theme_economist() +
scale_fill_economist() +
xlab(toupper("LC Grades ranging from A to G")) +
ylab(toupper("DTI (Debt to income ratio, in percent")) +
ggtitle(toupper("DTI distribution vs. LC Grades"))
# Boxplot of the distribution the DTI for each FICO score bucket:
qplot(FICO_bin_name_Original, dti, data = filter(LC, fico_range_high >= 660 ) , geom = "boxplot", color = FICO_bin_name_Original) +
theme_economist() +
scale_fill_economist() +
xlab(toupper("FICO score buckets")) +
ylab(toupper("DTI (Debt to income ratio, in percent")) +
ggtitle(toupper("Average FICO score vs. LC Grades"))
Public records:
Public Records: This feature is definitely correlated to charged off rates, but its value is very low. It is zero in most cases. Basically if a borrower has 0 public records is a strong indicator that he has greater chances to pay off his debts and conversely, if the number of public records is greater than 0, the borrower has a substantially greater chance of default.
# Report showing the amounts and number of loans in each category, here the number of public records of the borrower.
sumAmnts(LCmatured, rec_bucket)## Source: local data frame [2 x 3]
##
## rec_bucket total_issued n
## (chr) (chr) (chr)
## 1 0 459,397,325 44,381
## 2 1+ 18,458,225 2,156# Report showing some statistics for each category, here the number of public records of the borrower.
sumPerSatus(LCmatured, rec_bucket)## Source: local data frame [2 x 5]
##
## rec_bucket charged net_EL avg_fico avg_grade
## (chr) (dbl) (dbl) (dbl) (chr)
## 1 0 12.34 7.52 717 B5
## 2 1+ 19.85 11.71 695 C3Age of Credit history:
The age of credit history has a significant impact on default rates: Fully paying borrowers tend to have slightly older credit history, which is to be expected. But the main take on Credit history is on borrower that have relatively short credit history: We can see that the bottom 10% have credit histories of less than 6 years and have a significantly higher default rates. One should also be careful when looking into the age of the credit history since it correlates with the age of the borrower and hence ith his income and other features. This will need to be investigated further later.
# Report showing the amounts and number of loans in each category, here the age of the credit history of the borrower.
sumAmnts(LCmatured, credit_ym_bucket)## Source: local data frame [10 x 3]
##
## credit_ym_bucket total_issued n
## (fctr) (chr) (chr)
## 1 0-5.9 33,938,025 4,770
## 2 5.9-7.8 40,211,950 4,577
## 3 7.8-9.7 45,529,150 4,777
## 4 9.7-11 46,622,950 4,629
## 5 11-12.2 48,535,025 4,646
## 6 12.2-13.8 51,941,325 4,830
## 7 13.8-15.7 49,642,775 4,468
## 8 15.7-18.2 54,383,125 4,723
## 9 18.2-22.7 52,862,625 4,522
## 10 22.7-+inf 54,188,600 4,595# Report showing some statistics for each category, here the age of the credit history of the borrower.
sumPerSatus(LCmatured, credit_ym_bucket)## Source: local data frame [10 x 5]
##
## credit_ym_bucket charged net_EL avg_fico avg_grade
## (fctr) (dbl) (dbl) (dbl) (chr)
## 1 0-5.9 16.42 10.41 700 C3
## 2 5.9-7.8 12.39 7.71 707 B5
## 3 7.8-9.7 12.79 7.59 710 B5
## 4 9.7-11 13.11 8.15 713 B5
## 5 11-12.2 12.85 7.77 715 B5
## 6 12.2-13.8 12.57 7.59 717 B5
## 7 13.8-15.7 12.71 7.52 719 B5
## 8 15.7-18.2 11.54 6.75 723 B4
## 9 18.2-22.7 11.34 7.14 725 B4
## 10 22.7-+inf 10.99 6.46 730 B3Revolving utilization:
As expected, higher revolving utilization mean higher risk of default. The top 10% has default rates that are almost twice as low as the bottom 10%.
# Report showing the amounts and number of loans in each category, here the revolving utilization of the borrower.
sumAmnts(LCmatured, revol_bucket)## Source: local data frame [10 x 3]
##
## revol_bucket total_issued n
## (fctr) (chr) (chr)
## 1 0-9.8 44,018,750 4,673
## 2 9.8-22.3 43,895,900 4,640
## 3 22.3-33.2 45,472,925 4,663
## 4 33.2-43.2 47,226,350 4,641
## 5 43.2-52.5 49,336,950 4,679
## 6 52.5-61.3 50,583,775 4,668
## 7 61.3-69.5 51,250,550 4,639
## 8 69.5-78.4 51,094,575 4,669
## 9 78.4-87.9 49,511,500 4,623
## 10 87.9-+inf 45,464,275 4,642# Report showing some statistics for each category, here the revolving utilization of the borrower.
sumPerSatus(LCmatured, revol_bucket)## Source: local data frame [10 x 5]
##
## revol_bucket charged net_EL avg_fico avg_grade
## (fctr) (dbl) (dbl) (dbl) (chr)
## 1 0-9.8 9.44 5.97 757 B2
## 2 9.8-22.3 8.32 5.07 743 B1
## 3 22.3-33.2 10.32 6.07 730 B3
## 4 33.2-43.2 11.42 6.70 719 B4
## 5 43.2-52.5 11.93 7.36 713 B5
## 6 52.5-61.3 13.80 8.46 708 B5
## 7 61.3-69.5 13.95 8.24 704 B5
## 8 69.5-78.4 13.75 8.30 697 C2
## 9 78.4-87.9 15.75 9.57 694 C2
## 10 87.9-+inf 18.22 11.45 693 C5Annual income:
This is a very strong feature. People in the top 20% quantile have half as much charged off loans compared to the bottom 20%. It is worth noticing though, that even the top 20% still get a 9.5% chance off defaulting on their loan which is still very high. So annual income is not a silver bullet either.
# Report showing the amounts and number of loans in each category, here the annual income of the borrower.
sumAmnts(LCmatured, annual_inc_bucket)## Source: local data frame [10 x 3]
##
## annual_inc_bucket total_issued n
## (fctr) (chr) (chr)
## 1 0-28,995.6 25,222,225 4,654
## 2 28,995.6-36,000 36,453,525 4,879
## 3 36,000-43,000 38,081,475 4,625
## 4 43,000-50,000 48,413,525 5,308
## 5 50,000-57,000 38,356,150 3,978
## 6 57,000-65,000 52,714,125 5,084
## 7 65,000-75,000 52,040,275 4,636
## 8 75,000-89,100 50,592,425 4,067
## 9 89,100-114,000 62,729,225 4,668
## 10 114,000-+inf 73,252,600 4,638# Report showing some statistics for each category, here the age of the annual income of the borrower.
sumPerSatus(LCmatured, annual_inc_bucket)## Source: local data frame [10 x 5]
##
## annual_inc_bucket charged net_EL avg_fico avg_grade
## (fctr) (dbl) (dbl) (dbl) (chr)
## 1 0-28,995.6 17.40 11.14 712 B5
## 2 28,995.6-36,000 16.70 10.00 712 B5
## 3 36,000-43,000 14.31 8.68 713 B5
## 4 43,000-50,000 13.00 7.88 713 B5
## 5 50,000-57,000 12.47 7.42 714 B5
## 6 57,000-65,000 12.37 7.47 716 B5
## 7 65,000-75,000 11.32 6.88 717 B5
## 8 75,000-89,100 10.33 6.25 719 B5
## 9 89,100-114,000 8.95 5.47 720 B5
## 10 114,000-+inf 9.44 5.64 723 C1Inquieries in the last 6months:
This feature is supposed to represent how desperate the borrower is for credit. We can see that the correlation between this feature and charged off rates is very strong. This is the feature that has the highest impact on default rates. The EL of people with 0 inquieries is more than 4 times smaller than the EL of the 7+ bucket!
# Report showing the amounts and number of loans in each category, here the number of inquieries of the borrower.
sumAmnts(LCmatured, inq_bucket)## Source: local data frame [5 x 3]
##
## inq_bucket total_issued n
## (chr) (chr) (chr)
## 1 0 235,182,175 22,685
## 2 1-2 190,364,525 18,628
## 3 3-4 41,443,200 4,121
## 4 5-6 7,509,500 764
## 5 7+ 3,356,150 339# Report showing some statistics for each category, here the age of the number of inquieries of the borrower.
sumPerSatus(LCmatured, inq_bucket)## Source: local data frame [5 x 5]
##
## inq_bucket charged net_EL avg_fico avg_grade
## (chr) (dbl) (dbl) (dbl) (chr)
## 1 0 10.22 5.91 719 B4
## 2 1-2 13.29 8.17 714 C1
## 3 3-4 19.05 12.28 711 C1
## 4 5-6 26.57 18.13 710 C5
## 5 7+ 35.40 24.68 702 D5Geography:
Finally, here is a heat map of the USA. We see that Nevada is not doing so great…
# options:
l <- list(color = toRGB("steelblue"), width = 1)
g = list(scope = 'usa', projection = list(type = 'albers usa'), showlakes = F, lakecolor = toRGB('white'))
# plotting the map:
plot_ly(filter(sumStates, n > 200), z = charged, text = hover, locations = addr_state, type = 'choropleth',
locationmode = 'USA-states', color = total_issued, colors = 'Oranges',
marker = list(line = l), colorbar = list(title = "Default rates<br>in percentage")) %>%
layout(title = 'DEFAULT RATES PER STATES<br>(Hover for breakdown)', geo = g)## Success! Created a new plotly here -> https://plot.ly/~jfdarre/678Conclusion
Lending Club’s data is a great source of information on presonal credit. Additionally this data set is bound to grow exponentially over the next years. We tried to build a report to both present Lending Club and build the foundations to more in depth analyses.
To continue this analysis we need to be careful with the following:
- There is a geographical selection bias: as we saw in the previous maps most accounts are very concentrated geographically.
- By nature of the platform, we are selecting only applicants who are somewhat tech-savvy.
- We need to be careful with the implications of certain features when trying to extract correlation. As stated earlier, features like number of accounts or age of credit history are correlated to the age of the borrower. Could their relationship to default rates be linked to their age and indirectly to their income?
- We have to be careful with adverse selection, a mistake that the financial industry as know for a long time. Let’s keep in mind that the safe way to do financial services is to keep the segmentation down to a minimum and let the mathematics of risk pooling look after us.
The next steps of this analysis could be some of the following:
- Building a credit model and see if we can predict reliably defaults.
- Building a model to arb LC’s own model.
- Trying to replicated LC’s models using machine learning techniques using the Lending Club’s grades as our outputs.