Tuesday, 16 December 2014

Even More Statistical Analysis on Bondora P2P Loans

In a number of previous posts on the BTLFinder blog I wrote about some of the analysis I did of the Bondora loan spreadsheet using basic analysis using SQL Server and Excel.

See here, here and here for the analysis.

In this article I’ll show some of the results I obtained from an analysis of the Bondora loan data using SAS Studio. SAS is a popular software application used by organisations that need to perform statistical analysis on their data sets.

Now I’ll point out that I’m NOT a statistician and although I did study one module of statistics at University, that was over 20 years ago! So excuse me if I make a complete mess of this (but if I have and you spot any glaring errors, feel free to leave comments). I have a suspicion my analysis is flawed, but when I get the hang of things I'll publish some more results. </disclaimer>

Data Preparation for Analysis

Now I’ve quickly learnt that SAS is a lot less forgiving of bad data quality than are either Excel or SQL Server. And unfortunately (according to SAS anyway) there are quite a lot of data quality issues with the Bondora dataset.

So here are the steps I took to clean up the Bondora dataset and get it into SAS:

  1. I loaded the data into Excel.
  2. In Excel I removed all loans where WasFunded = 0 and CreditDecision = 0.
  3. Then I removed all of the loans with a LoanDate within the last 3 months, just to avoid new loans from skewing the results too much.
  4. After that I removed any loan without a value for Gender, and those loans with a credit_score of 0.
  5. I then imported the data into SAS by way of a CSV file exported from Excel.

My first attempt at loading the data into SAS was not successful due to numerous import errors. I tried playing around by removing the lines it was complaining about. But in the end I succeeded by just importing selected columns of data into SAS rather than the entire file. These are just some of the columns it was happy with: IsBusinessLoan, Age, Gender, Country, credit_score, CreditGroup, LoanDuration, UseOfLoan, ApplicationType, education_id, marital_status_id, employment_status_id, Employment_Duration_Current_Employer, work_experience, occupation_area, home_ownership_type_id, AD, DefaultedOnDay, AppliedAmount and FundedAmount.

As with my previous analysis I've filtered the data so that it mainly includes information about Estonian borrowers. I know a lot of Bondora lenders tend to invest solely in these - the other countries being a little riskier. See below for why non-Estonian loans are big trouble! Thankfully SAS is able to filter data using WHERE clauses, so I could just import the whole dataset, then filter the reports.

Age of Borrower

The chart below shows the age distribution of Bondora borrowers:



I did this analysis in Excel before but what I like about SAS is that it automatically groups ranges and puts in nice distribution curves.

Anyway, this chart is nice but it's not particularly useful other than telling us that peak demand for loans is from borrowers in their late 20's and early 30's.

What's more useful is default rates plotted against age of borrower. In the chart below you can see loans by age and grouped by default status (AD=1 is for defaulted loans, AD=0 is for non-defaulted loans):



The basic message here is as with my previous analysis, I found that younger borrowers are more likely to default on their loans.

Number of Days to Default

I wanted to model loan defaults to see how long it took for the average borrower to default on their loans. I read on various Bondora forums and blogs that most borrowers default at the start of their loan.

Indeed this is true:



As you can see from the chart above, nearly 40% of borrowers who default on their loans do so within the first 160 days (i.e. 6 months).

This surprised me, as my experiences on Zopa was that my borrowers mostly defaulted late into their loan repayments.

The data in this chart have important implications for Bondora and maybe other P2P platforms that have a secondary market. It implies that it could be very worthwhile buying loans on a secondary market where you can screen out the loans by the number of repayments. I’d be interested in knowing if this is the same on platforms like Funding Circle.

And anyone buying new loans and hoping to sell them on at a profit – you could get your fingers burnt!

By the way, I mentioned at the top that a lot of lenders have got their fingers burnt on non-Estonian loans. Are the other countries really that bad? Here are default rates for the other countries:




So as you can see from these charts, for each of the countries Spain, Finland and Slovakia, if loans in these countries go bad, then they usually go bad in the first 100 days. By comparison, Estonian loan defaults peak at around the 160 day mark.

