Hi guys!
I am looking to automate the calculation of interests for multiple payments based on the date of payment and the date for which I need the interest calculated in the situation where the interest rates are changed as per below table:
Start date End Date Interest rate
01.01.2007 31.12.2007 15,00%
01.01.2008 30.06.2011 14,00%
01.07.2011 31.07.2015 12,00%
01.08.2015 31.12.2015 8,14%
01.01.2016 30.06.2016 8,05%
01.07.2016 31.12.2016 7,88%
01.01.2017 30.06.2017 7,68%
01.07.2017 31.12.2017 7,41%
01.01.2018 30.06.2018 7,09%
01.07.2018 30.06.2019 6,82%
For example, if the payment of 100 was made on 1st Nov 2007 the interest on date of 14th June 2019 would have to be calculated asf:
Paymnt date no. days Interest amount
01.11.2007 31.12.2007 61 15,00% 2,51 formula 100*15%/365*61
01.01.2008 30.06.2011 1277 14,00% 48,98 100*14%/365*1277
01.07.2011 31.07.2015 1492 12,00% 49,05 ditto
01.08.2015 31.12.2015 153 8,14% 3,41
01.01.2016 30.06.2016 182 8,05% 4,01
01.07.2016 31.12.2016 184 7,88% 3,97
01.01.2017 30.06.2017 181 7,68% 3,81
01.07.2017 31.12.2017 184 7,41% 3,74
01.01.2018 30.06.2018 181 7,09% 3,52
01.07.2018 14.06.2019 349 6,82% 6,52
Total accumulated interest for the period is 129,52
Any help would be Greeley appreciated.
Thanks,
Sime
P.S. there was a post from 2014 with the attachment done by Bernie Deitrick but it does not work for the above needed calc.
Bookmarks