Rent roll template for Excel: what a small landlord's rent roll needs
A rent roll is one row per unit showing who lives there, what the rent is and what has actually been paid. Here are the columns that earn their place and the formulas that compare scheduled rent with rent collected.
Free: Rent Due & Late Fee Calculator, one Excel tab. Confirm your email and the file arrives right away, plus an occasional note (at most one a week) on running a small rental. Unsubscribe in one click. Privacy.
General information only, not tax, legal or accounting advice. Late-fee, deposit and notice rules vary by state, city and lease; check your own. Tax references describe US federal pages on irs.gov as of the date above.
What a rent roll is for
A rent roll is one row per unit: who lives there, what the rent is, when the lease ends and what has been paid. For a landlord with two to ten units it answers three questions on one screen. Which units are occupied? How much rent should arrive this month? How much actually did?
The columns
| Column | Typed or formula | Why it earns its place |
|---|---|---|
| Unit and property | Typed | One row per unit, vacant ones included. Deleting a vacant unit hides the vacancy. |
| Tenant | Typed | Blank when the unit is vacant. |
| Status | Typed (Occupied or Vacant) | Drives the totals. A dropdown list keeps the spelling identical, which SUMIFS needs. |
| Lease start and end | Typed | Real dates, not text, so you can subtract them later. |
| Monthly rent | Typed | Rent under the lease. For a vacant unit, the asking rent. |
| Due day | Typed | The day of the month rent is due. |
| Deposit held | Typed | Its own column, never mixed into rent (see below). |
| Collected this month | Typed | What actually arrived, including part payments. |
| Still owed | Formula | Rent minus collected, for occupied units only. |
Scheduled rent versus collected rent
Scheduled rent is what the leases say should arrive. Collected rent is what did arrive. The gap between them is your chase list. A vacant unit is a different kind of gap: nobody owes you that rent, so it should not be counted as scheduled or it will look like a tenant who did not pay.
Here is a four-unit building where unit 2B is empty and the tenant in 2A has paid half:
| Unit | Status | Rent | Collected | Still owed |
|---|---|---|---|---|
| 1A | Occupied | $1,450 | $1,450 | $0 |
| 1B | Occupied | $1,500 | $1,500 | $0 |
| 2A | Occupied | $1,600 | $800 | $800 |
| 2B | Vacant | $1,550 (asking) | $0 | $0 |
With headers in row 1 and the units in rows 2 to 5 (Unit in A, Status in B, Rent in C, Collected in D, Still owed in E):
Still owed (per row) E2: =IF(B2="Occupied", C2-D2, 0)
Scheduled rent =SUMIFS(C2:C5, B2:B5, "Occupied") 1,450+1,500+1,600 = 4,550
Collected =SUM(D2:D5) 1,450+1,500+800 = 3,750
Still owed =SUM(E2:E5) 4,550-3,750 = 800
Collection rate =IF(Scheduled=0, 0, Collected/Scheduled) 3,750/4,550 = 82.4%
Vacant asking rent =SUMIFS(C2:C5, B2:B5, "Vacant") 1,550
Potential rent =SUM(C2:C5) 4,550+1,550 = 6,100Scheduled, Collected and the other labels stand for the cells holding those results. Read the last figures together: potential rent of $6,100 minus the $3,750 collected is $2,350 not received, and it splits into $1,550 of vacancy and $800 of unpaid rent. Those are two different problems and the sheet keeps them apart.
Keep deposits and advance rent out of the wrong column
Collected means rent. The IRS treats a security deposit differently. Publication 527 (2025) says: "Don't include a security deposit in your income when you receive it if you plan to return it to your tenant at the end of the lease." It also says advance rent, meaning "any amount you receive before the period that it covers," is included in rental income in the year you receive it. Keep deposits in their own column so a deposit never inflates a month's collected rent, and if a tenant pays early, add a note so next month's "still owed" does not look wrong. Source: IRS Publication 527.
The 2025 Schedule E instructions say: "If you received rental income from real estate (including personal property leased with real estate), report the income on line 3." The rent you collect across the year is the figure that feeds that line. Source: IRS, Instructions for Schedule E.
Save a copy each month
The Collected column is overwritten every month, so a rent roll is a snapshot, not a history. Save the file under a new name at month end (for example, rent-roll-2026-10) and the snapshots become your history until you move to a one-row-per-unit-per-month ledger.
What this does not cover
A rent roll does not calculate late fees, keep a month-by-month record, track expenses or reconcile a deposit account. It does not know your state's rules on late fees, deposits or notices, and nothing here is a legal requirement for how a rent roll must look. It is a layout that works for a handful of units.
Sources
- IRS Publication 527, Residential Rental Property (2025)
- IRS, Instructions for Schedule E (Form 1040), 2025
More landlord guides
- Security deposit ledger template for Excel: columns and a reconciliation check
- Schedule E expense categories spreadsheet: sorting receipts into lines 5-19
- Rental property vacancy rate spreadsheet: physical vs economic vacancy
- Rental property income and expense spreadsheet: a 12-month duplex log
- Rent payment tracker spreadsheet: a late-status formula
- Prorated rent formula in Excel for a mid-month move-in
- Lease expiration tracker spreadsheet with a rent increase preview
- Late fee calculator in Excel: a formula with a grace period
- Duplex profit and loss per unit: splitting shared costs and finding NOI
Already built
The Units & Leases tab of the Landlord Rent & Expense Tracker is a rent roll: unit, property, tenant, lease start and end, rent, due day and deposit, with days to expiry and annual potential rent. The Rent Ledger tab keeps a row per unit and month with due, paid and a status of Paid, Paid late, Partial, Late or Due, and the P&L tab shows physical and economic vacancy. It is one Excel file, $29 one time, with no bank link and no account.