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.
Top comments (0)