A basic cash flow Excel template tracks cash received and cash paid, then calculates net cash flow as cash in minus cash out. To find ending cash, add that net change to beginning cash; the monthly layout and formulas below show how to build a reusable version in Excel for Microsoft 365 on Windows.
What a cash flow template shows
A cash flow template summarizes when money enters and leaves a business or household. Unlike a profit-and-loss report, which may include sales or expenses that have not yet been paid, a cash tracker focuses on cash timing. A sale made on credit can increase revenue without increasing cash that month; a customer payment can increase cash even if the related sale was recorded earlier.
The template below uses a direct cash-in/cash-out layout: it lists receipts and payments, groups them into operating, investing, and financing activity, and calculates the change in cash. It is useful for a short-term forecast as well as for recording actual monthly cash movements. Keep forecasts and actuals clearly labeled, because a planned receipt is not the same as cash already received.
This simple management template is not automatically a complete financial statement prepared under a reporting framework. Classification rules and reporting requirements can depend on the organization and purpose. If you need formal financial statements, confirm the appropriate treatment with a qualified accounting professional.
Set up the monthly worksheet
Start a blank workbook and enter a label in column A for each line. Use columns B through M for January through December, or for 12 consecutive months in a rolling forecast. Put the month-start dates in row 4 as real Excel dates, then format them to display as month and year. A sample row plan is:
- Row 5: Beginning cash.
- Rows 7–8: Customer receipts and other operating receipts; row 9: total cash received.
- Rows 11–15: supplier payments, payroll, rent, taxes paid, and other operating payments; row 16: total operating payments.
- Row 17: Net operating cash flow.
- Rows 19–20: equipment or other asset purchases and proceeds from asset sales; row 21: net investing cash flow.
- Rows 23–26: loan proceeds, loan repayments, owner contributions, and owner draws; row 27: net financing cash flow.
- Rows 29–30: Net change in cash and ending cash.
Use a consistent convention: receipts and payments are entered as positive amounts on their own lines, while the section formulas subtract payments where needed. Add rows for categories that matter to your situation, but avoid mixing cash transactions with noncash items such as depreciation.
Core cash flow formulas with a worked example
Assume the month is in column B and the worksheet uses the row plan above. Enter actual or forecast amounts in the detail rows. In this illustrative month, beginning cash is $10,000, customer receipts are $15,000, other receipts are $3,000, operating payments total $13,500, equipment purchases are $2,000, and financing activity is a net outflow of $500. These example values are for demonstrating spreadsheet arithmetic, not financial advice.
With receipts in B7:B8 and total receipts in B9, enter =SUM(B7:B8). For receipts of $15,000 and $3,000, the result is $18,000. SUM adds the values in the specified range, so the formula can be copied across the other month columns.
With operating payments in B11:B15 and the total in B16, enter =SUM(B11:B15). If those five payments add to $13,500, B16 displays $13,500. Record payments as positive numbers in those detail rows so the total remains easy to read.
With total receipts in B9, total operating payments in B16, and net operating cash flow in B17, enter =B9-B16. The example returns $4,500: $18,000 received minus $13,500 paid. A positive result means operating activity added cash for the period; a negative result means it used cash.
With asset purchase payments in B19, asset sale receipts in B20, and net investing cash flow in B21, enter =B20-B19. If B19 is $2,000 and B20 is zero, the result is -$2,000. The minus sign reflects cash spent on the equipment, even though the detail payment itself is entered as a positive amount.
With financing receipts in B23 and B25, financing payments in B24 and B26, and the financing total in B27, enter =SUM(B23,B25)-SUM(B24,B26). For a $500 loan repayment with no other financing activity, the result is -$500. The formula treats loan proceeds and owner contributions as cash in, and repayments and draws as cash out. Adapt the line labels to match the transactions you actually need to track.
With net operating cash in B17, net investing cash in B21, net financing cash in B27, and net change in B29, enter =SUM(B17,B21,B27). The example returns $2,000: $4,500 minus $2,000 minus $500. With beginning cash in B5 and ending cash in B30, enter =B5+B29. The example ending balance is $12,000, calculated as $10,000 plus $2,000.
For the next month, with January’s ending cash in B30 and February’s beginning cash in C5, enter =B30 in C5. It returns $12,000 in this example. Continue the pattern across the year so each month opens with the previous month’s closing balance. If you are building a forecast, enter the real starting cash balance in the first month and let later opening balances link forward.
Build a transaction-based version
A monthly summary is fast to read, but entering totals manually can make it harder to trace a number back to its source. For more detail, keep a separate transaction list with dates in A2:A1000, categories in B2:B1000, and positive transaction amounts in D2:D1000. For this example, use the exact category name Customer receipt. Put a real month-start date in B1 and calculate that category’s total for the month in B2.
With dates in A2:A1000, category names in B2:B1000, amounts in D2:D1000, and the month-start date in B1, enter =SUMIFS($D$2:$D$1000,$B$2:$B$1000,"Customer receipt",$A$2:$A$1000,">="&B$1,$A$2:$A$1000,"<"&EDATE(B$1,1)) in B2. If the matching receipts dated within that month total $15,000, the formula returns $15,000. SUMIFS adds amounts that meet all the listed conditions; using a start date and the next month’s start date includes transactions on the first day and excludes transactions on the next month’s first day.
Copy the summary formula to another month column after putting the next month-start date in its header. The dollar signs keep the transaction-list ranges fixed, while the month reference can adjust across columns. Keep category spelling consistent: an extra space or a different label can prevent a transaction from matching. This formula uses standard Excel functions available in Microsoft 365.
Forecasting, actuals, and useful checks
For a forecast, estimate each receipt and payment in the month you expect cash to move, rather than the month a contract is signed or an invoice is issued. For example, if a customer is expected to pay an invoice in April, forecast the receipt in April even if the sale occurred in March. For actual reporting, replace estimates with cash transactions from reliable records and retain a copy of the original forecast if you want to compare the two.
Include timing assumptions that affect the result: expected collection dates, supplier payment terms, payroll dates, tax payment dates, planned purchases, and known debt repayments. If a payment date is uncertain, a best-case and cautious-case forecast can make that uncertainty visible. Avoid treating a projected credit limit or unapproved loan as available cash.
- Check that the first month’s beginning cash agrees with the bank or cash balance used as the forecast starting point.
- Compare the final cash balance with bank statements or other records when entering actuals, allowing for timing items such as uncleared payments.
- Look for unusually large month-to-month changes and trace them to the detail rows or transaction list.
- Make sure each transaction appears once. Duplicating a receipt or payment inflates cash flow even when the formulas are correct.
- Use a separate label or column to mark figures as forecast or actual, and note when assumptions were last updated.
A cash flow report also should not be confused with a cash budget or a profit report. A cash budget focuses on expected cash timing and can help reveal a future shortfall. A profit report measures income and expenses under its accounting method, which may include noncash charges and unpaid invoices. Both can be useful, but they answer different questions.
Formatting and maintaining the template
Format cash amounts as currency or accounting numbers, and distinguish input cells from formula cells with a consistent fill color or font style. Add a note explaining the color convention so another person does not mistake a calculated total for an editable input. Keep dates as actual date values rather than text such as “Jan,” which can make date-based formulas unreliable.
In Excel for Microsoft 365 on Windows, select the relevant cells and use Home > Number to choose a currency or accounting number format. You can use Home > Conditional Formatting to highlight negative balances or cash flow values that need review. Choose thresholds that fit the purpose of your workbook rather than assuming every negative monthly figure is an error.
Protecting formula cells can reduce accidental edits, but protection is not a substitute for keeping a backup or controlling access to sensitive financial information. Store the workbook where authorized users can find the latest version, and avoid circulating multiple files with conflicting assumptions. Excel for Mac has similar formatting and worksheet features, though ribbon placement can vary by release; Excel for the web may also present some controls differently.
Common template problems
A frequent mistake is reversing the sign convention. If payment rows are entered as negative values and then subtracted again in the net formula, they increase rather than reduce cash. Choose one method and apply it consistently. The template above records both receipts and payments as positive detail values, then subtracts payments in section totals.
Another common issue is treating profit as cash flow. A business can report a profit and still face a cash shortage if customers have not paid, inventory purchases are large, or debt payments fall due. Conversely, borrowing can raise the bank balance without representing operating profit. Read the categories and the period dates, not just the final balance.
Check formulas after inserting or deleting rows. A SUM range may no longer include a newly added category if the row falls outside the range. Also inspect copied formulas for references that shifted unexpectedly. If a total appears as zero, check whether amounts are stored as numbers, whether the date is a genuine Excel date, and whether category text matches exactly.
Frequently Asked Questions
What is the basic cash flow formula?
Net cash flow is cash received minus cash paid for the period. Ending cash is beginning cash plus net cash flow. A template usually separates operating, investing, and financing movements before adding them to find the total change.
Can I use this template for personal cash flow?
Yes. Replace business categories with household categories such as wages, benefits, rent, groceries, utilities, savings transfers, and loan payments. Keep transfers between your own accounts distinct from income so you do not count the same money twice.
Should cash outflows be entered as negative numbers?
Not in the layout described here. Enter receipts and payments as positive amounts on separate lines, then subtract payment totals in the formulas. A different design can use negative outflows, but its net formulas must add those signed amounts rather than subtract them again.
How do I calculate beginning cash for the next month?
Link the next month’s beginning cash to the previous month’s ending cash. In the example, February’s beginning cash equals January’s ending cash, so the forecast rolls forward without retyping the balance.
Is a cash flow template the same as a cash flow statement?
Not necessarily. A simple template is a planning or tracking aid. A formal statement may require specific classifications, adjustments, and disclosures that this basic monthly worksheet does not provide, so confirm requirements with a qualified professional when formal reporting matters.
Related
- Calculate Percentile in Excel for Each Value in a List
- Loan Amortization in Excel: Calculate Payments and Schedule
- Business Loan Amortization Calculator in Excel: Steps
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Function to Extract Part of a String: ExamplesExcel has several functions for extracting part of a string: use LEFT for characters at the start, RIGHT…
- How to Merge Two Sheets in Excel with VLOOKUP FormulasTo merge information from two Excel sheets by a shared ID, add a column to the sheet you…
- Excel COUNTIF Between Two Dates: Formula and ExamplesTo count dates in Excel between two boundary dates, use COUNTIFS, which applies both a start-date and an…
- Excel INDEX and AVERAGE: How to Combine ThemExcel has no single function called INDEX AVERAGE; instead, combine INDEX with AVERAGE so Excel averages the value…
- Excel Conditional Formatting Based on a FormulaYes. In Excel for Microsoft 365 on Windows, select the cells to format, then choose Home > Conditional…
- Excel Autofill Keyboard Shortcuts for Fill Down and RightTo autofill a selected range in Excel, include the source cell and the cells you want to fill,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.