Cash Flow Tracker Excel Template: Build and Use It

How-to basics · Updated November 21, 2026

A cash flow tracker Excel template records when money comes in and goes out, then calculates the balance left after each transaction. In Excel for Microsoft 365 on Windows, make one by listing transactions with dates, descriptions, income and expenses, then use a running-balance formula; a monthly summary can show totals by period.

What a cash flow tracker shows

A cash flow tracker focuses on the timing of cash receipts and payments. It can help answer practical questions such as whether there is enough cash for an upcoming bill, which weeks have unusually high spending, and whether expected customer payments arrived. It is not the same as a profit-and-loss statement: a sale recorded as revenue may not have been paid yet, and a loan payment may include principal even though it is not an ordinary operating expense.

A useful tracker keeps the underlying transaction list simple and preserves enough detail to explain each amount. At minimum, record:

  • Date: the date cash is expected to move or actually moved.
  • Description: a short explanation, such as customer payment or rent.
  • Category: a consistent label for grouping and review.
  • Money in and money out: enter each transaction in one column, using positive amounts.
  • Running balance: the opening cash plus all net changes up to that row.

For a business, you may also need an account or payment method column to distinguish bank accounts, cards, and cash. For a household, categories might include pay, housing, utilities, groceries, and transfers. Choose labels that help you make decisions rather than creating a long list that is hard to maintain.

Set up the transaction sheet

Start with a blank worksheet. Put an opening cash balance in B1 and label A1 as Opening balance. This is the cash available at the beginning of the tracking period, not an income transaction. In row 4, enter these headers: Date in A4, Description in B4, Category in C4, Money In in D4, Money Out in E4, Net Change in F4, and Running Balance in G4. Begin transaction data on row 5.

Enter income in column D and payments in column E, not as negative numbers. For example, a customer receipt of $1,200 goes in D5, while a $900 rent payment goes in E6. Keeping income and expenses in separate positive-value columns makes it easier to check totals and reduces sign errors. Use real Excel dates in column A, not dates typed as text; apply a date format with Home > Number Format if needed.

To turn the range into an Excel table, select the headers and a few rows of data, then choose Insert > Table and confirm that the table has headers. Tables make filtering and sorting more convenient, and they can extend as you add transactions. Keep a blank line out of the middle of the data so that filters and summaries can interpret the list consistently. If you prefer not to use a table, the same layout and formulas work in a regular range.

Calculate net change and the running balance

With the described layout, put this formula in F5 to calculate the first transaction’s net change, where income is in D5 and outflow is in E5: =D5-E5. With $2,000 in B1, a $1,200 receipt in D5 and no outflow in E5, the result is $1,200.

With the opening balance in B1 and the first transaction’s net change in F5, enter this formula in G5: =$B$1+F5. For the example, the balance after the receipt is $2,000 + $1,200 = $3,200. If you intended the example’s receipt and rent to occur as successive transactions, the balance after the first receipt is $3,200 and the next formula will subtract the rent.

With the previous running balance in G5 and the next transaction’s net change in F6, enter this formula in G6: =G5+F6. If row 6 contains the $900 rent payment, F6 is -$900 and G6 is $3,200 – $900 = $2,300. Copy the F and G formulas down for later transaction rows. In this example, the closing balance after both transactions is $2,300. The first balance formula uses an absolute reference to B1 so it continues to point to the opening balance when copied; the later balance formula adds each row to the balance immediately above.

Sort transactions by date before relying on the running balance. If dates tie, add a consistent ordering rule, such as entering transactions in the order they cleared. A tracker can display a negative balance, which is a useful warning that the recorded sequence dips below zero; it does not prove that an overdraft facility or credit is available.

Summarize cash flow by month

A monthly summary helps distinguish a one-off event from a recurring pattern. On the same sheet or a separate Summary sheet, set H4 to Month, I4 to Cash in, J4 to Cash out, and K4 to Net change. Enter the first day of a month as a date in H5, such as January 1, 2026, and format it to display as Jan 2026 if that is easier to read.

Assuming transaction dates are in A5:A1000, income amounts are in D5:D1000, and the month-start date is in H5, enter this formula in I5 to sum income from that month: =SUMIFS($D$5:$D$1000,$A$5:$A$1000,">="&H5,$A$5:$A$1000,"<"&EDATE(H5,1)). If the only income in January is the $1,200 receipt in the example, the result is $1,200. The inclusive first-day boundary and exclusive first day of the following month include dates throughout the month, including the final day.

With the same date range and month-start cell, and expenses in E5:E1000, enter this formula in J5 for monthly outflows: =SUMIFS($E$5:$E$1000,$A$5:$A$1000,">="&H5,$A$5:$A$1000,"<"&EDATE(H5,1)). If the $900 rent is the only January expense, the result is $900. With the monthly income total in I5 and monthly expense total in J5, enter this formula in K5: =I5-J5. The example’s net cash change is $1,200 – $900 = $300.

