Exploratory Analysis for Prosper Loan Data

## Source: local data frame [2 x 6]
## 
##   preAfter2009      mean median     n       LCI       UCI
##          (chr)     (dbl)  (dbl) (int)     (dbl)     (dbl)
## 1    after2009 0.1862954 0.1775 83982 0.1862936 0.1862971
## 2      pre2009 0.1730263 0.1600 29084 0.1730213 0.1730312

i. Do estimated returns show changes across the under-writing period?

Although the yields are super-impressive (above 17%), I must note that it does not account for defaults and chargeoffs. Also, the yields are not representing the risk profile of the loans. Hence, I should use a different metric that examines the bottom line return to the investor. After examining the variable descriptions, I found that EstimatedReturn variable might be more appropriate as a measure of return. In the plots below, I will examine the EstimatedReturn variable on an individual yearly basis. In addition to that, I will plot the 95% confidence interval in estimated returns for each year.

Note that EstimatedReturn variable is provided only for loans after 2009. The black dot represents the mean. The numbers also represent the mean.

It seems like the mean and median returns have decreased as across time despite the fact that the increase in the number of loan listings, indicating that the underwriting process has become more efficient at weeding out risky loans.

ii.What is the 1% and 5% VAR?

Next, I want to find out what is my 1% VAR and 5% VAR at risk. In other words, I want to find out how much return should I expect to earn if I get really unlucky and pick the bottom 1% and bottom 5% of loans. I will then plot the loans that fall below 1% VAR in red and loans between 1% and 5% will be plotted in yellow.

Assuming that one constantly reinvests the interest income, there is a 99% chance that would’ve earned a minimum of 4% return or a 95% chance that the return could be a minimum of 5.298%. This assumes that one had the ability to invest in all the loans. These estimated returns aren’t that bad. Moreover, it seems that VAR is smaller in more recent periods, indicating that loans in the recent years have a very small risk of negative returns.

iii. How do estimated returns compare with actual returns?

One issue with the plot above is that returns are estimates provided by Prosper at the time the listing was created. This is an issue, since estimated returns are predictions about the future rather than historical values. In this section, I will try to achieve a measure of realized annualized historical return for loan whose status was either Completed, ChargedOff, or Defaulted.

First what I will do is create a column called AnnualizedReturn that finds the realized annualized returns for Completed, ChargedOff or Defaulted Loans.

For completed loans, I calculate the cumulative return as follows:

\[r_c=(CustomerPayments-LoanOriginalAmount-ServiceFees)/LoanOriginalAmount\]

For Chargeoffs and defaulted loans, the cumulative return formula is:

\[r_c=(CustomerPayments+NonPrincipalRecoveryPayments-ServiceFees-CollectionFees-\\ -LoanOriginalAmount-NetPrincipalLoss)/LoanOriginalAmount\]

After finding the cumulative return, I annualized it using this formula:

\[r_a=(1+r_c)^{12/LoanMonthsSinceOrigination} \]

Below, I will plot the annualized returns for each year. The mean annualized return is provided slightly above the boxplot in the graph.

One thing becomes super clear: the estimated returns do not provide a good understanding for what the actual returns might be because they do not account precisely for defaulted loans and chargeoffs. Note the y-axis, where quite a few loans have negative returns. Nonetheless, the year 201 and 2015 showed pretty good returns (much better than a lot of other fixed income instruments on the market).

Next, I will plot the annualized returns by faceting on loan status

## $title
## [1] "Actual Returns from Past Loans for each loan status"
## 
## attr(,"class")
## [1] "labels"

There seems to be a bit too much information in this plot, so below I will replot the figure above by removing year as a variable. On the plot on the right, the y scale goes from -1 to .3; On the plot to the left, I zoom in the scale from -.05 to .3.

It seems that the median and mean return for all three types of loans is positive, which means that Prosper does a relatively good job with recovering some of their loses on chargedoff and defaulted loans. Moreover, the chargedoff loans and completed loans don’t seem to be that different from each other when it comes to mean and median returns. Below, I will examine some of the descriptive statistics for each loan type (i.e., mean, median, 95%CI).

