Trying to calculate past due dates

[CityLaurel]

Hi, I'm trying to create a spreadsheet to tackle late dates on payments -- I need to be able to track how far past due someone is on a loan while factoring in partial payments. So for example, a payment of $500 is due in March, and $200 is paid. Loan is past due $300 and thirty days. Payment of $900 is now due in April, and $250 is made, so the loan is now 60 days past due (March and April). In May $1200 is due, and a payment of $200 is made. March is no longer past due, only April and May, so the loan is still only 60 days past due.

I cannot for the life of me figure out how to track this and it is a nightmare trying to do it manually.

Anyone able to help me?

[#2]

@CityLaurel 

To track past due dates for loans with partial payments in Excel, you can use a combination of Excel formulas and conditional formatting.

Here is a step-by-step guide on how to set up your spreadsheet:

Assumptions:

• Loan due dates are in column A.
• Payment amounts are in column B.
• You want to calculate the number of days past due for each month.

Step 1: Set Up Your Data

• In column A, list the due dates for the loan payments.
• In column B, list the payment amounts.

Step 2: Calculate Days Past Due

In a new column (let us say column C), you can calculate the number of days past due for each month using the following formula:

=IF(B2=0, "", MAX(0, TODAY()-A2))

This formula checks if a payment has been made (B2=0) for each month. If a payment has been made, it leaves the cell blank; otherwise, it calculates the days past due by subtracting the due date (A2) from today's date. This formula goes in cell C2, and you can drag it down to fill for all your loan payments.

Step 3: Calculate Total Days Past Due

In another cell (let us say D2), calculate the total days past due by summing the values in column C:

=SUM(C2:C100)

This formula sums all the days past due for all months.

Step 4: Conditional Formatting

To highlight the months that are past due, you can use conditional formatting:

• Select the cells in column A (the due dates).
• Go to the "Home" tab.
• Click on "Conditional Formatting" and choose "New Rule."
• Select "Use a formula to determine which cells to format."
• Enter the following formula:

=$C2>0

• Click on the "Format" button to choose your desired formatting (e.g., fill the cell with a color).
• Click "OK" to apply the formatting.

This will highlight the due dates in column A for months that are past due.

Step 5: Updating Payments

When a payment is made, simply enter the payment amount in column B for the corresponding month. The "Days Past Due" in column C will automatically update, and the conditional formatting will adjust to reflect the updated status.

This setup allows you to track past due dates and partial payments for your loans effectively. The "Days Past Due" column (column C) provides you with the number of days past due for each month, and the conditional formatting helps you visualize which months are overdue.

The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark them as best response and like it!

This will help all forum participants.

[#3]

The part that is actually tripping me up is figuring out when the partial payments equal up to a past due one.

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.

[#4]

@CityLaurel 

Are you able to do all this on paper? The reason I ask is that long before we come up with the Excel way to get

• from A (all of the various input data elements that comprise A)
• to B (all of the different outputs that comprise B)

especially with the number of moving parts in your situation, long before we get to the Excel spreadsheet that does that, one of the most helpful things we can do--you can do--is to spell out the steps you'd take on paper. Don't try to phrase it "in Excel"; use English words (and numbers, and dates), but write out in some detail the process steps.

Then, once that's done, the Excel part is pretty easy.

What you've given us is a lot of input data. I do see that in your first post you've written "It's a nightmare trying to do it manually," which may well be true. But that implies you've done it manually; so I'm assuming you can spell out for yourself (and us) what those process steps are, to get from all that input data to the desired output.

And if you have a partially completed spreadsheet, you could further help us help you by putting a copy of that partially completed workbook on OneDrive or GoogleDrive with a link here that grants access to it.

[#5]

@CityLaurel 

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:

1. Column A: Due Dates (e.g., April, May, June, ...).
2. Column B: Due Amount for Each Month.
3. Column C: Payments Made for Each Month.
4. Column D: Calculated Days Past Due for Each Month.

Formulas:

1. 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.

1. 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.

1. 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.

[#6]

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=1052...

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.

[#7]

@CityLaurel 

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.

1. I gather that these payments due each month come from something like a credit card statement for the month. Correct?
2. Shouldn't there be interest charged on amounts that are past due? (I've added a column that does this)
3. 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.

[#8]

@mathetes 

1. Yes, it's for credit cards.

2. The balance/interest doesn't really matter for this particular scenario. All of the additional interest just go into the total balance owed on the card, which isn't really what I'm tracking. 

3. Yup, the payments are variable based on the balance.

[#9]

And thank you for the spreadsheet. It's not exactly what I'm needing but I appreciate all the help!