Hi
I have a series of varying future cashflows and I have the initial loan amount, how do I calculate the effective interest rate in excel?
Example
Loan amount €100,000
Term: 60 months
Repayments quarterly uneven:
<tbody>
</tbody>
Thanks
Derek
I have a series of varying future cashflows and I have the initial loan amount, how do I calculate the effective interest rate in excel?
Example
Loan amount €100,000
Term: 60 months
Repayments quarterly uneven:
Months | Date | Balance B/f | Interest | Repayments | Balance C/f |
01/10/2012 | 100,000.00 | 100,000.00 | |||
3 | 31/12/2012 | 100,000.00 | 962.50 | (5,962.50) | 95,000.00 |
6 | 31/03/2013 | 95,000.00 | 914.38 | (5,914.38) | 90,000.00 |
9 | 30/06/2013 | 90,000.00 | 866.25 | (5,866.25) | 85,000.00 |
12 | 30/09/2013 | 85,000.00 | 818.13 | (5,818.13) | 80,000.00 |
15 | 31/12/2013 | 80,000.00 | 770.00 | (5,770.00) | 75,000.00 |
18 | 31/03/2014 | 75,000.00 | 721.88 | (5,721.88) | 70,000.00 |
21 | 30/06/2014 | 70,000.00 | 673.75 | (5,673.75) | 65,000.00 |
24 | 30/09/2014 | 65,000.00 | 625.63 | (5,625.63) | 60,000.00 |
27 | 31/12/2014 | 60,000.00 | 577.50 | (5,577.50) | 55,000.00 |
30 | 31/03/2015 | 55,000.00 | 529.38 | (5,529.38) | 50,000.00 |
33 | 30/06/2015 | 50,000.00 | 481.25 | (5,481.25) | 45,000.00 |
36 | 30/09/2015 | 45,000.00 | 433.13 | (5,433.13) | 40,000.00 |
39 | 31/12/2015 | 40,000.00 | 385.00 | (5,385.00) | 35,000.00 |
42 | 31/03/2016 | 35,000.00 | 336.88 | (5,336.88) | 30,000.00 |
45 | 30/06/2016 | 30,000.00 | 288.75 | (5,288.75) | 25,000.00 |
48 | 30/09/2016 | 25,000.00 | 240.63 | (5,240.63) | 20,000.00 |
51 | 31/12/2016 | 20,000.00 | 192.50 | (5,192.50) | 15,000.00 |
54 | 31/03/2017 | 15,000.00 | 144.38 | (5,144.38) | 10,000.00 |
57 | 30/06/2017 | 10,000.00 | 96.25 | (5,096.25) | 5,000.00 |
60 | 30/09/2017 | 5,000.00 | 48.13 | (5,048.13) | - |
Total | 10,106.25 | (110,106.25) |
<tbody>
</tbody>
Thanks
Derek