OUCH, OUCH, OUCH!

So yes, lenders are justified in being very wary of Spanish, Finnish and Slovakian loans.

One other word of caution - Bondora hasn't been so active in these other markets, so there is less historical data available compared to that available for Estonia.

Loan Defaults by Loan Amount

Whenever I've been investing in P2P platforms I've always been wary of the larger loans. For example, I used to like investing in the £3000 jewellery loans on Funding Secure and was wary of the £250,000+ loans.

But are larger loans any more risky?

According to this chart I generated for Bondora, apparently not:



This chart shows that defaults are much more likely with lower value loans. The bottom graph shows the data for loans that have defaulted (AD=1). The orange line is the one of interest, showing a spike of defaults for smaller loan amounts.

Well it's dangerous to extrapolate one chart to the entire P2P industry, but I'll definitely be happy to invest in some larger loans if they come my way.

As for the explanation, well I guess it's simply down to borrowers who borrow larger amounts of money find it easier to repay their loans. Somebody who borrows €10,000 is far more likely to be able to repay their loan than somebody who needs to borrow €500.

Summary


At the time of writing this blog post, Bondora have just announced that they've overhauled the loan credit scoring system. So I think it's back to the drawing board as far as my analysis goes.

I have a sneaking suspicion my SAS skills need a lot of work. However, it's pretty obvious from my basic analysis that non-Estonian loans are a major credit risk - especially if Bondora don't get their act together and chase defaulters as successfully as they've managed in Estonia.

If you want to repeat my analysis then I've left the SAS code below.

Have fun investing!

/*SAS Studio Bondora Analysis Code*/

/*Data import*/


proc import datafile="/folders/myfolders/LoanData.csv" out=mydata dbms=csv replace;
    getnames=yes;
run;

proc contents data=mydata;
run;

/*Number of days to default*/

ods graphics / reset width=6.4in height=4.8in imagemap;
title "Number of Days to Default";

/*--SGPLOT proc statement--*/
proc sgplot data=WORK.MYDATA;
    /*--Histogram settings--*/
    histogram DefaultedOnDay / scale=PERCENT;

    /*--Kernel Density plot settings--*/
    density DefaultedOnDay / type=Kernel;

    /*--Horizontal Axis--*/
    xaxis label="Day of Default";

    /*--Vertical or Response Axis--*/
    yaxis grid;
    discretelegend "DENSITY" / location=inside position=topright across=1;
   
    where Country = 'EE';
run;

/* Age Distribution*/

ods noproctitle;
ods select where=(lowcase(_path_) ? 'plot' or lowcase(_path_) ? 'gram');

proc univariate data=WORK.MYDATA noprint;
where Country = 'EE';
    histogram Age / kernel;
run;

/*Age by AD*/

ods noproctitle;
ods select where=(lowcase(_path_) ? 'plot' or lowcase(_path_) ? 'gram');

proc univariate data=WORK.MYDATA noprint;
    class AD;
    where Country = 'EE';
    histogram Age / kernel;
run;

/* Applied Amount*/

Title;
ods graphics on;
ods noproctitle;
**************************;
*** Test for normality ***;
**************************;
ods select TestsForNormality;

proc univariate data=WORK.MYDATA normal mu0=0;
    class AD;
    var AppliedAmount;
run;

ods select all;
**************************;
*** t Test ***;
**************************;

proc ttest data=WORK.MYDATA sides=2 h0=0 plots(only showh0)=(summaryPlot
        intervalPlot qqplot);
    class AD;
    var AppliedAmount;
run;


/*Number of Days to Default - by County*/

ods graphics / reset width=6.4in height=4.8in imagemap;
title "Number of Days to Default";

/*--SGPLOT proc statement--*/
proc sgplot data=WORK.MYDATA;
    /*--Histogram settings--*/
    histogram DefaultedOnDay / scale=PERCENT;

    /*--Kernel Density plot settings--*/
    density DefaultedOnDay / type=Kernel;

    /*--Horizontal Axis--*/
    xaxis label="Day of Default";
    title "Number of Days to Default (Estonia)";

    /*--Vertical or Response Axis--*/
    yaxis grid;
    discretelegend "DENSITY" / location=inside position=topright across=1;
   
    where Country = 'EE';
   
