93 replies to this topic

[Geoffj]

Hi,

A new version of MLB demonstration spreadsheet is in the process of being uploaded to the FAQs.

Version 2.10 will be available from the FAQ area in the normal manner.

This version provides three enhancements over the previous release
1) Closed Loans can be collapsed into 1 loan to speed up the responsiveness of the PC for users with lots of closed loans
2) There is a front page tab as suggested by GG
3) There is a mini cash flow statement that may be useful.

If you have any problems or suggestions related to previous versions, please test against verson 2.10 before posting to the forum.

Please post any questions or suggestions related to release 2.10 in this topic.

Many thanks
Geoffj

Edited by Geoffj, 17 July 2011 - 16:18.

[elljay]

Hi all, the xlsx file is now linked from the FAQ.

[magpie2020]

Geoffj, on 17 July 2011 - 16:17, said:

Hi,

A new version of MLB demonstration spreadsheet is in the process of being uploaded to the FAQs.


Many thanks
Geoffj

Thank you so much for all your hard work on this.  I find it a very useful tool and can't resist downloading MLB and updating the spreadsheet most days!

Best wishes,

magpie2020

[Lloyd]

Many thanks as ever Geoff - as magpie says, I update every morning and I really like the idea of the Front Page tab (thanks GG for putting it forward).

[Gorgeous George]

Excellent work Geoff.

I have added a calculation to estimate bad debt by market (discussed here).

Obviously, it is a guide only and its accuracy depends on loan book maturity. It shows me that, currently, my own bad debt is higher than Zopa's estimate in 2 markets, lower in 2 markets and 0% in the other 6.

GG

[alexp2000]

Gorgeous George, on 21 July 2011 - 10:31, said:

Obviously, it is a guide only and its accuracy depends on loan book maturity. It shows me that, currently, my own bad debt is higher than Zopa's estimate in 2 markets, lower in 2 markets and 0% in the other 6.

Errr... lower in 8 markets then?

[Geoffj]

Hi,

A question was asked in another thread about adding bad debt calculations to the demonstration spreadsheet.  The suggestion is to allow the borrower to know if they are less or more lucky than average.  I am not sure that this would provide any value beoyound curiosity.

This is something I have avoided as it can get very complicated with several ways to measure bad debt.

But to throw the question out more widely

Is there interest?  If bad debt was implemented, what would it be?  What would you do different/better with the information?

Regards
Geoffj

[giles]

I will be really interested in the replies to this as we find it very difficult to express meaningfully the bad debt lenders suffer. What is the numerator and what is the denominator? Returns are best understood on an annual basis but bad debt is absolute, not annual etc etc!

[Gorgeous George]

I'm interested. More to know if my bad debt experience is average or not. Am I lucky or unlucky? Is my cautious appraoch to risk working out?

Using the method that I introduced in the other thread, I can get some idea of how my bad debt is today. At the moment, my bad debt (written off AND lates) is 56.4% of the bad debt that I estimate that I should expect if I had experienced Zopa's estimates. I understand that the number is just a number and that my eventual experience could vary quite significantly from today's snapshot but it is, at the very least, a number.

I like numbers

GG

[Lloyd]

Gorgeous George, on 28 July 2011 - 18:17, said:

I like numbers

I live for numbers and data and trends and forecasts.

Ok, I don't live for it but if Zopa are going to place out estimates on bad debt, let's see how it sums up over time for each of us.

Caveats a-plenty in doing this, of course: the value of shares can go up and down, if you don't keep up payments on your mortgage etc. etc. but I highly doubt anyone with a hint of Zopalcoholism in him or her is going to take GeoffJ's work as anything but the choices they have made, displayed in a really good and useful way.

[magpie2020]

My opinion: I like the way GG portrays bad debt in the other thread.  Looking at it by market, and comparing my own loan book experience to the Zopa estimates/predictions seems to be the way to go. At the moment, I have only been lending a few months so fortunately I don't have any bad debt figures to play with, but I know it's going to happen so I'd like to be prepared and when it does, be able to monitor whether my lending strategy is achieving the expected results.

[Geoffj]

magpie2020, on 29 July 2011 - 10:44, said:

My opinion: I like the way GG portrays bad debt in the other thread.  Looking at it by market, and comparing my own loan book experience to the Zopa estimates/predictions seems to be the way to go. At the moment, I have only been lending a few months so fortunately I don't have any bad debt figures to play with, but I know it's going to happen so I'd like to be prepared and when it does, be able to monitor whether my lending strategy is achieving the expected results.

I want to throw this out there to see what the collective view is.

So now let's say Zopa report Market Xnn as having bad debt of Y%.  So the spreadsheet is changed to report actual bad debt performance against the base figure of Y% for market Xnn.

