Forum Discussion
Trying to calculate past due dates
For example: a payment of $500 is due in April. No payments are made, 30 days past due for $500.
In May the full April amount ($500) and the May amount ($500) are due and a payment of $200 is made. This isn't enough to cover April or May, so there is now $300 that is 60 days past due (April) and $500 that is 30 days past due (May). In June the amount due is the $300 from April, $500 from May, and $500 from June. Customer pays $350, so now April is paid in full.
May is now 60 days past due (for $450), and June is 30 days past due for $500. Customer is still 60 days past due.
So it's not just about whether a payment was made, I need to figure out a way for a spreadsheet to recognize when the delinquency started and if the subsequent payments are enough to cover that delinquent amount. Then it needs to adjust the number of days past due accordingly.
I hope that made sense.
To track partial payments and calculate the number of days past due based on your described scenario in Excel, you can set up a worksheet with columns for due dates, due amounts, payments made, and calculated days past due. Here is a step-by-step guide:
Worksheet Structure:
- Column A: Due Dates (e.g., April, May, June, ...).
- Column B: Due Amount for Each Month.
- Column C: Payments Made for Each Month.
- Column 😧 Calculated Days Past Due for Each Month.
Formulas:
- In Column D (Calculated Days Past Due):
- In cell D2, enter the following formula to calculate the days past due for the first month (April, in this example):
=IF(C2>=B2, 0, 30)
This formula checks if the payment made in Column C is greater than or equal to the due amount in Column B. If yes, it means the payment is on time, and the days past due are zero. Otherwise, it sets the days past due to 30.
- In cell D3 (for May), enter this formula to calculate the days past due based on the previous month's days past due:
=IF(C3>=B3, 0, D2+30)
This formula checks the payment for May, and if it is enough to cover the May due amount, it sets the days past due to zero. Otherwise, it adds 30 days to the previous month's days past due.
- Copy the formula in cell D3 down to the remaining months.
Partial Payment Adjustment:
- Whenever a payment is made, you can simply update the corresponding cell in Column C. The days past due in Column D will adjust automatically based on the payment and due amount.
- You can also use conditional formatting or color-coding to highlight months that are 30, 60, or more days past due for better visualization.
This setup allows you to track delinquency and adjust the number of days past due as payments are made. The calculation considers partial payments and keeps track of how overdue each month is. You can customize it to suit your specific needs and add more months as necessary.
If that does not help you either, I recommend, as mathetes has already informed, to add a file (without sensitive data) or photos with information about your Excel version, operating system, storage medium.
So, essentially, what I'm doing is tracking the credit history on a particular account. We currently have to break it down month by month. Look at statement 1, see the amount due and if payments were made sufficient to cover it. If yes, cool move on. If no, mark the date of first delinquency and the amount that is delinquent for that month.
Then we kind of have to skip through the statements until we find the next payments until we have received enough to satisfy the remainder of Month 1's payment (and this is sometimes broken down over several payment periods). Once Month 1 is finally satisfied then Month 2 becomes the first date of delinquency and we start counting from that date. So we need to be able to track if someone was 30/60/90 days past due at any given time, and the only way for us to know that is to look at past statements combined with their partial payments and do the math.
I have the start of a spreadsheet but it's just a few pieces of raw information (please ignore the ugly formatting and random numbers, I haven't gotten fancy with it yet). https://docs.google.com/spreadsheets/d/18LyEiXAlS08a1ThjkBmUrQCPzN3Tlj_T/edit?usp=drivesdk&ouid=105229868820871294487&rtpof=true&sd=true
So for example in there the first payment due is $450, the payment on 10/25 is $250, so that month is left with a $200 balance, and becomes 30 days delinquent. I can get it to recognize that the current month was not sufficient, but I need it to see that next payment (on 11/27) paid the rest of the amount due on 10/4 so that month is no longer past due. The number in F6 should now subtract 30, because there is one less month that is past due. And that's the part I can't figure out.I"m going to chime in here with some questions that (it seems to me) need to be answered to make this spreadsheet be fully coherent.
- I gather that these payments due each month come from something like a credit card statement for the month. Correct?
- Shouldn't there be interest charged on amounts that are past due? (I've added a column that does this)
- If they're monthly credit card balances--wouldn't they more randomly increase and decrease?
I would recommend that there be a starting line where the balance due is $0.00. Just for the sake of data integrity. It also enables the formulas to make reference to that starting balance and then create a running "Current Balance Due"
Anyway, I've modified it to add a column or two that charge monthly interest on any balance outstanding. With that, I'm not sure that number of days of delinquency is even relevant. But take a look and see if this makes any sense.
- And thank you for the spreadsheet. It's not exactly what I'm needing but I appreciate all the help!