Copy the summary formulas down and enter the first day of each later month in column H. The monthly net change is not the same as the ending account balance: it excludes the opening cash carried into the month. Check that the date cells contain valid dates and that transaction rows fall within the ranges used by the formulas. If your list may exceed row 1000, extend the ranges or use an Excel table-based approach.

Use the tracker for a cash forecast

A tracker can also support a simple forecast, but planned and completed cash movements should be distinguishable. One option is to add a Status column with values such as Planned and Actual. Filter the list to review one status at a time, or maintain a separate forecast sheet so a projected payment is not mistaken for money already received. Use expected dates for forecasts and update them when a supplier changes a due date or a customer payment is delayed.

For a useful short-term view, enter known upcoming receipts and payments, including their expected dates and amounts. A business might list payroll, rent, supplier invoices, tax payments, and customer collections; a household might list paydays, rent, utilities, and insurance. Compare the projected balance with the dates of major obligations. A forecast is only as dependable as its assumptions: uncertain receipts should be identified as uncertain rather than treated as guaranteed cash.

Keep a note of assumptions that could change the forecast, such as a bill that varies each month or a customer who usually pays late. Do not count a transfer between two accounts as new income if the tracker covers both accounts together; record transfers consistently so they do not inflate total cash inflows and outflows.

Make the sheet easier to maintain

Set a routine for entering or importing transactions. For a personal tracker, weekly updates may be sufficient for review; a business with frequent payments may need more frequent reconciliation. Compare entries with bank or payment-provider records and mark differences for investigation. A tracker is a working record, not a substitute for the underlying account statements or any required accounting records.

  • Use one row per cash movement, and avoid combining separate payments in one row if you need to review them individually.
  • Apply a consistent category spelling. A dropdown list can reduce variants such as Utilities and Utility.
  • Format income, outflow, and balances as currency with an appropriate number of decimal places.
  • Use filters to inspect a date range, category, or status without deleting other transactions.
  • Freeze the header row with View > Freeze Panes > Freeze Top Row if the list is long.
  • Save a backup or keep a dated copy before making extensive structural changes.

Conditional formatting can help flag balances below a chosen threshold. Select the balance cells, then use Home > Conditional Formatting > Highlight Cells Rules to choose a rule. Pick a threshold that makes sense for the account being tracked; a warning color is only a visual prompt and does not replace reviewing upcoming payments.

Common errors and how to check them

The most common arithmetic problem is entering an expense as a negative number in Money Out even though the formula already subtracts that column. That reverses the intended effect and makes the balance increase. Enter outflows as positive amounts and let the net-change calculation subtract them. Another frequent problem is including the opening balance as an income transaction, which counts the same cash twice.

Dates stored as text can prevent a monthly SUMIFS formula from matching transactions. If a date looks right but is not being included, check that Excel recognizes it as a date value and that the month-start cell is also a date. A range that stops too early is another quiet source of inaccurate totals; extend the transaction and summary ranges when needed. After sorting, verify that the formulas still refer to the row above and that the running balance follows the new order.

Reconcile the calculated balance to a real statement at a clear cutoff date. Differences can come from missing transactions, pending payments, duplicate entries, timing differences, or tracking the wrong account. Do not change a formula just to force a match; locate and explain the underlying difference. Also remember that a cash tracker does not automatically classify transactions for tax or financial reporting purposes.

When Excel is a good fit—and when to choose another tool

Excel is a practical choice when you want control over categories, a transparent calculation, or a tracker tailored to a small set of accounts. A straightforward workbook has no separate software subscription requirement if you already have access to Excel, though Excel itself may be provided through a paid Microsoft 365 plan or another license. The main ongoing cost is often the time needed to enter, check, and maintain the data.

A bank’s built-in spending view may be easier if you only want a quick look at one account, but its categories and history may not match your needs. Accounting software may be more appropriate when a business needs shared access, linked transactions, invoicing, or formal reporting workflows. A spreadsheet is less convenient when several people need to update it at once or when reliable automatic imports and audit trails are important. Choose based on the record-keeping and access requirements, not just on the appearance of a template.

Frequently Asked Questions

Is there a free cash flow tracker Excel template?
You can build a basic tracker in a blank workbook using the columns and formulas above. Whether a pre-made workbook is free depends on its provider and license; check its terms and inspect formulas before entering sensitive or important data.

What is the difference between cash flow and profit?
Cash flow tracks cash received and paid during a period. Profit is calculated under accounting rules and can include revenue or expenses that have not yet resulted in a cash movement. The two measures can differ in timing and in what they include.

How often should I update a cash flow tracker?
Update it often enough to make decisions using current information. A household may review it weekly, while a business with daily receipts and payments may need to update more frequently. Reconcile it to account records on a regular schedule.

Can I track more than one bank account in the same workbook?
Yes. Add an Account column and use consistent account names. Decide whether the running balance should be per account or for cash across all accounts. For per-account balances, keep separate running calculations or separate transaction sheets; a single combined balance can hide a shortage in one account.

Why does my monthly total not match my bank statement?
Check the date range, date values, missing or duplicated transactions, the account being tracked, and whether pending payments are included. A month summary based on transaction dates may also differ from a statement that uses posting dates or a different statement cutoff.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.