Self-managing

Free rental property spreadsheet with Schedule E categories (Excel and Google Sheets)

A free rental property spreadsheet whose columns map straight to Schedule E lines, with a line 18 depreciation tab so April is copy-paste.

5 min read

A rental property spreadsheet has one job in April: hand your CPA, or your tax software, the totals that Schedule E asks for, in the order it asks for them. Most free templates fail at exactly that job. They sort a year of spending into invented buckets like “Maintenance & Misc” or “Operations,” and since none of those buckets is a line on the form, the year ends with you re-sorting three hundred rows on a January weekend.

This template skips the invented buckets. Every category in the dropdown is a real line from Schedule E (Form 1040), lines 3 through 19. Depreciation gets its own tab because line 18 is math rather than a sum of receipts. The summary tab totals each line per property with one SUMIFS formula. Filing season becomes reading numbers off a sheet that already speaks the form's language.

The template

Copy the block below into Excel or Google Sheets. The pipe characters split cleanly into columns with one text-to-columns pass, and the bracketed placeholders mark what you fill in.

Build sheet: three tabs, Schedule E categories

RENTAL PROPERTY SPREADSHEET: BUILD SHEET Works in Excel and Google Sheets. Three tabs. Paste each block, then split the pipe rows into columns (Data > Text to Columns in Excel, Data > Split text to columns in Sheets). TAB 1: TRANSACTIONS (one row per dollar in or out, all year, all properties) Date | Property | Category | Schedule E line | Amount | Paid to / received from | Receipt or reference Category list (make this a data-validation dropdown; the line number rides along): Rent received | 3 Late fee received | 3 Advertising | 5 Auto and travel | 6 Cleaning and maintenance | 7 Commissions | 8 Insurance | 9 Legal and professional fees | 10 Management fees | 11 Mortgage interest | 12 Other interest | 13 Repairs | 14 Supplies | 15 Property taxes | 16 Utilities | 17 Other (HOA dues, bank fees, software) | 19 Capital improvement | DEPRECIATION TAB, not an expense line Security deposit held | NO LINE, not income while you hold it TAB 2: DEPRECIATION (feeds Schedule E line 18) Asset | Placed in service | Basis | Recovery period | This year's deduction [PROPERTY ADDRESS], building only | [DATE] | $[PURCHASE PRICE MINUS LAND VALUE] | 27.5 years | basis / 27.5 [IMPROVEMENT, e.g. NEW ROOF] | [DATE] | $[COST] | 27.5 years | basis / 27.5 [APPLIANCE OR CARPET] | [DATE] | $[COST] | 5 years | basis / 5 Year-one proration (mid-month): annual amount x (full months in service + 0.5) / 12 TAB 3: ANNUAL SUMMARY (one column per property, rows in Schedule E line order) First, name three ranges on the Transactions tab (Formulas > Define Name, or Data > Named ranges in Sheets): Amount, LineNo, Prop. Rents received (line 3) | =SUMIFS(Amount, LineNo, 3, Prop, [THIS PROPERTY]) Lines 5 through 17 and 19 | same formula, change the line number Depreciation (line 18) | pull this property's total from the Depreciation tab Total expenses (line 20) | sum of lines 5 through 19 Income or loss (line 21) | line 3 minus line 20

How to run it

  • One transactions tab for the whole year. Twelve monthly tabs feel organized and break every formula; SUMIFS wants one continuous range. Filter by month when you need a month.
  • Make the category column a dropdown. Data validation against the category list keeps “Repairs” from appearing as repairs, Repair, and R&M in the same year, and a VLOOKUP against the list fills the line number for you.
  • Enter rows on a schedule, not from memory. I close my own books on the 5th of each month, and the entries I trust least are always the ones reconstructed weeks later from a bank statement.
  • Keep the reference column honest. A receipt number, check number, or invoice reference on every expense row is what turns a list of claims into a record.

The categories here are deliberately the form's own. If you want finer detail underneath them, the rental property chart of accounts adds sub-categories that still roll up to the same lines, and Schedule E line by line explains what belongs on each one. The spreadsheet is the ledger half of a larger routine; the rest of the operating system, from maintenance to renewals, is in the complete self-managing guide.

The line 18 tab

Line 18 is the one number on Schedule E you cannot get by adding up receipts, which is why most templates omit it and why this one has a tab for it. Say you buy a duplex for $310,000 and the county assessor puts the land at $62,000. Land never depreciates, so the building basis is $248,000, and residential rental property recovers over 27.5 years:

$248,000 ÷ 27.5 = $9,018 per full year

The first year prorates under the mid-month convention. Place that duplex in service in May and you count 7.5 of 12 months, so year one is $9,018 × 7.5 ÷ 12 = $5,636. Each capital improvement gets its own row with its own start date, because a new roof is not a line 14 repair; it is a separate 27.5-year asset. The full math, including what happens when you sell, is in the 27.5-year depreciation guide, the depreciation calculator runs the proration for you, and IRS Publication 527 is the governing source.

Mistakes that break the April copy-paste

  • Logging security deposits as rent. A held deposit is not income, and counting it on line 3 means paying tax on money you may owe back at move-out.
  • Putting a capital improvement on line 14. A $9,400 roof deducted as a repair is the classic error; it belongs on the depreciation tab. The dividing line is covered in repairs vs improvements.
  • Collapsing the portfolio into one summary column. Schedule E reports each property in its own column, so a combined total has to be pulled apart again before anyone can file.
  • Skipping mortgage statement detail. Only the interest portion of a mortgage payment is a line 12 expense; principal and escrow are not deductions, and a full payment logged as interest overstates the line all year.

Where the spreadsheet tops out

A version of this exact sheet ran my own small portfolio for years, managed from two time zones away, and it was the spreadsheet dropping things, a missed improvement row here, a deposit counted as rent there, that pushed me to build rents.ai. It keeps the same Schedule E mapping but computes line 18 itself, mid-month convention included, tracks deposits through deductions and return, and will take this template's transactions tab as a CSV import so you do not start over. It does not connect to your bank: there are no bank feeds, so every transaction is still typed or imported by you, the same as the sheet above. When the row count or the property count makes that trade worth revisiting, spreadsheet vs landlord software works through the decision. Until then, a spreadsheet that speaks the form's language is a real system, not a stopgap.

The Schedule E mapping in this template organizes your year for your CPA; it is an estimate of where numbers belong, not tax advice. Depreciation basis, improvement classification, and deposit timing all carry conditions, so confirm line placement with your CPA before filing.

Questions landlords actually ask

Do I need a separate spreadsheet for each rental property?
No. Keep one transactions tab for everything, with a property column on every row, and give each property its own column on the summary tab. Schedule E reports each property separately, so the per-property split has to exist somewhere, and a column is much easier to maintain than a separate file.
Are security deposits income on a rental property spreadsheet?
Not while you hold them. A deposit only becomes rental income in the year you keep some or all of it for unpaid rent or damage. Log deposits with their own category and no Schedule E line so they never inflate your line 3 total.
How do I handle depreciation in a rental spreadsheet?
Give it a separate tab, because line 18 is a calculation rather than a sum of receipts. Building basis (purchase price minus land value) divides over 27.5 years, the first year prorates under the mid-month convention, and each capital improvement gets its own row with its own recovery period.