📅 March 10, 2026 · ⏱ 15 min read · 🏠 Rental Property

How to Calculate Rental Property ROI in Google Sheets: Cap Rate, Cash Flow & Cash-on-Cash Return

A rental property that "looks profitable" on Zillow can lose you money once you run the real numbers. The problem isn't real estate — it's that most investors don't analyze deals properly before buying. Here's how to build a complete rental property ROI calculator in Google Sheets so you never buy a money pit disguised as an investment.

8–12%

Typical long-term rental ROI

5–8%

Healthy cap rate range

$200+

Target monthly cash flow/door

In This Guide

The 4 Key ROI Metrics Every Investor Needs
Gross Rental Yield: The Quick Screen
Net Operating Income (NOI): The True Profit Engine
Cap Rate: Comparing Properties Apples-to-Apples
Cash-on-Cash Return: How Hard Your Money Works
Building the Complete Calculator in Google Sheets
Every Formula You Need (Copy-Paste Ready)
Full Worked Example: Analyzing a $300K Rental
Scenario Analysis: What-If Modeling
Quick-Screen Rules of Thumb
6 ROI Calculation Mistakes That Cost Investors Thousands

The 4 Key ROI Metrics Every Investor Needs

You wouldn't buy a car based solely on the sticker price. You'd check fuel economy, maintenance costs, and reliability ratings. Rental properties are the same — the asking price tells you almost nothing about profitability.

Here are the four metrics that actually matter:

Metric	What It Measures	Good Benchmark
Gross Rental Yield	Raw annual rent as % of purchase price	8%+ (strong market), 6%+ (premium market)
Net Operating Income (NOI)	Rental income minus all operating expenses (excluding mortgage)	Positive, and trending up
Cap Rate	NOI ÷ Purchase Price — unleveraged return	5–8% (residential), varies by market
Cash-on-Cash Return	Annual pre-tax cash flow ÷ total cash invested	8%+ (good), 12%+ (excellent)

Each metric tells a different part of the story. Used together in a spreadsheet, they give you a complete financial picture of any deal before you commit.

Gross Rental Yield: The Quick Screen

Gross yield is the simplest way to filter properties. It takes about 10 seconds to calculate:

Gross Rental Yield = (Annual Rent ÷ Purchase Price) × 100

Example: ($2,400/mo × 12) ÷ $350,000 = 8.2%

Use this to quickly eliminate properties that don't meet your minimum threshold. If a property doesn't hit 6% gross yield, the numbers rarely work after expenses — especially with today's mortgage rates.

The 1% Rule (quick screen): Monthly rent should be ≥ 1% of the purchase price. A $300K property should rent for at least $3,000/month. It's not gospel, but it eliminates most duds fast.

Gross yield completely ignores expenses, though. A property with 9% gross yield and massive repair bills will underperform one at 7% with minimal maintenance. That's why we need the next metric.

Net Operating Income (NOI): The True Profit Engine

NOI strips away the noise and shows you what the property actually generates from operations, independent of how you financed it.

NOI = Effective Gross Income − Total Operating Expenses

Calculating Effective Gross Income

Start with maximum possible rent (Gross Scheduled Income), then adjust for reality:

Gross Scheduled Income (GSI) = Monthly Rent × 12
+ Additional Income (parking, laundry, pet fees, storage)
= Gross Potential Income

− Vacancy Allowance (typically 5–8%)
= Effective Gross Income (EGI)

A 5% vacancy rate is standard for single-family rentals in strong markets. Use 8–10% for multi-family or weaker markets. Never use 0% — no property stays occupied forever.

Operating Expenses to Include

Operating expenses are everything required to keep the property running except mortgage payments. This separation is critical because it lets you evaluate the property's performance independent of your financing decisions.

Expense Category	Typical Range	How to Estimate
Property Taxes	0.5–2.5% of value/year	County assessor website (exact number)
Insurance	$800–$2,500/year	Get quotes from 3+ providers
Maintenance & Repairs	1% of property value/year	Budget higher for older properties
Capital Expenditures (CapEx)	1% of property value/year	Roof, HVAC, water heater reserves
Property Management	8–10% of collected rent	Include even if self-managing (your time has value)
Landlord-Paid Utilities	$0–$300/month	Water, sewer, trash, common area electric
HOA Fees	$0–$500/month	Check listing details

