Geoffj, on 25 February 2012 - 06:19, said:
Any ideas to develop these suggestions further or add to them?
1) Aged debtors and the estimated 10% recovery rate
The more I look at the 10% assumed recovery rate for written-off loans, the more I realise this is far too simplistic. For mature MLB this 10% figure results in an over statement that then impacts the yield calculation. Further, for an MLB in run-off mode, the bad debt figures become an ever greater proportion of the money on loan.
I was thinking of reporting “aged debtors” and making the statistics accessible. The idea being that a recently written off loan would have a recovery rate of say 10%, but as it ages this would be written down to 0% after say 3 years (The figures will be tuneable)
2) Average duration of closed loans
It is clear that there is a tendency for loans to be paid down early. My assumption is that the early pay down will be very dependent on your lending style, and so general statements about early pay down are not very useful. I could present stats on early pay down for your MLB, so that you have applicable numbers related to your lending style.
3) Front page
Is anyone using the front page tab? Is this something that can be removed?
Starting from the bottom...
I had forgotten that the Front Page tab existed, so I'm obviously not using it. But it does provide a nice summary, and no doubt there will be some who do use it, and would miss it if it were removed. I expect it's a case of many users and many overlapping features means some people use some features and others use others. So the only way to please all the people all the time is to leave all the features in. Until, of course, the spreadsheet gets so big that its size and performance become an issue. This used to affect me, but I've been forced to upgrade my PC so now the spreadsheet works like a dream.
Info about early payments would be very useful. I think this is an aspect of Zopa that has a much greater effect on their achieved returns than most lenders realise, so this might bring the issue to more people's attention. As an illustration of how much early payment is happening... In the last week I've taken a good look at how the actual payments received into my account compare to the spreadsheet's predictions, and I've been amazed by the results -- I've been getting
THREE TIMES as much money into my account as predicted! (And for anyone who doesn't know me, I'll add that I have a pretty large loan book, so these results are not being unduly influenced by one or two overpayments.)
As for the expected recovery rate on Written Off loans, the feature proposed would be helpful. I don't expect 10% on those any more, and I've used the Variables tab to change my assumption to 1%.
While on that subject, I'll also mention that I think the default recovery rates for Collections (50%) and Arrangement (25%) are directionally wrong. A loan with an Arrangement indicates a borrower that acknowledges their debt and is trying to do something about it, whereas a Collections loan suggests to me that the borrower is ignoring the problem and hoping it will go away on its own. In my case I've swapped the suggested recovery rates and am using 50% for an Arrangement and 25% for a loan in Collections. I'd be interested to know whether others think I've got the direction right or not.
As far as possible further features to include... One addition that I've made to my spreadsheet -- with a lot of Excel help from GG -- is to add a Bad Debt tab. On it are shown the estimated Bad Debt expectations for each of the markets I have lent in, and that is then compared to the actual bad debt I've incurred. This allows me to see how my actual bad debt compares to the estimates, both by market and overall.
5
Having said that, the spreadsheet is very large for some users even with relatively high-powered laptops, so their needs should be kept in mind - as others have said.
