Calculate total Interest paid

[Ricky62]

Hi Guys
My 1st post ever so please allow for any protocol errors.

I use Excel 2010 but any solution must cater for Excel 2003
[TABLE="width: 656"]
<tbody>[TR]
[TD]
Here's simple extract from spreadsheet - person owes £79.72 & is charged interest at 2.843% periodically on each statement. The extract shows how I currently calculate total interest to be paid on ONE account before debt is cleared.
[TABLE="width: 656"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Payment number
[/TD]
[TD="align: center"]balance
[/TD]
[TD]Interest rate[/TD]
[TD]interest charged[/TD]
[TD]set payment rate[/TD]
[TD="align: right"]revised balance
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]£79.72
[/TD]
[TD]2.843%[/TD]
[TD]£2.27[/TD]
[TD]£11.84
[/TD]
[TD="align: right"]£70.15
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: center"]£70.15[/TD]
[TD]2.843%[/TD]
[TD]£1.99[/TD]
[TD]£11.84[/TD]
[TD="align: right"]£60.30[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: center"]£60.30[/TD]
[TD]2.843%[/TD]
[TD]£1.71[/TD]
[TD]£11.84[/TD]
[TD="align: right"]£50.18[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: center"]£50.18[/TD]
[TD]2.843%[/TD]
[TD]£1.43[/TD]
[TD]£11.84[/TD]
[TD="align: right"]£39.76[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: center"]£39.76[/TD]
[TD]2.843%[/TD]
[TD]£1.13[/TD]
[TD]£11.84[/TD]
[TD="align: right"]£29.05[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: center"]£29.05[/TD]
[TD]2.843%[/TD]
[TD]£0.83[/TD]
[TD]£11.84[/TD]
[TD="align: right"]£18.04[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: center"]£18.04[/TD]
[TD]2.843%[/TD]
[TD]£0.51[/TD]
[TD]£11.84[/TD]
[TD="align: right"]£6.71[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: center"]£6.71[/TD]
[TD]2.843%[/TD]
[TD]£0.19[/TD]
[TD] [/TD]
[TD="align: right"]£6.90[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Total Interest paid[/TD]
[TD]£10.06[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]So total paid is £79.72 plus total interest payable of £10.06 - ie 1 payment @ £11.84, 6 @ £11.84, plus final 1 @ £6.90

I now need to do the calc for dozens of accounts every day; is there a formula to calc total interest to be paid

Thanks in advance
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[xchaser]

here's one to try

https://www.box.com/s/33521ed1d02f0c108258