Need help calculating interst on invoices with partial payments

Hi Guys,

I am new to this forum. I have been working on this for the last 3 years to no avail, I just dont understand how to obtain what I am after and could really use a hand!

I am working with a set of invoices issued to a "customer". Each invoice has a date, and must be paid within 14 days otherwise interest accrues at the rates provided for in my attached workbook.

Over the years the customer has incurred various bills as can be seen, and made various payments. There is an amount oustanding.


I want to Workout the interest owing total and I am wondering what the best way to do this would be? I have a list of bills and payments owing with payments made and the date of each.
- I know my interest rate
- I know my overdue dates as that would be date of payment minus date due.

Im getting stuck though in effecting this.

I think I need a running total of the "amount oustanding" and instead of applying each payment to a partciular invoice, it should be each payment goes toward the "amount oustanding" and interest is worked out date to date between each payment and charged accordingly.

I have a attachedan IMAGE of what I think it should look like, but just dont know how to put this into excel.

ANY HELP WOULD BE AWESOME. Thank you guys.

INTEREST CHARGES.xlsx Excel Schedule (Updated)

interest.jpg (sample calculation image)

Hi and welcome to the forum

a few questions...

the 1st 2 values in TOTAL DUE are...
18,090.75
12,864.94

but there was only 1000 paid in the 18090 - so how do you arrive at 12864? was that another transaction?

Thanks

OK so for the 1st 3 rows, you have this owning to you...
18,090.75
30,955.69
39,503.00
but they have only paid...
$1,000.00
$11,000.00
$21,000.00

so the outstanding balance is...
17,090.75
19,955.69
18,503.00


now, based on that, how would you cals the interest due (if you were doing this manually)?

When I was reading your 1st post, thats what I was thinking too. If they owe you money from 1/1/13, dont pay it all and then owe you more (for whatever reason) on 1/2/13, then they owe interest on the outstanding (1st) amount) + the 2nd amount. If they then pay again, and that payment covers the outstanding 1st amount, but doersnt cover any of the 2nd (or even if it does), then they still owe interest on the (reduced) outstanding balance.

Another question is whether you want to compound the interest or simple, and when do uou want to apply it (like, 1st of teh month)?

With this said, try this. In I7, copied down...
=D7+F6-G7
This will calc the interest due, but we need to link it to a time frame (I think)
=I7*$H$4/100

double post. sorry

Did you try my suggestion from post # 6?

With this said, try this. In I7, copied down...
=D7+F6-G7
This will calc the interest due, but we need to link it to a time frame (I think)
=I7*$H$4/100

I modified it slightly and now it works perfectly. Thank you FDibbins!