How I Built a Rental Property ROI Calculator in Google Sheets
If you're analyzing rental properties, you've probably spent hours in spreadsheets trying to figure out cash flow, cap rate, and return on investment. I got tired of doing this manually, so I built an automated Google Sheets template that does all the math for you.
The Problem with Manual Analysis
Most retail investors I know start with a napkin calculation: "Rent is $X, mortgage is $Y, so I make $Z." But real estate investing requires more nuance — vacancy reserves, maintenance, property management, insurance, HOA, capex, and taxes all eat into your cash flow.
I used to maintain a messy Excel file with formulas I'd copy-pasted from random blog posts. It worked, but it was fragile and hard to share.
The Solution: A Structured Google Sheets Template
I built a 6-tab spreadsheet that covers everything:
- Quick Summary — Enter purchase price, down payment, rent, and expenses. See cash flow, Cap Rate, and CoC Return instantly.
- Income & Expenses — Pre-filled categories with realistic default percentages (5% vacancy, 8% management, 10% maintenance, 5% capex).
- Loan Calculator — Uses Google Sheets' PMT formula to calculate monthly P&I, plus a full amortization schedule.
- 12-Month Cash Flow — Monthly projection with conditional formatting for negative months.
- ROI Projections — 5-year and 10-year scenarios with appreciation, rent growth, and expense inflation assumptions.
- Scenario Comparison — Compare up to 5 properties side-by-side.
Key Formulas Used
- PMT(rate/12, term*12, -loan_amount) — calculates monthly P&I
- NOI = Gross Rent × 12 - Total Annual Expenses
- Cap Rate = NOI / Purchase Price
- Cash-on-Cash Return = Annual Pre-Tax Cash Flow / Total Cash Invested
- Total ROI (10yr) = (Sale Proceeds + Cumulative Cash Flow) / Initial Investment - 1
The Sample Data
I included a sample property — a $350,000 duplex in Austin with $2,800/mo rent and 20% down. At current rates (6.5%), it shows:
- Monthly cash flow: $680
- Cash-on-Cash Return: 11.7%
- 10-year ROI: 148%
This gives you a benchmark to compare your own deals against.
How You Can Get It
I've published the complete template on Gumroad. It costs $29 — about the price of a coffee and a burrito, and it'll save you hours on every property you analyze.
The tool is immediately usable. Just open the link, make a copy in Google Sheets, and enter your numbers in the blue cells. Everything else auto-calculates.
If you're serious about real estate investing, having a standardized analysis template will help you move faster and make better decisions. Give it a try and let me know what you think!
Top comments (0)