Tracking Debt Repayment in Excel

Overview

In this article, we’ll use Excel to manage and analyze debt payments. By setting up a table that tracks balances, interest (if applicable), and payment frequency, you can visualize your payment progress and see how changes in payment amounts impact your debt. This approach helps you compare different repayment options, such as paying the debt off slowly over time, making a lump sum payment, or negotiating a lower payoff.

How Debt Tracking in Excel Can Help

When paying off debt, particularly if the debt is in collections, it’s important to understand:

• Total Debt: The full amount you owe.
• Minimum Payment: The minimum payment required to avoid further collection actions.
• Interest (if applicable): Some debts accrue interest, though many collections accounts don’t.
• Time to Pay Off: Knowing how long it will take based on current payments.

Excel Formulas Used:

• =SUM() to calculate totals.
• =IF() to set conditions for different outcomes.
• =PMT() to calculate payments needed based on loan terms (even if not formally a loan).

Setting Up the Excel Table for Debt Tracking

1. Create the Columns:In Excel, create a table with the following headers:
   • A1: Date
   • B1: Payment Amount
   • C1: Balance
   • D1: Remaining Balance After Payment
   • E1: Notes
2. Input Initial Data:
   • A2: Start Date of First Payment (e.g., 01/01/2024).
   • C2: Initial Balance (e.g., $3,000).
   • B2 and onward: Payment amount for each period you make a payment (e.g., $100).
3. Formulas:
   • Remaining Balance After Payment (Column D): =C2-B2
   • Copy the formula down to track how each payment affects the balance.
   • In each row after the first, the remaining balance in Column D will feed into Column C of the next row (e.g., in cell C3, input =D2).
4. Extra Details:Add conditional formatting to flag when the balance hits zero.

   Track additional details like notes in Column E (e.g., “Extra payment” or “Missed payment”).

Paying Down a $3,000 Medical Debt

Imagine you have a $3,000 debt in collections with no interest but minimum payments of $100 per month. Here’s how this scenario would look in Excel.

Total Paid and Final Balance

You can calculate the Total Paid by summing up Column B:
=SUM(B2:B13) if you reach zero balance by Row 13, or adjust to the last row.

The Final Balance should be zero once all payments are accounted for, signaling debt completion.

Other Approaches and Advanced Tips

1. Additional Payments:If you can afford an additional payment, increase the payment in the corresponding cell of Column B to see how it impacts the remaining balance.
2. Negotiated Settlement:Some collection agencies may settle for a lump sum lower than the total amount owed. You can enter a one-time payment in the appropriate cell and leave subsequent cells blank.
3. Different Payment Frequencies:Try changing monthly payments to bi-weekly or weekly. Calculate new payment amounts by dividing your intended monthly payment by the number of periods (e.g., for bi-weekly, divide by two).

Adjusted Scenario with Lump Sum Settlement

Suppose the collection agency agrees to a $2,500 lump sum if you pay it within three months. Adjust your table to reflect a larger payment in Month 3:

Total Paid: $2,700

Savings Compared to Original Plan: $300