Pro workbook: Bills, Year, Categories, Setup, Formulas. Sample data, real formulas.
| Bills and subscriptions | |||||||||||||
| One row per recurring payment. Monthly and yearly cost, days to the next due date and what's coming up are calculated. | |||||||||||||
| As of | 14 Sep | Per month | 2,170.47 | ||||||||||
| Per year | 26,045.64 | ||||||||||||
| Due in 7 days | 178.99 | ||||||||||||
| Not on auto-pay | 4 | ||||||||||||
| Bill | Category | Amount | Every | Next due | Account | Auto | Per month | Per year | Days | Status | Overdue | 2 | |
| Rent | Housing | 1,450.00 | Monthly | 1 Oct | Current account | ✓ | 1,450.00 | 17,400.00 | 17 | This month | |||
| Electricity | Utilities | 86.00 | Monthly | 18 Sep | Current account | ✓ | 86.00 | 1,032.00 | 4 | This week | |||
| Internet | Utilities | 39.00 | Monthly | 20 Sep | Card | ✓ | 39.00 | 468.00 | 6 | This week | |||
| Phone | Phone | 25.00 | Monthly | 12 Sep | Card | ✓ | 25.00 | 300.00 | -2 | Overdue | |||
| Car insurance | Insurance | 640.00 | Yearly | 15 Mar | Current account | 53.33 | 640.00 | 182 | Later | ||||
| Home insurance | Insurance | 310.00 | Yearly | 2 Nov | Current account | 25.83 | 310.00 | 49 | Later | ||||
| Netflix | Streaming | 15.99 | Monthly | 15 Sep | Card | ✓ | 15.99 | 191.88 | 1 | This week | |||
| Spotify | Streaming | 10.99 | Monthly | 23 Sep | Card | ✓ | 10.99 | 131.88 | 9 | This month | |||
| iCloud | Software | 2.99 | Monthly | 9 Sep | Card | ✓ | 2.99 | 35.88 | -5 | Overdue | |||
| Figma | Software | 45.00 | Monthly | 28 Sep | PayPal | ✓ | 45.00 | 540.00 | 14 | This month | |||
| Domain names | Software | 68.00 | Yearly | 10 Dec | PayPal | ✓ | 5.67 | 68.00 | 87 | Later | |||
| Gym | Health | 42.00 | Monthly | 3 Oct | Card | ✓ | 42.00 | 504.00 | 19 | This month | |||
| Water | Utilities | 96.00 | Quarterly | 20 Nov | Current account | 32.00 | 384.00 | 67 | Later | ||||
| Train pass | Transport | 38.00 | Weekly | 15 Sep | Card | 164.67 | 1,976.00 | 1 | This week | ||||
| Council tax | Housing | 172.00 | Monthly | 1 Oct | Current account | ✓ | 172.00 | 2,064.00 | 17 | This month | |||
| Twelve months ahead | |||||||||||||
| What each bill costs in each month, from its frequency and next due date. Yearly bills land in their month, quarterly every third month. | |||||||||||||
| First month | Oct 2026 | ||||||||||||
| Bill | Oct 2026 | Nov 2026 | Dec 2026 | Jan 2027 | Feb 2027 | Mar 2027 | Apr 2027 | May 2027 | Jun 2027 | Jul 2027 | Aug 2027 | Sep 2027 | Year |
| Rent | 1,450.00 | 1,450.00 | 1,450.00 | 1,450.00 | 1,450.00 | 1,450.00 | 1,450.00 | 1,450.00 | 1,450.00 | 1,450.00 | 1,450.00 | 1,450.00 | 17,400.00 |
| Electricity | 86.00 | 86.00 | 86.00 | 86.00 | 86.00 | 86.00 | 86.00 | 86.00 | 86.00 | 86.00 | 86.00 | 86.00 | 1,032.00 |
| Internet | 39.00 | 39.00 | 39.00 | 39.00 | 39.00 | 39.00 | 39.00 | 39.00 | 39.00 | 39.00 | 39.00 | 39.00 | 468.00 |
| Phone | 25.00 | 25.00 | 25.00 | 25.00 | 25.00 | 25.00 | 25.00 | 25.00 | 25.00 | 25.00 | 25.00 | 25.00 | 300.00 |
| Car insurance | 640.00 | 640.00 | |||||||||||
| Home insurance | 310.00 | 310.00 | |||||||||||
| Netflix | 15.99 | 15.99 | 15.99 | 15.99 | 15.99 | 15.99 | 15.99 | 15.99 | 15.99 | 15.99 | 15.99 | 15.99 | 191.88 |
| Spotify | 10.99 | 10.99 | 10.99 | 10.99 | 10.99 | 10.99 | 10.99 | 10.99 | 10.99 | 10.99 | 10.99 | 10.99 | 131.88 |
| iCloud | 2.99 | 2.99 | 2.99 | 2.99 | 2.99 | 2.99 | 2.99 | 2.99 | 2.99 | 2.99 | 2.99 | 2.99 | 35.88 |
| Figma | 45.00 | 45.00 | 45.00 | 45.00 | 45.00 | 45.00 | 45.00 | 45.00 | 45.00 | 45.00 | 45.00 | 45.00 | 540.00 |
| Domain names | 68.00 | 68.00 | |||||||||||
| Gym | 42.00 | 42.00 | 42.00 | 42.00 | 42.00 | 42.00 | 42.00 | 42.00 | 42.00 | 42.00 | 42.00 | 42.00 | 504.00 |
| Water | 96.00 | 96.00 | 96.00 | 96.00 | 384.00 | ||||||||
| Train pass | 164.67 | 164.67 | 164.67 | 164.67 | 164.67 | 164.67 | 164.67 | 164.67 | 164.67 | 164.67 | 164.67 | 164.67 | 1,976.00 |
| Council tax | 172.00 | 172.00 | 172.00 | 172.00 | 172.00 | 172.00 | 172.00 | 172.00 | 172.00 | 172.00 | 172.00 | 172.00 | 2,064.00 |
| By category | |||||||
| Monthly cost per category, and its share. | |||||||
| Category | Bills | Per month | Per year | Share | Not on auto-pay | 275.83 | |
| Housing | 2 | 1,622.00 | 19,464.00 | 75% | Subscriptions (Software + Streaming) | 80.64 | |
| Utilities | 3 | 157.00 | 1,884.00 | 7% | Biggest category | Housing | |
| Insurance | 2 | 79.17 | 950.00 | 4% | |||
| Software | 3 | 53.66 | 643.88 | 2% | |||
| Streaming | 2 | 26.98 | 323.76 | 1% | |||
| Phone | 1 | 25.00 | 300.00 | 1% | |||
| Transport | 1 | 164.67 | 1,976.00 | 8% | |||
| Health | 1 | 42.00 | 504.00 | 2% | |||
| Other | |||||||
| All | 15 | 2,170.47 | 26,045.64 | ||||
| Per month is the sum of each bill's monthly equivalent, so a yearly bill contributes a twelfth here. |
| Setup | |||||
| The as-of date, the first month of the Year view, and the lists. | |||||
| As of | 2026-09-14 | Type =TODAY() to keep it live | |||
| Year view starts | Oct 2026 | ||||
| Every | per month | Categories | Accounts | ||
| Monthly | 1.000 | Housing | Current account | ||
| Yearly | 0.083 | Utilities | Card | ||
| Quarterly | 0.333 | Insurance | PayPal | ||
| Weekly | 4.333 | Software | |||
| Streaming | |||||
| Phone | |||||
| Transport | |||||
| Health | |||||
| Other | |||||
| 'per month' is the factor a bill's amount is multiplied by: 1, 1/12, 1/3, 52/12. The Year sheet's month logic is written for these four names — keep them. |
| How the formulas work | ||
| Every formula in this workbook, in plain words. Nothing is hidden and nothing needs a macro. Formulas are shown without the leading = sign. | ||
| Where | Formula | What it does |
| Bills · Per month | C8*INDEX(freq_factor,MATCH(D8,freqs,0)) | Amount times the factor for its frequency, looked up on Setup: 1, 1/12, 1/3 or 52/12. |
| Bills · Days | E8-$B$3 | Next due minus 'as of'. Negative when overdue. |
| Bills · Status | IF(E8<$B$3,"Overdue",IF(E8<=$B$3+7,"This week",IF(E8<=$B$3+30,"This month","Later"))) | Four bands against 'as of'; the row colours follow it. |
| Bills · Due in 7 days | SUMIFS(C8:C67,E8:E67,">="&$B$3,E8:E67,"<="&$B$3+7) | Full amounts of bills whose next due date falls in the next week. |
| Bills · Not on auto-pay | COUNTA(A8:A67)-COUNTA(G8:G67) | Bills minus ticks. |
| Year · a month cell | Monthly → amount · Weekly → amount×52/12 · Yearly → amount if MONTH(next due)=MONTH(month) · Quarterly → amount if MOD(MONTH(month)−MONTH(next due),3)=0 | One nested IF per cell on the bill's frequency. Yearly and quarterly need the next-due date to find their month. |
| Year · vs average | B66-$N$66/12 | This month's total minus a twelfth of the year — positive in the months with renewals. |
| Categories · Per month | SUMIF(Bills!$B$8:$B$67,A5,Bills!$H$8:$H$67) | Sum of the monthly equivalents in that category. |
| Categories · Not on auto-pay | Bills!H68-SUMIF(Bills!$G$8:$G$67,"✓",Bills!$H$8:$H$67) | Total monthly cost minus the ticked ones — what you have to pay by hand each month. |