proc sgplot data=WORK.MYDATA;
    /*--Histogram settings--*/
    histogram DefaultedOnDay / scale=PERCENT;

    /*--Kernel Density plot settings--*/
    density DefaultedOnDay / type=Kernel;

    /*--Horizontal Axis--*/
    xaxis label="Day of Default (Spain)";
    title "Number of Days to Default (Spain)";   

    /*--Vertical or Response Axis--*/
    yaxis grid;
    discretelegend "DENSITY" / location=inside position=topright across=1;
   
    where Country = 'ES';

proc sgplot data=WORK.MYDATA;
    /*--Histogram settings--*/
    histogram DefaultedOnDay / scale=PERCENT;

    /*--Kernel Density plot settings--*/
    density DefaultedOnDay / type=Kernel;

    /*--Horizontal Axis--*/
    xaxis label="Day of Default (Finland)";
    title "Number of Days to Default (Finland)";

    /*--Vertical or Response Axis--*/
    yaxis grid;
    discretelegend "DENSITY" / location=inside position=topright across=1;
   
    where Country = 'FI';

proc sgplot data=WORK.MYDATA;
    /*--Histogram settings--*/
    histogram DefaultedOnDay / scale=PERCENT;

    /*--Kernel Density plot settings--*/
    density DefaultedOnDay / type=Kernel;

    /*--Horizontal Axis--*/
    xaxis label="Day of Default (Slovakia)";
    title "Number of Days to Default (Slovakia)";   

    /*--Vertical or Response Axis--*/
    yaxis grid;
    discretelegend "DENSITY" / location=inside position=topright across=1;
   
    where Country = 'SK'; 
run;

Sunday, 30 November 2014

Effect of Employment Factors on Bondora Default Rates

Hi,

I'm Brett, and in this article I'll present some more findings from an analysis of the Bondora Loan dataset.

Bondora is a P2P Lending Platform where lenders can potentially earn over 20% annual interest on their savings. That's nice in theory, but you're loaning your money out to people via unsecured loans, which can be risky!

So in this article I'll look at borrower employment factors, and whether they're a good indicator of likely loan default rates.

All of the factors I look at here are available in the Search Filter on the Bondora Market and Secondary Market screens.

Employment Status

See below for the chart on employment status:
Percentage default rates (Y) plotted against borrower's employment status (X)

The most noticeable thing here is that self-employed borrowers appear to have a much higher risk of defaulting on their loan obligations, and "entrepreneurs" have a much lower risk of default.

I'm not sure what the distinction Bondora makes between the self-employed and entrepreneurs. But this might go along with my previous analysis that suggested that borrowers borrowing for business purposes seemed to have a lower level of default.

Other than that, there seem roughly consistent default rates between employed, part-timers and retirees.

Occupation Area

The type of job a borrower has seems to have an influence the likelihood of them defaulting:
Percentage default rates (Y) plotted against borrower's occupation type (X)
I'm not sure I would read too much into this chart though. Industries come and go. For example, at the time I'm writing this we've witnessed the price of oil dropping from $110 to less than $70 in less than a year. That will likely lead to a lot of redundancies in the previously booming energy sector.
Other industries are notoriously cyclical, like IT in which I work.

Employment Duration with Current Employer
Percentage default rates (Y) plotted against borrower's time with their current employer (X)
As can be seen from the red trend line, there is a rough relationship between time with current employer and the chance of a default. However, without running the figures through a statistics package, I'm not sure how significant the differences are.

There is another category called trial period and there were no defaults amongst borrowers in this group. So these borrowers might be worth seeking out. However, there was quite a small sample size of just 58 loans for borrowers in this category, whereas the other categories had between 450 and 2700 loans.

Work Experience Years

There's a pretty good relationship between a borrower's number of years work experience and their likelihood of defaulting on their loans:
Percentage default rates (Y) plotted against borrower's years of total employment experience (X)
However, this factor is probably just a proxy for a borrower's age; my previous analysis showed that age has a big influence on the borrower's likelihood of defaulting on their loan obligations.