⚠️ The 50% Rule: As a rough check, total operating expenses for residential rental properties typically run 35–50% of gross rental income. If your estimate is under 35%, you're likely underestimating costs. If your expenses look too low, you're setting yourself up for negative surprises.

Cap Rate: Comparing Properties Apples-to-Apples

Cap rate is the gold standard for comparing rental properties because it removes financing from the equation. Two investors can buy the same property with different loans and get different cash-on-cash returns — but the cap rate stays the same.

Cap Rate = (NOI ÷ Purchase Price) × 100

Example: $18,000 NOI ÷ $300,000 = 6.0% cap rate

What's a "Good" Cap Rate?

Cap Rate	What It Typically Means
3–4%	Premium market (NYC, SF, LA). Low yield, betting on appreciation.
5–6%	Solid suburban markets. Balanced risk/return.
7–8%	Strong cash flow markets. Higher yield, potentially more management.
9%+	High yield but investigate why — higher risk, rough neighborhood, or deferred maintenance?

Cap rate is market-relative. A 5% cap rate is excellent in Boston but mediocre in Indianapolis. Always compare within the same market.

Cash-on-Cash Return: How Hard Your Money Works

While cap rate ignores financing, cash-on-cash return focuses entirely on your actual out-of-pocket investment. This is arguably the most important metric for leveraged investors.

Cash-on-Cash Return = (Annual Pre-Tax Cash Flow ÷ Total Cash Invested) × 100

What Counts as "Total Cash Invested"?

Down payment (typically 20–25% for investment properties)
Closing costs (2–5% of purchase price)
Immediate repairs/rehab (anything needed before renting)

What's "Annual Pre-Tax Cash Flow"?

Annual Cash Flow = NOI − Annual Mortgage Payments (P&I)

This is the actual cash that hits your bank account each year, before taxes.

Worked Example

Purchase Price:      $300,000
Down Payment (20%):   $60,000
Closing Costs (3%):    $9,000
Total Cash Invested:  $69,000

NOI:                  $18,000
Annual Mortgage:      $17,172  ($240K loan @ 7%, 30yr = $1,431/mo)
Annual Cash Flow:        $828

Cash-on-Cash Return: $828 ÷ $69,000 = 1.2%

Ouch. At 7% mortgage rates, this $300K property barely breaks even. The cap rate (6%) looked reasonable, but once financing enters the picture, the return on your actual dollars invested is anemic. This is why you need both metrics.

The leverage trap: High mortgage rates dramatically compress cash-on-cash returns. In a 4% rate environment, the same property might yield 8%+ cash-on-cash. Always run the numbers with your actual financing terms — not idealized rates.

Track Your Rental Property Finances in Google Sheets

Our templates are built for landlords and real estate investors who want clear, organized financial tracking without expensive software. Check out the SheetStackStudio Etsy shop.

Browse Templates on Etsy →

Building the Complete Calculator in Google Sheets

Here's the tab structure for a professional rental property ROI calculator:

Tab 1: Property Inputs

Input Fields

Purchase price
Down payment percentage
Closing costs ($ or %)
Rehab/repair budget
Loan interest rate
Loan term (years)
Monthly rent
Additional monthly income (parking, storage, laundry)
Vacancy rate (%)
Annual property taxes
Annual insurance premium
Maintenance budget (% of value or flat $)
CapEx reserve (% of value or flat $)
Property management fee (%)
Monthly landlord-paid utilities
Monthly HOA fees

Color-code all input cells (light blue or light yellow) so you immediately know which cells to edit versus which auto-calculate.

Tab 2: Analysis Dashboard

This tab pulls every input and calculates all four ROI metrics automatically. Structure it in clear sections:

Income Section: GSI → Additional Income → Vacancy → EGI
Expense Section: All operating expenses totaled
NOI Section: EGI − Operating Expenses
Financing Section: Monthly mortgage payment, annual debt service
Returns Section: Gross Yield, Cap Rate, Cash Flow, Cash-on-Cash

Tab 3: Scenario Comparison

Use side-by-side columns to compare different deals or different financing options for the same property. This is where spreadsheets really shine over online calculators — you can see 3–5 properties next to each other instantly.

Every Formula You Need (Copy-Paste Ready)

Assume your Property Inputs tab has these cells:

