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:

• Prosper Debt Sale Percentages With the release of the new private data I thought I would write most of my posts this week on nuggets on information that can...
• Does Prosper’s Public Data Allow Loan to Listing Correlation? Using today’s public data, 98% of the time I can correlate loans in arrears to the listing with 100% accuracy. There are 488 loans with...
• 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...
• Updated Vintage Curves November 1st 2007 Here are the updated general population vintage curves for November, 1st 2007.  Click the image for a larger version… One change to the  graphs these...
• Updated 0DQ Vintage Curves November 1st 2007 Here are the updated 0 current delinquencies vintage curves for November, 1st 2007.  Click the image for a larger version… One change to the  graphs these...

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


Categories:

Uncategorized

Tags:

azerbaijan, mozambique