How I Built a Rental Property ROI Calculator in Google Sheets

When I started investing in rental properties, I spent more time doing math than analyzing deals. Every property required calculating cap rates, cash-on-cash returns, GRM, DCR... the list goes on. And if I wanted to compare multiple properties? That meant rebuilding everything from scratch for each one.

So I built a Google Sheets template that does all of it automatically.

The 8 Metrics That Matter

Most real estate investors focus on 1-2 metrics and miss the big picture. Here's what your rental property analysis should include:

1. Cash-on-Cash Return

This tells you the actual cash return on your cash investment. Formula: Annual Pre-Tax Cash Flow / Total Cash Invested

2. Cap Rate

The property's raw earning power, independent of financing. Formula: Net Operating Income / Purchase Price

3. Gross Rent Multiplier (GRM)

A quick valuation check — lower means potentially better value. Calculate: Purchase Price / Annual Gross Rent

4. Debt Coverage Ratio

Bankers use this — and so should you. It tells you if the property can pay its own mortgage.

5. 5-Year Total ROI

Combines cash flow and appreciation into one forward-looking number.

The Automated Spreadsheet

I created a 5-tab Google Sheets template that calculates all 8 metrics for up to 20 properties side by side. Here's how it works:

Tab 1: Input Sheet — Enter purchase price, financing terms, rent, expenses. That's it.

Tab 2: Monthly Cash Flow — Automatically calculates NOI, mortgage payment, and pre-tax cash flow using built-in PMT formulas.

Tab 3: Key Metrics — Every metric above calculated instantly from your inputs.

Tab 4: Comparison Dashboard — Add up to 20 properties. The sheet auto-ranks them by a weighted score (you can adjust the weights).

Tab 5: Sensitivity Analysis — Test different down payments (15-30%), interest rates (5-8%), and vacancy rates (3-10%) to stress-test your assumptions.

Real Example

I ran two sample properties through the calculator:

123 Main St: $250,000 purchase, $2,200/mo rent

• Cash-on-Cash: 8.3%
• Cap Rate: 6.7%
• 5-Year ROI: 42%

456 Oak Ave: $180,000 purchase, $1,600/mo rent

• Cash-on-Cash: 10.1%
• Cap Rate: 7.2%
• 5-Year ROI: 51%

Property #2 scored higher on the weighted ranking — better cash flow relative to investment, even though it's less expensive overall.

The Template

If you want to skip the formula building and just get analyzing, I published the complete template on Gumroad: Rental Property ROI Calculator - Google Sheets

It includes all 5 tabs, sample data, and instructions. You'll be analyzing your first property in under 5 minutes.

What metrics do you prioritize for rental property analysis? I'm always looking to improve this tool.