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 Stores

• Time Travel Romance Books
• Gold Key By Publisher Bronze Age 1970-79 Comics Collectibles
• Long Life Light Bulbs Home Lighting
• Time Travel Romance Audio Books
• Key Chains Vintage Sports Memorabilia Sports Collectibles

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


Categories:

Uncategorized