Rental Property Tracker Excel Template: The Complete Guide for Real Estate Investors (2026)

Published August 2026 · 14-minute read · Category: Business & Money

If you own rental properties and you're still tracking cash flow on the back of an envelope — or worse, in five disconnected Google Sheets — you're losing money. Not in obvious ways, but in the slow leaks that kill real estate returns: the vacancy you didn't notice for two months, the expense category you forgot to log at tax time, the mortgage where you never checked whether the amortization was building equity or just feeding the bank interest.

A rental property tracker Excel template fixes all of this in one file. You enter raw data — purchase price, rent collected, the plumber's invoice — and the spreadsheet calculates cap rate, cash-on-cash return, NOI, vacancy rate, and portfolio-level cash flow automatically. No subscription, no cloud dependency, no data exports. Just a spreadsheet that works the way real estate investing works: offline, on your terms, forever.

This guide explains exactly what to track, which metrics matter, how to set up the formulas yourself (or use a pre-built template), and how a spreadsheet compares to paid property management software. If you just want the template, skip to the CTA below.

Frequently Asked Questions

Rental Property Tracking: Free Excel vs Paid Apps

Before diving into the mechanics, let's settle the question every landlord asks: should you use a free spreadsheet or pay for property management software? The answer depends entirely on portfolio size and what you're actually trying to do.

The pattern is clear. Spreadsheets dominate financial analysis — cap rate, cash-on-cash, amortization, portfolio-level rollups — and they cost nothing ongoing. Software dominates operations — rent collection, tenant screening, maintenance tickets. If you have under 20 doors and your pain point is "I don't know if my properties are actually profitable," you need a spreadsheet, not software. If your pain point is "I can't track who's paid rent this month," you need software.

The 12 Data Points Every Landlord Must Track

Most amateur landlords track two things: rent in and mortgage out. That's why they're surprised at tax time and can't answer the question "is this property actually making money?" Here are the 12 data points a professional rental property tracker Excel template records for every property:

Property-Level Data (Static — Enter Once)

1. Purchase price — the basis for all ROI calculations
2. Purchase date — for annualized return calculations
3. Current market value — updated annually; the denominator in cap rate
4. Property type — single-family, duplex, triplex, fourplex, condo, multi-family
5. Total cash invested — down payment + closing costs + rehab capital; the denominator in cash-on-cash return
6. Loan details — loan amount, interest rate, term (years) — feeds the PMT mortgage formula

Monthly Operational Data (Dynamic — Enter Monthly)

7. Gross rental income — rent + late fees + laundry + parking + any other income, per unit
8. Operating expenses — property tax, insurance, maintenance, property management, HOA, utilities, lawn care, per property per month
9. Mortgage payment — principal and interest, auto-calculated from loan details via PMT
10. Vacancy status — occupied or vacant, with vacancy rate auto-calculated from actual vs. expected rent

Calculated Metrics (Auto-Generated — Never Enter Manually)

11. Net operating income (NOI) — Gross Income − Operating Expenses (before debt service)
12. Cash flow — NOI − Mortgage P&I (the number that tells you if the property is profitable)

If your tracker doesn't capture all 12, you're flying blind on at least one dimension of your investment. A template like the Rental Property Portfolio Tracker pre-builds all 12 into labeled columns with dropdowns and auto-calculations so you never miss one.

The 5 Metrics That Determine If Your Rental Is Profitable

Tracking data is useless if you don't calculate the right metrics. These five are the ones lenders, investors, and experienced landlords actually use to evaluate performance. Every one of them should auto-calculate in your rental property tracker Excel template.

1. Cap Rate (Capitalization Rate)

Formula: (Annual Gross Income − Operating Expenses) / Current Property Value

Cap rate measures unleveraged return — what the property earns before financing. It's the single most-used metric for comparing properties across markets. A 6% cap rate in Cleveland and a 3% cap rate in San Francisco tell you the same thing about the local market's yield, even though the property prices are wildly different.

