Please note that the screenshot in the post above was taken on 7th January 2018, while the XIRR calculation was on 31 December 2017 when total interest received was just $1282.86
Printable View
Please note that the screenshot in the post above was taken on 7th January 2018, while the XIRR calculation was on 31 December 2017 when total interest received was just $1282.86
Thanks. Realised that I did not fully answer your question.
RAR was
14.57 at 6 mths
13.94 at 1 yr
13.80 at 18 mths
14.33 at 2 yr
14.40 at 30 mths
atm 14.39 at 30+mths
was hoping to get to 15% before version 1.5 came about. Now happy to stay between 14 and 14.5%
Your RAR from your previous posts is higher. Congrats to you too.
To provide some background I work in Financial Services (on board as exec), 8 of us are on here and we have a relatively unimportant wager going on for bragging rights whoever can maximise return after 1 year. Our respective accounts are between 4 to 7 months old (mine August). Theres been a degree of conjecture over whether RAR is the most appropriate measure. Hence this discussion.
For your friends portfolio you are doing the right thing if your calc XIRR by stripping the PP. I assume 8.26% is after tax? (Also note - accrued income will also weigh heavy that early).
What I can say, pretty authoratively. Is if your account is a year or under, you should not use XIRR unless you adjust out the PP. Its probably easier to take it out all together. To be more correct use a "back of the fagg packet" earn method, and add back in the earn element pp/50 x m /2 (where pp = the total pp on your active loans, m = months since your first trade). To be pedanticly right you can use that spreadsheet I uploaded(for us our calcs need to be right so we know who is ahead!).
At 2 years, XIRR still won't be quite right. Depending on the amount of Payment Protect and how you are growing your portfolio, your XIRR will be out by between 1 to 3%. Beyond 2 years (assuming you grew your portfolio early and are maintaining the capital), the PP effect should diminish to below 1% (not 100% sure on that as haven't run the stats).
Iin conclusion, simplistically you probably want to use RAR if your under a year - or make the adjustments to XIRR. After one year then you can rely more on XIRR and knock off a few % depending on the age.
RAR is a very poor indicator for at least the first 12 months - Harmoney don't provide it until around month 3/4. Have a look at a typical RAR graph...:scared:
Everyone wants to max return, and when you "trust" harmoney you end up thinking you can model and optimise based on their stats. Thats what you seem to be doing.
Go back through this thread about a year ago under the old version of harmoney by pulling their stats on loan grads, the default rates, the interest all that sort of stuff it was pretty easy to model which loans where the best, a lot of us (I can go back and pull the posts if I really want to) worked out the optimal grade was E (E2, E3, maybe iirc). This is all based off harmoneys stats.
But reality is, and I did raise this issue as a big concern, is all harmoneys stats are probably based on a data set pulled from the last couple of years, years which the economy has been strong, hence default rates maybe artificially low. This comes back to bite you later
So what I ended up doing, is putting together a portfolio about a year ago, with lots of E's - cause according to harmonies stats, when I modeled these, they maximised ROI.
But reality is 16 months in, all that flies out the window when your defaults start sky rocketing. I peaked at 166 E loans which was 13.5% of my portfolio by count, 15.5% by value. Thats a decent sample size. Thusfar my E's are running at 9% default rate. Thats after 16 months. Maybe I just got unlucky, 166 loans is not a massive sample size in the scheme of things
So what i am saying is after a while, my actual performance doesn't seem reconcile with much of harmoney what reports to me. Hence I am skeptical of RAR and more trusting in a simple Xiir of cashflows (with outstanding principle treated as a outgoing cashflow).
Leesal, since you provided your background, I will do the same. I am a semi-retired accountant (by training). Although I got my ACA very early and except for an 18 month period after graduation in the early 80s working for a Chartered Accountant firm, I did not work as an accountant per se. Instead I was with a very big company overseas (revenue in the billions) for many years in various roles. I had been a very early user of spreedsheets - Vision and Symphony in the early 80s, then Lotus 123, even modelling an entire company (a very small subsidiary) on a very large spreedsheet with heaps of "if then else" formulas (within "if then else" formulas).
I can understand where you are going with your spreadsheet. My only observation is that you are taking individual loans and individual transactions in Harmoney for your XIRR calculation instead of an overall simple calculation with the cash and o/s loan balance at the end (adjusted for tax and the total est PP). I am taking the investment in Harmoney as a single investment - so cash flows into Harmoney versus cash out (including end balances). Your method could be interpreted as taking each loan as a separate investment.
Anyway, I dare not try your formulas on my (personal) transaction details page from Harmoney - my latest statement has 470,000 lines on it - as it may crash my old PC. So I will stick to the simpler way of calculating.
As for my friend's investment with the two screenshot above, the 8.26% XIRR at 31/12/17 (3 months) is after adjusting for the PP. It is also before tax, as I added back the total tax deducted as a lump sum on 31/12. If I do not add back tax paid, it is 5.4%.
At 2 months, the adjusted XIRR was 4.2% before tax (ie. adding back tax paid) and 2.7% after tax.
I know the deficiencies of both XIRR and RAR. We had discussed it quite a bit earlier in this thread.
As mentioned above: For my personal investment in Harmoney of about 30 months at 31/12, the RAR is 14.4%, XIRR before tax and including PP is also 14.4% (a coincidence as it is not the same thing). XIRR before tax and taking out PP (estimate) is 13.4%. The difference of 1% is the same as predicted by Harmoney in its PP writeup. But a good portion of my loans are before PP came about. So hopefully the difference for mine will grow closer to 1.5%.
My apologies to those that are bored by XIRR discussion. The main difference is that XIRR is a truer indicator as it takes into account your idle un-invested cash sitting in Harmoney.
Maybe leesal does have a point, that I should be valuing my outstanding principle in a different way for my Xirr calc. Cause I'm ~7% (pre tax) on Xirr but harmoney RAR is 14.5%
Attachment 9417
Attachment 9418
I am sitting abut 1% lower than you after about 3 years/6000 loans . I am interested in gaining some understanding of the difference ie due to higher risk adopted or better selection of loans, or a combination. Do you read the stories or just rely on filters? I essentially rely on autolend and filter out all business loans, all loans above $35k and all A's, E's and F's and 25% income ratio. Until 1.5 I also invested in A's and had a roughly equal weighting in each major grade. Any insights would be most welcome.
I have a weekly RAR list from September to January and I have shown the charge-offs along the way. I have ended the RAR with a 4 pip difference at 14.68%
The odd charge-off does not have much impact on my ongoing RAR's, I now have a very stable concentrated loan portfolio in the mid-range grades.
Attachment 9419