Rental Property Cash Flow Calculator: The Formula, Worked Through
Rental property cash flow is the rent you collect, minus vacancy, minus every operating cost (taxes, insurance, maintenance, a capital reserve and management), minus the mortgage payment. On an illustrative $250,000 house renting for $2,500 a month, bought with 25% down at 7%, that comes to $781 a year - about $65 a month. Leave the reserves out and the same house appears to make $9,481. Nothing about the house changed between those two numbers. Only what was counted.
The formula
Every rental property cash flow analysis is the same three steps:
- Net operating income (NOI) = gross rent - vacancy - maintenance - capital reserve - management - property tax - insurance (- HOA, if any)
- Debt service = the mortgage payment (principal and interest) x 12
- Cash flow = NOI - debt service
Three ratios come out of those numbers, and they answer different questions:
- Cap rate = NOI / purchase price. The return on the property as if you paid cash.
- Cash-on-cash = cash flow / cash you put in (down payment plus closing costs). The return on your money, after the loan.
- DSCR (debt service coverage ratio) = NOI / debt service. Below 1.00, the property does not cover its own mortgage.
Worked example: one house, counted two ways
The inputs: a $250,000 purchase, $2,500 a month in rent (exactly 1% of the price, the old “1% rule”), 25% down ($62,500), a $187,500 loan at 7% over 30 years, property tax of 1.5% of the price ($3,750 a year) and $1,800 a year of insurance. The monthly mortgage payment works out to $1,247.44.
The reserve lines are set as shares of gross rent: vacancy 7%, maintenance 8%, capital reserve 6% and management 8% - 29% in total. These are illustrative allowances, not market figures; your own should come from the property’s age, your local rental market and quotes you can get.
| Line | Reserves left out | Counted properly |
|---|---|---|
| Gross rent ($2,500 x 12) | $30,000 | $30,000 |
| Vacancy (7%) | $0 | -$2,100 |
| Maintenance (8%) | $0 | -$2,400 |
| Capital reserve (6%) | $0 | -$1,800 |
| Management (8%) | $0 | -$2,400 |
| Property tax | -$3,750 | -$3,750 |
| Insurance | -$1,800 | -$1,800 |
| Net operating income | $24,450 | $15,750 |
| Mortgage, principal and interest | -$14,969 | -$14,969 |
| Annual cash flow | $9,481 | $781 |
| Per month | $790 | $65 |
| Cap rate | 9.78% | 6.30% |
| Cash-on-cash | 13.5% | 1.1% |
| DSCR | 1.63 | 1.05 |
Counted properly, this house clears $781 a year. Leave the four reserve lines out and it shows $9,481 - about 12 times as much. The difference is exactly the $8,700 of reserves ($725 a month) that the left-hand column pretends will never be spent.
Look at the cash-on-cash row too. On the $70,000 invested, the honest column earns 1.1%. The left-hand column claims 13.5%, which is the kind of number that gets a deal bought.
Why “cash-flowing” can mean almost nothing
The vacancy, maintenance and capital reserve lines are not money you spend every month. They are averages of money you will spend unevenly: a tenant leaves and the unit sits empty, a water heater fails, the roof reaches the end of its life. In a month when nothing breaks, the left-hand column looks like the truth. That is why it is easy to believe.
But the honest margin on this house is thin. The $781 of real cash flow is only 2.6% of gross rent, or about 0.3 months of rent. That means:
- If vacancy runs at 9.6% instead of the 7% allowed, the year’s cash flow is zero.
- One more empty month than the allowance - a single extra month - turns the year to -$1,719.
A deal that “cash-flows $790 a month” and a deal that has $65 a month of slack against everything going slightly wrong are the same deal. The reserves are what decide which one you own.
Why cap rate can’t tell you this
Cap rate is calculated from NOI, before the mortgage. So it doesn’t move when interest rates move. Here is the same house at five different rates:
| Rate | Cap rate | Mortgage per year | DSCR | Cash flow per year | Cash-on-cash |
|---|---|---|---|---|---|
| 4% | 6.30% | $10,742 | 1.47 | $5,008 | 7.2% |
| 5% | 6.30% | $12,078 | 1.30 | $3,672 | 5.2% |
| 6% | 6.30% | $13,490 | 1.17 | $2,260 | 3.2% |
| 7% | 6.30% | $14,969 | 1.05 | $781 | 1.1% |
| 8% | 6.30% | $16,510 | 0.95 | -$760 | -1.1% |
The cap rate is 6.30% in every row. Meanwhile cash flow falls from $5,008 at 4% to -$760 at 8%, and DSCR drops from 1.47 to 0.95 - below 1.00, where the rent no longer covers the loan. That is why two people can look at one listing and disagree completely: one is quoting cap rate, the other is living with the mortgage.
It is also why the 1% rule is not a cash flow test. This house meets the rule exactly, cash-flows at 7%, and loses money at 8%. A rule of thumb that never asks for an interest rate is quietly assuming one. The house flipping calculator post tests the rule of thumb flippers use to set an offer.
And notice the size of the two levers. Going from 6% to 7% adds $1,479 a year to the mortgage on this loan. The reserves are $8,700 a year - about 5.9 times as much. If you get one thing right in a rental property cash flow analysis, get the reserves right before you worry about the rate.
Building a rental property cash flow spreadsheet in Excel
You don’t need a template to do this. Put the inputs in column B:
| Cell | Input | Example |
|---|---|---|
| B1 | Purchase price | $250,000 |
| B2 | Monthly rent | $2,500 |
| B3 | Down payment | 25% |
| B4 | Interest rate | 7% |
| B5 | Loan term, years | 30 |
| B6 | Annual property tax | $3,750 |
| B7 | Annual insurance | $1,800 |
| B8 | Reserves + management, share of rent | 29% |
| B9 | Closing costs, share of price | 3% |
Then the outputs:
| Cell | Output | Formula |
|---|---|---|
| B11 | NOI | =B2*12*(1-B8)-B6-B7 |
| B12 | Debt service | =-PMT(B4/12,B5*12,B1*(1-B3))*12 |
| B13 | Annual cash flow | =B11-B12 |
| B14 | Cap rate | =B11/B1 |
| B15 | DSCR | =B11/B12 |
| B16 | Cash-on-cash | =B13/(B1*(B3+B9)) |
With the example inputs, B11 should read $15,750, B12 $14,969 and B13 $781. Then set B8 to zero and watch B13 jump to $9,481. That jump is the whole lesson. The same formulas work in Google Sheets, Numbers and LibreOffice.
If the property has an HOA fee, subtract it in B11 alongside tax and insurance. If you plan to self-manage, it is still worth keeping a management line: it is the price of your own time, and the cost you would face if you ever handed the property over.
How these were calculated
NOI is gross annual rent less each reserve line (a share of gross rent), less property tax and insurance. Debt service is a standard fully amortizing 30-year fixed-rate payment, times twelve; property tax, insurance and rent are held flat. Cash invested is the down payment plus closing costs of 3% of the price. Every figure on this page was computed twice: once by the code that built the products below, and once by an independent program that finds the mortgage payment by simulating the loan month by month rather than using the payment formula. The two agree to the cent. The house, rent, rate and reserve percentages are illustrative, not typical or recommended values, and none of this is financial, tax or investment advice.
If you want to run this on a property you are actually looking at, the workbook below has each of these steps as its own sheet - including the side-by-side “counted two ways” comparison and the rate table - with every formula checked against an independent implementation.