# 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.

---

- **Canonical URL:** https://dothecalculation.com/blog/templates/excel-rental-property-cash-flow-model
- **Category:** Templates
- **Author:** Do The Calculation Team
- **Published:** 2026-08-03
- **Reading time:** 12 min read
- **Publisher:** Do The Calculation (https://dothecalculation.com)
- **Methodology:** https://dothecalculation.com/methodology

---

## 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.

Tool: [Try the rental property ROI calculator](https://dothecalculation.com/calculators/rental-property-roi-calculator) — Get cash flow, cap rate, and cash-on-cash return from purchase, financing, and operating inputs.

## The deal

**Acquisition and financing**
| 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

**Effective gross income**

```
=MonthlyRent*12*(1−VacancyRate)
```
- $3,150 × 12 = $37,800 gross
- At a 7% vacancy allowance: $35,154
- Vacancy is not just empty months. It covers turnover, a month of make-ready, and the occasional non-paying tenant.

> **A vacancy rate is not a market statistic** — Do not use the neighbourhood vacancy rate. Use your own turnover expectation: one tenant change every two years, with six weeks between tenants, is about 6% before any bad debt. Long-term tenants make this look pessimistic right up until one leaves.

## Operating expenses, including the two people skip

**Annual operating expenses**
| 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.

> **Reserves are not maintenance** — Maintenance is the leaking tap. Reserves are the roof, the heating system, and the flooring, which fail rarely and expensively. A $12,000 roof on a 20-year life is $600 a year whether or not you spend it this year. A model with no reserve line is a model that will be surprised.

## What the model returns

**Year one**
| 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

**Year one, all four**
| 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.

**The total return line, broken out**

```
Cash flow −$3,552 + principal paydown $2,876 + appreciation at 3% $11,550 = $10,874
```
- Principal paydown: =-CUMPRINC(Rate/12, 360, LoanAmount, 1, 12, 0) returns $2,876 in year one.
- Appreciation is an assumption, not a return. At 0% growth the total return falls to −0.65%.
- The appreciation assumption is larger than the entire total return; strip it out and the figure is negative.

> **Report both, and label the assumption** — Cash-on-cash is what the property does. Total return is what you hope the market does. A deal that only works on appreciation is a bet on prices, and it should be described that way rather than as a rental investment.

## What would make this deal work

**One-variable sensitivity on year-one cash flow**
| 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.

Tool: [Try the property cash flow calculator](https://dothecalculation.com/calculators/property-cash-flow-calculator) — Run 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.

---

_Source: [Do The Calculation](https://dothecalculation.com/blog/templates/excel-rental-property-cash-flow-model). Quote freely with attribution and a link to this page._