How to Track Rental Income and Expenses by Schedule E Line (Free Spreadsheet Method)
Short on time? The Rental Property Tracker ($19) is the finished, tested version of what this tutorial builds.
If you own a rental or two, the IRS wants your year summarized on Schedule E: rents received on line 3, then expenses on lines 5–19 (advertising, insurance, repairs, taxes and so on).
The easiest way to get there is to decide the line when you record the transaction, not in a panic in March. This tutorial shows the three-part setup we use.
1. A category table that knows the Schedule E line
Make a tab called Categories with two columns: the category name you'll pick from a dropdown, and its line code.
| Category | Line |
|---|---|
| Rent | Line 3 |
| Advertising | Line 5 |
| Auto and travel | Line 6 |
| Cleaning and maintenance | Line 7 |
| Insurance | Line 9 |
| Legal and professional fees | Line 10 |
| Management fees | Line 11 |
| Mortgage interest | Line 12 |
| Repairs | Line 14 |
| Supplies | Line 15 |
| Taxes | Line 16 |
| Utilities | Line 17 |
| Capital improvement | None |
Use text codes like Line 9, not bare numbers. A bare 9 can be stored as a number in one cell and as text in another, and then SUMIFS silently misses it. Line 9 is always text.
Capital improvements (a new roof, a new furnace) are not deducted on the expense lines; they are depreciated. Mark them None so they stay out of the totals and hand them to your preparer.
2. A transactions table
Put one row per payment in or out on a Transactions tab:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Property | Category | Amount | Line | Year |
| 2 | 2026-01-03 | Maple St | Rent | 1450 | formula | formula |
The two formula columns:
E2: =IFERROR(VLOOKUP(C2, Categories!$A$2:$B$50, 2, FALSE), "?")
F2: =YEAR(A2)
Fill them down. Any category typo shows as ?, so you can spot it instantly. Add a dropdown to column C (Data → Data validation → List from a range → Categories!A2:A50) and typos mostly disappear.
3. The Schedule E summary
On a Summary tab, put property names across row 1 (B1, C1, …) and line codes down column A (A3, A4, …). One formula fills the whole grid:
=SUMIFS(Transactions!$D$2:$D$2000, Transactions!$B$2:$B$2000, B$1,
Transactions!$E$2:$E$2000, $A3, Transactions!$F$2:$F$2000, 2026)
The $ signs are the whole trick. B$1 keeps the row fixed so the property follows the column. $A3 keeps the column fixed so the line follows the row. Copy it across and down.
Total expenses (line 20) is the sum of the expense rows. Income or loss (line 21) is line 3 minus line 20.
Common mistakes
- Mixing the tax year. Always filter on the Year column, and start a fresh copy each January.
- Recording loan payments as an expense. Only the interest part goes on line 12. The principal is not deductible.
- Security deposits. A deposit you expect to return is not income when you receive it. Track deposits separately.
Want it done for you?
The Rental Property Tracker is this exact method, built out and tested:
- 10 properties and 2,000 transaction rows
- a rent roll with late-rent flags
- a deposit ledger, a mileage log and a dashboard
- a Schedule E summary with lines 3–21
This is general information, not tax advice. Check the current IRS Schedule E instructions or ask a tax professional about your situation.
Free tool: Bank CSV Categorizer for Schedule E and Schedule C
Written with AI assistance. Every formula, command and number on this page was tested before publishing (see each section). Product links go to our store.