Current Job Title

Unfortunately I couldn't find this factor in the loan data spreadsheet, so there is no data available for this factor.

Data Generation

To generate this data, I used the following process:
  • I downloaded the loan Excel spreadsheet from Bondora.
  • I imported the Excel spreadsheet into SQL Server.
  • I wrote some custom SQL queries to analyse the data.
  • I exported the results sets from SQL Server back into Excel in order to turn them into charts.
  • I have assumed that the AD column equaling 1 indicates that a loan has defaulted. I have excluded loans that were applied for within the last 3 months or so. Finally, I've only included Estonian loans in all the queries except for the one relating to country.
If you want to have a go at analysing the data yourself, then this is the basic SQL query I used, in this case the query for the occupation_area factor:

SELECT
    case occupation_area when 1 then 'Other'
    when 2 then 'Mining'
    when 3 then 'Processing'
    when 4 then 'Energy'
    when 5 then 'Utilities'
    when 6 then 'Construction'
    when 7 then 'Retail and wholesale'
    when 8 then 'Transport and warehousing'
    when 9 then 'Hospitality and catering'
    when 10 then 'Info and telecom'
    when 11 then 'Finance and insurance'
    when 12 then 'Real-estate'
    when 13 then 'Research'
    when 14 then 'Administrative'
    when 15 then 'Civil service & military'
    when 16 then 'Education'
    when 17 then 'Healthcare and social help'
    when 18 then 'Art and entertainment'
    when 19 then 'Agriculture, forestry and fishing'
    end 'Occupation Area',
    (Sum(AD) / Count(*) * 100) AS 'Percentage Defaulted',
    SUM(AD) as NumberInDefault,
    COUNT(*) as NumberOfLoans
From Loans
where country = 'EE' and creditdecision = 1
and occupation_area is not null and occupation_area > 0
and LoanApplicationStartedDate < '2014-10-27'
group by occupation_area
order by convert(int, occupation_area)


Summary and Conclusions

Once again there are some useful factors available that can help you to reduce your possible future default rates on Bondora.

Just bear in mind that economies are cyclical and industries come and go. So this can have a big influence on the ability of people to repay their loans, especially if they can't easily find employment in an alternative sector. Mining would have been booming when the price of gold hit $2000 and ounce, but at the time of writing it's back down to $1200, and could go a whole lot lower. 

Comments? Questions? Suggestions? Leave feedback below!

Thursday, 27 November 2014

More Credit Risk Analysis on Bondora Default Rates

Hi,

I'm Brett, and in this article I'll present some more findings from an analysis on the Bondora Loan dataset.

Click here to see the previous analysis I did on this dataset. The previous analysis looked at how to potentially reduce default rates by using the Portfolio Manager.

In this article I'll look at some of the factors you can use to choose loans through the Marketplace and Secondary Market. Note that I'm only presenting data from Estonian borrowers, and also that past performance is no indicator of future performance.

So with that in mind, what factors are useful in screening out higher risk borrowers, and which factors bear little relationship with loan default rates?

Read on to find out...

Gender

There is quite a significant difference between the loan default rates of male and female borrowers:

Percentage default rates (Y) plotted against borrower's gender (X)
I found that default rates for men were 10.36%, against 8.92% for women. So men appear to be 16% more likely to default on their loans.

Only loaning money to women does significantly cut down on the number of available loans though. Men comprise 53% of the market, and women 47%.

Age

As far as age goes, the first observation is the data quality isn't great for anyone younger than 21 or older than 65. So I'd definitely avoid anyone outside of the core 21 - 60 age group.

But in this core age group, it's fairly obvious that there's a good relationship between age and default rates on loans:

Percentage default rates (Y) plotted against age of borrower (X)
So younger people are a higher credit risk. There's a much lower credit risk once borrowers hit 30.

Again, I would avoid lending to borrowers older than 60 as there is a spike in defaults at 65 for some reason.

Marital Status

There's also some useful information that can be found in the marital status factor:

