Payment Allocation

You are correct in doubting that a single table can do everything you want. The approach to take is to design data capture first and think of data evaluation later. When approaching data evaluation you might try to include the display of results you need in the tables created for data capture but abandon that attempt if it becomes messy. If your data capture is clear and simple you can do the evaluation in many ways. Once you have a mess the only thing you can do is to start over.

Based on that concept, the first data to capture is the amount due, basically your columns A:D. The other data is payment received, in essence columns O:P. These two tables would better be on separate sheets.

The Due table could be expanded to calculate the interest. If interest is calculated monthly your column E might help. I didn't find where you say when interest is received except that it is after the principal. I would receive the first $1567.05, then the interest due on that amount, and then start on the next $1567.05 etc.etc. But that may not be the agreement. Bear in mind that if all interest is left to the end there will be a substantial loss of interest on unpaid interest. Whichever way, there should be a column for the interest due for each of the amounts in column C. Interest must be calculated on the total amount due (original debt - payments received) and then distributed to each row. Never mind how to do that. Just provide the space for it at this point. There is a cut-off date for calculating interest. This must be in the header of the Due sheet. Normally, this date would be TODAY() but in practise you may want to set it for the end of last month. So you need a non-automated cell where it can be entered. You would also need a cell for the rate of interest, and if you expect this to be variable during the lifetime of your workbook provisions must be made for that.

The Paid table pay include application, your columns Q:S, but there should be one more column for interest.

Columns for Remaining Balance could be added to the Due sheet. I think your column V is inadequate. The table designed as suggested above would show separate amounts due for principal and interest for each item. Hence there would also be separate remaining balances.

The Paid sheet provides a total of how much was paid. This can be distributed to the amounts on the Due sheet using a MIN() function. The amount paid against C7 =MIN([Total paid]-[Allocated to C4:C6]). Same method for the interest. Note that it doesn't matter how much the debtor pays or when. Whatever he pays is allocated to the oldest first (question of interest allocation to be resolved).

I'm not sure how to calculate the interest. Since it is an amount due it must appear on the Due sheet. Since interest stops on the postmark date you will need a column for interest rate numbers. This column must be on the Paid sheet because that is where the postmark dates are. Perhaps, once I see the actual worksheets I may have a better idea which would make the calculation more transparent, but a correct calculation would result from calculating interest from due date till cut-off date on the Due sheet and for postmark date to cut-off date on the Paid sheet. (May want to display a copy of the cut-off date set on the Due sheet here.) The actual interest due would be the first number minus the second which can be distributed using the MIN() function.

I would start designing the Paid sheet along the above lines. Later, if you find that you need to make the interest calculation more transparent, move it out to a dedicated tab.