james
Posts: 2,205
Likes: 955
|
Post by james on Oct 16, 2015 12:14:37 GMT
Yes, EXCEL 2002 here, perhaps I should upgrade... May I take this opportunity to heartily recommend LibreOffice? At least as compatible with MS Office as other versions of MS Office, and infinitely cheaper. It's not remotely close to true that LibreOffice is as compatible with other versions of MS Office as MS Office. It has large areas of features that just don't exist and in things like graph formatting can produce completely different results from the same data and formatting choices. Both OpenOffice and the LibreOffice downstream fork of it are OK as spreadsheets but MS Office compatibility claims are overblown. Fine if you're just starting out and don't need full MS Office compatibility, though.
|
|
grahamg
Member of DD Central
Posts: 220 
Likes: 62
|
Post by grahamg on Oct 16, 2015 12:39:57 GMT
Hi All a question for the financial gurus out there! Is there any simple way to compare the potential return on an amortising loan with fixed interest borrowing. Specifically I'm heavily into SS - for lots of reasons not least its simplicity. Each month you get 1 per cent of your capital, but of course it's locked away for the loan term (and in practice for many of their loans for rather longer as we know). I'm trying to compare this against an opportunity to invest £2500 at 18 per cent with a business known to many ReBS investors. Again a two year term. I would earn £495.42 interest over the two years with the latter versus £600 with SS. But of course with the amortising loan I would have a large chunk of capital repayment each month to reinvest unlike SS when I only get the interest to reinvest. And the risk with an amortising loan diminishes with time (if the capital isn't reinvested that is). I imagine that somewhere, someone has a fancy s/s which allows a comparison between returns assuming that all monies earned/returned each month for the balance of the 24 month term, but so far I've not been able to find one! Thanks in advance! John If you invest £1000 at 10% for 12 months. With SS or other interest only loans at the end of the year you have £1100 if you do nothing. With Rebs or amortising loans if you do nothing you have only got £1054.99. (5.5%) If you want your 10% you have to keep reinvesting the returned principal without delay every month in another 10% loan.
|
|
adrianc
Member of DD Central
Posts: 10,041
Likes: 5,156
|
Post by adrianc on Oct 16, 2015 12:48:39 GMT
It's not remotely close to true that LibreOffice is as compatible with other versions of MS Office as MS Office. You've clearly missed out on some of the intra-version shenanigans that I've had to deal with, then. My favourite was a Word doc that SWMBO was sent. A job app form with lots of protected fields, it opened to over four hundred pages in a different version of MS Office to the one it was created in. In Libre, perfectly usable. But we're getting away from the thread's subject.
|
|
james
Posts: 2,205
Likes: 955
|
Post by james on Oct 16, 2015 14:11:16 GMT
Yes, MS Office to MS Office compatibility also has glitches at times, though I haven't encountered something like your unpleasant Word experience.
|
|
johnfleet
Member of DD Central
Posts: 170
Likes: 85
|
Post by johnfleet on Oct 17, 2015 20:02:27 GMT
Hi johnfleet , try this. Email me with any questions : Basically, enter the structure in B7, B8, B10, B11, B12, B13 There's a secondary function if you need more help. Hope this does what you need.... Thanks Ablate Andy. Apart from the fact that the dropdown function to select Amortising isn't working in my Excel 2016, I don't think that this very fancy s/s answers my question (although I may be missing something!). My specific question was around the effects of reinvesting either a) the interest earned each month i.e £20 on my £2000 investment example at 12 flat a la SS or b) whatever the monthly combined capital/interest repayment for an amortising loans would be at 18 per cent - in both cases for the balance of the two year period. Does that makes sense? John
|
|
|
|
Post by ablrateandy on Oct 17, 2015 20:11:08 GMT
Ah ok. You'd have to plug in each loan in that case. I'll knock something up this evening that will do the job for you.
|
|
|
|
Post by ablrateandy on Oct 17, 2015 22:03:51 GMT
johnfleet My spreadsheet is too messy to post  . However assuming £2,500 initial amount : 1. Amortising loan 18% over 2 years pays £124.81 per month ---> £2,995.45 over the duration (assuming that there is no re-investment) 2. Amortising loan 18% over 2 years pays £124.81 per month. If each payment is re-invested at 12% interest-only so that maturities all match the end of the original 2 year term ---> £6,551.5 at the end. 3. Interest only 12% over 2 years ---> £3,400 over the duration (assuming that there is no re-investment) 4. Interest only 12% over 2 years ---> £3,400 over the duration. If each payment is re-invested at 12% interest-only so that maturities all match the end of the original 2 year term ---> £4,468.47 at the end. This assumes that you can invest same day. I have not rounded up or down so you would need maximum efficiency on investment.
|
|
arbster
Member of DD Central
Posts: 810
Likes: 426
|
Post by arbster on Oct 18, 2015 5:20:48 GMT
What if you invested the principal at 18% in an amortising loan (if it's a one-off opportunity) and re-invested the monthly payments at 12%, interest-only, re-investing interest payments on the same basis...?  |
|
|
|
Post by ablrateandy on Oct 18, 2015 5:54:08 GMT
What if you invested the principal at 18% in an amortising loan (if it's a one-off opportunity) and re-invested the monthly payments at 12%, interest-only, re-investing interest payments on the same basis...? I allowed for that in answer 2 - sorry I wasn't clear Both reinvesting scenarios assume that interest payments on interest payments are reinvested at 12pc so all cash is churned.
|
|
arbster
Member of DD Central
Posts: 810
Likes: 426
|
Post by arbster on Oct 18, 2015 5:58:17 GMT
What if you invested the principal at 18% in an amortising loan (if it's a one-off opportunity) and re-invested the monthly payments at 12%, interest-only, re-investing interest payments on the same basis...? I allowed for that in answer 2 - sorry I wasn't clear Both reinvesting scenarios assume that interest payments on interest payments are reinvested at 12pc so all cash is churned. Of course it was clear, I just failed to read it properly, or had forgotten by the time I got to the end of the other calculations. Duh.
|
|
arbster
Member of DD Central
Posts: 810
Likes: 426
|
Post by arbster on Oct 18, 2015 6:00:39 GMT
Incidentally, are you sure about those calculations? I make interest only on £2500 over 2 years to be £3100 total return, not £3400. And the reinvestment ones seem awfully high.
|
|
|
|
Post by ablrateandy on Oct 18, 2015 6:12:09 GMT
Whoops sorry. Still had a test number in IO.
IO is £3.100 at 12pc
IO at 12pc continually reinvested is 3,812.31
Amortising is right I'm pretty sure.
Main loan total is 2995.
First payment is 124.81 and is reinvested at 12pc and total return on that is 153.52 (23 payments of 1.2481 plus return of principal)
Second is 22 payments of 1.2605 (based on 124.81 plus 1.248) plus return of principal totalling 153.79
Etc etc
|
|
james
Posts: 2,205
Likes: 955
|
Post by james on Oct 18, 2015 9:07:54 GMT
IO is £3.100 at 12pc IO at 12pc continually reinvested is 3,812.31 Amortising is right I'm pretty sure. Try a basic sniff test. 6551.50 / 3812.31 = 1.72 times. The interest rate difference between 18% and 12% is only 1.5 times so somehow the reinvested amortising case is generating a larger gain than if the money was being reinvested at 18% rather than 12%. From the magnitude of the numbers it looks as though the amortising case may have been calculated as interest only 18% plus reinvestment of the payments at 12%.
|
|
james
Posts: 2,205
Likes: 955
|
Post by james on Oct 18, 2015 9:25:55 GMT
Here's a non-spreadsheet way of calculating it. 1. go to a loan repayment calculator and enter £2,500 amount borrowed, 2 years term and 18% as the interest rate. It'll tell you that the monthly repayment is 124.81 same as ablrateandy. It also says that the total charge is £495.45, that is, the interest paid is that much. 124.81 * 24 = 2995.44 and 2500 + 495.45 = 2995.45 so a quick logic check passes. 2. Now go to a regular savings calculator and enter 124.81 as the monthly payment, two years duration and 12% interest rate. It'll tell you that the final value is £3,366.56. 3366.56 - ( 124.81 * 24 ) = 371.12 in interest. Any substantial difference from the £3,366.56 from the regular savings calculator has to be wrong because that is taking into account both the 18% original investment (via the repayment level) and the reinvested 12%. Small timing differences can produce small differences, though. So approximate results: £3,366.56 final value of that, £495.45 is interest on the money at 18% and £371.12 is interest on the money at 12% And roundings mean that the numbers don't add up perfectly to 2500 + 495.45 + 371.12 = 3366.57. Easy enough to use those two tools to calculate for other combinations of terms and interest rates. Later, there's one error in the numbers, I used 12% AER for the regular saver. Correcting that to the 12.68 for compounding monthly gives £3,389.27 as the final value. |
|
|
|
Post by ablrateandy on Oct 18, 2015 10:21:17 GMT
On the amortising re-investment scenario assume that loan starts on 1Jan16 and ends 1Jan18. On 1Feb16 you receive 124.81. This then gets reinvested to generate 1.24 each month for 23 months. This gives you a total of (1.24*23) + 124.81 = 153.33. On 1Mar16 you receive 124.81 from the amortising loan plus 1.24 in interest from the first IO loan. ie. 126.00. This gets re-invested for 22 months giving you a total of (1.26*22) + 126.00 = 153.72. On 1Apr16 you receive 124.81 from the amortising loan plus 1.24 from first loan + 1.26 from the second loan = 127.31. This gets re-invested for 21 months giving you a total of (1.27*21) + 127.31 = 153.98 On 1May16 you receive 124.81 from the amortising loan plus 1.24 from first loan + 1.26 from the second loan + 1.27 from the third loan = 128.58. This gets tre-invested for 20 months giving you a total of (1.28*20) + 128.58 = 154.18 ad infinitum... without rounding I am pretty happy with the calculation . |
|
. However assuming £2,500 initial amount :
