How to Calculate ROI on a Rental Property (Spreadsheet)

A rental ROI calculation looks simple until you start filling in the expense rows. Most online calculators go wrong there. They use 2 percent for repairs, skip capex entirely, and assume 5 percent vacancy in every market. The result is a fantasy return that makes any property look like a winner.

This walkthrough covers the three metrics that matter and the inputs that drive them. The worked example uses a $250,000 duplex. The goal is not to tell you whether to buy it - it is to show you how to model it honestly.

Month-to-month cash flow tracking once you own a property is a different problem. We covered that in the rental property cash flow spreadsheet piece. This one is about analyzing the deal before you sign.

What “ROI” actually means for a rental

The phrase “return on investment” gets used for at least three different things in real estate. Mixing them up is how people end up surprised.

Cap rate is net operating income divided by purchase price. NOI is rent minus operating expenses, before debt service. A 6 percent cap rate means the building, free and clear, throws off 6 percent of its price per year in operating income.

Cash-on-cash return is annual pre-tax cash flow divided by cash invested. Cash invested is down payment plus closing costs plus upfront repairs. Cash flow is NOI minus mortgage payment. This is the metric that includes financing.

Total return layers in everything else: mortgage principal paid down, appreciation (if any), and the tax benefits of depreciation. The most complete picture, and the easiest to fudge - appreciation is a guess.

Cap rate gets quoted in listings. Cash-on-cash is what hits your bank account. Total return is what matters over a decade. Different metrics, different jobs.

The spreadsheet inputs

A workable rental analysis spreadsheet has around 25 input cells:

Everything else is calculated. Gross rent minus vacancy gives effective gross income. EGI minus operating expenses gives NOI. NOI minus debt service gives pre-tax cash flow. Add principal paydown and appreciation for total return.

The structure is the easy part. The four variable expenses (vacancy, repairs, capex, management) are the hard part. That is where templates get fudged.

Cap rate, formula and limits

Cap rate is the cleanest number to calculate.

Cap rate = NOI / Purchase price

NOI excludes mortgage payments, income tax, and depreciation. It includes all operating expenses: tax, insurance, repairs, capex, vacancy, management. Some analysts exclude capex (treating it as capital, not operating); we include it because capex is real money leaving the bank account over a long enough horizon.

Cap rate captures the asset’s intrinsic return. Useful for comparing properties regardless of financing, and for comparing real estate to other asset yields like Treasury rates or dividend income.

What it misses: leverage. A 6 percent cap rate with a 4 percent mortgage produces leveraged returns that look nothing like the same cap rate with a 7 percent mortgage. Cap rates also vary by market and property class - a class-A property in a tier-one metro might trade at 4 percent; a class-C duplex in a smaller city at 10 percent. The difference reflects risk, growth expectations, and management intensity.

Cash-on-cash return

This is the one most small investors track month to month.

Cash-on-cash = Annual pre-tax cash flow / Total cash invested

Cash invested includes the down payment, closing costs (typically 2 to 5 percent of purchase price), and upfront repairs to make the property rentable.

Cash-on-cash captures what your money is earning right now, with this financing. It is the metric for comparing a rental to keeping the money in index funds or a high-yield savings account.

What it misses: principal paydown (the mortgage balance shrinks every month, even if no cash hits your account), appreciation, and tax effects. Over a 10-year hold, cash-on-cash is often the smallest piece of total return.

It also misses that expenses tend to rise faster than rents early on. A 7 percent cash-on-cash in year one can drop to 4 percent in year three if property tax reassessments and insurance renewals come in hot. The mortgage payment is the only fixed line on the sheet.

Total return with appreciation

For a longer view, total return rolls in three things cash-on-cash ignores.

Principal paydown. Every mortgage payment pays down some principal. Early years it is the smaller piece; later years it dominates. Over a 10-year hold of the $250k example with a $200k loan at 7 percent, total principal paid is roughly $24,000. Real equity, whether the property appreciates or not.

