Forum Discussion
Trying to calculate past due dates
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.