That is basically what has been described.

Q1) Now Zopa change the expected bad debt rate for Xnn from Y% to Z%.
What do we do in the spreadsheet?
I can't see how reporting bad debt by market can really be useful.  We need a time dimension.  But as soon as we add time then for most people the number of loans is too small to be statistically relevant.

Q2) My finance training tells me that bad debt analysis must consider value at risk (VaR).  Now for Zopa, I think that means correlating bad debt and loan size.    VaR seems to apply to Zopa in that we would expect (?) a different bad debt performance for a given market between those that offer ZopaRate-1% and therefore collect a high percentage of small loans, compared to those that offer ZopaRate+1% with the aim of getting only big loans.  So this drives toward having a loan size dimension for bad debt reporting.  So this reduces the number of loans per reporting point even more.

I hope you can see the mental block I have on this subject- If I am making this too complicated, you can just let me know.  

Regards
Geoffj

Edited by Geoffj, 29 July 2011 - 18:05.

[Gorgeous George]

Geoffj, on 29 July 2011 - 18:04, said:

Q1) Now Zopa change the expected bad debt rate for Xnn from Y% to Z%.
What do we do in the spreadsheet?
I can't see how reporting bad debt by market can really be useful.  We need a time dimension.  But as soon as we add time then for most people the number of loans is too small to be statistically relevant.

I understand where you are coming from Geoff. However, I feel the time dimension is understood by most of us. Yes, bad debt may rise or fall but we know that our bad debt is a snapshot in time.

Geoffj, on 29 July 2011 - 18:04, said:

Q2) My finance training tells me that bad debt analysis must consider value at risk (VaR).  Now for Zopa, I think that means correlating bad debt and loan size.    VaR seems to apply to Zopa in that we would expect (?) a different bad debt performance for a given market between those that offer ZopaRate-1% and therefore collect a high percentage of small loans, compared to those that offer ZopaRate+1% with the aim of getting only big loans.  So this drives toward having a loan size dimension for bad debt reporting.  So this reduces the number of loans per reporting point even more.

My experience is that (loan) size makes little difference if any any at all

Geoffj, on 29 July 2011 - 18:04, said:

Q2)I hope you can see the mental block I have on this subject- If I am making this too complicated, you can just let me know.  

Regards
Geoffj

I don't know. I will bow down to your knowledge on the subject but I like a number, however flawed it may be.

GG

[ucbtjsc]

Thanks, that's exactly what I was looking for!

Not quite seeing what I should be but I'm figuring out what I've done wrong atm.

Cheers again!

[MikeS1531]

Geoffj, on 29 July 2011 - 18:04, said:

Q1) Now Zopa change the expected bad debt rate for Xnn from Y% to Z%.
What do we do in the spreadsheet?

Not being an experienced Excel 'programmer' I don't know whether this is feasible or not...

Since the date of each loan application is part of the MLB data, and I'm pretty sure Zopa have published their historical bad debt estimates as well as their current ones, might it be possible to have a table of bad debt rate estimates vs. time within the spreadsheet (as fixed data) and to use the bad debt estimate in effect at the time the loan was made for the calculation?

If I've made a bunch of B60 loans in Feb.'12 when the BD estimate was 2.3% p.a., then the fact that Zopa change their B60 BD estimate in the summer shouldn't affect the BD I expect from my B60 loans.  So I think I should be using the 2.3% rate to compare with the actual BD I incur on my Feb.'12 B60 loans.

[fuzzyiceberg]

MikeS1531, on 21 March 2012 - 11:45, said:

If I've made a bunch of B60 loans in Feb.'12 when the BD estimate was 2.3% p.a., then the fact that Zopa change their B60 BD estimate in the summer shouldn't affect the BD I expect from my B60 loans.  So I think I should be using the 2.3% rate to compare with the actual BD I incur on my Feb.'12 B60 loans.

Depends whether any revision is simply an acknowledgement of conditions that were already occurrring in the past ie it is effectively a retrospective revision (see 2008 for more details ).  In that case you would want to use the 'new' rates and not whatever were the estimates at the time.   Tricky stuff, forecasting and estimates.

[MikeS1531]

fuzzyiceberg, on 22 March 2012 - 08:58, said:

Depends whether any revision is simply an acknowledgement of conditions that were already occurrring in the past ie it is effectively a retrospective revision (see 2008 for more details ).  In that case you would want to use the 'new' rates and not whatever were the estimates at the time.   Tricky stuff, forecasting and estimates.

I'm not convinced.  If I'm trying to judge the performance of an investment, I feel that I ought to be comparing its actual performance against what I expected at the time I made the investment.  