Percentage default rates (Y) plotted against borrower's marital status (X)
So single people are the highest risk, with those co-habiting a safer credit risk and married people even safer.

But lowest risk of all are divorced people. I'm a little surprised at this, so it could definitely be a factor worthy of further investigation in a future article...

Use of Loan

In my previous attempt at analysing the factors you could use to select loans using the Bondora portfolio manager I was somewhat disappointed.

Not so with the factors you can use in the Marketplace or Secondary Market!

Another really useful factor is Use of Loan:

Percentage default rates (Y) plotted against borrower's declared use of loan monies (X)
According to my analysis, loan defaults are much lower for loans taken out for the purpose of business and travel.

I was a little surprised about this, and previously I would avoid investing in any travel related loans on Bondora.

I was flat wrong!

I guess that people who book vacations are feeling reasonably confident that their financial situation is stable and that they'll be able to repay their loan.

And I suppose that people who borrow for a business are the type of personality who works hard and is able to find enough opportunities out there to repay their loans.

After producing this chart, I would definitely be more wary of loans relating to education and consolidation of existing loans.

I read on a forum that somebody likes to avoid vehicle loans. Well these do look higher risk, but they're not the highest risk - that honour belongs to education.

Finally, I'll definitely be seeking out real estate or home improvement loans, which appear to have a lower risk profile compared to some other uses.

Education Level

From the chart below, it appears that loan risk is lower the more educated the borrower is:

Percentage default rates (Y) plotted against borrower's highest level of education (X)
 I guess that more educated people tend to find it easier to find good jobs in Estonia's labour market.
Incidentally, take the default rate for primary level education with a pinch of salt due to the comparatively low number of borrowers in this group.

Data Generation

To generate this data, I used the following process:
  1. I downloaded the loan Excel spreadsheet from Bondora.
  2. I imported the Excel spreadsheet into SQL Server.
  3. I wrote some custom SQL queries to analyse the data.
  4. I exported the results sets from SQL Server back into Excel in order to turn them into charts.

I have assumed that the AD column equaling 1 indicates that a loan has defaulted. I have excluded loans that were applied for within the last 3 months or so. Finally, I've only included Estonian loans in all the queries except for the one relating to country.

If you want to have a go at analysing the data yourself, then this is the basic SQL query I used, in this case the query for the education_id factor:

SELECT
    case education_id when 1 then 'Primary'
     when 2 then 'Basic'
     when 3 then 'Vocational'
     when 4 then 'Secondary'
     when 5 then 'Higher'
     end 'Education',
    (Sum(AD) / Count(*) * 100) AS 'Percentage Defaulted',
    SUM(AD) as NumberInDefault,
    COUNT(*) as NumberOfLoans
From Loans
where country = 'EE' and creditdecision = 1
and education_id between 1 and 5
and LoanApplicationStartedDate < '2014-10-27'
group by education_id
order by education_id


Summary and Conclusions

The basic message is that if you want more control over your loan future default rates, then you have to buy loans on the Marketplace and possibly in the Secondary Market as well.

There are definitely some good factors that can be used to lower your potential default rates.

One thing I'll point out is that in this article I've only considered the effect of one factor at a time on loan default rates.

I'd sure like to drill down to get the likely default rates of a particular group (e.g. 50-55 year old women who want travel loans). I suspect that default rates for these types of groups will be significantly lower than the market average. 

Comments? Questions? Suggestions? Leave feedback below!

Sunday, 23 November 2014

Reducing Bondora Default Rates Using the Portfolio Manager

Hi,

I'm Brett, and in this article I'll present some findings from an analysis on the Bondora Loan dataset available here.

Bondora is a P2P Lending Platform where you can potentially earn up to 25% interest a year. That pretty much beats any other bank account, stock or BTL property you're ever likely to put money into.

The downside? Loan default rates are very high and there is an additional currency risk if (like me) your main income isn't in Euros.

However, if you know what you're doing, then it's possible to make very good returns on Bondora.

One way of potentially increasing returns on P2P lending platforms is by profiling borrower default rates, and only lending to those who are statistically less likely to default on their loans.

