Rental property ROI spreadsheet
How to work out return on investment for a rental property, with the formulas and a worked example.
Updated October 30, 2026
See the Rental Property Analysis Spreadsheet
What ROI means for a rental property
Return on investment is what you get back compared with what you put in. For a rental there is no single ROI, because the money comes back in three ways: monthly cash flow, the loan being paid down, and the property's value going up. Each common measure looks at a different part:
- Cap rate: how much the property earns compared with its price, ignoring the loan.
- Cash-on-cash return: how much cash you get each year compared with the cash you put in.
- IRR: the yearly return over the whole time you own it, including the sale.
Why one number is not enough
A property can have a good cap rate and poor cash flow, because the mortgage eats it. It can have thin cash flow and a good IRR, because most of the return comes at the sale. Looking at all three on the same page is how you see what kind of deal you are looking at, and how much of it depends on prices rising.
How to set up an ROI spreadsheet
Put each property in its own column so you can compare listings side by side. Split the sheet into inputs and results:
- Purchase: price, closing costs, repairs before renting, down payment, interest rate, loan term.
- Income: number of units, monthly rent per unit, other income, vacancy rate.
- Expenses: property tax, insurance, HOA fees and utilities as fixed amounts; maintenance, management and a reserve for big repairs as a percentage of collected rent.
- Growth and sale: rent growth, expense growth, value growth, selling costs, years you plan to hold.
Then a projection tab: one row per year with rent, expenses, cash flow, loan balance and value, ending with the sale. Excel and Google Sheets both have PMT for the mortgage payment and IRR for the return.
See the Rental Property Analysis Spreadsheet
The formulas
| Result | Formula |
|---|---|
| Monthly mortgage payment | PMT(rate/12, years*12, -loan) |
| Collected rent | Gross rent × (1 − vacancy) |
| Net operating income (NOI) | Collected rent − operating expenses |
| Cash flow | NOI − mortgage payments |
| Cap rate | NOI ÷ purchase price |
| Cash-on-cash return | Cash flow ÷ (down payment + closing costs + repairs) |
| Debt service coverage ratio | NOI ÷ yearly mortgage payments |
| Gross rent multiplier | Price ÷ yearly gross rent |
| Loan balance after n months | Loan × (1+r)n − payment × ((1+r)n − 1) ÷ r, with r the monthly rate |
| Sale proceeds | Value × (1 − selling costs) − loan balance |
Operating expenses never include the mortgage. That is what makes NOI and cap rate useful for comparing properties no matter how they are financed.
Cash, a mortgage, or a high rate
Buying with cash puts cash-on-cash return close to the cap rate, since there is no loan; it is a little lower because closing costs and repairs are part of the cash you put in. A mortgage lowers the cash you put in, so cash-on-cash can be higher, but only while the rate is low enough. When the interest rate is above the cap rate, each borrowed dollar costs more than it earns, and cash flow gets squeezed. That is the main thing to check when rates are high.
Worked example
A single-family house for $240,000. These are the sample numbers in the Reamwise analysis template.
| Input | Value |
|---|---|
| Price / closing costs / repairs | 240,000 / 7,200 / 8,000 |
| Down payment, rate, term | 20%, 7.00%, 30 years |
| Rent | 2,200 a month |
| Vacancy | 5% |
| Property tax / insurance | 3,000 / 1,500 a year |
| Maintenance / management / reserve | 5% / 8% / 5% of collected rent |
| Rent, expense and value growth | 3% a year each |
| Hold, then sell with 6% costs | 10 years |
| Result | Value |
|---|---|
| Loan | 192,000.00 |
| Monthly mortgage payment | 1,277.38 |
| Cash needed to buy | 63,200.00 |
| Collected rent, year 1 | 25,080.00 |
| Operating expenses, year 1 | 9,014.40 |
| NOI, year 1 | 16,065.60 |
| Cash flow, year 1 | 737.03 (61.42 a month) |
| Cap rate | 6.69% |
| Cash-on-cash return | 1.17% |
| Debt service coverage ratio | 1.05 |
| Total cash flow over 10 years | 30,888.40 |
| Sale proceeds after paying off the loan | 138,427.76 |
| IRR over 10 years | 11.20% |
The first year barely breaks even: $61 a month, a cash-on-cash return of about 1%. The 7% interest rate is above the 6.69% cap rate, so the loan takes almost all the income. The 11.2% IRR comes mostly from the sale, which assumes the house gains 3% a year for ten years. Change value growth to 0% and see how much of the return is left; that is the honest test of this deal.
Common mistakes
- Leaving out vacancy and repairs. Rent minus mortgage is not cash flow. Budget for empty months, maintenance and the big items like a roof.
- Putting the mortgage in operating expenses. Then NOI and cap rate stop meaning anything.
- Forgetting closing costs and repairs in cash invested. It makes cash-on-cash look better than it is.
- Trusting one growth rate. Try the deal at 0% value growth and with rent flat for a few years.
- Treating the 1% rule as a verdict. It is a quick screen for listings, not an analysis.
Questions
What is a good cap rate?
It depends on the area and the type of property. Compare a listing with similar properties nearby, and with your interest rate: a cap rate below the rate means the loan will squeeze cash flow.
What is a good cash-on-cash return?
There is no fixed number. Compare it with what the same cash could earn elsewhere with similar risk, and remember it ignores the sale and the loan paydown.
Why is IRR higher than cash-on-cash?
IRR includes the money from selling: the value growth and the loan you paid down. Cash-on-cash only counts the yearly cash flow.
Does the ROI include income tax?
Not in this method. Results are before income tax and before tax on the sale, because those depend on your country and situation.
What is the 1% rule?
A shortcut: monthly rent should be at least 1% of the price. The example house is at 0.92%. Use it to sort listings quickly, then run the full numbers.
Can I do this in Google Sheets?
Yes. PMT, IRR, INDEX and MATCH all work the same way in Google Sheets as in Excel.
Summary
Look at cap rate, cash-on-cash return and IRR together, because each sees a different part of the return. Keep the mortgage out of operating expenses, count every dollar of cash you put in, and test the deal with lower growth. The Rental Property Analysis Spreadsheet does these calculations for three properties at once and comes with this example built in.
Disclaimer. For general information only. Not tax, legal or financial advice. Check the rules for your country or ask a qualified adviser. Read the full disclaimer.