A horribly oversimplified Excel model for modelling defaults
Please, please first read (or skim) my 4 posts on "What an X% default rate should mean to me" -- which cover Prosper.com loans from the perspective of lenders
Default Rates, Part I
Default Rates, Part II
Default Rates, Part III
Default Rates, Part IVUPDATE: Same file, but slightly more user friendly: don't hotlink it here...
posted by cellardoor @ 5/04/2006 09:52:00 AM
5 comments
links to this post
5 Comments:
Would it be possible to get a small tutorial on this excel sheet?
For instance, how do we decide if we should select 12 months or 36 months, what does this mean?
What is the Present Value and why would I want to modify it? Why would I want to modify "expected return"? When I did, nothing else seemed to work...
Thanks!
Sorry -- the 12 vs 36 reflected an old debate over whether the prosper-provided default numbers were annualized or not.
I've fixed it at 12 months, as we finally got an answer from prosper.
More generally, it was poor documentation on my part to call all yellow fields "modifiable" -- some were modifiable by virtue of your changing the values in those boxes; others were only indirectly modifiable. That is, once you changed the values in the other boxes, those boxes would change.
I've relabelled the boxes yellow and green instead.
short answer -- don't modify PresentValue and ExpectedReturn -- both are "modified," or calculated, for you by the model itself.
Download the new version above...
Thanks -- it makes a lot more sense now and I find this really useful! Appreciate all the work and thought you've put into it.
I understand that 0.5% also needs to be subtracted from expected return, due to Prosper fees on servicing the loan -- is there anything else I'm missing that would affect the expected bottom line?
no worries. glad you could use it.
well, nothing else explicit, except for the fact that assuming defaults occur smoothly over time is just ridiculous.
Is assuming they occur smoothly ridiculous? Page 4 of this file:
http://www.ots.treas.gov/docs/9/961951.pdf
Seems to state that they convert a two year default rate into a one year default rate by assuming a constant monthly charge off. The footnote also says they did this with Equifax data and it matched their experience as well... That seems to imply that major credit card companies are using a model similar to this to calculate default rates or am I misreading it?
Thanks!
Post a Comment
Links to this post:
Create a Link
<< Home