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’
Categories:
Uncategorized
Related Articles Related Stores
6 comments ↓
Can you please post this on the prospers.org forum. I haven’t seen it yet on my radar.
[...] Kevin presents Prosper Vintage Curves From Private Data [...]
[...] If you’re new here, you may want to subscribe to my RSS feed or via email. Thanks for visiting!LazyMan is hosting the first ever carnival of peer-to-peer lending. Thank you Lazy for including my post (Prosper Vintage Curves From Private Data) in the rotation. [...]
[...] Prosper Vintage Curves From Private Data (Updated Curves: Prosper Vintage Curve Update — 1/1/2008) [...]
[...] RateLadder posted new Prosper Vintage Curves. He posted previously an explanation of vintage curves if the graph leaves you scratching your head. [...]
[...] methodolgy or the sql for obtaining the data these curves from the private data export has already been explained in detail, but [...]
Leave a Comment