## Source: local data frame [3 x 6]
## 
##   LoanStatus        mean     median     n         LCI         UCI
##        (chr)       (dbl)      (dbl) (int)       (dbl)       (dbl)
## 1 Chargedoff 0.048190189 0.03526307 11992 0.048182447 0.048197932
## 2  Completed 0.049918143 0.03626365 38061 0.049915681 0.049920606
## 3  Defaulted 0.008111227 0.01632544  5011 0.008074417 0.008148038

This confirms the suspicion that after accounting for chargeoffs and defaults, the actual return is much lower than the estimated return given by prosper.

Next, let’s find the mean return for all loans.

## [1] 0.03540652

The mean return an investor would expect to get by investing in all loans should be about 3.54%.

We still have one issue with how we compared our annualized returns and the estimated returns. The estimated returns in section 1.i include the period after Prosper restarted it’s operations. As I pointed out above, during that period (2009-2014) Prosper had lower volatility in estimated returns, likely because they perfected their underwriting model.Therefore, I need to compare the estimated returns and actual returns using the same periods in order to understand how closely are the two returns aligned with each other. In the plot below, I will plot the difference between actual and expected return for the completed, chargedoff, and defaulted loans after 2009. The numbers below the boxplots represent the means.

This is a really disconcerting picture because it shows that the bulk of all loans types have over-estimated the returns when the loan was listed (note how all the boxes are below zero and the mean and median are negative). Note also that in come cases the difference between two rates is below -1. This happens when the Estimated Return is positive but the actual return is a 100%loss on the loan.

Let’s find out what the 1% and 5% VAR for actual returns is.

##   Var1percent Var5percent
## 1 -0.01128738 0.003281142

The 1% and 5% VAR for annualized returns is -1.12% and 0.328% respectively. In other words, there is a 99% chance that one would lose at most 1.12% of their investment

In the next section, I will examine risk adjusted returns.

i. Examine returns for each prosper score. The prosper score is a custom built score using historical Prosper data. The score ranges from 1-11, with 11 being the lowest risk score.

In the figure below, I plot in the top two panels the annualized returns (which we calculated above) and the estimated returns for each prosper score. The bottom panel represent the number of loans that are in each prosper score. Note that ProsperScore variable is available for loans after 2009.

The graphs above should show that with lower risk (i.e., higher prosper score), the return earned on the loan should decrease. The relationship however is not that clean. For instance, loans with scores 2 and 3 show lower return profile, which is unusual. Moreover, it seems like there are very few loans that have a score of 3. Let’s examine the statistical relationship between prosper score and annualized return. I’ll use the spearman rank correlation test.

## 
##  Spearman's rank correlation rho
## 
## data:  pastLoans$AnnualizedReturn and as.integer(pastLoans$ProsperScore)
## S = 4.154e+12, p-value < 2.2e-16
## alternative hypothesis: true rho is not equal to 0
## sample estimates:
##        rho 
## -0.4203658

The correlation test between prosper score and annualized return is significant, but it’s important to point out that that significance doesn’t imply a sign of a strong relationship because we have thousands of observations.Next, I’ll examine the relationship between prosper score and estimated return.

## 
##  Spearman's rank correlation rho
## 
## data:  pastLoans$EstimatedReturn and as.integer(pastLoans$ProsperScore)
## S = 4.2194e+12, p-value < 2.2e-16
## alternative hypothesis: true rho is not equal to 0
## sample estimates:
##        rho 
## -0.4417101

It seems like there is a similar relationship between prosper score and annualized return rather than between prosper score and estimated return.

I’m not very satisfied with the relationship between prosper score and the two measures of return. I’ll examine next another ordinal scale for risk (see definition below) > ProsperRating (numeric) The Prosper Rating assigned at the time the listing was created: 0 - N/A, 1 - HR, 2 - E, 3 - D, 4 - C, 5 - B, 6 - A, 7 - AA. Applicable for loans originated after July 2009.

