Rental Property Spreadsheet Template: What to Track and How to Build One

You're scrolling through listings. A duplex in Memphis pops up — $185,000, two units at $950 each. Looks good. But here's the thing: you don't know if it's a deal until you run the numbers. And running the numbers means having a spreadsheet that actually captures what matters.

Most investors either wing it with a napkin calculation or overcomplicate things with 47 tabs. There's a middle path. A rental property analysis spreadsheet that tracks the four metrics lenders and experienced investors actually care about: NOI, cash-on-cash return, DSCR, and cap rate. Build it once. Use it every time.

What Your Spreadsheet Must Track

Start with gross income. Not projected. Not optimistic. What's actually under lease or what comparable units are renting for right now. I've seen investors plug in $1,200 when the comps say $1,050 — that $150/month gap destroys your numbers. Be conservative. You can always be pleasantly surprised.

Then subtract operating expenses. That's rent — and only rent. Don't pad it with "maybe I'll add laundry" or "storage could bring in $50." Use actual lease amounts or, if vacant, conservative market rent from Rentometer or Zillow. For our Memphis duplex: $950 × 2 × 12 = $22,800 a year. That's your top line.

Then subtract operating expenses. Property tax, insurance, utilities you pay, maintenance reserve (figure 8–12% of rent), property management if you use it (usually 8–10%), and a vacancy rate allowance — 5–8% depending on the market. For Memphis, 6% is reasonable. That gives you NOI — net operating income. It's the number that tells you what the property earns before you factor in debt.

Here's where it gets interesting. Take that NOI and divide by the purchase price. That's your cap rate. A $185K property with $14,200 in NOI? That's a 7.7% cap. In Memphis, that's solid. In San Francisco, you'd be lucky to see 4%. Cap rate tells you what the property would yield if you paid cash. It's your first filter: too low, and the deal doesn't pencil.

Compare your cap rate to your financing cost. If you're borrowing at 7% and the cap rate is 5.5%, you're negative from day one — the property earns less than your loan costs. You're betting on appreciation. That's speculation, not cash flow investing. You want the cap rate above your financing cost. The spread is your margin of safety.

The Four Metrics That Matter

NOI — Rent minus operating expenses. No mortgage in this number. It answers: "What does this property actually produce?"

Cap rate — NOI ÷ purchase price. It answers: "What yield am I buying at today's price?"

Cash-on-cash — Annual cash flow ÷ total cash invested (down payment, closing costs, any upfront rehab). It answers: "What am I earning on the money I put in?"

DSCR — NOI ÷ annual debt service. Lenders use this. Most want 1.2–1.25 minimum. It answers: "Can the property pay its own mortgage with a cushion?"

You need all four. Skip one and you're flying blind. A property can show a 7% cap rate and still fail DSCR if you're over-leveraged. Or it can pass DSCR but deliver miserable cash-on-cash because you put too much down. The spreadsheet forces you to see the full picture. That's why building it right matters — one formula feeds the next, and the numbers either work together or they don't. A property can have a decent cap rate but terrible cash-on-cash if you're over-leveraged. Or it can cash flow nicely but fail DSCR and kill your loan approval.

How to Build the Template

Row 1: Purchase price, down payment %, loan amount, interest rate, term. Row 2: Gross rent (monthly × 12). Row 3: Vacancy (%). Row 4: Effective gross income. Rows 5–10: Each operating expense — tax, insurance, utilities, maintenance, PM, other. Sum them. That's your NOI.

Below that: debt service (use PMT in Excel or Google Sheets). Then cash flow = NOI − debt service. Cash-on-cash = annual cash flow ÷ total cash in. DSCR = NOI ÷ annual debt service.

One sheet. One property per tab, or one row per property if you're comparing. The Deal Analysis guide walks through each formula in depth — this is the spreadsheet version.

Pro tip: add a "sensitivity" section. What if rent drops 8%? What if your insurance quote was low and it comes in 20% higher? Toggle those assumptions and watch your cash-on-cash move. You'll quickly see which variables matter most. Usually it's rent and interest rate. Get those right, and the rest follows.

A Real Example: Memphis Duplex

Let's run the numbers. $185,000 purchase. 25% down ($46,250), 7% rate, 30-year term. Monthly payment: $923. Annual debt service: $11,076.

Gross rent: $22,800. Vacancy at 6%: $1,368. Effective gross: $21,432. Operating expenses: tax $2,200, insurance $1,100, maintenance 10% of rent $2,280, PM 8% $1,824. Total expenses: $7,404. NOI: $14,028.

Cap rate: $14,028 ÷ $185,000 = 7.6%. Cash flow: $14,028 − $11,076 = $2,952/year, or $246/month. Cash invested: $46,250 + $4,500 closing (about 2.5%) = $50,750. Cash-on-cash: $2,952 ÷ $50,750 = 5.8%. DSCR: $14,028 ÷ $11,076 = 1.27. Lenders like that.

That's a 23% spread between cap rate and your financing cost — room to breathe. The spreadsheet makes it obvious. Without it, you're guessing.

Common Spreadsheet Mistakes

Three things trip people up. First: forgetting closing costs in your cash-in calculation. You didn't just put down $46,250 — you paid title, appraisal, origination. Add 2–5% of purchase price. Second: using list price instead of your offer. If you're buying at $175K, your cap rate uses $175K, not the $185K ask. Third: underestimating maintenance. A 5% reserve is wishful thinking. Plan for 8–12%. When the HVAC goes at 2 a.m., you'll be glad you did.

What to Do Next

Build your template. Use the structure above. Add a sensitivity section: what if rent drops 5%? What if rates go to 8%? Stress-test before you offer. I keep a "worst case" column in my spreadsheet — 8% vacancy, 12% maintenance, 8% rate. If the deal still cash flows in that column, I'm comfortable. If it goes negative, I either negotiate a better price or pass. The goal isn't to hope for the best. It's to survive the worst and still make money.

Then plug in every deal you look at. After a dozen, you'll spot patterns — which neighborhoods pencil, which don't. Your spreadsheet becomes your filter. And when you find one that works, you'll know.

One last thing: share your template with your team. If you're working with a partner, a lender, or a mentor, a standardized format makes communication faster. "I'm looking at a 7.2% cap, 5.1% cash-on-cash, 1.24 DSCR" — they'll know exactly what you mean. Consistency in how you analyze deals is almost as important as the numbers themselves.

For the full framework on running numbers and comparing deals, start with the Deal Analysis guide. It covers NOI, cash-on-cash, DSCR, and cap rate in depth — the same metrics, with the formulas and the "what's good or bad" breakdown for each.