This is where Bondora has a big advantage over other P2P lenders - they make their historic loan data available for analysis.

In this article I'll run through some VERY basic credit analysis I did on the dataset I downloaded on 3 November 2014. See towards the end of the article for exactly how I did the analysis.

The Bondora loan dataset contains dozens of columns of data about each loan. In this article I have focused purely on the factors that are available to lenders who want to set up Portfolio Managers to automate the buying of loan parts.

This is the basic Portfolio Manager:

The Bondora Portfolio Manager configuration screen


See below for some basic analysis on each factor, and whether it's actually useful for reducing the risk of a default.

Credit Group

Bondora borrowers are assigned one of three Credit Groups: A, B or C.

Surprisingly, I found an inverse relationship between credit group and default rate:

Percentage default rates (Y) plotted against borrower's assigned credit group (X)


One thing to bear in mind is that there are far less "C" than "B" or "A" rated borrowers. In my sample data there were 6895 "A" loans, 1090 "B" loans and only 419 "C" loans.

So there may simply be less reliable data for the "C" loans.

My experiences with Zopa do suggest though that credit rating is not that great a future indicator of the possibility of a borrower defaulting. Most of my Zopa defaults were actually in the "A" credit group, and my "B" and "C" loans fared surprisingly well.

Verification Type

This factor specifies how Bondora verified the borrower's income and expenditure. The four options for this are:
  • Income unverified
  • Income unverified, cross-referenced by phone
  • Income verified
  • Income and expenses verified

I'm a bit puzzled by my results from this query:
Percentage default rates (Y) plotted against income verification method (X)
According to my analysis, no borrowers with unverified income have defaulted. Hmmm. If you have an explanation, then please do leave your comments at the end of this article.

Aside from this oddity, the rest of the data appears more reliable. Loans where the borrowers have had both their income and expenses verified appear half as likely to go into default compared to loans where only the borrower's income has been verified.

When setting up my Portfolio Managers I would definitely prefer to select only loans where the borrower's income and expenses have both been verified. But then so does everybody else...

Credit History

A borrower's is assigned a Credit History score according to how recently they had problems making repayments on a loan. Bondora scores credit history from 500 - 1000, the criteria being:
  • 1000: No previous payments problems
  • 900: Payments problems resolved 24-36 months ago
  • 800: Payments problems resolved 12-24 months ago
  • 700: Payments problems resolved 6-12 months ago
  • 600: Payment problems resolved <6 months ago
  • 500: Borrower has active payment problems
The chart shows that in general, the higher the borrower's credit history, the lower the chance of default:
Percentage default rates (Y) plotted against borrower's credit history (X)
This chart also shows that there's a much higher chance of default for the 500 credit history borrowers.

Canny lenders might infer from this chart that borrowers in the 800 credit history group might be worth a chance.

Loan Term

The Loan Term factor expresses how long the loan is for, in months.

I'm not sure how useful this factor is when using Bondora, as most loans these days seem to be for 60 months (i.e. 5 years) duration.

Anyway, here's the chart of loan default rates against loan term:
Percentage default rates (Y) plotted against loan term in months (X)

Personally I'd prefer to lend for 36 months rather than 60 months. This is because my experience with Zopa was that most of my bad loans only went into default after 3 years.

I guess this is because most borrowers who end up defaulting on a loan do so because of a drastic change in life circumstance (e.g. sickness, losing their job, divorce). And it's much more likely that somebody will have such an unforeseen event happen in 3 or 4 years time, rather than in the first few months after they take out their loan.

Debt to Income Ratio

The Bondora Portfolio Manager allows you to only select loans where the borrower's Debt to Income Ratio is less than 59%.

How useful is this ratio?

Not very!

I'm not too sure if debt to income ratio is a worthwhile inclusion on the portfolio manager.

In my database I could only find 105 loan applications where the debt to income ratio was greater than 59%. Most borrowers have a debt to income ratio of far less than 59%.

By plotting defaults against debt to income ratio, it appears that if anything, loan defaults are slightly lower at higher debt to income ratios.
Percentage default rates (Y) plotted against borrower's debt to income ratio (X)


