Debt Payoff Date
Calculate the exact date when a debt will be fully paid off based on your current payment schedule in Google Sheets.
=EDATE(TODAY(), NPER(rate/12, -payment, balance)) How It Works
Combining NPER (number of periods) with EDATE gives you an actual calendar date - not just a count of months. This makes it concrete: you can see the specific month and year you’ll be debt-free.
Syntax
=EDATE(TODAY(), NPER(rate/12, -payment, balance))- EDATE: Adds months to a date
- TODAY(): Today’s date as starting point
- NPER: Calculates months until payoff
Format the result as a date.
Example
Credit Card:
- Balance: $6,500
- APR: 21%
- Monthly Payment: $250
Formula: =EDATE(TODAY(), NPER(21%/12, -250, 6500))
NPER result: 32.4 months
Payoff date: December 2028
Multiple Debt Payoff Dates
| Debt | Balance | Rate | Payment | Months | Payoff Date |
|---|---|---|---|---|---|
| Credit Card | $6,500 | 21% | $250 | 32 | Dec 2028 |
| Car Loan | $14,000 | 6% | $465 | 32 | Dec 2028 |
| Student Loan | $28,000 | 5.5% | $320 | 112 | Aug 2035 |
Formula for each:
=EDATE(TODAY(), NPER(rate/12, -payment, balance))Variations
With Extra Payments
See how extra payments move the date forward:
=EDATE(TODAY(), NPER(rate/12, -(payment + extra), balance))Credit card example with $100 extra:
=EDATE(TODAY(), NPER(21%/12, -350, 6500))Payoff moves from December 2028 to August 2027 - over a year sooner.
Days Until Debt-Free
=EDATE(TODAY(), NPER(rate/12, -payment, balance)) - TODAY()Returns the number of days remaining.
Formatted Result
=TEXT(EDATE(TODAY(), NPER(rate/12, -payment, balance)), "MMMM YYYY")Returns something like “December 2028” instead of a raw date number.
Countdown Display
=DATEDIF(TODAY(), payoff_date, "Y") & " years, " & DATEDIF(TODAY(), payoff_date, "YM") & " months"Returns “2 years, 8 months” style output.
Setting Up a Payoff Date Dashboard
| A | B | C | D | E |
|---|---|---|---|---|
| Debt | Balance | Rate | Payment | Payoff Date |
| Visa | $6,500 | 21% | $250 | =EDATE(TODAY(),NPER(C2/12,-D2,B2)) |
| Car | $14,000 | 6% | $465 | =EDATE(TODAY(),NPER(C3/12,-D3,B3)) |
| Student | $28,000 | 5.5% | $320 | =EDATE(TODAY(),NPER(C4/12,-D4,B4)) |
| Last Debt-Free Date | =MAX(E2:E4) |
The MAX formula shows when you’ll be completely debt-free.
Scenario Comparison
How does payment amount change the payoff date for $6,500 at 21%?
| Monthly Payment | Payoff Date | Total Interest |
|---|---|---|
| $150 (minimum) | March 2032 | $4,350 |
| $200 | June 2029 | $2,480 |
| $250 | December 2028 | $1,680 |
| $350 | August 2027 | $1,050 |
| $500 | May 2027 | $645 |
Pro Tips
-
Pin the date somewhere visible - knowing the exact date makes the payoff feel real and motivating
-
Recalculate monthly - as the balance drops, the date moves closer; tracking progress helps
-
Use conditional formatting - highlight cells green when a payoff date is less than 12 months away
-
Stack debts - once one debt is paid off, roll that payment into the next for faster results
-
Account for rounding - NPER returns a decimal; round up since you can’t make a partial month’s payment
Common Errors
- #NUM! error - payment is too small to cover monthly interest; increase the payment amount
- Negative months result - double-check that the payment is entered as negative in NPER
- Date shows as a number - format the cell as a date (Format > Number > Date)
- Forgetting variable rates - if the rate can change, the payoff date will shift; recalculate periodically