Selected Answer
I have seen this exact same workbook with the exact same question before. If it's for work I'm not surprised they didn't find a solution. The task isn't described in sufficient detail and the workbook doesn't support a solution. If it's a task from school the job is to calculate "compound interest". Find it explained here.
In order to calculate compound interest you need the following parameters.
- An amount
The "Balance due" in column J might be the amount or perhaps "Total contributions due" in column C, but these aren't suitable because the amount on which interest is charged changes whenever a partial payment is made. In fact, a new calculation starts on the day on or after each partial payment.
- An interest rate
You say it's 12% annually. Fair enough.
Column H ("Interest % assessed") converts this to 1% monthly. Possible.
To hide this vital parameter in a formula is unprofessional in the highest degree. For you to guess at it like a highschool student is unacceptable in business.
- A compounding period.
The task doesn't say. It seems that the workbook's intention is to compound interest monthly. I point to the legal implications. Most people don't understand compound interest. Therefore, banks etc. are required to show the true cost. Here I meet a task where the business leaves the decision of how to charge interest to an employee who asks for help on the internet. My verdict: unethical, in many jurisdictions illegal. My recommendation: Let the worksheet show the interest rate and compound period openly, as opposed to hiding both in some formula somewhere.
- The period for which interest is charged.
Again, the worksheet is designed to hide facts. The required fact is that "compounded interest is charged for 231 days at the rate of 1% per month". The "231" days should be shown. Ostensibly, the number is hidden between the "Due date" and "Postmark date", which the worksheet immediately repudiates however by having several postmark dates.
Of course, the worksheet also doesn't calculate the period like that at all. It uses a different rule: interest is charged at 1% per month for every completed month (formula in column E). The unfinished month during which payment was made is free, and if payment was made on the exact date on which the month ends, that last month is completely free, too.
In short, the workbook fails to provide precise data for all the four parameters a calculation of compound interest invariably requires. It also fails to disclose its methods of calculation which is the reason why I wouldn't sign off on it if I were the boss who must decide how much interest my company must charge a client. Bear in mind, this is a calculation of overdue payments, very likely to go to court, where this amateurish calculation would be my pleasure to tear to pieces.
So, if this is a task from school after all, I suggest you become very familiar with the method of calculating compound interest (see the link above) and then argue that calculation based on this workbook is impossible because it provides no precise parameters for its calculation. On the other hand, if this is a commercial undertaking a completely different approach must be taken. Your worksheet can't be made to work.