Payment Allocation


I need to determine what the best approach is to document the payments received each month, as well as a formula to allocate them appropriately.This employer is delinquent on their employee monthly contributions: they agreed to pay $1k per month(they can pay more at any given time) until all outstanding contributions are paid, and then the payments will begin applying to the interest due.

Things to factor in:

•monthly contribution due: 1,567.05 monthly (this is allocated as follows: 53% to HW, 30% to Pension, and 17% to Surcharge)

•Payment agreed per month:$1,000 until full contribution for each month is paid off; interest will be paid last. also, as soon as the agreed amount is received each month, principal will stop accumulating as of the post marked date in column K. This is why it is crucial this enter the postmarked date in K each month so that interest will stop accumulating for the oldest outstanding invoice.

I want to somehow make it where excel knows to apply the payments to the oldest outstanding, but not to exceed $1567.05. 

Since $1,000 is less than the principal due. It will take 2-3 months of payments for the full 1,567.05 to be paid off for any given month . Also, if they ever send a payment greater than $1000, I want excel to allocate and distribute payment appropriately as mentioned above.

In addition. Once the full outstanding contributions are paid (grand total of $37,609.20) I want excel to start applying the payments to the "remaining balance" (interest due), which should be column V if all of the above works as appropriately.

Is it better if I make a separate table to log each payment when received and somehow make it to where it is liked to the existing table to allocate the payment how I want? What formula would do that?

I entered an example in the table of what my initial thoughts process is for documenting the received payments; I essentially want excel to know how to do it automatically.




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.


Answer the Question

You must create an account to use the forum. Create an Account or Login