ashtondav
Member of DD Central
Posts: 1,814 
Likes: 1,092
|
Post by ashtondav on Oct 16, 2017 8:56:52 GMT
This confirms my suspicion that you need a VERY large investment before you can achieve the projected returns.
In fact, if my September statement is correct (and it has been changing over the last few weeks) I have hit a bad month. £300 interest and £211 bad debt on £65,000 (34,000 plus, £31,000 classic/core.)
Not selling up but withdrawing repayments until I can understand what is going on.
|
|
aju
Member of DD Central
Posts: 3,500
Likes: 924
|
Post by aju on Oct 16, 2017 9:54:39 GMT
I wonder if your split between plus and core/classic is over exposed. I've had mine and Mrs aju's split as 10% in plus to get a feel for it, i'm a lot more risk averse than many people on p2p. I've upped the anti slightly on our ISA sides to see what happens. I see your split is almost 50:50. I'm guessing all the defaults aer in the plus side as core is relatively new and classic has and still is protected.
I've also notice that a lot of core, in ISA in my case, is picking up approx 25%. I'm not happy with my losses in Plus but its much lower than everyone who seems to be flagging problems. I have all my Plus in £10 loans so that helps, although to be fair I probably should have more in plus (1000) at the moment to make it even safer 0.5 instead of 1%. The strategy of trying to limit to £10 loans may not work but time will tell on the £15k ISA book I have almost finished disbursal on.
|
|
|
|
Post by geoffp on Oct 16, 2017 10:29:13 GMT
aju sir
Thank you for your help. You say that anyone who uses it might want to know that it trashes any data you may be relying on if you run it in a table screen. One thing I would say is AFTER it has run you should only save the processed data as a new file, otherwise you lose the original csv.
DataFilter_and_freezetop_row Macro is useful and could be incorporated.
Anyway, I take it that the code ran OK for you. That's good.
|
|
|
|
Post by propman on Oct 16, 2017 10:59:45 GMT
This confirms my suspicion that you need a VERY large investment before you can achieve the projected returns. In fact, if my September statement is correct (and it has been changing over the last few weeks) I have hit a bad month. £300 interest and £211 bad debt on £65,000 (34,000 plus, £31,000 classic/core.) Not selling up but withdrawing repayments until I can understand what is going on. Does anyone here know how to combine what are essentially poisson distributions? I would expect the default rates to be driven by the performance of the higher rate loans that only make up a small proportion of the whole. As a result a large number of these would be required to have a high chance of the defaults being close to the population average. ie to have loans with 12% annual default rates have a 95% chance of not being double the average default in 3 months requires over 70 of these loans, if these are only 15% of the full loanbook, then the loanbook would need to be about 500 loans. That said, not sure to what extent this would be mitigated by C1 and lower risk loans.
- PM
|
|
angrysaveruk
Member of DD Central
Say No To T.D.S
Posts: 1,326
Likes: 784
Member is Online
|
Post by angrysaveruk on Oct 16, 2017 12:25:09 GMT
This confirms my suspicion that you need a VERY large investment before you can achieve the projected returns. In fact, if my September statement is correct (and it has been changing over the last few weeks) I have hit a bad month. £300 interest and £211 bad debt on £65,000 (34,000 plus, £31,000 classic/core.) Not selling up but withdrawing repayments until I can understand what is going on. Does anyone here know how to combine what are essentially poisson distributions? I would expect the default rates to be driven by the performance of the higher rate loans that only make up a small proportion of the whole. As a result a large number of these would be required to have a high chance of the defaults being close to the population average. ie to have loans with 12% annual default rates have a 95% chance of not being double the average default in 3 months requires over 70 of these loans, if these are only 15% of the full loanbook, then the loanbook would need to be about 500 loans. That said, not sure to what extent this would be mitigated by C1 and lower risk loans.
- PM
Why do you want to use the Poisson Distribution? The number of defaults in a portfolio should follow a Binomial Distribution. Individual defaults are Bernoulli Distributed (like the flip of a coin), the total number of defaults is the sum of these Bernoulli distributed random variables so is Binomial. If the loan book is fairly large (like 500 loans) you can use the Normal Distribution as an approximation due to the Central Limit Theorem.
|
|
angrysaveruk
Member of DD Central
Say No To T.D.S
Posts: 1,326
Likes: 784
Member is Online
|
Post by angrysaveruk on Oct 16, 2017 12:53:43 GMT
Ok here is a calculation. Let us assume we have 500 loans and each has a probability of default of 12%. On average the number of defaults will be:
12% * 500 = 60
To calculate how the total number can vary from this we have to calculate the variance of each default. The variance of the default on each loan is:
0.12 * ( 1 - 0.12) = 0.1056
The variance of the number of defaults over 500 loans is:
500 * 0.1056 = 52.8
And the standard deviation is the square root of this:
sqrt(52.8) = 7.266
Ok so assuming that the total number of defaults is roughly normally distributed what is the maximum number of defaults we can expect. Well it is very unlikely that a normally distributed random variable will be great than 3 standard deviations above its average (only 0.1% of the time it will be more than this). So we could say that on that the number of defaults will only be greater than 60 + 3 * 7.266 = 81.79 or rounded to 82 by chance once in a thousand. It would be virtually impossible for this to be anywhere close to 120 defaults.
|
|
aju
Member of DD Central
Posts: 3,500
Likes: 924
|
Post by aju on Oct 16, 2017 15:16:03 GMT
aju sir
Thank you for your help. You say that anyone who uses it might want to know that it trashes any data you may be relying on if you run it in a table screen. One thing I would say is AFTER it has run you should only save the processed data as a new file, otherwise you lose the original csv.
DataFilter_and_freezetop_row Macro is useful and could be incorporated.
Anyway, I take it that the code ran OK for you. That's good. Thank you kind sir yourself for showing me something I had never used or seen before, how embarrassing is it that I didn't know about SUBTOTAL ;-). I have been giving it a once (or twice) over, I have some similar correlation spreadsheets so it was very useful to check my sheets against your results. At least mine line up with yours at a very simple top level. My spreadsheet of this nature is a version of the old loanbook where you got much more information than the current one. I was hoping at some point to make it fully similar to the old one but sadly at the moment I have the loanbook correlator and the statements consolidator separated so have to copy the loan id from system to system - I can have them on one screen in 2 windows though. I'm not a great VBA man sadly and have never really used/understood relative (RC) coding in excel, so I'm wondering if the code would work for me other than as a standalone item, understanding it so to speak. Having said that my mate runs his chaufeur business on a system I set up for him a few years ago and that uses some similar techniques of moving data around in vba - my statements Loan creator is quite a complex beast too. Its very fast for ISA statements but my Investment side for me alone has some 150000 statement records and it makes my I7 Cpu work for hard its money. It took a little while to understand that Excel defaults to use all threads so it locked up my PC for large lender loans for about 3 mins until I realised I could control this and reduce the threads used in excel's advanced options. At first I did not understand the "+" and "-" thingys at first, right clicking them for help - sad git that I am. I think I've got my head round that one now and it looks a very interesting tool indeed. Just goes to show where I come from I guess. Old school bluffer ;-). I am hoping to use it as a one shot glance but as its all VBA based and having had some issues with table column positioning changes in my own VBA code - especially when Zopa was changing and adding columns a while back - I started limiting my VBA processing where I could do it in excel without using VBA. The problem I found with excel VBA - in my 2007 version at least - any changes in the columns etc seemed to always get mangled and require VBA changes as they did not get passed through to the VBA code. Perhaps thats where Relative References are more useful I'm not sure. I'm pretty certain that Zopa moved some columns around in the early days of the new loanbook CSV's especially. In most of my sheets I try not to play with the Zopa columns too much as I like to load the CSV into my sheets where possible using data|FromText option. In your sheets you are not quite so bothered about borrower names but another of my reasons for loading the CSV's using this method is to ensure the names are text rather than General. In my data set there are quite a few numbers that Excel mangles in the "General" default format. I change the loader to make text data as "text" and anywhere I know that data is a date I force it too. I still get caught out but all I have to do is see the error and insert the relevant column and Excel sorts the rest of my sheets out when I reload the data. Of course in bigger data files than the loanbook csv's, statements in particular, this can mean that too many calculations or records increases time of calculations etc. Thanks for sharing...
|
|
|
|
Post by propman on Oct 17, 2017 7:35:05 GMT
Does anyone here know how to combine what are essentially poisson distributions? I would expect the default rates to be driven by the performance of the higher rate loans that only make up a small proportion of the whole. As a result a large number of these would be required to have a high chance of the defaults being close to the population average. ie to have loans with 12% annual default rates have a 95% chance of not being double the average default in 3 months requires over 70 of these loans, if these are only 15% of the full loanbook, then the loanbook would need to be about 500 loans. That said, not sure to what extent this would be mitigated by C1 and lower risk loans.
- PM
Why do you want to use the Poisson Distribution? The number of defaults in a portfolio should follow a Binomial Distribution. Individual defaults are Bernoulli Distributed (like the flip of a coin), the total number of defaults is the sum of these Bernoulli distributed random variables so is Binomial. If the loan book is fairly large (like 500 loans) you can use the Normal Distribution as an approximation due to the Central Limit Theorem. The reason is that defaults are a discretye variable and there is a lower bound of 0 so the normal distribution only provides a reasonable approximation where the number of defaults expected is quite large. I agree that this is the case in your example, but that would require a very large loanbook or a long period in which to run the comparison. The tendancy always is to perform the test on past information. This is of course not a fair test as it is only the apparently large number of defaults that has lead to the test. Waiting another year for a response is not helpful. My point was looking specifically at the D & E loans which are now only 15% of the new loans and so getting a large enough sample in a reasonably short period to see whether the new loans are meeting the expectations will be a struggle.
All that said, the assumption for any of these to work is that defaults occur evenly as we don't typically have even portfoios that will average out variations through the terms.
- PM
|
|
angrysaveruk
Member of DD Central
Say No To T.D.S
Posts: 1,326
Likes: 784
Member is Online
|
Post by angrysaveruk on Oct 17, 2017 9:23:21 GMT
Why do you want to use the Poisson Distribution? The number of defaults in a portfolio should follow a Binomial Distribution. Individual defaults are Bernoulli Distributed (like the flip of a coin), the total number of defaults is the sum of these Bernoulli distributed random variables so is Binomial. If the loan book is fairly large (like 500 loans) you can use the Normal Distribution as an approximation due to the Central Limit Theorem. The reason is that defaults are a discretye variable and there is a lower bound of 0 so the normal distribution only provides a reasonable approximation where the number of defaults expected is quite large. I agree that this is the case in your example, but that would require a very large loanbook or a long period in which to run the comparison. The tendancy always is to perform the test on past information. This is of course not a fair test as it is only the apparently large number of defaults that has lead to the test. Waiting another year for a response is not helpful. My point was looking specifically at the D & E loans which are now only 15% of the new loans and so getting a large enough sample in a reasonably short period to see whether the new loans are meeting the expectations will be a struggle.
All that said, the assumption for any of these to work is that defaults occur evenly as we don't typically have even portfoios that will average out variations through the terms.
- PM
The use of the normal distribution is an approximation but for the number of loans people have on Zopa it is probably a reasonable approximation and would certainly give you an idea if a large number of defaults such as those being experienced by people making a negative loss is possible due to random fluctuation. The binomial distribution however is the exact distribution describing the number of independent defaults over a portfolio. Poisson Distribution is related to the Poisson Process which cannot be directly linked to the number of defaults and default probabilities.
|
|
aju
Member of DD Central
Posts: 3,500
Likes: 924
|
Post by aju on Oct 17, 2017 17:22:24 GMT
The reason is that defaults are a discretye variable and there is a lower bound of 0 so the normal distribution only provides a reasonable approximation where the number of defaults expected is quite large. I agree that this is the case in your example, but that would require a very large loanbook or a long period in which to run the comparison. The tendancy always is to perform the test on past information. This is of course not a fair test as it is only the apparently large number of defaults that has lead to the test. Waiting another year for a response is not helpful. My point was looking specifically at the D & E loans which are now only 15% of the new loans and so getting a large enough sample in a reasonably short period to see whether the new loans are meeting the expectations will be a struggle.
All that said, the assumption for any of these to work is that defaults occur evenly as we don't typically have even portfoios that will average out variations through the terms.
- PM
The use of the normal distribution is an approximation but for the number of loans people have on Zopa it is probably a reasonable approximation and would certainly give you an idea if a large number of defaults such as those being experienced by people making a negative loss is possible due to random fluctuation. The binomial distribution however is the exact distribution describing the number of independent defaults over a portfolio. Poisson Distribution is related to the Poisson Process which cannot be directly linked to the number of defaults and default probabilities. I think there quite a bit too much poison distributed across the Zopa works at the moment ... ;-) sorry I couldn't resist.
|
|
angrysaveruk
Member of DD Central
Say No To T.D.S
Posts: 1,326
Likes: 784
Member is Online
|
Post by angrysaveruk on Oct 17, 2017 18:45:23 GMT
I think there quite a bit too much poison distributed across the Zopa works at the moment ... ;-) sorry I couldn't resist. There is certainly too much of something going on there 
|
|
