A Rental Property Cash Flow Model in Excel That Does Not Flatter the Deal
Most rental spreadsheets omit vacancy, management, and capital reserves, which is how a property that loses $296 a month shows a profit. Here is the full model on one deal, with the four return figures it should produce.
Three Lines That Decide Whether the Deal Works
Rent minus mortgage is not cash flow. The three lines that separate a spreadsheet from a fantasy are vacancy, property management, and capital reserves, and they are the three most commonly left out.
Together they take about 20% of gross rent. Omit them and a marginal deal looks comfortable. This article builds the model with them in, on a deal that turns out not to work on cash flow.
Try the rental property ROI calculatorGet cash flow, cap rate, and cash-on-cash return from purchase, financing, and operating inputs.The deal
Swipe sideways to compare columns.
| Item | Value |
|---|---|
| Purchase price | $385,000 |
| Down payment, 25% | $96,250 |
| Closing costs | $8,000 |
| Total cash invested | $104,250 |
| Loan amount | $288,750 |
| Rate and term | 7.10%, 30 years |
| Monthly principal and interest | $1,940.51 |
Income, after vacancy
Operating expenses, including the two people skip
Swipe sideways to compare columns.
| Line | Basis | Amount |
|---|---|---|
| Property taxes | From the public record | $5,400 |
| Insurance | From a quote, not an estimate | $1,850 |
| Property management | 8% of effective gross income | $2,812 |
| Repairs and maintenance | 6% of gross rent | $2,268 |
| Capital reserves | 5% of gross rent | $1,890 |
| Landscaping and common utilities | Contract | $1,200 |
| Total operating expenses | $15,420 | |
| Operating expense ratio | of effective gross income | 43.9% |
Management and reserves total $4,702, or 12.4% of gross rent. Self-managing does not remove the management line; it converts it into your unpaid time, and it should stay in the model so the deal is judged on its own merits rather than on your labour.
What the model returns
Swipe sideways to compare columns.
| Line | Annual | Monthly |
|---|---|---|
| Gross scheduled rent | $37,800 | $3,150 |
| Less vacancy at 7% | −$2,646 | −$221 |
| Effective gross income | $35,154 | $2,930 |
| Less operating expenses | −$15,420 | −$1,285 |
| Net operating income | $19,734 | $1,645 |
| Less debt service | −$23,286 | −$1,941 |
| Cash flow | −$3,552 | −$296 |
The deal loses $296 a month. A spreadsheet without vacancy, management, and reserves would have shown $3,150 minus $1,941 minus $604 of taxes and insurance, or a positive $605 a month. The three omitted lines are the entire difference between those two answers.
The four returns the model should report
Swipe sideways to compare columns.
| Measure | Formula | Result |
|---|---|---|
| Cap rate | =NOI/PurchasePrice | 5.13% |
| Cash-on-cash return | =CashFlow/CashInvested | −3.41% |
| Debt service coverage ratio | =NOI/DebtService | 0.85 |
| Total return including equity | =(CashFlow+Principal+Appreciation)/CashInvested | 10.43% |
The coverage ratio of 0.85 is the one a lender looks at. Below 1.0 the property does not cover its own debt, and most commercial lenders want 1.20 or better. On a residential loan underwritten against your income rather than the property, nobody will stop you.
What would make this deal work
Swipe sideways to compare columns.
| Change | Cash flow, monthly |
|---|---|
| As modelled | −$296 |
| Rent $3,400 instead of $3,150 | −$110 |
| 35% down instead of 25% | −$37 |
| Rate 6.10% instead of 7.10% | −$105 |
| Price $355,000 instead of $385,000 | −$145 |
| Self-manage, removing the management line | −$62 |
No single change fixes it, which is the honest finding. Two together do. That is a much more useful output than a single verdict, and it is what a one-variable data table across each input gives you in about a minute.
Try the property cash flow calculatorRun the same structure on your own numbers before building the workbook.Extending to multiple years
One year is a snapshot. A ten-column projection with rent growing at 3%, expenses at 3.5%, and taxes at whatever your jurisdiction actually does is where the deal either turns positive or does not.
- Escalate rent and expenses separately. Expenses usually grow faster, which compresses cash flow over time.
- Debt service is flat on a fixed-rate loan, which is why cash flow improves in real terms even when it starts negative.
- Add a turnover cost every second or third year rather than smoothing it into vacancy, since it lands in one year.
- Carry the loan balance forward with CUMPRINC per year so equity accumulates correctly.
- Add a sale in the final year: value at your growth assumption, less selling costs, less the loan balance, gives the exit proceeds.
What the model does not know
- Tax. Depreciation, interest deductibility, passive loss rules, and depreciation recapture on sale can change the after-tax result substantially and are specific to your circumstances.
- Whether the expense estimates are right. Percentage rules of thumb are placeholders. The tax record, an insurance quote, and a real management proposal are facts.
- Condition. A model cannot see a roof with three years left, and the reserve line is an average that says nothing about timing.
- The tenant. One eviction can cost more than a year of modelled cash flow, and no percentage allowance covers it properly.
- Financing risk. A fixed rate makes the model stable; an adjustable rate or a balloon puts a date in the future where the whole thing is recalculated.
- Whether the rent is achievable. Modelled rent should come from comparable listings that actually let, not from asking prices.
- Liquidity. A property with negative cash flow needs the shortfall funded every month regardless of what the total return column says.
What vacancy rate should I use?
Build it from your own turnover expectation rather than a market figure. One tenant change every two years with six weeks to re-let is about 6%. Add for bad debt, and use a higher figure in a market with short tenancies. Anything below 5% is optimistic for a single-family rental.
Should I include property management if I manage it myself?
Yes. It keeps the model honest about the property rather than about your labour, and it protects the deal if you later need to hand it over. Self-managing then shows up as a return on your time rather than as a property that only works while you work.
How much should I budget for capital reserves?
Better to build it from the components than to use a percentage. List the roof, heating, water heater, flooring, and appliances with a replacement cost and a remaining life, and divide. On most single-family homes this lands somewhere between 4% and 8% of gross rent, and it is higher on older properties.
Is negative cash flow always a bad deal?
Not automatically, but it should be a deliberate choice. Negative cash flow means funding the property from other income while betting on appreciation and principal paydown. That can work. It is a different risk profile from a property that pays for itself, and it should be described accurately.
What is a good cash-on-cash return?
It has to beat what the same cash would do elsewhere at a comparable risk, which is a personal benchmark rather than a market one. Many investors look for 8% or more on residential rentals. What matters more is whether the figure was computed with vacancy, management, and reserves in it.
How do I calculate principal paid in year one?
Use =-CUMPRINC(rate/12, 360, loan, 1, 12, 0). On this loan it returns $2,876. Note the leading minus, since CUMPRINC returns a negative value, and note that it requires the Analysis ToolPak in very old versions.
Written by
Do The Calculation Team
Do The Calculation
Do The Calculation is built by a small team of data analysts and spreadsheet developers. Where a guide depends on a published formula, standard, or government rule, the calculator it links to names that source directly so you can check the number yourself.
About the team