How to create a debt payoff spreadsheet in Excel or Google Sheets
Short answer
List each debt in a row (name, balance, APR, minimum payment), use =NPER(APR/12, -payment, balance) for the months to pay it off, and build a month-by-month schedule where each month's interest is balance × APR / 12 and the new balance is old balance + interest - payment. Total interest = total paid - starting balance.
Copy-paste code
Formulas (row 2 = one debt: B balance, C APR, D monthly payment)
Months to pay off: =ROUNDUP(NPER(C2/12, -D2, B2), 0)
Total paid: =D2 * NPER(C2/12, -D2, B2)
Total interest: =D2 * NPER(C2/12, -D2, B2) - B2
Debt-free date: =EDATE(TODAY(), ROUNDUP(NPER(C2/12, -D2, B2), 0))
Month-by-month schedule (A = month, B = balance; B2 = starting balance):
Interest C2: =B2 * APR/12
Payment D2: =MIN(payment, B2 + C2)
New bal. B3: =B2 + C2 - D2 (fill down until it reaches 0)Good to know
If the payment is less than a month's interest (balance × APR / 12), NPER returns an error: the debt never gets paid off. Raise the payment.
This is general information, not financial advice.
Debtfell Debt Payoff Planner: See your debt-free date, then get there faster
from $9Debtfell is a ready debt payoff planner spreadsheet that runs snowball and avalanche on your real debts and shows each debt-free date side by side (Excel and Google Sheets).
Secure checkout · 14-day fix-or-refund if it doesn't work as described (terms)Questions
Example?
$4,200 at 22.9% APR with $180 a month: NPER gives 31.2, so 32 payments (the last one small).
Snowball or avalanche?
See the avalanche vs snowball guide; the maths here is the same for both, only the order of extra payments changes.
Free: the Free Planner Sampler
4 printable previews (budget, debt payoff, focus planner, habit tracker), then 5 short emails with formulas that make a budget stick.