• Below 4%: High-appreciation markets (coastal cities) — you're betting on value growth, not cash flow
• 4-7%: Balanced markets — cash flow and appreciation both contribute
• Above 7%: Cash flow markets (Midwest, South) — the property pays for itself, less appreciation upside

Your template should display cap rate per property with color-scale conditional formatting — green for strong returns, amber for marginal, red for underperforming.

2. Cash-on-Cash Return

Formula: Annual Pre-Tax Cash Flow / Total Cash Invested

Where cap rate ignores financing, cash-on-cash return is all about leverage. It answers: "for every dollar I put into this deal, what am I getting back annually?" A property with a 4.8% cap rate can deliver a 14% cash-on-cash return if you used a 75% LTV mortgage at a low rate. This is why leverage is real estate's superpower — and why you must track cash-on-cash, not just cap rate.

• Below 5%: Marginal — barely beats a high-yield savings account
• 5-8%: Decent — solid return with appreciation upside
• 8-12%: Strong — the target range for most leveraged investors
• Above 12%: Excellent — usually means good financing or an undervalued purchase

3. Net Operating Income (NOI)

Formula: Gross Rental Income − Operating Expenses

NOI strips out financing to show pure operational performance. It's what a property would earn if you owned it free and clear. Lenders use NOI to calculate Debt Service Coverage Ratio (DSCR), and buyers use it to compare properties without the distortion of different financing structures. Your tracker should calculate NOI per property and as a portfolio total on the dashboard.

4. The 1% Rule

Formula: Monthly Rent / Purchase Price ≥ 0.01

The 1% rule is a quick screen: if monthly rent is at least 1% of the purchase price, the property will likely cash-flow positively. A $200,000 property needs $2,000/month in rent to pass. It's a heuristic, not a guarantee — high property taxes or insurance can break a property that passes the 1% rule — but it's the fastest way to screen deals before running full numbers. Your template should flag pass/fail with green/red conditional formatting.

5. Equity Built (Amortization)

This is the metric most landlords completely ignore, and it's often worth more than cash flow. Every mortgage payment splits into principal (your equity) and interest (the bank's profit). In the early years, most of your payment is interest. By year 15, it's roughly 50/50. By year 25, it's mostly principal.

A proper mortgage calculator tab in your template uses the PMT function to calculate the payment, then tracks how much of each payment is principal vs. interest, building an amortization schedule that shows cumulative equity built per property per year. On a $240,000 loan at 6% over 30 years, you build roughly $3,400 in equity in year 1, $9,200 in year 10, and $17,800 in year 20. That's wealth accumulation that doesn't show up in cash flow but absolutely shows up in net worth.

Building the Formulas Yourself: Step by Step

If you want to build your own rental property tracker from a blank spreadsheet, here are the core formulas you need. Each one is a building block; the full template combines them into a system that auto-populates a dashboard.

Mortgage Payment (PMT Formula)

The PMT function calculates the monthly principal-and-interest payment from three inputs: interest rate, number of payments, and loan amount.

=PMT(interest_rate/12, term_years*12, -loan_amount)

Example: A $240,000 loan at 6% interest for 30 years:

=PMT(0.06/12, 30*12, -240000) = $1,438.92/month

The negative sign before the loan amount ensures the result is a positive number (money you pay out). Multiply by 12 for annual P&I, or build a full amortization schedule with PPMT (principal portion) and IPMT (interest portion) functions for each payment period.

Cap Rate

=(SUMIF(Income!Property, A2, Income!Amount) - SUMIF(Expenses!Property, A2, Expenses!Amount)) / Properties!CurrentValue

This SUMIF approach lets you keep a single income log (one row per property per month) and auto-sum it per property without manual filtering. Format the result as a percentage.

Cash-on-Cash Return

=(NOI - Annual_Mortgage_PI) / Properties!CashInvested

Where NOI is calculated above and Annual Mortgage PI is PMT(...) * 12. This gives you the leveraged return on your actual cash invested.

Vacancy Rate

=1 - (Actual_Rent_Collected / Expected_Rent)

Track actual collected rent vs. expected rent per unit per month. A vacancy rate above 10% means you have a tenant quality problem or a pricing problem. The best templates apply color-scale formatting — green for 0-5%, amber for 5-10%, red for above 10%.

