I do not trust various online lending analysis tools any more because their results are too different (actually I do not know which one I should believe in). Their magic analysis methods are not public (I am too stupid to guess them out). So today I tried doing some simple analysis with Excel on the data I directly download from lendingclub.com
Analysis Results
I applied my filter to the historical data from 2012-1-1 ~ 2013-6-30 (1.5 years duration).
The total issued loan comply with my filter conditions were 2722. And the bad loans among them were 148 so far. In terms of how I count bad loans, I query the load_status are Charged off, Default or Late 31~120 days.
I learned from lending academy there are some interesting data inside the downloaded loan data which you could not see on lendingclub's webpage and I found them.
- mort_acc: how many mortgages the borrower had when apply loan.
- total_bal_ex_mort: total mortgages balance
I checked those two data average for loans/defaulted under my filter conditions.
Total average mort_acc: 2.27
Total average total_bal_ex_mort: 33800
Default average mort_acc: 2.4
Default average total_bal_ex_mort: 35815
It's not obvious those two fields are useful for loan filtering.
Following is how the loan distribute by grade using my load condition. Looking at that, I should exclude F- from my filter conditions.
| Grade | Total | Bad | Bad Rate |
| C | 1886 | 82 | 4.3% |
| D | 527 | 40 | 7.6% |
| E | 230 | 13 | 5.7% |
| F | 75 | 13 | 17.3% |
| G | 4 | 0 | 0.0% |
Next Steps
It's really not convenient to use Excel to do analysis. I will create a database to do data analysis in the next couple of months.
I have not had time to look into how to count loan profit and loss for different status. I should research how it works over time.
There are some data columns inside the downloaded tabular data file I still do not understand what their meaning is. I will try to figure them out over time.
