Rental Property Analysis Spreadsheet: What to Track Beyond Cap Rate

Cap rate answers a narrow question

Cap rate — net operating income divided by purchase price — measures an unlevered yield at a single point in time. It tells you how the asset performs before financing, before capital expenditures that fall outside normal operating expenses, and before any assumption about how rents or expenses change over the hold period. That is a useful number for comparing two similar assets in the same market. It is not a useful number for deciding whether a specific deal works for a leveraged investor with a 30-year fixed mortgage and a five-year hold.

The fixation on cap rate comes partly from commercial real estate convention, where institutional buyers often pay cash or use debt terms that are close enough across deals to make unlevered comparisons meaningful. Most individual rental investors are not in that position. The mortgage rate, down payment, and loan structure materially change the outcome, and cap rate captures none of it. Build a spreadsheet that starts with cap rate and then keeps going.

Gross income needs four adjustments before it becomes useful

Gross scheduled rent is what the unit would collect if it were occupied and paying tenants sent every check on time for every month of the year. That number is not your income. A rental analysis spreadsheet should apply four reductions in sequence: vacancy loss (units sitting empty between tenants), credit loss (tenants who do not pay in full), concessions (move-in specials or free months used to attract tenants), and non-rent income adjustments (pet fees, parking, laundry) that should be added back separately so they are visible. Collapsing all of these into a single 'vacancy factor' hides assumptions that matter when the market softens.

Vacancy rates vary significantly by market, submarket, property class, and unit type. A surface-level assumption of five percent vacancy on a C-class property in a softening market is likely wrong. Pull actual days-on-market data for comparable rentals in the immediate area, look at historical absorption trends if you can find them, and be conservative. Underwriting at three percent vacancy when the neighborhood is running closer to ten percent is how investors end up with cash flow that exists only in a spreadsheet.

• Vacancy loss: estimated empty days per unit per year, converted to a dollar figure
• Credit loss: partial or non-payment risk, typically 1-3% for stabilized assets
• Concessions: any rent-free periods used to lease up or retain tenants
• Non-rent income: itemized separately so it can be stress-tested independently

Operating expenses belong in categories, not a single percentage

Some underwriting models apply a blunt expense ratio — say, forty-five or fifty percent of gross rents — and call it done. That approach works as a quick sanity check on a deal you have not committed to yet, but it collapses important distinctions. Property taxes are fixed and predictable; maintenance is variable and property-specific; property management fees scale with rent but change if you switch managers or self-manage. Insurance premiums have spiked in many markets and deserve their own line. Utilities matter only if any are landlord-paid, but if they are, they need to be itemized by type.

The categories worth tracking individually in a rental analysis spreadsheet: property taxes, insurance, property management fees, repairs and maintenance, landscaping and snow removal if applicable, HOA dues if applicable, accounting and legal, and advertising or leasing costs. Each one has a different volatility profile and a different lever for optimization. When a deal stops penciling out, knowing which expense line is the culprit lets you figure out whether it is fixable or structural.

• Property taxes: pull the actual bill, not an estimate — check for reassessment risk after sale
• Insurance: get a real quote for the property type and location, not a rule of thumb
• Property management: model at market rate even if self-managing, so you see the true cost
• Repairs and maintenance: separate from CapEx — these are recurring, not one-time
• Advertising and leasing: often omitted, but real if tenant turnover is high

CapEx reserves need a schedule, not a percentage

Capital expenditures — roof replacement, HVAC systems, water heaters, flooring, appliances — are not operating expenses, but they are real cash outflows that erode returns over a hold period. The standard workaround is to reserve a flat percentage of gross rents, often five to ten percent, as a CapEx line. That is better than ignoring CapEx entirely, but it does not tell you when the cash actually leaves your account or how large each draw will be.

A more useful approach is to build a simple component schedule. For each major system, estimate its current age, its expected useful life, and its replacement cost. Divide the remaining useful life into the replacement cost to get an annual reserve figure for that component. Sum them up. A fifteen-year-old roof on a property with a five-year hold is a different situation than a two-year-old roof, and a flat percentage treats them identically. The goal is not precision — replacement costs are estimates — but to force yourself to look at the physical reality of the asset before you close.

• Roof: age, estimated remaining life, replacement cost per square foot
• HVAC: age, system type, replacement cost for the unit(s)
• Water heater(s): age, tank vs. tankless, replacement cost
• Flooring and interior finishes: condition-based estimate, not age-based
• Appliances: especially if landlord-supplied — track age and warranty status

Cash-on-cash return and DSCR complete the picture cap rate cannot

Once the spreadsheet has gross income, vacancy and credit adjustments, operating expenses, and a CapEx reserve, net operating income falls out naturally. From there, two metrics should sit at the top of the output: cash-on-cash return and debt service coverage ratio. Cash-on-cash return — annual pre-tax cash flow divided by total cash invested — measures what the deal actually returns on the dollars you put in. It incorporates the mortgage payment and the down payment, which cap rate ignores entirely. A deal with a seven percent cap rate can produce a two percent cash-on-cash return or a twelve percent cash-on-cash return depending on the financing structure. Those are not the same deal.

Debt service coverage ratio — net operating income divided by annual debt service — measures how much cushion exists between income and the mortgage obligation. Lenders typically want to see 1.20 or higher; anything below 1.0 means the property does not cover its own debt payment from operations. Running both metrics at your base-case assumptions and then stress-testing them with a ten percent rent reduction and a vacancy rate fifty percent higher than your estimate will tell you more about deal risk than any single number. Tools like Propseek can help surface property-level data — ownership history, tax records, assessed values — that informs the income and expense inputs before the spreadsheet work begins.