But again, this Portfolio Manager option appears to be of limited use.

Country

Bondora started off operating in Estonia, but has now started offering loans to people in Spain, Finland and Slovakia.

This analysis comes with a disclaimer - Bondora hasn't been operating in the other three countries for as long as it has in Estonia, so the default rates for the other counties may rise significantly over time.

The loan defaults by country chart is below:
Percentage default rates (Y) plotted against borrower's country (X)

Most Bondora lenders prefer to lend to Estonian borrowers. This appears to be because Bondora has a good record of chasing late loans in that country.

Slovakia appears to be a particularly high risk country to invest in P2P loans through Bondora.

Both Spain and Finland appear to offer opportunities. Clearly more analysis is required here. But Estonian loans are becoming harder to come by on Bondora, so more lenders are interested in diversification into other countries, especially those with more than €5,000 to invest.

Min Interest

As can be seen in the chart below, there's a pretty good relationship between loan interest rate and the chance of a default happening:
Percentage default rates (Y) plotted against loan interest rate (X)

This is likely due to two factors:
  1. Higher risk borrowers are charged a higher rate of interest due to their risk profile.
  2. Loans with higher rates of interest are more of a financial burden on the borrower.
Incidentally, in the chart above I've left out the data for the few borrowers borrowing at less than 10%. There were only 38 loans in this category, and no defaults.

Data Generation

To generate this data, I used the following process:
  1. I downloaded the loan Excel spreadsheet from Bondora.
  2. I imported the Excel spreadsheet into SQL Server.
  3. I wrote some custom SQL queries to analyse the data.
  4. I exported the results sets from SQL Server back into Excel in order to turn them into charts.
I have assumed that the AD column equaling 1 indicates that a loan has defaulted. I have excluded loans that were applied for within the last 3 months or so. Finally, I've only included Estonian loans in all the queries except for the one relating to country.

If you want to have a go at analysing the data yourself, then this is the basic SQL query I used:

SELECT
CreditGroup,
    (Sum(AD) / Count(*) * 100) AS 'Percentage Defaulted',
    SUM(AD) as NumberInDefault,
    COUNT(*) as NumberOfLoans
From Loans
WHERE country = 'EE' AND creditdecision = 1
AND CreditGroup IS NOT null
AND LoanApplicationStartedDate < '2014-10-27'
GROUP BY CreditGroup
ORDER BY CreditGroup


Summary and Conclusions

Avoid Slovakia and borrowers with a 500 credit history score? That seems to be the main findings of my number crunching in SQL Server.

I have to say that if you want to fine tune your portfolio's risk of default, then the Portfolio isn't really the best place to do that.

In order to really reduce your potential default rates, you need much more control over your loan buying, and that just isn't possible using the basic settings within the Portfolio Manager.

In a future post I'll cover some of the factors that are much better indicators of future potential default rates! I hope to also cover some different tools for analysing the loan data. SQL Server is good, but there are plenty of other tools that can also give great results with this data.

One last thing... There is one other factor that I haven't  mentioned in the analysis here, but is an important consideration for the Portfolio Manager. When investing in a P2P platform it's generally better to invest €5 in 20 different loans, than invest the entire €100 in a single loan. Eggs and baskets and all that. So bear that in mind when you come to configure your Portfolio Manager.

Comments? Questions? Suggestions? Leave feedback below!

Monday, 22 September 2014

Welcome to BTL Finder

A big welcome to the BTL Finder blog...

Hi,

I'm Brett, and I've been a BTL landlord since 2006.

In 8 years I've had 4 sets of tenants, a big panic in the financial crisis of 2008 and various plumbing catastrophes. But I remain convinced that UK property is a solid long term investment.

In 2014 I thought about buying another property and renting it out. But when I went to research the property market online, I found that no site gave an indication of where the best yields were.

So I created BTL Finder.com.

The site contains a load of information to help you trawl the UK property market in terms of yield.

It's very much a work in progress, but here are some key features so far:

Any questions, comments or suggestions? Leave feedback below!

BrettB, RH12, 22.09.2014.