SteveT
Member of DD Central
Posts: 6,875
Likes: 7,924
|
Post by SteveT on Sept 25, 2016 15:47:34 GMT
Don't include any entries for interest, only your cash deposits to and withdrawals from the platform. Then add one final entry for current total account value. Also make sure your values and dates are the right way round in the XIRR formula.
|
|
|
Post by wiseclerk on Sept 25, 2016 16:24:06 GMT
Mine are Auxmoney 6% (not repeatable) Bondora 20% (not repeatable, I discounted for value of lates and defaults; even in worst case if they'd all have 0% recovery I'd come out with 15%) Crosslend 1 - 2% Estateguru 11% Funding Circle CE, too early to tell, depending on outcome of several late loans but I expect 0 - 3%. Investly 13% Mintos 13% (not repeatable) Moneything 12% (but much lower if I would factor in currency fluctations at current rate - that might change for the better or worse) MyC4 negative (that one was interesting, but ended badly) Rebs - hard to tell, depending on my proceeds from my defaults, but I would say maybe 0% (before currency losses) Saving Stream 12% (but around 0% if I would factor in currency fluctations at current rate - that might change for the better or worse) Smava 6% (not repeatable) Twino 14% (not repeatable) some others are too early to tell
all pre-tax; tax effects differs widely by platform specifics, as I cannot deduct losses Those marked not repeatable is because market conditions changed considerably since I started investing and it would be different if one were to start today. More details on my blog.
|
|
arbster
Member of DD Central
Posts: 810
Likes: 426
|
Post by arbster on Sept 25, 2016 16:26:14 GMT
I've added the effects of bonuses into the original table. I've treated A****o's as a loss for LC, because I'm generally pessimistic on unsecured loans, although if it is realised as a total loss then it'll probably be the final nail in the coffin of that platform for me.
As for calculating the XIRR, I only include my deposits to the platform as +ve numbers (and treat the bonus payments in this way too, to avoid skewing the interest rate), withdrawals as -ve numbers, and the current balance as a -ve number, using today as the date for the final balance.
|
|
bigfoot12
Member of DD Central
Posts: 1,817
Likes: 816
|
Post by bigfoot12 on Sept 25, 2016 16:26:33 GMT
ablender - No need to provide the guess it is optional. Here is a simple example. I have shown payments out as negative, return is 19.94% 17/10/15
| -500 | 17/10/16 | 600 | | 0.1994023733
|
B3 is "=XIRR(B1:B2,A1:A2)"
|
|
arbster
Member of DD Central
Posts: 810
Likes: 426
|
Post by arbster on Sept 25, 2016 16:30:24 GMT
ablender - No need to provide the guess it is optional. Here is a simple example. I have shown payments out as negative, return is 19.94% 17/10/15
| -500 | 17/10/16 | 600 | | 0.1994023733
|
B3 is "=XIRR(B1:B2,A1:A2)" Helpful example, although in case people are wondering, with P2P you'd usually expect the early number to be positive (initial deposit) and the last number to be negative (representing the current balance).
|
|
ablender
Member of DD Central
Posts: 2,204
Likes: 555
|
Post by ablender on Sept 25, 2016 16:33:08 GMT
Don't include any entries for interest, only your cash deposits to and withdrawals from the platform. Then add one final entry for current total account value. Also make sure your values and dates are the right way round in the XIRR formula. That was the problem - I was including the interest. Without them, it worked and for SS I got 12.83%. There was some cashback at some point. re guess. I am leaving it out. Thanks for the suggestions MT: 10.64% FS: 12.5%
|
|
SteveT
Member of DD Central
Posts: 6,875
Likes: 7,924
|
Post by SteveT on Sept 25, 2016 16:38:59 GMT
Not if you expect it to generate a positive IRR for a positive return. From an investment perspective, cash deposits are outflows from the lender to the platform (so negative), withdrawals are returns from the platform to the lender (so positive)
|
|
beh
Member of DD Central
Posts: 175
Likes: 77
|
Post by beh on Sept 25, 2016 16:43:59 GMT
Zopa - 4.95% (since 2011)
Everything else is too early to say.
|
|
arbster
Member of DD Central
Posts: 810
Likes: 426
|
Post by arbster on Sept 25, 2016 16:52:09 GMT
Not if you expect it to generate a positive IRR for a positive return. From an investment perspective, cash deposits are outflows from the lender to the platform (so negative), withdrawals are returns from the platform to the lender (so positive) Maybe it's different for Google Sheets, but with mine, +ve deposits and -ve withdrawals give me a +ve return IRR.
|
|
|
Post by chielamangus on Sept 25, 2016 17:20:46 GMT
A couple of points. The time period for these IRRs should be specified if comparisons are to be made. The return in 2014 was a lot higher than today on most platforms.
And if one has unusual cash flows with abnormally high negatives or positives in the middle of the period, then XIRR can give wrong results. This point was made some time ago in some other comparisons (and no doubt someone here could locate the thread).
|
|
|
Post by wickedxuk on Sept 25, 2016 18:29:26 GMT
Can anyone get this working straight from the SS CSV download? Mine just gives me a crazy small number. But then again I've only beven involved in this since the beginning July! 😆
Update: OK got it I think. I've deleted all rows except deposit and withdrawal and then added today's date and current loan balance.
I get 9.64% for SS. I'm guess my holdings have fluctuated a lot as I started. Doing more due diligence on loans I had bought at the beginning and subsequently sold.
Will do this for all the platforms when I get chance.
I'm assuming the end goal is to use all the results from each platform and then average for an overall actual return?
|
|
ablender
Member of DD Central
Posts: 2,204
Likes: 555
|
Post by ablender on Sept 25, 2016 20:09:14 GMT
Can anyone get this working straight from the SS CSV download? Mine just gives me a crazy small number. But then again I've only beven involved in this since the beginning July! 😆 Update: OK got it I think. I've deleted all rows except deposit and withdrawal and then added today's date and current loan balance. I get 9.64% for SS. I'm guess my holdings have fluctuated a lot as I started. Doing more due diligence on loans I had bought at the beginning and subsequently sold. Will do this for all the platforms when I get chance. I'm assuming the end goal is to use all the results from each platform and then average for an overall actual return? You are on a learning curve like me, but if I understood it well, that is how it should be done. Take care of negative for deposits and positive for withdrawal and balance.
|
|
|
Post by wickedxuk on Sept 25, 2016 20:43:50 GMT
Can anyone get this working straight from the SS CSV download? Mine just gives me a crazy small number. But then again I've only beven involved in this since the beginning July! 😆 Update: OK got it I think. I've deleted all rows except deposit and withdrawal and then added today's date and current loan balance. I get 9.64% for SS. I'm guess my holdings have fluctuated a lot as I started. Doing more due diligence on loans I had bought at the beginning and subsequently sold. Will do this for all the platforms when I get chance. I'm assuming the end goal is to use all the results from each platform and then average for an overall actual return? You are on a learning curve like me, but if I understood it well, that is how it should be done. Take care of negative for deposits and positive for withdrawal and balance. it was the opposite for me to get a positive result: positive for deposits, negative for withdrawals. 🖒
|
|
|
Post by mrclondon on Sept 25, 2016 21:20:30 GMT
Generally from launch of the platform to date, my XIRR's are
Assetz Capital 11.1% (no losses allowed for; telecoms, optical, and holiday park outstanding) Saving Stream 12.8% Funding Secure 9.6% (predominately selling on SM before maturity at a discount covering buyers tax liability) ThinCats 9.8% (no losses allowed for, but my first one looks inevitable - FCF) Funding Knight 6.4% (no 2 shield loans, treating "in recovery" as default to provide direct comparison with FC) Funding Circle 7.1% (loans written 2010 to 2013 only) Funding Empire 10.1% MoneyThing 12.3% Ratesetter 5.8% Wellesley & Co 2.4% (interest on maturity, expect c. 6% at end of terms) Abundance Gen. 3.2% (very specific yield curve, expect c. 7% long run) Zopa 8% (loans written prior to Q3 2010)
but worth adding that these are for periods of benign economic conditions.
|
|
bigfoot12
Member of DD Central
Posts: 1,817
Likes: 816
|
Post by bigfoot12 on Sept 26, 2016 7:01:41 GMT
Can anyone get this working straight from the SS CSV download? Mine just gives me a crazy small number. But then again I've only beven involved in this since the beginning July! 😆 Update: OK got it I think. I've deleted all rows except deposit and withdrawal and then added today's date and current loan balance. I get 9.64% for SS. I'm guess my holdings have fluctuated a lot as I started. Doing more due diligence on loans I had bought at the beginning and subsequently sold. Will do this for all the platforms when I get chance. I'm assuming the end goal is to use all the results from each platform and then average for an overall actual return? You are on a learning curve like me, but if I understood it well, that is how it should be done. Take care of negative for deposits and positive for withdrawal and balance. I do it from my bank account not a sheet from the platform. I list all the payments to the platform (from my bank account) and those back again, and then I take the current value from the platform and put today's date to it. I then adjust this number to deal with any troubled loans.
|
|