Also, I can't get interest to calculate correctly. It calculates from the principal amount every time when I need it to calculate from the actual amount due (principal - sum(amount paid).
I'm beginning to think this is impossible.
I finally got this to work. Here's what I did:
In Invoices, I have a cal field called 'interest", a "principal balance" field which is 'TotalInvoice - sum (payments::payment). Interest is calculated from this field. Then I have a field called "Balance" which is "principal balance +sum(payments::interest).
In payments, I made interest a lookup (from invoices) number which makes it unique. Balance forwarded is also a number lookup (from invoices - Principal balance).and therefore unique and New balance is a cal - "balance brought forward - payments + interest", so it is a running balance based on a unique number.
Now I have a nice Payment record which posts payments and interest and maintains a running balance. This becomes an itemized statement of accounts for past due balances.