Free Excel template · Excel for personal finance and taxes
Free debt payoff calculator for Excel
For anyone paying down a credit card or loan who wants to know what an extra $50 or $100 a month really does. Type the balance, the APR from your statement, the minimum payment and the extra you can add. The sheet runs both plans side by side for up to 60 months and shows the months to pay off, the total interest and the interest you save.
Preview and try it
This is the template's sheet, calculated by the same engine as the Cellwise app. Change any number to see the formulas react before you download.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Debt payoff calculator | ||||
| 2 | Balance today | 4800 | |||
| 3 | APR (from your statement) | 0.229 | |||
| 4 | Minimum payment | 144 | |||
| 5 | Extra you can add each month | 100 | |||
| 6 | Payment with extra | 244 | |||
| 7 | Check | OK | |||
| 8 | |||||
| 9 | Minimum only | With extra | |||
| 10 | Months to pay off | 54 | 25 | ||
| 11 | Total interest | 2901.05 | 1275.34 | ||
| 12 | Interest saved by paying extra | 1625.71 | |||
| 13 | Month | Interest (minimum) | Balance (minimum) | Interest (with extra) | Balance (with extra) |
| 14 | 1 | 91.6 | 4747.6 | 91.6 | 4647.6 |
| 15 | 2 | 90.6 | 4694.2 | 88.69 | 4492.29 |
| 16 | 3 | 89.58 | 4639.78 | 85.73 | 4334.02 |
| 17 | 4 | 88.54 | 4584.32 | 82.71 | 4172.73 |
| 18 | 5 | 87.48 | 4527.8 | 79.63 | 4008.36 |
| 19 | 6 | 86.41 | 4470.21 | 76.49 | 3840.85 |
| 20 | 7 | 85.31 | 4411.52 | 73.3 | 3670.15 |
How the formulas work
Interest charged each month
Last month's closing balance times the APR divided by 12, rounded to cents. Once the balance hits zero the IF returns 0, so no interest is charged after payoff. Card issuers usually work interest out daily, so your statement will differ by a few cents; the comparison between plans is what matters.
Balance after each payment
Previous balance plus this month's interest minus the payment. MAX(0, ...) stops the last payment from pushing the balance below zero. Column E does the same with the bigger payment in B6.
Months to pay off
COUNTIF counts the months that still end with a balance, and the final month that clears it is added with +1. If the balance is still above zero after month 60, the cell says so instead of giving a wrong count. In the sample, minimum payments take 54 months while adding $100 a month takes 25.
Interest saved by paying extra
Total interest on the minimum-only plan minus total interest with the extra payment. This is the number that makes the extra $100 worth it: in the sample it saves about $1,626, and you are debt-free more than two years sooner (25 months instead of 54).
Make it yours
- If the Check cell says the minimum does not cover the interest, the balance can never fall on minimum payments alone. Any extra payment is the only way out.
- For several debts, copy the sheet once per debt (right-click the tab > Move or Copy > Create a copy). When one debt is paid off, add its old payment to the extra on the next one; that rolling payment is how both the snowball and avalanche methods speed up.
- Minimum payments on many cards shrink as the balance falls. This sheet keeps the minimum fixed, which is what you should do anyway if you want to finish sooner.
- If payoff takes more than 60 months, the totals only cover the first 60. Raise the extra payment until Months to pay off shows a number.
FAQ
How do I calculate how long it takes to pay off a credit card in Excel?
Build a month-by-month table: interest is the previous balance times APR/12, and the new balance is previous balance plus interest minus payment. Count the months until it reaches zero, as B10 does. Excel's NPER function gives the same answer in one cell: =NPER(B3/12,-B4,B2).
Why is my statement interest slightly different?
Most cards charge interest on the average daily balance, and months have different numbers of days. This sheet uses APR/12 on the month-end balance, which is close enough to compare plans.
Does this work for a car loan or student loan?
Yes. Enter the loan balance, its APR and your monthly payment. Fixed-rate loans are calculated much the same way, so the months and interest come out close to your lender's schedule.
Functions used
IFPicks a result from a testROUNDRounds to digitsMAXLargest numberCOUNTIFCounts cells that matchSUMAdds numbers
Related guides
- How to calculate a loan payment in Excel
- How to calculate compound interest in Excel
- How to make a running total in Excel
More free templates
- Mileage log template for ExcelFor freelancers, gig drivers and employees who claim mileage back and need a clean record of every trip.Free .xlsx
- Expense report templateFor employees who need to claim back business costs, and for the managers and bookkeepers who check those claims.Free .xlsx
- Purchase order templateFor small businesses, office managers and buyers who send purchase orders to suppliers and want to track what actually arrives.Free .xlsx
- Accounts receivable aging report templateFor owners, bookkeepers and credit controllers who invoice customers on terms and need to see who owes what and for how long.Free .xlsx
Updated