Appreciation. Long-term US housing has averaged 3 to 4 percent nominal appreciation per year, per the FHFA House Price Index and Case-Shiller data. Real (inflation-adjusted) appreciation is much closer to 1 percent. Some metros do far better; some do worse. Conservative modeling uses 2 to 3 percent nominal; aggressive uses 5 percent or more.

Tax benefits. Depreciation reduces taxable rental income (residential property is depreciated straight-line over 27.5 years per IRS Publication 527). No cash produced, but the tax bill on cash you do receive drops.

Two things to flag before the worked example: only the cash flow piece hits your bank account. Principal paydown is real but trapped until you sell or refinance. Appreciation is a guess. If only the appreciation-included version of total return makes the deal look good, that is a flag.

The four expense rows rental ROI templates fudge

These four assumptions drive your output. Bad ones produce fantasy returns.

Vacancy: 5 to 10 percent. Five percent is roughly one month every other year on a single-family rental in a stable market. Ten percent fits class-C properties, turnover-heavy markets, or units priced over-market. Calculators that default to 3 percent or skip vacancy entirely are not modeling real life - tenant turnover involves cleaning, listing time, and showing time even when the next tenant signs the day after.

Repairs and maintenance: 5 to 10 percent of rent. The ongoing stuff: leaky faucets, broken appliances, paint. Newer properties (under 10 years) run nearer 5 percent; older properties (50+ years) hit 10 to 15 percent in years with surprises. A common shortcut is “1 percent of property value per year” - on a $250k property that is $2,500, or roughly 10 percent of $25,000 annual rent.

Capex: 5 to 10 percent of rent. The big-ticket replacement reserve: roof, HVAC, water heater, flooring, kitchen, bath. None of these happen every year; all of them happen on a 15-to-30-year cycle. Amortize the lifetime cost annually:

On a property with $25,000 annual rent, that is 10 percent. Spreadsheets that skip capex or roll it into a 2 percent repair allowance are pretending it does not exist. It does.

Property management: 8 to 12 percent of rent. Self-management is “free” except for time. Hired-out management typically runs 8 to 12 percent of collected rent, plus leasing fees (often half to one month’s rent per new tenant). Modeling 0 percent only makes sense if you plan to self-manage for the entire hold, including evictions and after-hours calls.

Conservative spreadsheets sum these variable expenses to 25 to 35 percent of gross rent. Aggressive ones use 10 to 15 percent. The same property can show 8 percent cash-on-cash in the aggressive model and 1 percent in the conservative one. Both can be written with a straight face. Neither is necessarily wrong; they answer different questions.

A worked example: $250k duplex

A $250,000 duplex, two units at $1,050 and $1,100 per month.

Inputs:

• Purchase price: $250,000; closing costs: $6,250; upfront repairs: $4,800
• Total cash invested: $61,050 (20% down + closing + repairs)
• Loan: $200,000 at 7.0% on 30-year term; monthly P&I $1,331
• Gross monthly rent: $2,150
• Property tax: $3,600/yr; insurance: $1,400/yr; lawn/snow: $600/yr; HOA: $0
• Vacancy: 7%; repairs: 8%; capex: 8%; management: 0% (self-managed)

Calculation:

Cap rate: $14,266 / $250,000 = 5.7%

Cash-on-cash: -$1,706 / $61,050 = -2.8%

The property loses money on cash flow at these inputs. That is not unusual in 2026; with mortgage rates where they are, plenty of properties at retail prices don’t cash flow on the operating side. The total-return view changes the picture: principal paydown in year one is roughly $2,000, and 3 percent appreciation on $250k is $7,500 unrealized. Net of the $1,706 cash flow loss, total economic return is around $7,800 in year one - about 12.8 percent of cash invested. The deal “works” only if the appreciation arrives.

Now rerun the same property with aggressive assumptions: 3 percent vacancy, 3 percent repairs, no capex. NOI jumps to roughly $18,700. Cap rate: 7.5 percent. Cash-on-cash: 4.4 percent. Same property, different story.

The honest version probably understates a touch. The aggressive version overstates a lot. Reality sits somewhere in between, and the spreadsheet that lets you see both is more useful than the one that gives you a single confident number.

Depreciation and the after-tax view