Graphically, the relationship between the second type of prosper score and annualized or estimated return is much more pronounced relative the previous prosper score. The correlations bellow should confirm that.

## 
##  Spearman's rank correlation rho
## 
## data:  pastLoans$EstimatedReturn and as.integer(pastLoans$`ProsperRating (numeric)`)
## S = 4.6854e+12, p-value < 2.2e-16
## alternative hypothesis: true rho is not equal to 0
## sample estimates:
##        rho 
## -0.6009421

## 
##  Spearman's rank correlation rho
## 
## data:  pastLoans$AnnualizedReturn and as.integer(pastLoans$`ProsperRating (numeric)`)
## S = 4.2946e+12, p-value < 2.2e-16
## alternative hypothesis: true rho is not equal to 0
## sample estimates:
##        rho 
## -0.4684499

The Spearman rho between estimated return and prosper rating is -.60. For annualized return and prosper rating, the correlation is a bit lower (-.468). Ultimately, this indicates that we should use the prosper rating rather than prosper score as a measure of risk.

ii. Examine the ratio between expected return and prosper rating) (r/p). How much return are we gaining for each point decrease in prosper rating)?

In this section, I will examine the ratio between annualized return and prosper rating and the ratio between estimated return and prosper rating. The purpose of these ratios is to have a metric that combines both return and risk. This ratio will tell us how much are we losing in our return for every unit increase in prosper rating (i.e., for every unit decrease in risk).

## Source: local data frame [8 x 5]
## 
##   ProsperRating (numeric) meanRealized medianRealized meanEstimated
##                    (fctr)        (dbl)          (dbl)         (dbl)
## 1                       1   0.09275413    0.097533960   0.108986215
## 2                       2   0.04517407    0.045551404   0.069046109
## 3                       3   0.02588969    0.027190204   0.044068175
## 4                       4   0.01679657    0.016654241   0.027899384
## 5                       5   0.01083538    0.010722636   0.018051077
## 6                       6   0.00571007    0.005422859   0.011731071
## 7                       7   0.00296951    0.003159571   0.008149978
## 8                      NA          NaN             NA           NaN
## Variables not shown: medianEstimated (dbl)

It seems like we gain the largest increase in return when we are buying the most the loans with a prosper rating of 1. The graph shows that there is a non-linear decrease in return for every unit of risk taken. Although one could jump to the conclusion that the risk-adjusted profile for loans with prosper rating of 1 looks the best, the prosper rating is not granular enough to account to measure risk. For instance, one issue with the prosper rating is that it has an ordinal scale, so that a decrease in risk from 1 to 2 does not translate to the same magnitude in the decrease in risk from 2 to 3. For this reason, below I will try to create a Sharpe measure for each loan that will be based on purely interval measures.

iii. Create a Sharpe measure for each loan using historical federal interest rates as the risk free rate, estimated returns, and standard deviation of estimated returns. The goal of this measure is to provide a risk adjusted return.

The formula for the Sharpe measure will take the annualized returns for each loan and subtract the risk free rate and divide by the standard deviation of realized annualized returns of all the loans.

\[s=\frac{r_a - r_f}{\sigma}\]

The risk free rate in this case will be the 1-year US treasury yield. The treasury yield will be a dynamic measure because I will be using the daily yields rather than a static annual measure.

The point of this feature is to answer the following question: how much can an investor earn above a risk-free investment such as the 1-year US treasury for every unit of risk (ie, standard deviation)? If the Sharpe measure is 0 or bellow 0, then the investor is better off putting his money in the risk-free instrument rather than investing in prosper loans.

First, I will download the daily 1-year US treasury yield curve rates using Quantdl. I will merge this data with the prosper dataset. Afterwards, I will calculate the Sharpe ratio for each loan.

Now, I will plot the Sharpe measures for each year. The numbers in the second right panel represent the mean Sharpe ratio for each year.

