A real estate Excel cash-flow template can track monthly rent, other income, vacancy, operating costs, capital spending, loan payments, and the cash left over. With rent in B2, other income in C2, vacancy loss in D2, operating expenses in E2, capital spending in F2, and debt payments in G2, calculate monthly cash flow in H2 with =B2+C2-D2-E2-F2-G2; for illustrative inputs of $2,400, $50, $120, $850, $0, and $1,100, the result is $380. The layout and formulas below work in Excel for Microsoft 365 on Windows.
What a real estate cash-flow template measures
A cash-flow spreadsheet estimates when money comes into and leaves a property. It is useful for reviewing an existing rental, comparing assumptions for a potential purchase, or monitoring a property over time. It is a planning model, not a guarantee of future results: rent, occupancy, repairs, financing terms, and expenses can change.
Keep cash flow distinct from related measures. Net operating income, often abbreviated NOI, is generally rental and other operating income less operating expenses, before financing costs and capital expenditures. Cash flow after debt service includes loan payments; a more complete cash budget may also show capital work, taxes, and owner contributions separately. A cap rate compares NOI with a property’s value or price, while cash-on-cash return compares a defined cash-flow measure with cash invested. These measures answer different questions, so a template should label them rather than treat them as interchangeable.
A practical worksheet layout
Use one row per month and one column per category. Put headers in row 1, then enter January through December in A2:A13. The sample columns are A Month, B Scheduled rent, C Other income, D Vacancy or credit loss, E Operating expenses, F Capital expenditures, G Debt payments, H Net cash flow, and I Cumulative cash flow.
Enter assumptions as positive amounts and subtract vacancy, expenses, capital spending, and debt payments in the cash-flow calculation. For example, D2 can hold a $120 vacancy allowance rather than a negative number. This convention makes inputs easier to read and reduces sign errors. If you prefer to enter expenses as negatives, change the formula consistently; do not both enter a negative and subtract it.
- Income: scheduled rent and separately identified fees, parking, laundry, or other property income.
- Operating expenses: recurring costs such as property management, insurance, utilities paid by the owner, maintenance, and property taxes.
- Nonroutine cash items: larger repairs or improvements, loan payments, and any owner contributions or distributions. Keep them identifiable rather than burying them in a single expense total.
For a portfolio, use a separate property identifier or a separate worksheet for each building, then consolidate totals carefully. A single-property sheet is easier to audit; a multi-property workbook is more convenient but can become harder to maintain if every property has different rows or categories.
Build the monthly sheet in Excel
- Create a blank workbook and rename the first worksheet Property Cash Flow. Enter the headers above, then fill A2:A13 with the months for the forecast period.
- Enter monthly assumptions in columns B:G. Use actual statements or leases for historical inputs when available, and clearly label estimates. Keep one-time costs in the month they are expected to occur.
- Enter the cash-flow formula in H2 and copy it down through H13. In Excel, drag the fill handle at the cell’s lower-right corner, or copy and paste into the remaining monthly cells.
- Enter a cumulative total in column I so the running cash position is visible. Format dollar amounts consistently using the number-format controls on the Home tab; use a date or month label that makes the forecast period unambiguous.
- Consider freezing the header row with View > Freeze Panes > Freeze Top Row, especially if you add many months. On Excel for Mac or Excel for the web, the ribbon layout can differ, but the formulas and basic worksheet structure are the same.
With the same sample inputs repeated in each of the twelve months, the annual total is $4,560. That is a deliberately simple illustration, not a forecast: actual rent, vacancy, expenses, and capital work usually vary by month.
Formulas for monthly and cumulative cash flow
With the column layout above and January’s inputs in B2:G2, enter this in H2. The illustrative row returns $380: $2,400 + $50 − $120 − $850 − $0 − $1,100.
=B2+C2-D2-E2-F2-G2
With monthly net cash flow in H2:H13 and the opening cumulative balance assumed to be zero, enter this in I2. The illustrative January result is $380.
=H2
With the January cumulative amount in I2 and February net cash flow in H3, enter this in I3 and fill down. If February also has $380 of net cash flow, I3 returns $760. The same formula then adds each new month to the prior running balance.
=I2+H3
With the twelve monthly totals in H2:H13, enter this annual-total formula in a summary cell. If every month is the illustrative $380, it returns $4,560.
=SUM(H2:H13)
These formulas show cash flow before items not included in the columns. Add clearly labeled rows or columns for income taxes, insurance escrow changes, owner contributions, or other relevant items when the purpose of the model requires them. Make sure a debt-payment input represents the payment amount you intend to model; principal and interest may be treated differently in accounting or tax contexts.
Choose useful assumptions, not false precision
A template is only as credible as its inputs. Start with a documented base case, and distinguish known figures from estimates. For a rental property, the lease may support scheduled rent, while bank records and invoices can help establish collected income and actual costs. If a property has a seasonal rental pattern, a flat monthly average can hide periods with little income and periods with concentrated expenses.
- Vacancy: estimate lost rent or uncollected income explicitly. Avoid subtracting vacancy twice—for example, once by reducing scheduled rent and again as a vacancy-loss line.
- Repairs and reserves: separate ordinary maintenance from major projects. A reserve is a planning assumption, not necessarily a bill paid each month; show it distinctly if you want to track cash set aside.
- Financing: enter the payment schedule that matches the loan assumptions. Confirm rate, payment timing, fees, and any adjustable-rate terms with the lender or loan documents.
- Timing: record expenses in the period cash is expected to leave the account if the workbook is a cash budget. A financial statement model may use different recognition rules.
For a basic sensitivity check, duplicate the base assumptions in a separate scenario area or worksheet and change a small set of inputs, such as rent, vacancy, or repairs. Label each scenario and keep the original assumptions intact. Avoid presenting a single optimistic case as though it were certain. Spreadsheet results are not financial, tax, investment, or legal advice; check actual loan terms with the lender and consult a qualified professional about matters that require professional advice.
Common spreadsheet problems and checks
The most common errors are usually input or structure problems, not complicated formula failures. A formula may calculate correctly while the model still omits a major cost or counts income twice. Review the worksheet by tracing a sample month from source documents through the final cash-flow result.
- Mixing NOI and cash flow: loan payments do not belong in an NOI calculation, but they matter to cash remaining after debt service. Label the subtotal that each measure represents.
- Omitting irregular costs: roofs, appliances, tenant turnover, and other lumpy costs can make a monthly average look smoother than real cash needs. Include plausible timing or a clearly disclosed reserve assumption.
- Inconsistent periods: do not compare annual rent with monthly expenses without converting one of them. Check that every input has the same monthly or annual basis.
- Broken references: after inserting or moving columns, inspect formulas and confirm they still point to the intended categories. Test a known month with a hand calculation.
- Confusing cash with profit: cash flow does not by itself determine taxable income, accounting profit, or investment performance. Those calculations can follow different rules.
Use a visible assumptions section, a source note, and a date for each update. You can also use Home > Conditional Formatting to highlight negative monthly cash flow, but confirm the rule covers the intended cells. Do not rely on color alone: retain readable labels and values for printing, accessibility, and later review.
Excel template, downloaded workbook, or property software?
Building a simple workbook costs time but gives you control over categories and assumptions. A downloaded template can save setup effort, but inspect its formulas, hidden rows, and definitions before relying on its totals; templates vary in what they count as income, operating expense, and cash flow. Some are free and others are sold, so check the provider’s terms and whether the workbook supports your Excel version.
Dedicated property-management or accounting software may be more suitable when you need bank feeds, tenant records, recurring invoices, document storage, or reports across many properties. Software can reduce manual entry but may involve subscription fees and setup. A spreadsheet is often a reasonable starting point for a small, transparent model; it is not automatically safer or more accurate than software. Keep a backup copy before making structural changes, and protect sensitive financial information when sharing a workbook.
Frequently Asked Questions
Can I use Excel to calculate rental property cash flow?
Yes. List income and cash outflows by month, then calculate the difference and, if helpful, a running balance. The result depends on which categories you include, so label whether it is before or after debt service, capital spending, taxes, or other items.
What expenses should a rental cash-flow spreadsheet include?
Common categories include management, insurance, property taxes, owner-paid utilities, maintenance, vacancy, capital work, and loan payments. The appropriate detail depends on the property and the question the model is meant to answer; do not treat a generic list as exhaustive.
Is cash flow the same as net operating income?
No. NOI generally excludes financing payments and capital expenditures, while a cash-flow budget may include them. State the definition used in your workbook so a reader can interpret the totals correctly.
Should vacancy be entered as a negative number?
Either convention can work. The example uses a positive vacancy-loss amount and subtracts it in the formula. Keep the convention consistent and check that vacancy is not already reflected in a reduced rent input.
Can one Excel template handle several properties?
Yes, but keep property data consistently structured and identify each property clearly. Separate sheets are easier to review for a small portfolio; a consolidated table can help with comparisons but requires careful checks to avoid mixing assumptions or totals.
Related
- Password Protect an Excel Document From Opening
- Password Protect an Excel Document to Open
- Password Protect an Excel File Before Opening It Safely
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets: How to Generate Unique IDsYes. Google Sheets can generate unique IDs with a row-based formula for simple lists, or with Google Apps…
- How to Apply Styles in a Word Document for Clear FormattingTo apply a style in Microsoft Word, click in a paragraph or select text, then choose a style…
- How to Present PowerPoint and See Your Speaker NotesTo present PowerPoint while keeping your notes visible, connect your computer to a second screen, set the displays…
- Out of Office in Outlook on the Web: A Setup GuideTo set up an out-of-office reply in Outlook on the web, open Settings, go to Automatic replies, turn…
- Excel Merge Cells Across Rows: Methods, Limits, TipsYes. In Excel for Microsoft 365 on Windows, select the cells spanning the rows, then choose Home >…
- Excel Keyboard Shortcut to Add a Row (Windows & Mac)In Excel for Microsoft 365 on Windows, select the row where the new row should go, then press…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.