The rent roll is the operating heartbeat of a rental business
Your cash flow projection is a forecast. The rent roll is what actually happened. Every month, a rent roll answers four questions in one place: who paid, who did not, which units are empty, and which leases expire soon enough to matter.
Small landlords often run this out of memory or a bank statement. That works with two units and breaks with five. Once you cannot hold the whole portfolio in your head, you start missing things: a lease that rolls over in 45 days, a tenant who has slowly drifted three weeks behind, a maintenance category that has quietly doubled.
- Collection rate: rent collected ÷ rent scheduled. Below 95% means either a process problem or a tenant problem.
- Economic vacancy: market rent lost to vacant units ÷ total market rent. This is what vacancy actually costs you.
- Delinquency: the dollar gap between what was due and what arrived. Track it monthly, not annually.
Categorise expenses the way the IRS does — from day one
Reconstructing a year of expenses in April is miserable. Categorising each invoice as it arrives takes seconds and produces a tax-ready summary in December. The category list in this tool mirrors what a supplemental-income Schedule E actually asks for.
| Category | Typical range | Schedule E treatment |
|---|---|---|
| Repairs | $50–500 per incident | Fully deductible in the year incurred |
| Maintenance (routine) | 5–10% of gross rent | Fully deductible |
| Capital improvements | $2,500+ per project | Depreciated, not expensed |
| Insurance | $1,200–3,200/yr per unit | Fully deductible |
| Property taxes | Varies by county | Fully deductible |
| Property management | 8–10% of rent | Fully deductible |
| Legal & professional | As incurred | Fully deductible |
How the Schedule E summary is built
Schedule E, Part I reports supplemental income from rentals. The form separates income (lines 3–5) from a list of deductible expenses (lines 7–21). The third tab of the workbook groups every ledger entry into those same categories using SUMIF formulas, so your year-end numbers are already sorted.
Two lines deserve special attention because they never appear in your bank feed as an expense: depreciation and mortgage interest. Depreciation is a non-cash deduction (27.5-year straight line on the residential building basis, land excluded) and mortgage interest comes from your lender’s Form 1098. Both reduce taxable income without reducing cash flow.
Net rental income = Collections − Deductible expenses
Taxable income = Net rental income − Depreciation − Mortgage interest
Cash flow = Collections − All cash outflows (including capitalised improvements)Using the maintenance ratio to see problems early
Repairs and maintenance should run 5–10% of gross rent on a stabilised residential property. When the ratio climbs above 12%, it is usually telling you one of three things: the property is reaching the end of a component’s life (roof, HVAC, water heater), a tenant is causing damage, or you are deferring replacement and paying for it in emergency calls.
Tracking the ratio by category, not just in total, distinguishes those cases. A spike concentrated in plumbing is different from one spread across everything. This workbook computes repairs and maintenance per unit per year and as a percentage of scheduled rent, so a trend is visible within two or three months.
- Per-unit annual maintenance above $1,800 on a single-family rental is a warning sign.
- Capital improvements are excluded from the maintenance ratio — they are a separate investment decision.
- A single large repair should be reviewed for whether replacement is more economical than repeated fixes.
A five-minute monthly workflow
The system only works if maintaining it is trivial. Here is the loop this workbook is designed around.
- On the 1st: note which rents hit the bank. Enter the collected amount against each unit — leave blank if nothing arrived.
- During the month: every invoice gets one row in the ledger with a category. Ten seconds each.
- On the last day: read your collection rate and delinquency. Anything under 95% collected or a balance older than 10 days gets a phone call.
- Quarterly: check the leases expiring within 90 days and start renewal conversations early. Renewal conversations started 90 days out close at far higher rates than ones started at 30 days.
How to use this tool
- Load your units. Rename each unit, enter the tenant, market rent, status and lease end date. The vacancy and collection metrics calculate from these rows automatically.
- Enter this month’s collections. Type the amount that actually arrived for each unit. Leave it at zero if nothing came in — that becomes your delinquency figure.
- Log every invoice with a category. Use the category list that matches Schedule E. Mark capital improvements as non-deductible so they stay out of your expense ratio and get depreciated instead.
- Download the workbook at quarter end. The Schedule E tab produces a tax-ready summary and a cash flow reconciliation you can hand to your CPA without a shoebox of receipts.
What is inside the download
A three-tab operations workbook: a unit-by-unit rent roll with automatic balance and vacancy maths, a categorised expense ledger with SUMIF breakdowns, and a Schedule E summary that maps every invoice to its tax category.
Rent Roll— a separate worksheet inrent-roll-expense-tracker.xlsx.Operating Expense Ledger— a separate worksheet inrent-roll-expense-tracker.xlsx.Schedule E Summary— a separate worksheet inrent-roll-expense-tracker.xlsx.