I set my offering rates today based on what Zopa are estimating today for bad debt.  Say Zopa decide next year that their estimates are wrong -- presumably based on experience -- and double them.  If I want to see how I'm doing, I want to compare my actual bad debt experience with what I used to set my rates at the time I made my loans.  If my results are typical of the whole Zopa experience, then I should find that my actual bad debt is double what was expected when I made my investment, and therefore I would conclude that my actual return is less than I had expected when I had made my investment.  

If, on the other hand, I do what fuzzyiceberg has suggested, then I'd conclude that my bad debt experience matched the estimates.  At which point I'd think everything was great and my Zopa investment was performing as expected.  But it wouldn't have been!  And I might even have been better off with my money in a bank savings account, though I wouldn't know that unless I calculated my actual rate of return.  I suppose it would be an example of "ignorance is bliss".

[Trium]

MikeS1531, on 22 March 2012 - 14:35, said:

I'm not convinced.  If I'm trying to judge the performance of an investment, I feel that I ought to be comparing its actual performance against what I expected at the time I made the investment.  

I agree. You have added a premium to your rates (be it Zopa's estimates, historic figures or simply a wild guess) which you hope will bring in an additional return that will cover any bad debt you experience. If you change your expectations, and therefore change your premiums, those will only apply to new loans. You're stuck with the level of 'contingency' built into existing loans.

My spreadsheet calculates how much of the interest I've received so far is 'set aside' as a bad debt reserve. The total is brought into the debit side of the ZopaReturn calculation as a reserve, so that I get a figure net of expected bad debt (this currently knocks about 1% off my return). I then do a tax calulation to get down to a final net return and, out of interest, an 'effective tax rate' (currently 29.4%).

If I change the premiums built into my rates I will need to do a different calculation for loans issued after I made the change. To apply that calculation to older loans would be to imply that I can arbitrarily vary the amount of bad debt contingency that I have built into them, which is patently not the case.

My MLB is still young enough to have no bad debt. When it occurs, the amount will be set against the reserve. If the reserve runs out, I've not provided enough contingency in my rates and I may need to increase it, but it's too late to raise additional reserve from existing loans.

[sl75]

Trium, on 22 March 2012 - 16:54, said:

I agree. You have added a premium to your rates (be it Zopa's estimates, historic figures or simply a wild guess) which you hope will bring in an additional return that will cover any bad debt you experience. If you change your expectations, and therefore change your premiums, those will only apply to new loans. You're stuck with the level of 'contingency' built into existing loans.

Depends on what kind of analysis you're trying to do...

... you can compare your historic loan book performance with what you expected at time of investment.
... you can compare your historic loan book performance with what, in hindsight, you expect the performance should be now.
... you can compare what you expect the performance should be now with what you expected at time of investment.

I wouldn't see any of those options for analysis as any less "valid" than the others - they'd just be used for different purposes.

[chilterncom1]

MikeS1531, on 22 March 2012 - 14:35, said:

I'm not convinced.  If I'm trying to judge the performance of an investment, I feel that I ought to be comparing its actual performance against what I expected at the time I made the investment.  

I set my offering rates today based on what Zopa are estimating today for bad debt.  Say Zopa decide next year that their estimates are wrong -- presumably based on experience -- and double them.  If I want to see how I'm doing, I want to compare my actual bad debt experience with what I used to set my rates at the time I made my loans.  If my results are typical of the whole Zopa experience, then I should find that my actual bad debt is double what was expected when I made my investment, and therefore I would conclude that my actual return is less than I had expected when I had made my investment.  

If, on the other hand, I do what fuzzyiceberg has suggested, then I'd conclude that my bad debt experience matched the estimates.  At which point I'd think everything was great and my Zopa investment was performing as expected.  But it wouldn't have been!  And I might even have been better off with my money in a bank savings account, though I wouldn't know that unless I calculated my actual rate of return.  I suppose it would be an example of "ignorance is bliss".

As fuzzyiceberg says, there can be several reasons for a change in estimates.  The two most obvious examples (which are not mutually exclusive) are a revision to reflect experience that differs from past expectations and a revision to reflect a change in underwriting policy.

Obviously current Zopa estimates are more useful than past Zopa estimates as a factor to consider in setting lending rates.  Which estimate one should use for "valuing" a loan book or for measuring past performance depends on the reasons for any changes to the estimates.

The difficulty with what you are suggesting for measuring performance (or, for that matter, for valuing a loan book) is that there is no easy way to make allowances for the future bad debt inherent in a given loan portfolio;  one can look at actual bad debts, one can look at lates and make an educated guess as to what might happen to them, but determining how much bad debt is likely to emerge from the ones that have not yet shown a problem is a challenge. Ultimately, you can only determine the "actual" bad debt experience (or something close to it) for the population of loans that are past the date on they were due to be fully repaid.