B2 = Purchase Price
B3 = Down Payment %
B4 = Closing Cost %
B5 = Rehab Budget
B6 = Interest Rate (e.g., 0.07 for 7%)
B7 = Loan Term (years)
B8 = Monthly Rent
B9 = Additional Monthly Income
B10 = Vacancy Rate %
B11 = Annual Property Taxes
B12 = Annual Insurance
B13 = Maintenance % of value
B14 = CapEx % of value
B15 = Property Management %
B16 = Monthly Utilities (landlord-paid)
B17 = Monthly HOA

Loan Amount

=Inputs!B2 * (1 - Inputs!B3)

Total Cash Invested

=(Inputs!B2 * Inputs!B3) + (Inputs!B2 * Inputs!B4) + Inputs!B5

Monthly Mortgage Payment (P&I)

=PMT(Inputs!B6/12, Inputs!B7*12, -(Inputs!B2*(1-Inputs!B3)))

The PMT function is built into Google Sheets. It returns the monthly payment for a fixed-rate loan.

Gross Scheduled Income

=(Inputs!B8 + Inputs!B9) * 12

Effective Gross Income

=GSI * (1 - Inputs!B10)

Total Operating Expenses

=Inputs!B11
 + Inputs!B12
 + (Inputs!B2 * Inputs!B13)
 + (Inputs!B2 * Inputs!B14)
 + (EGI * Inputs!B15)
 + (Inputs!B16 * 12)
 + (Inputs!B17 * 12)

Net Operating Income (NOI)

=EGI - Total_OpEx

Gross Rental Yield

=GSI / Inputs!B2

Cap Rate

=NOI / Inputs!B2

Annual Cash Flow

=NOI - (Monthly_Mortgage * 12)

Monthly Cash Flow

=Annual_Cash_Flow / 12

Cash-on-Cash Return

=Annual_Cash_Flow / Total_Cash_Invested

Pro tip: Use named ranges (Data → Named ranges) for key cells like NOI, EGI, Total_Cash_Invested. This makes formulas human-readable instead of a sea of cell references.

Full Worked Example: Analyzing a $300K Rental Property

Let's run a realistic deal through the calculator:

Input	Value
Purchase Price	$300,000
Down Payment	20% ($60,000)
Closing Costs	3% ($9,000)
Rehab Budget	$5,000
Interest Rate	7.0%
Loan Term	30 years
Monthly Rent	$2,600
Additional Income	$50/mo (pet fee)
Vacancy Rate	5%
Property Taxes	$3,600/year
Insurance	$1,400/year
Maintenance	1% ($3,000/year)
CapEx Reserve	1% ($3,000/year)
Property Management	8%
Utilities (landlord)	$100/month (water/sewer)
HOA	$0

Step-by-Step Calculation

Line Item	Monthly	Annual
Gross Scheduled Income	$2,650	$31,800
Less: Vacancy (5%)	−$132	−$1,590
Effective Gross Income	$2,518	$30,210
Operating Expenses
Property Taxes	$300	$3,600
Insurance	$117	$1,400
Maintenance (1%)	$250	$3,000
CapEx Reserve (1%)	$250	$3,000
Property Management (8%)	$201	$2,417
Utilities	$100	$1,200
Total Operating Expenses	$1,218	$14,617
Key Metrics
Net Operating Income (NOI)	$1,299	$15,593
Mortgage Payment (P&I)	$1,597	$19,164
Cash Flow	−$298	−$3,571

The Verdict

Metric	Result	Assessment
Gross Rental Yield	10.6%	✅ Looks great on the surface
Cap Rate	5.2%	⚠️ Average — acceptable in many markets
Monthly Cash Flow	−$298	❌ Negative — you're paying out of pocket
Cash-on-Cash Return	−4.8%	❌ Losing money on invested capital

This property has a decent gross yield and passable cap rate, but negative cash flow at current mortgage rates. The 7% interest rate eats all the profit and then some. This is a common pattern in 2026 — properties that would have been winners at 4% rates are losers at 7%.

What Would Make This Deal Work?

Higher rent: $2,900/month flips cash flow to +$153/month
Lower price: $260,000 gets you to breakeven
Larger down payment: 30% down produces +$80/month cash flow
Lower rate: At 5.5%, cash flow is +$143/month

This is exactly why the scenario comparison tab is so valuable — you can model all these adjustments in seconds.

Scenario Analysis: What-If Modeling