1% Rule Test

=IF(Monthly_Rent / Purchase_Price >= 0.01, "PASS", "FAIL")

A simple IF formula that outputs PASS or FAIL. In a full template, wrap it in conditional formatting so PASS is green and FAIL is red, giving you instant visual screening on every property.

Portfolio Cash Flow (All Properties)

=SUMPRODUCT((Properties!CashFlow_Range))

Or, more simply, =SUM(Properties!CashFlowColumn) if your per-property cash flow is already calculated on the Properties tab. This is what goes in the dashboard's headline KPI: "Total Monthly Cash Flow: $X,XXX."

If this looks like a lot of formula-building, it is — and that's the point. A pre-built template saves you 4-8 hours of setup and eliminates the formula errors that silently corrupt your numbers for months before you notice. The Rental Property Portfolio Tracker has all of these formulas pre-wired, tested, and formatted.

What a Professional 8-Tab Template Includes

A serious rental property tracker isn't a single sheet with everything crammed together. It's a multi-tab workbook where each tab has a single job and they feed into each other. Here's the structure of a professional template — the same structure used by the Kowhai Goods Rental Property Portfolio Tracker:

Tab 1: Dashboard

The command center. Auto-populates from all other tabs via cross-tab formulas. Shows KPI cards for total properties, total portfolio value, monthly rent roll, net cash flow, total equity, blended cap rate, and blended cash-on-cash return. Includes a per-property cash flow table with positive/negative status indicators, a monthly cash flow bar chart, and a portfolio value allocation pie chart. You never enter data here — you only read it.

Tab 2: Properties

Your property database. One row per property (or per unit, for multi-unit). Columns: address, property type (dropdown), purchase date, purchase price, current market value, square footage, bed/bath count, annual appreciation rate, loan details (amount, rate, term), and total cash invested. Pre-loaded with 5 sample properties so you can see how it works before entering your own.

Tab 3: Rental Income

Monthly rent tracker. One row per unit per month, with 12 monthly columns. Auto-calculates annual totals per unit and vacancy rate (actual collected vs. expected). Color-scale formatting highlights months where rent wasn't collected. Pre-loaded with 11 sample units across 5 properties.

Tab 4: Expenses

Monthly operating expenses per property. Columns for tax, insurance, maintenance, property management, HOA, and other. Auto-calculating monthly totals with data bars showing relative magnitude. This is the tab that saves you at tax time — every deductible expense in one place.

Tab 5: Mortgage Calculator

Auto-calculating amortization for each property's loan. Inputs: loan amount, interest rate, term. Outputs (all via PMT formula): monthly P&I, annual P&I, remaining balance, cumulative interest paid, cumulative principal paid, equity built, LTV ratio, and payoff date. Pre-loaded with 5 sample mortgages. This tab alone is worth the template price — it shows equity accumulation that most landlords never calculate.

Tab 6: ROI Analysis

The performance scorecard. Per property: cap rate, cash-on-cash return, 1% rule test (PASS/FAIL), with color-scale formatting and two bar charts (cap rate by property, cash-on-cash by property). This is where you spot underperformers and decide whether to hold, improve, or sell.

Tab 7: Annual Summary

Year-over-year performance tracking. NOI and net cash flow per property per year, with a trend line chart showing portfolio performance over time. Pre-loaded with 3 years of sample data across 5 properties. This is the tab you show your accountant or a potential lender.

Tab 8: Instructions

Step-by-step guide for every tab, plus real estate investing tips and compatibility notes. A good template doesn't make you guess where to enter data — it tells you.

Free Templates vs Paid Templates: What You Actually Get

There are hundreds of free rental property spreadsheets online. BiggerPockets has one, Reddit's r/realestateinvesting has a shared Google Sheet, and a quick search for "rental property Excel template free" returns dozens. So why would anyone pay $12?

Because free templates have a consistent set of limitations that cost you more than $12 in wasted time and missed deductions:

