You may have seen my Prosper vintage curves…  While they were hard to make since the data had to be pulled off the Prosper Loan Performance page.

With the new private lender data these vintage curves can be pulled with sql and excel greatly expediting the process.

Check out the Daily Count Late Percentage Vintage curve…

Using this sql:

(You need a table alldays with 1 column aday and a row for every day).  (Unfortunately this sql takes a long time to run (10 minutes) so you won’t be able to run it on ProProsper as the query timeout is set to 30 seconds).

DECLARE @DTD int
SET @DTD=30
SELECT
cast(aday-originationdate as int) as 'PIT',
l.creditgrade,
sum(PrincipalBalance+NetDefaults) as 'Amount',
count(l.[key]) as 'Count',
sum(case WHEN (mld.DPD!=0 and (mld.DPD+(aday-observationdate))>@DTD) THEN PrincipalBalance+NetDefaults ELSE 0 END) as 'AmountLate',
sum(case WHEN (mld.DPD!=0 and (mld.DPD+(aday-observationdate))>@DTD) THEN PrincipalBalance+NetDefaults ELSE 0 END)/sum(PrincipalBalance+NetDefaults) as AmountLatePercentage,
sum(case WHEN (mld.DPD!=0 and (mld.DPD+(aday-observationdate))>@DTD) THEN 1 ELSE 0 END) as 'CountLate',
sum(case WHEN (mld.DPD!=0 and (mld.DPD+(aday-observationdate))>@DTD) THEN 1.0 ELSE 0.0 END)/count(l.[key]) as 'CountLatePercentage'
FROM
loan l
inner join creditprofile cp on cp.listingkey=l.listingkey
inner join LoanPerformance mld on l.[key]=mld.loankey cross join alldays
where
mld.observationdate = ( select top 1 observationdate from LoanPerformance sub where sub.observationdate < aday and sub.loankey=mld.loankey order by sub.observationdate DESC )
and aday < getDate()
and aday >= '02/01/2006'
and l.creditgrade!='NC'
group by
cast(aday-originationdate as int),
l.creditgrade
having
count(l.[key])>250 and
sum(PrincipalBalance+NetDefaults)>0
order by
'PIT'

Related Posts

• Bidding With the Prosper API Code Example QuickSnipe.java For my ProsperDays presentation this year I will demonstrating bidding with the Prosper API...  I have built a quick and dirty bid snipping java program.  (This program is not ready for primetime as bid sniping tool, but it is very close and a very effective demo.  The reason I say......
• 1 Month Late or Worse Curves by Credit Grade (July 1 Update) This is an update to my original 1 month late or worse curves. I belive we are starting to see the flattening of the curves. I have pulled the public data on the performance tab by hand in order to produce a spreadsheet of loan performance. This spreadsheet was then......
• Prosper Interest Rates On the Rise Prosper interest rates are on the rise -- in case you where wondering this is a good thing for lenders!  The release of the lender bidding guidance (general announcement, secondary analysis) seems to line up with a rise in near prime and sub prime interest rates.  Similarly, the graph that......
• Prosper Vintage Curves 12/1/2007 Today we are updating the vintage curves for 12/1/2007.  (Here is the original explanation.) The number of loans required for display is 250. There are 3 different populations shown across both count late % and amount late %. General population. All loans in credit grade. Loan Count Late % Dollar......
• Lending Club Loan Volume Misquoted in National Newspaper I encourage all reporters to check facts. In the p2p lending space, both major US p2p lending companies provide data and this data can usually corroborate or debunk basic marketplace claims.  I was reading a recent Pittsburgh Post-Gazette article on p2p lending: The credit crunch: How did it happen and where......

Online Stores

• Case Agriculture Advertising Collectibles

If you liked this article, vote for it on del.icio.us and stumbleupon.


Categories:

Uncategorized

Tags:

azerbaijan, mozambique