The real power of a spreadsheet over a web calculator is scenario modeling. Set up columns for different assumptions and instantly see how changes affect your returns:

Scenarios to Model

Scenario	What to Change	Why It Matters
Best case	Low vacancy (3%), rent increase	Upside potential
Base case	Your realistic estimates	Most likely outcome
Worst case	High vacancy (10%), major repair	Can you survive this?
Rate drop	Refinance at lower rate in 2–3 years	If rates decline, what happens?
Rent growth	3–5% annual rent increase	5-year projected returns

Duplicate your analysis tab for each scenario, or build a comparison table that pulls results from different input columns. The key question: can you survive the worst case while you wait for the base case?

Quick-Screen Rules of Thumb

Before building a full spreadsheet analysis, use these filters to quickly eliminate non-starters:

Rule	Formula	Pass/Fail
1% Rule	Monthly Rent ÷ Purchase Price ≥ 1%	$300K property needs ≥ $3,000/mo rent
50% Rule	Assume 50% of rent goes to expenses	$2,600 rent → $1,300 for expenses → $1,300 for mortgage + profit
70% Rule (flips)	Max Offer = ARV × 70% − Repair Costs	For fix-and-flip properties
GRM (Gross Rent Multiplier)	Purchase Price ÷ Annual Rent	Lower is better. Under 12 is generally attractive.

These rules are screening tools, not analysis tools. They help you decide which properties deserve the full spreadsheet treatment. A property that fails the 1% rule can still be profitable, and one that passes can still lose money. Always run the complete numbers.

6 ROI Calculation Mistakes That Cost Investors Thousands

1. Forgetting Vacancy

Assuming 100% occupancy inflates your numbers and sets you up for cash flow crunches. Budget 5–8% minimum — even in hot markets, you'll have turnover gaps.

2. Skipping CapEx Reserves

A new roof costs $8,000–$15,000. An HVAC replacement runs $5,000–$10,000. If you don't set aside reserves monthly, a single repair can wipe out years of cash flow. Budget 1% of property value per year for CapEx.

3. Ignoring Property Management Costs

Even if you self-manage, include 8–10% for property management in your analysis. Why? Because your time has value, and someday you may want to hire it out. If the deal only works with free labor, it's not a good deal.

4. Using the Listing Price as Purchase Price

Your actual cost includes closing costs (2–5%), inspection fees, appraisal, and any immediate repairs. A "$300K" property might actually cost you $320K all-in. Use the total acquisition cost in your calculations.

5. Not Accounting for Tax Benefits

Rental properties offer significant tax advantages — depreciation, mortgage interest deduction, the $25K passive loss allowance, and Section 199A. Your after-tax return can be substantially better than your pre-tax cash-on-cash number. Factor in tax benefits for a complete picture.

6. Analyzing at Today's Rent Only

If rents in your market grow 3–5% per year, a property that barely breaks even today could be cash-flowing $300+/month in three years — with the same mortgage payment. Model rent growth over a 5-year horizon, not just day one.

Your Property Analysis Checklist

Run the 1% rule as a quick screen
Build a Google Sheets calculator with all four ROI metrics
Include vacancy (5%+), maintenance (1%), CapEx (1%), and management (8%+)
Use your actual financing terms, not idealized rates
Calculate total cash invested (down payment + closing + rehab)
Run best-case, base-case, and worst-case scenarios
Model 3–5 year rent growth projections
Compare against alternative investments (stocks, bonds, index funds)
Don't forget tax benefits: depreciation, mortgage interest, $25K allowance
Never buy on emotion — let the spreadsheet decide

The Bottom Line

Real estate investing is math, not magic. The investors who consistently profit are the ones who analyze every deal the same way — methodically, conservatively, and with a spreadsheet that doesn't lie to them.

Build your Google Sheets ROI calculator once, and use it for every property you evaluate. Over time, you'll develop an intuition for what works in your market — but that intuition should always be backed by the numbers.

And remember: a deal that doesn't work at current rates isn't automatically bad. It might be a great property to revisit if rates drop — or if you can negotiate a better price. The spreadsheet will tell you exactly what price or rate makes it work.

Need a Ready-Made Financial Tracking Template?

SheetStackStudio builds Google Sheets templates for freelancers, landlords, and goal-setters. Clean design, automated formulas, instant clarity on your finances.

Visit the Etsy Shop →