Rental property analysis template: what every spreadsheet needs (and a better option)
A rental property analysis spreadsheet needs at minimum 12 core inputs: purchase price, down payment, loan terms, gross rent, vacancy, property taxes, insurance, management, maintenance, CapEx reserves, utilities, and other expenses. Most templates floating around the internet miss at least three of these or miscalculate the outputs.
Every real estate investor has either built a rental analysis spreadsheet, downloaded someone else's, or inherited one from a mentor with formulas that nobody fully understands. The spreadsheet approach has a fundamental problem: you built it, you maintain it, and when the formula in cell G47 breaks, nobody tells you. You just make decisions based on wrong numbers until you figure it out.
This guide covers what a proper analysis template should contain, where most templates fail, and why an interactive calculator that does the math for you is the better tool for the job. If you want the full step-by-step process before building a template, start with how to analyze a rental deal.
The 12 inputs every analysis needs
Whether you use a spreadsheet, a calculator, or a napkin, these are the numbers that determine whether a deal works:
Acquisition inputs
| # | Input | What to enter | Common mistake |
|---|---|---|---|
| 1 | Purchase price | Asking or offer price | Forgetting to adjust for negotiation |
| 2 | Down payment % | 20% to 25% for investment | Not including closing costs in total cash |
| 3 | Interest rate | Current rate for your loan type | Using owner-occupied rates instead of investor rates |
| 4 | Loan term | 30 years (standard) | Not accounting for ARM reset periods |
Income inputs
| # | Input | What to enter | Common mistake |
|---|---|---|---|
| 5 | Monthly gross rent | Market rent from comparables | Using the seller's projected rent without verification |
| 6 | Vacancy rate | 3% to 12% depending on market | Defaulting to 5% everywhere |
Expense inputs
| # | Input | Typical range | Common mistake |
|---|---|---|---|
| 7 | Property taxes | 0.5% to 2.5% of value/year | Using the current bill instead of the post-purchase reassessment |
| 8 | Insurance | $1,200 to $5,000/year | Not getting a landlord policy quote (using homeowner rates) |
| 9 | Property management | 8% to 10% of collected rent | Leaving it at 0% because "I will self-manage" |
| 10 | Maintenance | 5% to 10% of gross rent | Not budgeting because the property "looks fine" |
| 11 | CapEx reserves | 5% to 10% of gross rent | Confusing CapEx with maintenance (they are separate) |
| 12 | Other expenses | HOA, utilities, pest, lawn | Forgetting landlord-paid water/sewer |
The outputs your template should calculate
From those 12 inputs, a complete analysis produces:
- NOI (Net Operating Income): rent minus vacancy minus all operating expenses
- Cap rate: NOI divided by purchase price
- Monthly cash flow: NOI minus annual debt service, divided by 12
- Cash-on-cash return: annual cash flow divided by total cash invested
- DSCR: NOI divided by annual debt service
- Total ROI: (cash flow + paydown + appreciation) divided by cash invested
- GRM: purchase price divided by annual gross rent
- Expense ratio: total expenses divided by gross rent
Where most templates fail
After looking at dozens of popular rental analysis spreadsheets, these are the patterns that produce wrong answers:
- Missing CapEx reserves.The most common omission. A property that "cash flows $400/month" without CapEx reserves is actually cash flowing $200 after you budget for the roof, HVAC, and water heater that will eventually need replacing. Our guide on estimating operating expenses covers how to size each line item correctly.
- No management fee (because "I self-manage"). Your time has value. More importantly, if you ever want to stop self-managing, the deal needs to support a PM fee. Always include 8% to 10%.
- Property tax not adjusted for purchase. Many counties reassess property taxes on sale. The current owner might pay $2,400/year, but after your purchase at a higher price, it could be $3,800. Call the county assessor.
- Using homeowner insurance rates. Landlord policies cost 15% to 25% more than homeowner policies. Get an actual quote.
- Closing costs excluded from cash invested. Your cash-on-cash return denominator should include every dollar spent: down payment, closing costs, and any immediate repairs.
- Broken formulas nobody notices. A cell that once referenced C14 now references C15 after someone inserted a row. The output looks plausible, so nobody questions it. This is the silent killer of spreadsheet analysis.
Why interactive calculators beat spreadsheets
A good interactive calculator solves every problem listed above:
- No formula errors. The math is built, tested, and maintained by the tool. You cannot accidentally break it by inserting a row.
- Nothing is missing. Every input field is visible. You cannot forget CapEx reserves when the field is staring at you with a default value.
- Instant sensitivity analysis. Change one input and watch every output update in real time. A spreadsheet can do this too, but most templates are not built for it.
- Mobile-friendly. Analyze a deal on your phone at a property showing. Try that with a Google Sheet.
- Always current. No need to update formulas when tax rates change or when you learn a new metric. The tool maintainer handles that.
If you still want a spreadsheet
Some investors prefer spreadsheets for the flexibility to add custom calculations. If that is you, here are the rules for building one that works:
- Separate inputs from calculations. All editable inputs go in one clearly marked section. All formulas go in another. Never mix.
- Color-code editable cells. Yellow or light blue for inputs, white for formulas. If you cannot tell what is editable at a glance, you will break something.
- Lock formula cells. Protect the sheet and only allow editing in input cells. This prevents accidental overwrites.
- Include all 12 inputs listed above. If a field is optional (HOA, for example), include it with a zero default. Better to see a zero than to forget it exists.
- Test with a known deal. Before trusting your spreadsheet, run a deal with known outcomes (or a published case study) and verify every output matches.
Frequently asked questions
What is the best rental property analysis spreadsheet?
The best spreadsheet is one that includes all 12 core inputs (price, financing, rent, vacancy, and every expense category), calculates at least 6 key outputs, and has protected formula cells. Better yet, use an interactive calculator that cannot have broken formulas.
What metrics should a rental analysis template calculate?
At minimum: NOI, cap rate, monthly cash flow, cash-on-cash return, DSCR, and total ROI. GRM and expense ratio are useful additions. Any template that only shows cash flow without cap rate and DSCR is incomplete.
Should I include property management in my analysis if I self-manage?
Yes. Always include 8% to 10% for property management even if you plan to self-manage. Your time has value, and if you ever hire a PM, the deal needs to support it. A deal that only works without management is fragile.
What is the difference between maintenance and CapEx reserves?
Maintenance covers routine repairs (leaky faucet, broken lock, appliance fix). CapEx reserves cover major replacements that happen every 10 to 25 years (roof, HVAC, water heater, flooring). Both should be budgeted separately.
How do I know if my spreadsheet formulas are correct?
Test with a known deal where you can verify the outputs. Or compare your results against a trusted calculator on the same inputs. If the numbers match, your formulas are probably correct. If they differ, trace each formula back to find the error.