|
|
Post by ablrateandy on Sept 14, 2015 21:19:39 GMT
Hello all, Pursuant to a couple of requests, here is a spreadsheet that should be usable by all. The way that our secondary market calculations are done, the yield calculation is done in the background so you can only see the AER once you input the price and your bid or offer is live. This spreadsheet should let anyone do the calculation in advance though  . It also shows where there is a difference between our calculation, IRR and XIRR (in case anyone wonders!). B7, B8, B10, B11, B12, B13 are the inputs for the loan details. Underneath that in cells B15, B16, B17 and B18 you can see the equivalent annual, semi, quarterly and monthly rates based upon your inputs and cells G8, G9 and G10 show the IRR, XIRR and Manually calculated rates. To look at the AER on a secondary market trade, input the details as above and then fill in D7, D8 and D9. D9 should be the AER that you are looking to achieve. The price that you need to enter into the system is then shown in cell D11. You can solve the opposite way by filling in as above, entering the price into F16 and pressing the 'Calculate' button. It will show the AER for that price in D9. As you can see, accrued interest etc are all in there too. Any questions, please ask |
|
ilmoro
Member of DD Central
'Wondering which of the bu***rs to blame, and watching for pigs on the wing.' - Pink Floyd
Posts: 10,836 
Likes: 11,063
|
Post by ilmoro on Sept 14, 2015 21:59:12 GMT
Hello all, Pursuant to a couple of requests, here is a spreadsheet that should be usable by all. The way that our secondary market calculations are done, the yield calculation is done in the background so you can only see the AER once you input the price and your bid or offer is live. This spreadsheet should let anyone do the calculation in advance though . It also shows where there is a difference between our calculation, IRR and XIRR (in case anyone wonders!). B7, B8, B10, B11, B12, B13 are the inputs for the loan details. Underneath that in cells B15, B16, B17 and B18 you can see the equivalent annual, semi, quarterly and monthly rates based upon your inputs and cells G8, G9 and G10 show the IRR, XIRR and Manually calculated rates. To look at the AER on a secondary market trade, input the details as above and then fill in D7, D8 and D9. D9 should be the AER that you are looking to achieve. The price that you need to enter into the system is then shown in cell D11. You can solve the opposite way by filling in as above, entering the price into F16 and pressing the 'Calculate' button. It will show the AER for that price in D9. As you can see, accrued interest etc are all in there too. Any questions, please ask Very nice Im sure, unfortunately as I dont have the full version of Office it wont open. In the meantime, perhaps a friendly mod might consider pinning this thread Ton ⓉⓞⓃ
|
|
|
|
Post by ablrateandy on Sept 14, 2015 22:01:44 GMT
Thanks for the link to Ton ⓉⓞⓃ . Hmmm. How about this one? |
|
|
|
Post by ablrateandy on Sept 14, 2015 22:03:12 GMT
|
|
ilmoro
Member of DD Central
'Wondering which of the bu***rs to blame, and watching for pigs on the wing.' - Pink Floyd
Posts: 10,836
Likes: 11,063
|
Post by ilmoro on Sept 14, 2015 22:06:12 GMT
Lovely, thanks
|
|
rogerbu
Member of DD Central
Posts: 398
Likes: 213
|
Post by rogerbu on Sept 28, 2015 17:49:28 GMT
Re lack of Office Can I recommend the Kingsoft Office package (Now called WPS Office Suite). I find it handles all current Office file types well and can be made to look like Old & New versions of Office. download.cnet.com/WPS-Office-10/3000-18483_4-75563178.htmlIt has the advantage over Office that it also works from Google Play & the App Store I use it in preference to MS Office most of the time and the basic version is free |
|
james
Posts: 2,205
Likes: 955
|
Post by james on Sept 28, 2015 19:31:55 GMT
|
|
ablender
Member of DD Central
Posts: 2,204
Likes: 555
|
Post by ablender on Mar 6, 2016 3:41:33 GMT
I have a problem with yield of the ATR 42-500 MSN 532
I have placed an offer: Sell £100 at 102.5%; and it is showing 2.243% (today is 6/3/16, Maturity 29/5/16, interest rate 13%)
The way I understand it is the following:
For £100 @ 13% I will get £13 in a year
Dividing £13 by the price I pay:
£13 / £102.5 = 12.68%
I tried various other ways, taking into account that I am holding this for only 84days, etc. they all come to the same number.
The problem is that on ABLrate website it is showing 2.243%
Where am I going wrong?
I also tried the calculations on the other offer £731.58 at 101%. I get 12.87% and ABLrate shows 8.993%
|
|
SteveT
Member of DD Central
Posts: 6,873
Likes: 7,918
|
Post by SteveT on Mar 6, 2016 8:45:29 GMT
I have a problem with yield of the ATR 42-500 MSN 532 I have placed an offer: Sell £100 at 102.5%; and it is showing 2.243% (today is 6/3/16, Maturity 29/5/16, interest rate 13%) The way I understand it is the following: For £100 @ 13% I will get £13 in a year Dividing £13 by the price I pay: £13 / £102.5 = 12.68% I tried various other ways, taking into account that I am holding this for only 84days, etc. they all come to the same number. The problem is that on ABLrate website it is showing 2.243% Where am I going wrong? I also tried the calculations on the other offer £731.58 at 101%. I get 12.87% and ABLrate shows 8.993% Looks to me like you're not taking account of the % premiums correctly. If you buy £100 of capital at a 2.5% premium (102.5%) then you've paid £102.50 upfront, so your first £2.50 of interest only offsets the initial capital loss. With a loan that's got 3 months to run, even at 13%, that doesn't leave much in the way of actual profit. Crudely you can estimate it as: % Profit = £ Profit / £ PaidIn your first example, £ Profit = interest earned - premium paid = (£100 x 13% x 84/365) - £2.50 = £2.99 - £2.50 = £0.49 whilst, £ Paid = £102.50 so, % Profit = £0.49 / £102.50 = 0.48% The AER calculation is more accurate, taking account of the actual timing of individual payments (£1 tomorrow is worth more than £1 in 30 days, since you can reinvest it for the intervening 29 days), so there can be quite a difference over such a short period with so few payments. But the estimated return calculation above will generally give you a good steer.
|
|
ablender
Member of DD Central
Posts: 2,204
Likes: 555
|
Post by ablender on Mar 6, 2016 9:08:42 GMT
I have a problem with yield of the ATR 42-500 MSN 532 I have placed an offer: Sell £100 at 102.5%; and it is showing 2.243% (today is 6/3/16, Maturity 29/5/16, interest rate 13%) The way I understand it is the following: For £100 @ 13% I will get £13 in a year Dividing £13 by the price I pay: £13 / £102.5 = 12.68% I tried various other ways, taking into account that I am holding this for only 84days, etc. they all come to the same number. The problem is that on ABLrate website it is showing 2.243% Where am I going wrong? I also tried the calculations on the other offer £731.58 at 101%. I get 12.87% and ABLrate shows 8.993% Looks to me like you're not taking account of the % premiums correctly. If you buy £100 of capital at a 2.5% premium (102.5%) then you've paid £102.50 upfront, so your first £2.50 of interest only offsets the initial capital loss. With a loan that's got 3 months to run, even at 13%, that doesn't leave much in the way of actual profit. Crudely you can estimate it as: % Profit = £ Profit / £ PaidIn your first example, £ Profit = interest earned - premium paid = (£100 x 13% x 84/365) - £2.50 = £2.99 - £2.50 = £0.49 whilst, £ Paid = £102.50 so, % Profit = £0.49 / £102.50 = 0.48% The AER calculation is more accurate, taking account of the actual timing of individual payments (£1 tomorrow is worth more than £1 in 30 days, since you can reinvest it for the intervening 29 days), so there can be quite a difference over such a short period with so few payments. But the estimated return calculation above will generally give you a good steer. Isn't your calculation taking into account your extra payment of £2.50 twice: Once when you reduced it from Profit and then when you divided the profit by amount paid? (Underlined in quote)
|
|
SteveT
Member of DD Central
Posts: 6,873
Likes: 7,918
|
Post by SteveT on Mar 6, 2016 9:15:38 GMT
Looks to me like you're not taking account of the % premiums correctly. If you buy £100 of capital at a 2.5% premium (102.5%) then you've paid £102.50 upfront, so your first £2.50 of interest only offsets the initial capital loss. With a loan that's got 3 months to run, even at 13%, that doesn't leave much in the way of actual profit. Crudely you can estimate it as: % Profit = £ Profit / £ PaidIn your first example, £ Profit = interest earned - premium paid = (£100 x 13% x 84/365) - £2.50 = £2.99 - £2.50 = £0.49 whilst, £ Paid = £102.50 so, % Profit = £0.49 / £102.50 = 0.48% The AER calculation is more accurate, taking account of the actual timing of individual payments (£1 tomorrow is worth more than £1 in 30 days, since you can reinvest it for the intervening 29 days), so there can be quite a difference over such a short period with so few payments. But the estimated return calculation above will generally give you a good steer. Isn't your calculation taking into account your extra payment of £2.50 twice: Once when you reduced it from Profit and then when you divided the profit by amount paid? (Underlined in quote) Yes, correctly, which is why it give the correct answer!! The premium you pay not only reduces your £ Profit but also increases the amount of your funds that you had to invest (the £ Paid) to make that profit.
|
|
ablender
Member of DD Central
Posts: 2,204
Likes: 555
|
Post by ablender on Mar 6, 2016 9:22:38 GMT
OK. I need to think more about this to understand it. Thanks SteveT. |
|
|
|
Post by ablrateandy on Mar 6, 2016 17:08:55 GMT
Hello! Sorry - been a busy weekend. I'll look and come back on this later but am pretty happy that our system is calculating correctly.
|
|
|
|
Post by ablrateandy on Mar 6, 2016 20:43:14 GMT
I'm back Steve's summary is correct. Our AER calculation is similar (but more refined) than XIRR or IRR on Excel. Your return should always be based on how much you actually paid, so takes into account accrued interest and any premium. You return should be based on that. I have a new spreadsheet that I will put up st some point and am always happy to chat through it. I'm a big fan of AERs as it gives you the only true view of your return that can be compared with non-p2p products. |
|
|
|
Post by wickedxuk on Jul 31, 2016 21:19:42 GMT
Hi ablrateandy I also cannot open the spreadsheet on Android, it's because of the ACTIVE components. |
|
. It also shows where there is a difference between our calculation, IRR and XIRR (in case anyone wonders!). 
