Analysing the loan book with MS Excel

Hmmm!, arrears is not always a good indicator especially as there are many that may be in arrears at any one time but then may become paid only to be in arrears again next month. Some DD's don't always work - look at the comments field. defaults only really occur when they have not paid for 4/5 months. Default date field is good indicator. The ones in collections are the next ones to watch as they are nearest to a default. You could say these are maturing towards a default.

You should be most interested in actual defaults as they have the potential to be more damaging if they are in Plus rather than classic and access where they are currently (Until December for new lends) protected by the safeguard. There may be defaults in the classic and access but at the moment they will be paid of by SG.

You may be interested to know that defaults are not necessarily damaging until they actually fail. Bankruptcy or worse for the borrower, settled which I think may be a PC term for a death. As I have spoken about elsewhere I have many defaults, most from PreSafeguard days, where borrowers are still paying some even come out of the woods and start paying interest some will finish in 30 years time ;-). So even if you have a default all is not necessarily lost. Hopefully if you have defaults you may also have noticed in the statements that bad loans are being recovered.

It may be too late for you now but the key word is diversification of loans. Zopa will try to lend at 1% but you can be canny and lend at rates you can control it takes a bit more work and can take longer to lend. The key is lend at rates of <2000 but greater than >1000 the higher the better as instead of 1% you will get 0.5%. Defaults in £10 loans are much easier to come to terms with than £50 or worse £100 loans. A few of them could skew the lending quite a bit. The other thing to watch out for with defaults, similar to shares, is not to lose your nerve and bail out when it looks like the default rate is higher than you feel comfortable with.

On the excel front you are right to start playing with the loan tables in this way, watch out for the current table compared to the alltime - remember that the current one only contains loans that are active any "closed","settled" ones will only be in the alltime csv as they are not active.

If you need any help for further ideas then feel free to ask on here there are many people that have their own ways of analysing the data.

The key thing to be aware of is you will only get the headline rate if you re-invest at similar rates, since only investing in loans and not re-investing means the rate on any given loan is only slightly more than just above half the headline rate for that loan. Its not like a bank as you are lending on a loan that is also repaying capital.

Sorry if you are already clued up on a lot of this but hopefully these insights have been useful.

I'm not sure to be honest, it may not show in arrears until after its noted in the comments have you tried looking at that. I checked a few of my loans to see if there was much correlation between the acquired day of the month and the repayment day. but that doesn't not seem to have much of a pattern as far as I can tell.

help.zopa.com/customer/en/portal/articles/2406403-can-i-change-my-repayment-day-

Seems to suggest that the default is 30 days after approval but this can be changed. Its a bit slim on detail but it does say

"If you change your repayment day, your next monthly repayment will increase or decrease slightly.

We do this to adjust for the change in interest payable caused by the increased or decreased number of days from your original repayment day to your new repayment day.

Don’t worry, it’s a one-off! Your repayments will return to normal the following month."

Looking at the FAQ entry it seems it may be possible to set the 1st payment day to beyond 30 days after the aquirred date. 

My Guess:  
   Loan approved 1st of month
   Payment day set to 30th
   user decides to change payment day on 28th
   new payday set to 24th say

Assuming that is possible then it will seem delayed by more than 30 days of course it does say that the new payment initially would be adjusted up to account for the additional delay.

Someone else may have a more accurate idea on this.

edit: just found this faq entry may enlighten further and confirms some of the above.

help.zopa.com/customer/en/portal/articles/1103797-when-do-i-start-getting-repayments-

Hi, and welcome to the forum.

You are correct - it can be the 2nd month before you receive the first repayment. If you go to the loan book tab and scroll down to the bottom of the page, is says under the graph "We don’t ask borrowers to make repayments for at least the first month. So your first expected repayments will be larger than the following ongoing ones."

You will also get some borrowers making an early repayment immediately after taking out the loan - I can only presume that they have borrowed more than wanted to get a better rate for the larger loan, then immediately repay the extra that they didn't want in the first place!

I think it is when you have bought a safeguarded loan from the old markets which another lender has sold.  Over 60% of my ISA loans are covered by safeguard.



The other factor to be aware of is that a fair number of initial payments fail due to problems with direct debits.  Typically Zopa will contact them and the borrower will pay that month on the spot, and then try to set up another direct debit for the following month.   One of my own bank accounts had real issues with direct debits for the first year or so - it was about 50/50 whether the direct debit would work first time or not.  Therefore I'd say that even if the first payment fails, it isn't time to panic.  The red flag is when you get 2 or more failures and no manual payment.