Home › Templates › Bill tracker spreadsheet
Bill Tracker Spreadsheet
A free bill tracker spreadsheet for Excel and Google Sheets: list up to 40 bills of any frequency (weekly to annual), and the sheet works out each bill's monthly-equivalent and annual cost, the next due date, a 12-month paid checklist with overdue bills in red, the bills due in the next 7, 14 and 30 days, cost by category and how much of it runs on autopay.
Use it in Google Sheets: choose File → Import → Upload in Sheets and pick the .xlsx, or upload it to Google Drive and open it with Google Sheets. No sign-up, no add-on, no bank connection: you type the bills yourself.
| Bill | Category | Amount | Frequency | Due | Autopay | Monthly equiv. | Annual | Next due | Days |
|---|---|---|---|---|---|---|---|---|---|
| Rent | Housing | $1,450.00 | Monthly | day 1 | N | $1,450.00 | $17,400.00 | 1 Nov 2026 | 0 |
| Electricity | Utilities | $95.00 | Monthly | day 18 | Y | $95.00 | $1,140.00 | 18 Nov 2026 | 17 |
| Water & sewer | Utilities | $120.00 | Quarterly | from 20 Jan 2026 | N | $40.00 | $480.00 | 20 Jan 2027 | 80 |
| Internet | Phone & internet | $65.00 | Monthly | day 9 | Y | $65.00 | $780.00 | 9 Nov 2026 | 8 |
| Mobile phone | Phone & internet | $48.00 | Monthly | day 22 | Y | $48.00 | $576.00 | 22 Nov 2026 | 21 |
| Phone installment plan | Phone & internet | $37.50 | Monthly | from 14 Mar 2026 | Y | $37.50 | $450.00 | 14 Nov 2026 | 13 |
| Car insurance | Insurance | $540.00 | Semiannual | from 15 Mar 2026 | N | $90.00 | $1,080.00 | 15 Mar 2027 | 134 |
| Renters insurance | Insurance | $168.00 | Annual | from 20 Nov 2026 | N | $14.00 | $168.00 | 20 Nov 2026 | 19 |
| Car loan | Debt | $315.00 | Monthly | day 5 | Y | $315.00 | $3,780.00 | 5 Nov 2026 | 4 |
| Student loan | Debt | $230.00 | Monthly | day 28 | Y | $230.00 | $2,760.00 | 28 Nov 2026 | 27 |
| Streaming service | Subscriptions | $15.49 | Monthly | day 12 | Y | $15.49 | $185.88 | 12 Nov 2026 | 11 |
| Cloud storage | Subscriptions | $99.99 | Annual | from 3 Feb 2026 | Y | $8.33 | $99.99 | 3 Feb 2027 | 94 |
| Gym membership | Health | $34.00 | Monthly | day 1 | Y | $34.00 | $408.00 | 1 Nov 2026 | 0 |
| Childcare | Childcare & education | $210.00 | Weekly | from 5 Jan 2026 | N | $910.00 | $10,920.00 | 2 Nov 2026 | 1 |
| Cleaning service | Housing | $90.00 | Biweekly | from 9 Jan 2026 | N | $195.00 | $2,340.00 | 13 Nov 2026 | 12 |
| Storage unit | Other | $72.00 | Monthly | day 31 | N | $72.00 | $864.00 | 30 Nov 2026 | 29 |
| Vehicle registration | Taxes & fees | $185.00 | Annual | from 31 Jul 2026 | N | $15.42 | $185.00 | 31 Jul 2027 | 272 |
| Total (17 bills) | $3,634.74 | $43,616.87 |
What's inside the spreadsheet
| Tab | What you type | What it calculates |
|---|---|---|
| Start here | Nothing | Steps, how the maths works, colour key, bill-pay tips from the CFPB and FTC, limits, disclaimer |
| Bills | Up to 40 bills: name, category (dropdown), amount, frequency (dropdown: monthly, quarterly, semiannual, annual, weekly, biweekly), due day or first due date, autopay Y/N, account, notes; an optional "today" date in B3 | Monthly-equivalent cost, annual cost, next due date, days until due, a check on every row, totals |
| Checklist | Pick Paid next to each amount when you pay; optional year in B3 | The amount due for every bill in every month of the chosen year, colour-coded paid / overdue / due soon; monthly totals for due, paid, unpaid, overdue and bill counts |
| Dashboard | Nothing | Total due this month, paid vs unpaid, overdue this year, bills and amounts due within 7/14/30 days (with the autopay part), a sorted list of upcoming bills, cost by category, autopay share, and two native charts |
| Lists | Your own categories (optional) | Feeds the category dropdown and the Dashboard category table |
| Example Bills, Example Checklist, Example Dashboard | Already filled | 17 fictional bills with ten months of paid marks, so you can see every formula working |
This tracker answers "what is due, and has it been paid". To plan the money for big yearly bills in advance, use the sinking funds tracker; to see bills next to income and other spending, use the budget spreadsheet.
How to use it
- Collect one month of statements. The CFPB's bill calendar asks for "All your bill statements from a single month" and "Statements of any bills that are online". Then scan 12 months of bank and card statements for bills that are not monthly.
- Open Bills. For each bill type the name and amount, and pick a category and a frequency (yellow cells).
- Monthly bills: type the due day (1–31). Every other frequency: type a first due date; any real due date works, past or future, and the sheet repeats it. A monthly bill that starts later, such as a new phone plan, can use a first due date instead of a due day.
- Set Autopay to Y or N and, if useful, the account it comes out of. Column N says OK when a row is complete; B5 counts rows that need a fix.
- Open Checklist. Each month shows the amount due for each bill. When you pay, pick Paid in the cell to its right. Unpaid amounts turn red once the due date has passed.
- Check Dashboard once a week: what is still unpaid this month, what is due in the next 7 days, and how much autopay will take from your account.
Leave Bills!B3 blank and the sheet uses today's date. Type a date there to see the sheet as of that day, for example the first of next month. Leave Checklist!B3 blank to show the current year, or type a year such as 2027.
Autopay, due dates and late fees: what regulators say
The CFPB's blog post "You have protections when it comes to automatic debit payments from your account" puts the trade-off plainly: "Automatic payments can help you avoid late fees on your bills. But if you forget to track your account balance and it’s too low when an automatic (or other) payment is due, you might have to pay overdraft or NSF fees." Its advice: "Pay close attention to your bank account balance and upcoming automatic payments to make sure there will be enough money in your account when the payment is scheduled." That is why the Dashboard shows the autopay amount due within 7, 14 and 30 days, not just the share of bills on autopay.
The same post says you can stop an automatic debit, and that "To stop the next scheduled payment, give your bank the stop payment order at least three business days before the payment is scheduled." It also warns that "cancelling your automatic payment does not cancel your contract with the company." Use the Notes column to record how to cancel each one.
For bills you pay yourself, the CFPB's Your Money, Your Goals bill calendar suggests marking the payment date "7 days before the due date for mail, 2 days before the due date for online", and adds: "If you find you’re often late with a particular bill, negotiate a new due date to better line it up with the dates you receive income or benefits."
For subscriptions and free trials, the FTC's guide "Getting In and Out of Free Trials, Auto-Renewals, and Negative Option Subscriptions" says "Mark your calendar" and "Monitor your credit and debit card statements." Add each subscription as a bill with its renewal date, so the next charge shows up in the upcoming list.
Autopay or bank bill-pay? The CFPB explains the difference: "For recurring bill-pay, you give permission to your bank to send payments to the company. With automatic debits, you give your permission to the company to take the payments from your bank account." Either way, set Autopay to Y if you do not pay the bill by hand.
The formulas, explained
Exact formulas from the file (row 8). On Bills, A is the name, C the amount, D the frequency, E the due day, F the first due date and G autopay; $B$4 is the date used. The Lists tab holds payments per year (12, 4, 2, 1, 52, 26), the step in months (1, 3, 6, 12) and the step in days (7, 14). Columns O to U on Bills and AB to AM on Checklist are grey helper columns.
| Field | Formula |
|---|---|
| Date used (Bills!B4) | =IF(B3="",TODAY(),B3) |
| Monthly equivalent (J) | =IF(N8<>"OK","",C8*INDEX(Lists!$B$3:$B$8,MATCH(D8,Lists!$A$3:$A$8,0))/12) |
| Annual cost (K) | =IF(N8<>"OK","",C8*INDEX(Lists!$B$3:$B$8,MATCH(D8,Lists!$A$3:$A$8,0))) |
| Row check (N) | =IF(A8="","",IF(N(C8)<=0,"Add amount",IF(COUNTIF(Lists!$A$3:$A$8,D8)=0,"Pick frequency",IF(F8<>"",IF(ISNUMBER(F8),"OK","First due date must be a date"),IF(D8<>"Monthly","Add first due date",IF(AND(ISNUMBER(E8),E8>=1,E8<=31),"OK","Add due day 1-31")))))) |
| Anchor date (O, helper) | =IF(N8<>"OK","",IF(F8<>"",F8,DATE(YEAR($B$4),MONTH($B$4),MIN(E8,DAY(EOMONTH($B$4,0)))))) |
| Month offset (S, helper) | =IF(OR(N8<>"OK",N(R8)>0),"",IF($B$4<=O8,0,INT(((YEAR($B$4)-YEAR(O8))*12+MONTH($B$4)-MONTH(O8))/Q8)*Q8)) |
| Candidate date (T, helper) | =IF(S8="","",DATE(YEAR(O8),MONTH(O8)+S8,MIN(P8,DAY(EOMONTH(DATE(YEAR(O8),MONTH(O8)+S8,1),0))))) |
| Next due date (L) | =IF(N8<>"OK","",IF(R8>0,IF($B$4<=F8,F8,F8+R8*ROUNDUP(($B$4-F8)/R8,0)),IF(T8>=$B$4,T8,DATE(YEAR(O8),MONTH(O8)+S8+Q8,MIN(P8,DAY(EOMONTH(DATE(YEAR(O8),MONTH(O8)+S8+Q8,1),0))))))) |
| Days until due (M) | =IF(L8="","",L8-$B$4) |
| Checklist: first due date in January (AB8, helper) | =IF(Bills!$N8<>"OK","",IF(Bills!$R8>0,IF(Bills!$F8+Bills!$R8*ROUNDUP((MAX(AB$6,Bills!$F8)-Bills!$F8)/Bills!$R8,0)>EOMONTH(AB$6,0),"",Bills!$F8+Bills!$R8*ROUNDUP((MAX(AB$6,Bills!$F8)-Bills!$F8)/Bills!$R8,0)),IF(Bills!$F8="",DATE(YEAR(AB$6),MONTH(AB$6),MIN(Bills!$P8,DAY(EOMONTH(AB$6,0)))),IF(OR(AB$6<DATE(YEAR(Bills!$F8),MONTH(Bills!$F8),1),MOD((YEAR(AB$6)-YEAR(Bills!$F8))*12+MONTH(AB$6)-MONTH(Bills!$F8),Bills!$Q8)<>0),"",DATE(YEAR(AB$6),MONTH(AB$6),MIN(Bills!$P8,DAY(EOMONTH(AB$6,0)))))))) |
| Checklist: amount due in January (C8) | =IF($AB8="","",Bills!$C8*IF(Bills!$R8>0,INT((EOMONTH(AB$6,0)-$AB8)/Bills!$R8)+1,1)) |
| Checklist: paid total for January (C50) | =SUMIFS(C8:C47,D8:D47,"Paid") |
| Checklist: overdue for January (C52) | =SUMIFS(C8:C47,D8:D47,"<>Paid",AB8:AB47,"<"&$B$5) |
| Dashboard: total due this month (B8) | =IF($B$5=YEAR($B$3),INDEX(Checklist!$C$49:$Z$49,1,2*MONTH($B$3)-1),"") |
| Dashboard: bills due within 7 days (B24) | =COUNTIFS(Bills!$M$8:$M$47,">=0",Bills!$M$8:$M$47,"<="&A24) |
| Dashboard: autopay share by cost (B20) | =IF(N(B16)=0,"",SUMIFS(Bills!$J$8:$J$47,Bills!$G$8:$G$47,"Y")/B16) |
| Dashboard: 1st upcoming bill, sort key (G30) | =IF(ROWS($G$30:G30)>COUNT(Bills!$U$8:$U$47),"",SMALL(Bills!$U$8:$U$47,ROWS($G$30:G30))) |
| Dashboard: 1st upcoming bill, name (B30) | =IF($G30="","",INDEX(Bills!$A$8:$A$47,MATCH($G30,Bills!$U$8:$U$47,0))) |
Next due date for month-based bills: count the whole steps from the anchor date to the date used (month offset), build that date (day 29–31 becomes the last day of a shorter month), and if it is already past, add one more step. For weekly and biweekly bills: round the days since the first due date up to the next multiple of 7 or 14. A bill due today shows 0 days.
Checklist amounts show what is actually due in that month, so a $210.00 weekly bill shows $1,050.00 in a month with five due dates and $840.00 in a month with four. The monthly equivalent spreads the yearly cost evenly ($210.00 × 52 ÷ 12 = $910.00). Both are useful: one for cash flow this month, the other for comparing costs.
The upcoming list avoids FILTER and SORT: each bill due within 30 days gets a key of days until due + row ÷ 1000, and SMALL picks the 1st, 2nd, 3rd… smallest key, so ties keep their row order. INDEX and MATCH then fetch the bill.
Worked example from the sample data
The Example tabs use 17 made-up bills as of 1 Nov 2026, with every bill from January to October marked Paid except two.
This month
November 2026 has $3,759.99 due across 13 bills. Rent ($1,450.00) and the gym ($34.00), both due on the 1st, are marked Paid: $1,484.00, or 39.5% of the amount. $2,275.99 is still unpaid. Nothing in November is overdue yet, because no due date has passed.
Childcare: weekly
$210.00 every Monday from 5 Jan 2026. Monthly equivalent $210.00 × 52 ÷ 12 = $910.00; annual $10,920.00. November has five Mondays (2, 9, 16, 23, 30), so the Checklist shows $1,050.00; December has four, so $840.00. The next due date is 2 Nov 2026, 1 day away.
Water and sewer: quarterly and overdue
$120.00 every three months from 20 Jan 2026: January, April, July and October. Monthly equivalent $40.00. The October payment was not marked Paid and 20 Oct is before 1 Nov, so the cell is red and counts as overdue. The next due date is 20 Jan 2027, 80 days away.
Cleaning service: biweekly, three payments in one month
$90.00 every other Friday from 9 Jan 2026. October 2026 has three of those Fridays (2, 16 and 30 Oct), so October shows $270.00. It was not marked Paid, so it is overdue too: $120.00 + $270.00 = $390.00 overdue this year.
Car insurance, storage unit and a plan that starts later
Car insurance is $540.00 semiannual from 15 Mar 2026, so it is due in March and September; the next due date is 15 Mar 2027 (134 days) and the monthly equivalent is $90.00. The storage unit is due on day 31, which becomes 30 Nov in November. The phone installment plan uses a first due date of 14 Mar 2026, so January and February are empty.
Upcoming bills and autopay
| Next due within | Bills | Amount | Of which autopay |
|---|---|---|---|
| 7 days | 4 | $2,009.00 | $349.00 |
| 14 days | 8 | $2,216.99 | $466.99 |
| 30 days | 13 | $2,829.99 | $839.99 |
9 of 17 bills (52.9%) are on autopay, but only 23.3% of the monthly-equivalent cost, because rent, childcare and the cleaning service are paid by hand. In the next 7 days, $349.00 will leave the account automatically (car loan $315.00 and gym $34.00).
Cost by category
Total monthly-equivalent cost $3,634.74, or $43,616.87 a year. Housing (rent plus cleaning) and childcare make up 70.3% of it.
The 12-month view
| 2026 | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Due | $3,464 | $3,444 | $4,132 | $3,502 | $3,472 | $3,592 | $3,687 | $3,592 | $3,922 | $3,592 | $3,760 | $3,382 |
| Paid | $3,464 | $3,444 | $4,132 | $3,502 | $3,472 | $3,592 | $3,687 | $3,592 | $3,922 | $3,202 | $1,484 | $0 |
| Unpaid | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $390 | $2,276 | $3,382 |
| Overdue | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $390 | $0 | $0 |
The year total due on the Checklist is $43,541.87, $75.00 less than the annual cost on Bills, because the phone installment plan only started in March (2 × $37.50 not due).
Common mistakes
- Typing a due day for a quarterly or annual bill. Non-monthly bills need a first due date; column N says Add first due date until you add one.
- Counting the monthly equivalent as this month's bill. An annual $168.00 renters insurance policy is $14.00 a month on average, but the whole $168.00 is due in November. Use the Checklist for cash flow.
- Bills that change every month. Type a typical amount (or last year's average) and update it when the statement arrives.
- Forgetting autopay bills. They still need money in the account. Mark them Paid when they clear, so the Dashboard's unpaid total stays real.
- Leaving cancelled bills in the list. Delete the row's inputs (or the name) after you cancel, and keep a note of the cancellation date.
- Checklist year mismatch. If Checklist!B3 holds last year, the Dashboard says so instead of showing this month's paid status.
Limits
- Each bill has one fixed amount. One currency. No bank connection.
- Paid marks a whole month for a bill, including every weekly payment in it.
- The 7/14/30-day windows count each bill's next due date once; a weekly bill due again within 30 days is counted once.
- Due dates on weekends or holidays are not moved. Twice-a-month (semimonthly) bills need two rows, one per date.
- Up to 40 bills; the Checklist shows one calendar year at a time.
FAQ
How do I make a bill tracker spreadsheet?
List every bill with its amount, how often it is due and the due date, then add a column for the next due date and a checklist where you mark each month's payment. This template already has those columns and formulas: you type the bills on the Bills tab and pick Paid on the Checklist tab. The Dashboard then shows what is due this month, what is still unpaid and what is coming up in the next 7, 14 and 30 days.
What should a monthly bill tracker include?
At minimum: bill name, amount, due date, how it is paid (autopay or manual) and a paid mark for each month. It also helps to record the frequency, the account it comes out of and a category. The CFPB's bill calendar suggests gathering "All your bill statements from a single month" plus "Statements of any bills that are online" before you start.
How do I track bills that are not monthly?
Give each bill a frequency and a first due date. The sheet repeats the date (every 3 months for quarterly, every 6 for semiannual, every 12 for annual, every 7 or 14 days for weekly and biweekly) and converts the cost to a monthly equivalent: amount x payments per year / 12. In the example, $120.00 water billed quarterly is $40.00 a month. To save up for large yearly bills, pair this with the sinking funds tracker.
Is it a good idea to put bills on autopay?
It can help, with one condition. The CFPB says "Automatic payments can help you avoid late fees on your bills", but if the balance is too low when a payment is due, "you might have to pay overdraft or NSF fees". It advises paying close attention to "your bank account balance and upcoming automatic payments". The Dashboard shows the autopay amount due in the next 7, 14 and 30 days for that reason.
How many days before the due date should I pay a bill?
The CFPB's bill calendar suggests marking the payment date "7 days before the due date for mail, 2 days before the due date for online." Check each biller's own cut-off time too. The Bills tab highlights any bill due within 7 days.
Can I use this bill tracker in Google Sheets?
Yes. In Google Sheets choose File → Import → Upload and pick the .xlsx, or upload it to Google Drive and open it with Google Sheets. The formulas use IF, AND, OR, N, INDEX, MATCH, SMALL, ROWS, SUM, SUMIFS, COUNTIF, COUNTIFS, DATE, YEAR, MONTH, DAY, EOMONTH, MOD, INT, ROUNDUP, MIN, MAX and TODAY, which work in Excel 2016+, Google Sheets and LibreOffice. No macros, no dynamic arrays.
Related tools and templates
- Budget spreadsheetBills next to income and spending
- Sinking funds trackerSave ahead for yearly bills
- Debt payoff spreadsheetSnowball or avalanche plan
- Emergency fund trackerA cushion for missed income
- Net worth trackerAssets minus debts over time
- All templatesFree spreadsheets
Educational template only, not financial advice. Example data is fictional: the bills, amounts, dates and paid marks are made up to show the formulas and are not a recommendation. CFPB and FTC material was retrieved on 1 November 2026.