Cash Flow Projection Template Excel: Free Monthly Setup

How-to basics · Updated November 21, 2026

Yes—you can create a free monthly cash flow projection template in Excel with a year of columns, rows for cash coming in and going out, and a running closing balance. The layout and formulas below work in Microsoft Excel for Microsoft 365 on Windows; you can build the same basic workbook in Excel for the web, too.

What a cash flow projection shows

A cash flow projection estimates when money is expected to enter and leave a business or household budget. Its central question is not simply whether income exceeds expenses over a year, but whether enough cash is likely to be available when bills fall due. It is a planning estimate, not a record of transactions that have already happened.

That makes it different from a profit-and-loss report. A sale recorded on an invoice may count as revenue before the customer pays. A cash projection should reflect the expected payment date, because the timing affects the bank balance. Likewise, buying equipment may use cash immediately even if accounting rules spread the expense over time.

A projection can help you spot a low-cash month early, plan the timing of purchases, compare possible sales outcomes, and decide what questions to raise with a lender or financial professional. It does not guarantee a particular outcome: estimates depend on assumptions, and actual payment dates and costs can change.

Free Excel template layout

Start a blank workbook and use one column for each month. Put labels in column A, then enter actual month-start dates in B1:M1 for a 12-month forecast. Format those dates to display as month names or month and year. A practical starter layout is:

  • Row 2: Opening cash balance.
  • Rows 4–5: Customer receipts and other cash inflows.
  • Row 6: Total inflows.
  • Rows 8–11: Supplier payments, payroll, rent and utilities, and other outflows.
  • Row 12: Total outflows.
  • Row 14: Net cash flow.
  • Row 15: Closing cash balance.

Leave blank rows between groups to make the sheet easier to scan. You can add more income or expense categories by inserting rows within the relevant group and adjusting the total formulas. Keep the categories specific enough to be useful, but not so detailed that updating the forecast becomes a chore. For example, a small shop might separate card receipts from cash sales if they arrive in the bank on different schedules.

Build the formulas and check the balance

Enter the first month’s opening cash manually, using the amount expected to be available at the start of that month. Enter forecast amounts in the appropriate inflow and outflow rows. In this example, January starts with $8,000, expects $15,000 in customer receipts and $2,000 in other inflows, and forecasts four outflows of $9,000, $4,000, $2,000, and $1,000.

With customer receipts in B4 and other inflows in B5, the January total-inflows formula goes in B6: =SUM(B4:B5). It returns $17,000 for the illustrative amounts above.

With outflows in B8:B11, the January total-outflows formula goes in B12: =SUM(B8:B11). It returns $16,000 ($9,000 + $4,000 + $2,000 + $1,000).

With total inflows in B6 and total outflows in B12, the net-cash-flow formula goes in B14: =B6-B12. It returns $1,000 ($17,000 − $16,000).

With opening cash in B2 and net cash flow in B14, the closing-balance formula goes in B15: =B2+B14. It returns $9,000 from an $8,000 opening balance plus $1,000 net cash flow.

For the next month, with February in column C and January’s closing balance in B15, enter this in C2: =B15. It returns $9,000, carrying January’s closing cash into February as its opening cash. Repeat the pattern across the forecast: each month’s closing balance should become the following month’s opening balance. After entering the first month’s formulas, copy them across the month columns, then check that the references moved to the intended columns.

Make the forecast more realistic

Build the forecast from expected dates and amounts rather than simply copying last month’s totals. Review invoices, order schedules, payroll dates, rent terms, subscription renewals, and planned purchases. If a customer usually pays 30 days after invoicing, the cash may belong in a later month than the sale. If a supplier gives you a payment term, forecast the likely payment date rather than the date you receive the bill.

For a useful first version, make assumptions visible. Add a notes column beside each category or create an assumptions tab for items such as expected collection timing, seasonal sales changes, and one-time costs. Record whether each amount is based on a confirmed contract, a recent average, or a rough estimate. This helps a future reader understand why the numbers are there and which ones need revisiting.

Use one consistent time basis. If the columns are months, assign each item to the month cash is expected to move, and decide how to handle payments that straddle month-end. Keep taxes, loan repayments, owner withdrawals, and major equipment purchases visible where they apply, rather than burying them in a broad miscellaneous line. Their treatment depends on the situation and the underlying agreement, so check real loan terms with the lender and seek a qualified professional for tax or other financial questions.

