shimself
Member of DD Central
Posts: 2,563 
Likes: 1,171
|
Post by shimself on Dec 19, 2014 15:01:31 GMT
Guru wanted please
If I buy a £1000 loan earning £10 (ie 1%) /month, with 71 weeks left to run can someone lay out the calculations for price paid vs rate earned. (As in if I pay £1020 what rate am I earning)
Please
|
|
pikestaff
Member of DD Central
Posts: 2,189
Likes: 1,546
|
Post by pikestaff on Dec 19, 2014 15:16:01 GMT
To do this properly more info is needed but give me a few minutes and I will attach a noddy version with comments.
Edit: Now attached, E&OE.
Further edit: superseded by my 21 Dec post below.
|
|
pikestaff
Member of DD Central
Posts: 2,189
Likes: 1,546
|
Post by pikestaff on Dec 19, 2014 16:29:33 GMT
I'm sure there will be a better version along soon, but I divide the £20 by 71 (28.17p) and take that away from your monthly £10. Not perfect, but a close enough calc for me to make a decision... The question said 71 weeks, not months. Not sure if this was intentional but I assumed that it was and rounded up to 17 months. To compare our results, dividing £20 by 17 would give £1.18 approx, leaving adjusted monthly interest of £8.82. This is quite a lot more than my spreadsheet would imply (first month's interest 0.7727% x £1,020 = £7.88). The main reason for the difference is that I have assumed an amortising loan. Your figure would be very close for an interest-only loan.
|
|
shimself
Member of DD Central
Posts: 2,563
Likes: 1,171
|
Post by shimself on Dec 19, 2014 16:46:23 GMT
yes 71 weeks
yes interest only, sorry I'd written that in my head but not in reality
|
|
mikes1531
Member of DD Central
Posts: 6,453
Likes: 2,320
|
Post by mikes1531 on Dec 19, 2014 21:58:32 GMT
Payback before the 2nd payment would represent the capital loss... There are also tax implications as on the example you'd pay tax on the full 12% p/a. So the return should range from about 10.4% (=£8.82x12/1020) if the loan isn't paid off until maturity down to a negative return if it's paid off less than two months after purchase. Doing a straight-line interpolation between those two points would mean a return of about 6.9% if the loan lasts for a full year. So you have to look into your crystal ball and see how long it will be before the loan is repaid. Based on those numbers, I don't think I'd want to touch a loan part offered at such a premium price, especially considering the unfavourable tax implications. Note: I can pretty well guarantee that a chart of return vs. repayment date wouldn't show a straight line. I'd guess that the curvature of the line would mean that the 1-year repayment case would produce a better return than 6.9% -- possibly more like 8-9%. (Would someone care to try to produce that chart?) I don't know how big a reduction the tax situation would make on the returns, but I think I'd still stay away from loan parts sold at above-par prices.
|
|
pikestaff
Member of DD Central
Posts: 2,189
Likes: 1,546
|
Post by pikestaff on Dec 20, 2014 0:31:34 GMT
I attach a somewhat less noddy version of the spreadsheet. There are now two tabs (one for amortising loans and one for interest-only) and it deals with tax as well.
Any delays in settling tax are ignored. As has been pointed out on another thread, HMRC may (depending on your circumstances) collect tax on account so there may or may not be a timing benefit.
For an interest only loan, you can investigate the effect of prepayment by changing its duration. With an interest rate of 1% per month and a premium of £20 per £1,000, a basic rate taxpayer will make an after-tax loss if the loan is repaid after 2 months turning to a small profit after 3 months, but a higher rate taxpayer will still make an after-tax loss if the loan is repaid after 3 months.
Because of the way I've built the spreadsheet, you can't investigate the effect of prepayment for amortising loans. However, for prepayment in the first few months, the IRR of an amortising loan won't be very different from that of an interest-only loan.
The spreadsheet is protected to prevent inadvertent changes (anywhere except the data entry cells) but it does not have a password. You can unprotect it if you wish.
As before, E&OE.
Edit: Attachment deleted. Superseded by my 21 Dec post below.
|
|
pikestaff
Member of DD Central
Posts: 2,189
Likes: 1,546
|
Post by pikestaff on Dec 20, 2014 0:41:26 GMT
...I can pretty well guarantee that a chart of return vs. repayment date wouldn't show a straight line. ...Would someone care to try to produce that chart? A bit challenging at this time of night! I'm not sure if there is an analytical solution that enables me to tabulate the numbers without preparing a full cash flow for each of the possible durations. If nobody else comes up with one I will ask the internet over the weekend...
|
|
|
|
Post by GSV3MIaC on Dec 20, 2014 21:00:58 GMT
Pretty sure there is no analytical solution - there isn't even one for calculating the basic IRR answer afaik; Excel iterates its way to the answer I believe (I certainly do when I do it in a program). It's a pretty fast iteration though
|
|
pikestaff
Member of DD Central
Posts: 2,189
Likes: 1,546
|
Post by pikestaff on Dec 21, 2014 11:39:57 GMT
New workbook with added charts for both amortising and interest only loans showing the impact of prepayment on the IRR. It's protected against inadvertent changes but there is no password. E&OE. SM IRR calcs with prepayment graphs.xlsx (964.19 KB) The impact is pretty front ended. Partly for this reason, but also for ease of reading, the charts look out a maximum of 30 months. However, the underlying data for the whole life of the loan (up to maximum of 120 months) is there if you want it. Here is an illustrative chart. The assumptions can be varied in the workbook:  |
|
mikes1531
Member of DD Central
Posts: 6,453
Likes: 2,320
|
Post by mikes1531 on Dec 21, 2014 20:13:18 GMT
New workbook with added charts for both amortising and interest only loans showing the impact of prepayment on the IRR. Brilliant tool! And very useful. Thanks for developing it. It confirms what I had thought before -- I shouldn't be buying loan parts on the secondary market at a premium, and particularly not if there's a significant risk that the loan might be repaid early.
|
|
pikestaff
Member of DD Central
Posts: 2,189
Likes: 1,546
|
Post by pikestaff on Dec 22, 2014 8:27:23 GMT
It confirms what I had thought before -- I shouldn't be buying loan parts on the secondary market at a premium, and particularly not if there's a significant risk that the loan might be repaid early. I think it depends on the platform. When I was building my portfolio I was happy to pay premiums of 2-3% on TC. In my head, 0.5-1% of that was for avoiding dead time, up to 1% (depending on timing) was for accrued interest which the buyer gets "free" on TC, and only about 1-1.5% was a true premium. I've not regretted that decision. Out of 14 purchases at a premium (the last one being about a year ago), one prepaid after 5 months, one defaulted after 13 months (full recovery is expected, but I won't get my premium back) and the others are doing fine. These days my portfolio's in more of a steady state and I tend only to buy at par, which on TC is really a small discount. I also paid significant premiums when I started out on FC. This was a less good decision because I subsequently decided that FC was not for me and I mostly sold those parts at a loss, although this was offset by premiums on the sale of primary market purchases so overall I got out OK.
|
|
|
|
Post by ablrateandy on Dec 24, 2014 11:07:31 GMT
pikestaff's solution is correct  . And a handy tool that it is very kind of him to share! One thing that I would say though is that IRR and XIRR are not always "correct", as in they don't always come up with a correct answer as per a full discounting of cashflows. IRR is weak on accrued interest and XIRR sometimes gets odd results. However they are generally close enough to get you close.
|
|
|
|
Post by mrclondon on Dec 24, 2014 11:42:07 GMT
pikestaff's solution is correct . And a handy tool that it is very kind of him to share! One thing that I would say though is that IRR and XIRR are not always "correct", as in they don't always come up with a correct answer as per a full discounting of cashflows. IRR is weak on accrued interest and XIRR sometimes gets odd results. However they are generally close enough to get you close. Now the real question is "Has this convinced you that ablrate should scrap the ability to charge premiums on their on secondary market ?". Now ablrate have solved their email problems I have been having a closer look at the platform. However, given I never buy p2p loans at a premium to face value on any platform, it is going to be slow to build up a diversified loan book on the platform.
|
|
. And a handy tool that it is very kind of him to share! One thing that I would say though is that IRR and XIRR are not always "correct", as in they don't always come up with a correct answer as per a full discounting of cashflows. IRR is weak on accrued interest and XIRR sometimes gets odd results. However they are generally close enough to get you close.