Depreciation is the tax benefit specific to real estate. Residential rental property is depreciated straight-line over 27.5 years on the building value (not the land). A $250,000 property with $50,000 land value depreciates the $200,000 building at $7,273 per year.

That $7,273 reduces taxable rental income, not cash flow. If a rental shows small positive cash flow, depreciation often pushes the taxable result into a paper loss. At a 22 percent marginal bracket, a $2,000 paper loss offsets $440 of other income (subject to passive activity rules that limit deductibility above certain incomes).

Depreciation recapture comes due when you sell. The IRS taxes accumulated depreciation at a 25 percent rate on sale. The tax-deferral story is real but not free.

Most rental ROI spreadsheets keep cash flow as the primary line and show after-tax return as a derived row. Cash flow funds life month to month; tax benefits show up once a year, on paper.

The 1% rule and other shortcuts

The 1 percent rule says monthly gross rent should be at least 1 percent of purchase price. On a $250,000 property, that means $2,500 a month. The duplex above at $2,150 misses it.

Treat it as a screening shortcut, not a rule. In low-cost markets (Midwest, South), 1 percent is achievable and properties that hit it often cash flow at reasonable assumptions. In high-cost coastal markets, almost nothing hits 1 percent, and the investment thesis shifts to appreciation. The rule is a fast filter for cash flow markets, useless in appreciation markets.

The 50 percent rule says operating expenses (excluding mortgage) settle near 50 percent of gross rent over time. On our duplex with $25,800 gross rent, expenses come in at $11,592 or 45 percent - close enough. Rougher than the 1 percent rule but useful when expense data is missing.

The gross rent multiplier (purchase price divided by annual gross rent) is another shortcut. The duplex is at 9.7x. Anything above 12-15x tends to be an appreciation play rather than cash flow.

Shortcuts filter properties before the full spreadsheet. They do not replace it.

When the analysis template runs out

Modeling a deal before you buy is one job. Tracking actuals once you own it is another. The analysis above uses estimates; the operational tracker uses real numbers.

The Rental Property Cashflow template ($19) covers a single property. Rent, mortgage payment, property tax, insurance and HOA go in as monthly amounts, then vacancy, maintenance reserve and property management go in as percentages, and the sheet returns cap rate, cash-on-cash, NOI and an expense ratio. There is no separate capex line, so a replacement reserve lives inside the maintenance percentage, and its cash-on-cash divides annual cash flow by the down payment rather than by total cash invested, which leaves closing costs and upfront repairs outside that ratio. For short-term rentals where income is per-night and seasonal, the Airbnb Rental Tracker ($19) logs each booking with check-in and check-out dates, nights, nightly rate, cleaning fee, platform fees and expenses, which long-term rental templates skip.

The Rental Property Cashflow template (Essentials tier, $19) surfaces annual cashflow, monthly net, cap rate, cash-on-cash ROI, NOI and expense ratio from the Property Setup inputs, with a monthly cash flow chart underneath.

Get the template

Three need-based options:

• “Running the numbers on one property” - the Rental Property Cashflow template ($19) turns the rent, mortgage, tax, insurance, HOA, vacancy, maintenance and management inputs into cap rate, cash-on-cash, NOI and expense ratio, with a 12-month row for recording what actually came in.
• “Tracking short-term rentals booking by booking” - the Airbnb Rental Tracker ($19) reports gross income, net income, total fees, bookings, nights booked and average nightly rate from a booking log.
• “Running a business with rental income as one piece” - the Cash Flow Forecast ($29) covers a 12-month forecast alongside actuals, with a cash reserves figure for how many months current cash would cover if income stopped.

The two rental templates are Excel files that also work in Google Sheets and LibreOffice. The Cash Flow Forecast is a Google Sheets file. All three are one-time purchases.

Related

• Rental Property Cash Flow Spreadsheet (Excel, Per-Unit Tabs) - operational tracker for after you buy
• Real Estate Net Worth: Valuing Your Property - estimating market value for net worth
• Investment Returns Calculator: Setting Realistic Expectations - comparing rental returns to other asset classes