Skip to content

Blog

Build a month end balance spreadsheet in 15 minutes

Set up a simple spreadsheet that shows your balance for every day of the month: five columns, one running balance, and a cell that finds your lowest day.

· 5 min read · By the GroFunds team

You’ve tried budgeting apps, maybe, or a notebook. What you want is something simpler: a sheet that shows, line by line, where your balance will be on each day this month, and that you can tweak in seconds when a bill moves.

You can build one in about fifteen minutes in Google Sheets, Excel or Numbers. No templates, no macros. Five columns and a little care.

What the sheet will do

When you’re done, you’ll have:

  • every payday and bill for the month on its own row, in date order
  • a running balance that updates the moment you change any amount
  • one cell showing your end of month balance
  • one cell showing the lowest your balance will go, and you’ll be able to spot the day

That last one is the most useful number in the sheet. Here’s why it matters more than the end figure: the lowest point in your month.

Before you open the spreadsheet

Gather three things from your banking app:

  1. Today’s balance in the account your bills and spending come from, with pending charges taken off.
  2. Your paydays this month, with dates and careful amounts.
  3. Your bills this month, with dates and amounts. Look back through two or three months of your statement to catch the ones that don’t come every month.

You’ll also want a rough daily spending figure for groceries, gas, coffee and the like. Add up last month’s everyday spending (not bills, not transfers between your own accounts) and divide by the number of days.

Step 1: Set up five columns

In the first row, type these headings:

ABCDE
DateWhatInOutBalance

Keep In and Out as separate columns. Money in goes in column C, money out goes in column D, always as a plain positive number. It keeps the sheet easy to read and stops sign mistakes.

Step 2: Enter your starting point

In row 2, put today’s date in Date, type “Starting balance” in What, and type today’s balance directly into the Balance cell. This is the only balance you type by hand.

Step 3: Add every payday and bill

From row 3 down, add one row for each payday and each bill:

  • Date: the day it comes out or lands
  • What: a short name, like “Rent” or “Pay”
  • In: the amount, if it’s money coming in
  • Out: the amount, if it’s money going out

Leave the other money column empty on each row.

Step 4: Add everyday spending

You have two choices:

  • One row per week. Simple. If your daily figure is $40, add a row each Sunday with “Everyday spending, week” and $280 in Out.
  • One row per day. More precise, and better for spotting the lowest point. Add a row for each day with your daily figure in Out.

Weekly rows are fine to start. You can switch to daily rows later if your month is tight.

Step 5: Sort by date

Select all your rows below the headings (not row 1) and sort by the Date column, oldest first. Keep the starting balance row at the top: give it today’s date, or the earliest date in the list.

Step 6: Build the running balance

This is the one formula that makes the sheet work. In the Balance cell of row 3, write a formula that does this, in words:

take the Balance from the row above, add this row’s In, then take away this row’s Out.

In most spreadsheets, that means referring to cell E2, adding C3, and subtracting D3. Type it once, then copy the cell down the Balance column to the last row. Each row now picks up the balance from the row above it and adjusts it, so the column becomes a running total.

Empty In or Out cells count as zero, so rows with only one amount work fine.

Step 7: Add the two summary cells

Below the list, or off to the side, add two labelled cells:

  • End of month: point this cell at the Balance in your last row. That’s where the month lands.
  • Lowest point: use the MIN function on the whole Balance column, for example MIN(E2:E60) with a leading equals sign. That’s the lowest your balance will go.

To find the day, look down the Balance column for the number that matches. If you like, colour that cell by hand so it stands out.

A worked example

Here’s an example with invented numbers, using weekly rows for everyday spending:

DateWhatInOutBalance
1stStarting balance$2,400
1stRent$1,450$950
4thPhone$60$890
7thEveryday spending, week$300$590
10thCar insurance$150$440
14thEveryday spending, week$300$140
15thPay$2,200$2,340
18thInternet$70$2,270
21stEveryday spending, week$300$1,970
28thEveryday spending, week$300$1,670
30thPay$2,200$3,870

End of month: $3,870. Lowest point: $140 on the 14th.

That low point is the useful bit. $140 isn’t a disaster, but one unplanned expense in the second week would put the account below zero. Moving the car insurance to the 16th would lift the low point to $290.

Keep it up in five minutes a week

  • Each payday: update the starting row with today’s real balance and today’s date, delete the rows that are now in the past, and check the new lowest point.
  • When a bill changes: change its amount or date and sort again. Everything below updates on its own.
  • At month end: copy the sheet to a new tab for next month. Most of your bills will be the same, so you only change dates.

Over a few months, compare what your sheet said with what really happened. If everyday spending keeps running over, raise your daily figure. If a bill keeps surprising you, add it.

When the sheet shows a problem

If the lowest point is below zero, you’ve found it early. You can move a bill date, delay a transfer to savings, or plan a careful week right before payday. For more ways to read and act on a forecast, see how to see your month before it’s over. If paper suits you better than a screen, a cash flow calendar does the same job on a wall.