Use scenarios, not false precision

A single forecast can look more certain than it really is. Instead, duplicate the forecast sheet or create clearly labeled scenarios such as “base,” “lower receipts,” and “higher costs.” Change the assumptions that drive the difference and compare closing cash by month. For instance, a business could model a customer paying one month later than expected, rather than treating an uncertain invoice as guaranteed cash in the current month.

Keep scenario inputs separate from formulas where practical, and label each version with its assumptions and date. Avoid changing a formula manually in one month without checking the months after it; that can create a forecast that appears consistent but no longer follows the same method. A simple comparison table can list each scenario’s lowest projected cash balance and the month it occurs, provided you calculate those figures from the underlying monthly balances and verify them.

Formatting can make the sheet easier to review. Apply a consistent currency format to money cells, distinguish input cells from formula cells with restrained fill colors, and freeze the top row if you need to scroll through a longer forecast. In Excel for Microsoft 365 on Windows, Freeze Panes is on the View tab. Use descriptive labels and avoid relying on color alone to communicate important information.

Common problems and simple checks

The most common projection errors are timing errors, omitted cash items, and broken links between months. A forecast may show a healthy annual total while one month dips below the cash needed to meet payments. Review the month-by-month closing balance, not only the year-end figure.

  • Check that opening cash for each month equals the previous month’s closing cash.
  • Make sure the totals include every category row and do not accidentally include a heading or omit a new line.
  • Separate cash receipts from sales that have not yet been collected.
  • Include irregular items, such as annual insurance bills or planned equipment purchases, in the month they are expected to be paid.
  • Compare earlier forecasts with actual bank activity and update assumptions when the pattern changes.

A negative projected closing balance is a warning in the model, not proof that a specific action is available or appropriate. It signals a period to investigate: confirm the assumptions, identify which payments or receipts drive the shortfall, and consider what options to discuss with relevant stakeholders or a qualified adviser. Do not “fix” the result by deleting a real cost or shifting a payment to a month when it is not expected to occur.

Excel, free access, and other options

The layout above uses basic worksheet cells and the SUM function, so it does not depend on newer Excel-only functions. Excel for the web supports ordinary workbook editing and these basic formulas. Access to Excel desktop and its features depends on your Microsoft account or license; check Microsoft’s current plans if you need to know what is included for you. You can also create the structure in another spreadsheet program, but menu names and some details may differ.

A workbook you build yourself costs nothing beyond access to a spreadsheet tool, though it takes time to set up and maintain. A downloaded template may save setup time, but inspect its formulas, categories, and assumptions before relying on it. Do not enable macros or enter sensitive banking information in an unfamiliar file without understanding what it contains. If several people update the forecast, agree on who owns the inputs and how changes are reviewed so the workbook does not become a collection of conflicting versions.

For a very small operation, one worksheet with monthly totals may be enough. A more complex organization may need separate schedules for customers, suppliers, payroll, debt, or multiple bank accounts, with a summary that brings them together. More complexity is useful only when the extra detail improves decisions and can be maintained accurately.

Frequently Asked Questions

Can I get a free cash flow projection template for Excel?
You can build one in a blank workbook using the layout and formulas above, or look for a downloadable template from a source you trust. Review the assumptions and formulas before using any downloaded file, and confirm that its periods and categories fit your needs.

How many months should a cash flow projection cover?
A 12-month monthly forecast is a practical starting point for seeing seasonality and recurring annual costs. A shorter or longer horizon may suit a particular decision; use a level of detail you can update reliably and revisit the forecast as new information arrives.

What is the difference between cash flow and profit?
Profit is measured under accounting rules, while a cash flow projection follows expected cash receipts and payments. A business can report a sale before collecting the money, so profit for a period does not necessarily mean the same amount is available in the bank.

Should I include sales tax or loan payments?
Include cash payments and receipts that affect the balance you are forecasting, and place them in the expected payment or receipt period. The correct amounts and accounting treatment depend on your circumstances and agreements; check applicable records, tax guidance, and lender terms with an appropriate professional.

Why does my closing balance not match the next month’s opening balance?
Check that the next month’s opening-balance cell refers to the prior month’s closing-balance cell, and that the formula was copied into the correct column. Also confirm that no month has a manually typed opening amount overriding the carry-forward formula.

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.