These Sharpe ratios are really impressive in the most recent years. For the year 2013 and 2014, the ratios were 1.48 and 1.322. In other words, if one invested in the loans issued during those years, they would gain on average 1.48 and 1.322 extra return above the risk-free rate for every unit increase in standard deviation of returns. It is important to note that Sharpe ratio is nothing more than a way of standardizing your returns around a baseline. In our case, the baseline happens to be the risk free rate. In part 3, I will explore different loan characteristics that would lead to an increase in the average loan Sharpe ratio.

i. What is the relationship between debt-to-income ratio and risk adjusted return measures like sharpe ratio?

First, I will try to get a better sense of the distribution in debt-to-income ratio before I examine it’s relationship to Sharpe ratio.

Ratios higher than 1 for debt to income is worrisome since it indicates that one has more debts that income. Moreover, we have one outlier. What I will do next is transform the data and remove the top 1% of outliers.

I used the sqrt transformation and removed top 1% of outliers. The transformed data looks much better

There seems to be a somewhat positive relationship between debt to income and sharp ratio (i confirmed the same relationship when I plotted EstimatedReturn and AnnualizedReturn on the y-axis). This means that borrowers with higher debt-to-income tend to provide better risk adjusted returns.

ii. What is the relationship between loan size and Sharpe?

There seems to be a slightly negative relationship between loan size and Sharpe.

iii. Do some loan categories (such as debt consolidation loans) show better risk-return profile than other categories?

The boxplot above indicates that loans that are used for baby&adoption provide the highest Sharpe ratio. Generally, most of the loans provide high Sharpe ratios. Loans that are categorized as Not Available should be avoided.

Plot 1: Estimated vs. Realized Returns

In the figure below I will plot the realized and the estimated monthly returns for the years 2009-2014. The data starts from the period when Prosper restarted its operations, so only a portion of the 2009 year is available.

This plot indicates that the realized returns are generally lower than estimated returns. Nonetheless, it also shows that the underwriting protocol has improved over the ears, because the realized and estimate returns are converging (the small dip in realized return for the period before 2014 is because there are a few datapoints in in those months since many loans have not yet matured).

Plot 2: Sharpe Ratios For Each Loan Category

The boxplot above shows the Sharpe ratios for each loan category. The red diamonds represent the mean Sharpe ratios. The decimal numbers next to each boxplot also represent the mean Sharpe ratios for each loan category. The boxplots have been ordered from smallest to highest median Sharpe ratio. This plot indicates that there are certain types of loans that an investor should generally avoid. For instance, loans that are not categorized in any of the loan category have negative Sharpe ratios, indicating that an investor is better off investing his money in 1-year Treasury yields. I would recommend that an investor stick to loans that have higher Sharpe ratios.

Plot 3: Thinking about pre-payment risk

The plot below contains two panels. The left panel shows how long it took for the loan to be paid off. The loans included in the plot will include loans that have been Completed or Charged Off. Note that the full payment is broken down by loan term (12months, 36months, and 60months). The red dot represents the median, because the distribution of payments is highly skewed.

The right plot shows the expected Sharpe ratio to be gained by investing in loans with either a 12, 36, or 60 month term. The red diamond represents the mean, since the distribution is not so heavily skewed.The number above each boxplot also represents the mean.

The left panel indicates that loans with maturity of 36 and 60 months have high repayment risk. Most of the borrowers chose to repay the debt in full before the loan matures. This is a risk for the lender, since he then must seek to reinvest the repaid money into newer loans, which might not be available on the market. Only loans with maturity of 12 months seems to have lower pre-payment risk, since most lenders finish their paying the loan in full close to the maturity date.

The right panel indicates that the best Sharpe ratio can be found in loans with higher maturity (60 months). Nonetheless, these loans also tend to have higher pre-payment risk (they get repaid in median within about 10 months). The suggestion for an investor would be to invest in 60month loans. They offer the best risk-adjusted returns and are relatively similar in pre-payment risk to the 12 month loans.