The math is simple. If your time is worth more than $1.50/hour (the break-even: $12 / 8 hours saved), a paid template is cheaper than a free one. And that's before counting the financial cost of missed deductions, uncaptured equity data, and decisions made on incomplete metrics.

Using the Template in Google Sheets

Not everyone has Microsoft Excel, and that's fine. The Rental Property Portfolio Tracker is built without macros, which means it works perfectly in Google Sheets — Google's free, cloud-based spreadsheet that runs in any browser and on mobile.

How to Use It in Google Sheets

1. Download the .xlsx file after purchasing from Gumroad.
2. Upload to Google Drive — drag the file into drive.google.com.
3. Right-click the file and select "Open with → Google Sheets."
4. All formulas, charts, and formatting convert automatically. The PMT function, SUMIF, conditional formatting, color scales, and all 5 charts work identically in Sheets.
5. Optional: File → Save as Google Sheets to convert it to a native Sheets file for real-time collaboration.

The advantage of Google Sheets is cloud access — you can log expenses from your phone at the property, share the file with a partner or accountant, and never worry about version control. The disadvantage is you need an internet connection to edit (though Sheets does have an offline mode for Chrome users).

The template also works in LibreOffice Calc (free, desktop) and Apple Numbers (Mac/iOS). No platform is left out — the only requirement is a spreadsheet application that supports the PMT function, which is all of them.

How This Template Saves You at Tax Time

Tax season is where most landlords lose money they don't even know about. The IRS allows 15+ deductible expense categories for rental property: mortgage interest, property tax, insurance, maintenance, repairs, property management, travel to the property, legal fees, HOA fees, utilities (if landlord-paid), advertising, cleaning, supplies, depreciation, and more. If you're not tracking every one of these monthly, you're overpaying your taxes.

A rental property tracker Excel template solves this in three ways:

1. Complete Expense Logging

The Expenses tab has dedicated columns for every common deductible category. You log the expense when it happens — not six months later when you're digging through bank statements. An expense logged in real time is an expense you deduct. An expense forgotten is money lost.

2. Per-Property Rollups

SUMIF formulas automatically total every expense category per property for the full year. When your CPA asks "how much did you spend on maintenance for 123 Main St. in 2026?" the answer is one cell reference away — not a 45-minute reconciliation project.

3. Annual Summary for Schedule E

The Annual Summary tab provides exactly the data structure you need for IRS Schedule E (Supplemental Income and Loss from Rental Real Estate): income per property, expenses by category per property, and net cash flow. Many accountants will accept this tab exported as a PDF and use it directly to prepare your return, saving you $200-$500 in bookkeeper time.

Scaling From 1 Property to 20: Portfolio-Level Tracking

Most landlords start with one property and a simple spreadsheet that works fine. The problems start at property 2 and compound with every acquisition. Suddenly you have:

• Three different mortgage start dates and amortization schedules
• Vacancy in unit A while unit B is fully occupied — different months, different cash flow
• One property in an LLC, another in your personal name — different tax treatment
• A property that's been appreciating 8% annually and one that's been flat — should you sell the laggard?
• A total portfolio cash flow number you can't calculate without manually adding 5 spreadsheets

This is where a portfolio-level dashboard becomes essential. The Dashboard tab in a proper template aggregates every property into portfolio KPIs:

• Total portfolio value — sum of all current market values
• Total monthly rent roll — gross income across all units
• Total net cash flow — the number that determines if you can quit your day job
• Blended cap rate — weighted by property value
• Blended cash-on-cash return — weighted by cash invested
• Total equity — sum of (current value − remaining loan balance) across all properties

With these numbers, portfolio decisions become clear. You can see that property 3 has a 3.2% cash-on-cash return dragging your blended return down from 11% to 9% — that's a sell candidate. You can see that your portfolio has built $87,000 in equity over 3 years without you doing anything but making mortgage payments — that's the wealth-building power of real estate made visible.

The template supports up to 20 properties with multi-unit support, meaning a fourplex counts as one property with four income rows. That's effectively up to 80 units if your entire portfolio is fourplexes. For most individual investors, that's a lifetime portfolio — and the template scales to it from day one.