|
|
Post by ingenue on Dec 28, 2018 20:24:50 GMT
Have ZOPA explained their rationale for not providing customers with their XIRR rate? It seems crazy to expect every individual investor to establish their own method of calculating this fundamental number when presumably ZOPA's team of financial IT professionals could do the coding correctly once and for all.
|
|
aju
Member of DD Central
Posts: 3,484 
Likes: 917
|
Post by aju on Dec 29, 2018 0:28:12 GMT
Have ZOPA explained their rationale for not providing customers with their XIRR rate? It seems crazy to expect every individual investor to establish their own method of calculating this fundamental number when presumably ZOPA's team of financial IT professionals could do the coding correctly once and for all. I'm not sure if any of them would be using XIRR or other financial functions on their basic information screens but as you say they could use them if they so wished. I personally find that there is not a great deal of similarity between the p2p systems that I have experienced (Zopa and RS so far). I have been with Zopa since 2005 and their systems have changed over the years moving from the quite useful and complex to more recently making things over simplified, almost misleading at times in my personal view. RS is new to me but it has its own idiosyncrasies too. I'm a long time user of excel, in my case 2007 as that was free in my company when I worked for a living and I was also able to avail myself of a program where I got it very cheap for home use. I've been very active over the years in creating my own analysis tools etc and as a result discovered XIRR some while ago. I personally use XIRR for both alltime and current tables in Zopa but I also use a much simpler monthly returns and extrapolate those out by multiplying month return by 12 to get yearly rolling return. It's not foolproof but it is a good guide. I use the statements tables in "year to date" mode as well as the Tax to date and of course the tax returns but just recently - moving ISA in from external and lending other new money in means that this gets quite skewed for all of these excel non XIRR approaches. If you don't have excel then many of the online spreadsheets google etc are capable of performing XIRR and many other *IRR functions too. They are very simple in their approach so it's not beyond most people and above someone even supplied a link to an excel spreadsheet have a look at it. It's not complicated so you should be able to upload to google spreadsheet and give it a go if you do not have excel. I agree it would be better to have the p2p sites provide this though. That said Zopa is making a pigs ear of many things of late and they would probably get this wrong too. (Oops bit controversial that one) For the most part XIRR will perform without complications but it does have some inherent issues that most people will come across very rarely but they are there none the less. I personally use the monthlies as a comparison check of the XIRR results just to be safe I'm not getting unhelpful results. HTHs
|
|
littleoldlady
Member of DD Central
Running down all platforms due to age
Posts: 3,017
Likes: 1,834
|
Post by littleoldlady on Dec 29, 2018 8:32:41 GMT
Have ZOPA explained their rationale for not providing customers with their XIRR rate? It seems crazy to expect every individual investor to establish their own method of calculating this fundamental number when presumably ZOPA's team of financial IT professionals could do the coding correctly once and for all. Absolutely! And not only Zopa, all platforms should provide this, and be made to do so by law if necessary.
|
|
Greenwood2
Member of DD Central
Posts: 4,238
Likes: 2,682
|
Post by Greenwood2 on Dec 29, 2018 11:08:12 GMT
Have ZOPA explained their rationale for not providing customers with their XIRR rate? It seems crazy to expect every individual investor to establish their own method of calculating this fundamental number when presumably ZOPA's team of financial IT professionals could do the coding correctly once and for all. ... If you don't have excel then many of the online spreadsheets google etc are capable of performing XIRR and many other *IRR functions too. ... Open Office is free and can do pretty much the same as Word, Excel etc.
|
|
|
|
Post by steamer on Dec 30, 2018 15:00:29 GMT
I want a trend so, as I have quite a lot of entries to put in the calculation and being lazy, I use the monthly numbers but as a CRUDE and SPEEDY method for a period of months 3,6 or 12
I use the total sum at the start of the period and total at the end. Find the average balance. Express the return for the period as a percentage of that. It is even more crude because I have 25% in classic and the balance in Plus In 2018 I was not adding any cash and for a while have been extracting all holding account cash weekly.
For the last 12 months
1 Jan 2018 Balance 11,501 30 Dec 2018 Balance 5,167 Average balance 8334 Return 162.97 =1.9% [The equivalent figure for 2017 was 3.96%]
In fact Interest was 689 which would give 8.27% however losses were 625 Investment recoveries were 22.58 + that large one off of 76.58. Without that one off the return would be 1.1%
Crude it may be but it gives the trend done on a rolling 3 months and confirmed the monthly numbers. I do not know or care how Zopa calculate their numbers but the trend suggested to me I get out of Zopa in 2017 as the return was heading down. Now at the end of 2018 it returns little more than my BS savings account and that is only because of that one off Investment recovery.
Maybe ZOPA have sorted their lending criteria in the last 12 months but I am not going to chance it.
|
|
|
|
Post by Ace on Dec 30, 2018 22:38:58 GMT
Many thanks for your reply aju . I tried the same examples in Google sheets on android, which is what I usually use, and only 2 out of the 5 examples came out correct. I have verified that the XIRR for my Zopa fund is correct, which was trivial to verify since I've only made a single deposit and no withdrawals. Not sure what to do about verifying the figures for the platforms where I have a more complicated transaction log. I need to find some time to investigate further. For now I'll just do some sanity checks to make sure the answers are in the right ballpark. (Was so tempted to make a 'sanity clause. joke there, given the date 🎅). At least the errors in the link you gave were so far out that they would be easily spotted, but can't guarantee that more subtle errors are not possible. Happy Christmas 🎄 I finally got some more time to look in to how to check that an XIRR result is correct. The answer seems to be to use the XNPV function, with the XIRR result as the discount rate. If the XNPV answer is very close to zero then the XIRR is correct. I've tried this for all of my accounts and the XIRR was correct for all of them. The largest XNPV was 3.34E-09, so pretty close to zero. I also tried it for the erroneous XIRRs in the links provided by aju above. These all resulted in very large XNPV values, clearly indicating that the XIRRs were incorrect. This gives me confidence that the XIRR problems are likely to be fairly infrequent in "real world" conditions, and that I can easily detect when they are incorrect. I've only tried it on Google Sheets, but it's bound to work for other spreadsheets too. Phew! 😊
|
|
aju
Member of DD Central
Posts: 3,484
Likes: 917
|
Post by aju on Dec 31, 2018 0:02:47 GMT
Many thanks for your reply aju . I tried the same examples in Google sheets on android, which is what I usually use, and only 2 out of the 5 examples came out correct. I have verified that the XIRR for my Zopa fund is correct, which was trivial to verify since I've only made a single deposit and no withdrawals. Not sure what to do about verifying the figures for the platforms where I have a more complicated transaction log. I need to find some time to investigate further. For now I'll just do some sanity checks to make sure the answers are in the right ballpark. (Was so tempted to make a 'sanity clause. joke there, given the date 🎅). At least the errors in the link you gave were so far out that they would be easily spotted, but can't guarantee that more subtle errors are not possible. Happy Christmas 🎄 I finally got some more time to look in to how to check that an XIRR result is correct. The answer seems to be to use the XNPV function, with the XIRR result as the discount rate. If the XNPV answer is very close to zero then the XIRR is correct. I've tried this for all of my accounts and the XIRR was correct for all of them. The largest XNPV was 3.34E-09, so pretty close to zero. I also tried it for the erroneous XIRRs in the links provided by aju above. These all resulted in very large XNPV values, clearly indicating that the XIRRs were incorrect. This gives me confidence that the XIRR problems are likely to be fairly infrequent in "real world" conditions, and that I can easily detect when they are incorrect. I've only tried it on Google Sheets, but it's bound to work for other spreadsheets too. Phew! 😊 That's an interesting idea, I think I saw it on a website but didn't really follow it. I checked it out again and it works a treat. Thanks for that Ace. I love the way I keep learning something new and different every day, lets hope I can learn to stave off the dementia too otherwise i'll forget what I have learnt as soon as I have learnt it  .
|
|
|
|
Post by fuzzyiceberg on Jan 1, 2019 13:53:04 GMT
For anyone interested in the technicalities:
There is a unique solution to the IRR equation (what discount rate yields a zero NPV when applied to a series of cash flows) for every time the cumulative cash flow switches from negative to positive or positive to negative. So for example a cumulative cash flow that starts negative, goes positive, goes negative and then goes positive again will have three unique solutions. In practice only one solution will be realistic in terms of a rate of return (the one closest to zero) any others will clearly be nonsense - typically hugely positive or negative. The XIRR function allows the user to input a 'guess' that is used to eliminate any spurious (although mathematically correct) results. If you put in, say, 5% as a guess that will eliminate any of those odd results.
I have used XIRR for years to keep tabs on my Zopa returns (given that as others have noted, shamefully, Zopa themselves do not provide investors with this data). My old pre ISA account started in 2010 has yielded an equivalent annual return of 5.1%. My ISA is running at a bit less than 3.6%, a disappointing return. I continue to monitor and update it monthly.
|
|
aju
Member of DD Central
Posts: 3,484
Likes: 917
|
Post by aju on Jan 1, 2019 14:26:55 GMT
For anyone interested in the technicalities:
There is a unique solution to the IRR equation (what discount rate yields a zero NPV when applied to a series of cash flows) for every time the cumulative cash flow switches from negative to positive or positive to negative. So for example a cumulative cash flow that starts negative, goes positive, goes negative and then goes positive again will have three unique solutions. In practice only one solution will be realistic in terms of a rate of return (the one closest to zero) any others will clearly be nonsense - typically hugely positive or negative. The XIRR function allows the user to input a 'guess' that is used to eliminate any spurious (although mathematically correct) results. If you put in, say, 5% as a guess that will eliminate any of those odd results.
I have used XIRR for years to keep tabs on my Zopa returns (given that as others have noted, shamefully, Zopa themselves do not provide investors with this data). My old pre ISA account started in 2010 has yielded an equivalent annual return of 5.1%. My ISA is running at a bit less than 3.6%, a disappointing return. I continue to monitor and update it monthly.
It's interesting that your figures are very close to my results over the data from 2015 for Invest and more recently the whole of ISA. Mrs Aju's is slightly less but in my case I also have the 0.5 early bonus to lift things up slightly. My rate is about 0.5% less than yours so I'm guessing you have perhaps been a Zopa member since the start and get the 1.0% bonus or your split of products may be slightly different to us. We used to limit our Plus split to 10% but recently have increased it to 20% since we have brought some external ISA's on board. Adding us all together as one effective investment, as that's basically what it is to us then since 2015, when Zopa statements first appeared we have an overall XIRR rate of 4.31%. This for us is better than most banks savings and since the banks have reduced their current account rates is considerably better than we were getting since the banks reduced their current account terms. Our objective has only ever been to match inflation tax rates and get a little on top.
|
|
|
|
Post by fuzzyiceberg on Jan 4, 2019 10:01:25 GMT
For the record: I get no early member bonus and I have never been invested in plus either in my non ISA account or my ISA (nor, back in the day, was I in listings).
|
|
zlb
Member of DD Central
Posts: 1,412
Likes: 331
|
Post by zlb on Jan 4, 2019 15:45:05 GMT
Have ZOPA explained their rationale for not providing customers with their XIRR rate? It seems crazy to expect every individual investor to establish their own method of calculating this fundamental number when presumably ZOPA's team of financial IT professionals could do the coding correctly once and for all. Absolutely! And not only Zopa, all platforms should provide this, and be made to do so by law if necessary. interesting if this can be forced. I think it comes into the realm of online petition to get something done, unless the FCA new regs will cover this. Z are especially practiced in saying they're addressing something but don't. Eg if you complain to them, eg about their diversification model, they say they're doing something about it, but two years after, nothing has happened.
|
|
gg
Posts: 83
Likes: 61
|
Post by gg on Jan 5, 2019 13:06:07 GMT
|
|
aju
Member of DD Central
Posts: 3,484
Likes: 917
|
Post by aju on Jan 5, 2019 15:49:08 GMT
For the record: I get no early member bonus and I have never been invested in plus either in my non ISA account or my ISA (nor, back in the day, was I in listings). Ah back in the day and listings i remember those days fondly - I think!. I'm not sure since they changed all the ID's a few years back now but I think I still have a listings item that is in default if wasn't rounded up by the recent loan sales to the heavy mob. Edit1: Yep just checked my defaults and settleds against my listings joins and found that in fact I still had 3 listings that were active defaults until one of them was settled recently. Now I have 2 of them left both in default still and were started from 2009 but are still paying capital off it would seem.
Edit2: Oops just checked the comments field and it would seem one is having more difficulties and talking with DMC and the other has not made any payments since Feb 2018. They were both paying at times though so perhaps Zopa felt they might start again and decided not to settle on them.
|
|
benaj
Member of DD Central
Posts: 4,848
Likes: 1,590
|
Post by benaj on Jan 8, 2019 20:34:18 GMT
I suppose there isn't exact way to measure return. In my case, I only use net withdrawal as I sold all my active loans of Zopa Plus by Jan 2018. Net withdrawal is 0.49% of all time deposit since Mar '17 and I am happy with "real" profit.
XIRR is not ideal to measure performance when selling or running down portfolio. My XIRR was 2.31% in Dec 2017 and current XIRR is 0.9%, I knew the 2.31% was meaningless at the time as I expected more defaults in 2018. I suppose my XIRR will continue to decrease as I don't hold any active loans while my net withdrawal will continue to grow from bad debt repayments.
|
|
|
|
Post by argonaut on Jan 10, 2019 17:45:44 GMT
Calculate return funds lent... simple method
Look at Statements on your account. Click on that and previous months are all shown in a column. Record interest earned..bad debts deducted and net interest earned in 3 columns.
Repeat for each of the last 12 months. Total net interest earned for last 12 months and divide by amount lent ( assuming fixed amount for 12 months...otherwise calculate an average balance) times 100 = % interest earned
|
|
.