A rent roll is one of the most useful tools for understanding a rental property’s income at a glance. It lists each unit, its tenant, lease terms, rent, and payment status—turning scattered lease and bookkeeping details into a single, practical record.
A well-organized rent roll template in Excel can help landlords monitor occupancy, follow up on unpaid rent, prepare for renewals, and communicate a property’s performance to lenders or investors. But a spreadsheet is only as useful as the information it contains. This guide explains what to include, how to build a reliable template, and how to use it without mistaking it for a substitute for proper accounting or lease records.
What Is a Rent Roll?
A rent roll is a summary of the rental units in a property or portfolio and the income associated with them. It commonly shows who occupies each unit, what rent is due, when the lease expires, and whether the unit is occupied.
Owners and property managers use rent rolls to:
- Review rental income across a property or portfolio
- See which units are vacant or occupied
- Track lease start and end dates
- Identify upcoming renewals and expirations
- Monitor rent collections and balances due
- Share an overview with lenders, investors, or advisers
A rent roll is a snapshot, not a complete accounting ledger. It summarizes key information but doesn’t necessarily show every charge, payment, credit, deposit movement, or expense. For those details, use a dedicated accounting system or a separate, well-maintained transaction log.
Why Use an Excel Rent Roll Template?
Excel is a practical option for owners who want a flexible, familiar way to organize property information. A template provides a consistent structure without requiring specialized property-management software.
It makes portfolio information easier to review
Instead of checking several leases or separate notes, you can view unit and tenant information in one table. Filters can help you display only vacant units, leases expiring soon, or tenants with a balance due.
It can support planning
Lease end dates and renewal terms help you anticipate turnover. Current and scheduled rents can also help you prepare a budget or assess whether you need to review rents at renewal, subject to applicable laws and lease terms.
It creates a useful reporting snapshot
A clearly presented rent roll can help a lender or prospective buyer understand the property’s occupancy and contracted rental income. However, the recipient may require a specific format or supporting documentation, so confirm their requirements before relying on your spreadsheet as a formal submission.
What to Include in a Rent Roll Template
The right columns depend on the property and how you manage it. A basic template should capture enough information to identify each unit, understand the lease, and track rent.
| Column | What it records |
|---|---|
| Property | Property name or address, especially for a portfolio |
| Unit | Apartment, suite, or unit number |
| Tenant | Tenant name or names listed on the lease |
| Occupancy status | Occupied, vacant, or another clearly defined status |
| Lease start date | Beginning of the current lease term |
| Lease end date | Scheduled expiration date |
| Monthly rent | Contracted monthly rent |
| Rent due date | Day of the month rent is due |
| Payment status | Paid, partial, unpaid, or another defined status |
| Amount received | Payment credited for the period |
| Balance due | Amount still outstanding for the period |
| Security deposit held | Deposit amount recorded for the tenancy |
| Other recurring charges | For example, a recurring parking charge, if applicable |
| Notes | Renewal discussions, maintenance coordination, or other relevant reminders |
Keep rent and other charges distinct
It may be tempting to combine rent, parking, utilities, and fees into one figure. Separate columns make the underlying amounts easier to review and reconcile. They also reduce the risk of presenting non-rent charges as rental income.
Define statuses clearly
Use a short, consistent list of statuses rather than free-form entries. For example:
- Occupied
- Vacant
- Notice given
- Pending move-in
For payments, distinguish paid, partial, and unpaid. A clear definition matters: “paid” should refer to the period and amount you are tracking, not simply a tenant’s general account standing.
How to Build a Rent Roll in Excel
1. Set up one row per unit
Create a header row and put each rental unit on its own row. If you manage multiple properties, add a property column so you can sort or filter by location.
Avoid merging cells within the data table. A simple rectangular structure is easier to sort, filter, and use in formulas.
2. Format the data as an Excel Table
Select the range and use Excel’s Format as Table feature. Tables make it easier to apply consistent formatting, filter records, and extend formulas when you add units.
Use descriptive column names, such as Lease End Date or Monthly Rent, rather than vague headings like Date or Amount.
3. Use consistent formats
Apply:
- A date format to lease and due dates
- Currency formatting to rent, deposits, and balances
- Consistent spelling for status fields
- A clear convention for blank, unknown, and not-applicable information
Do not use a zero to mean “unknown.” A zero rent amount and a missing rent amount mean different things.
4. Add simple formulas
For a basic monthly collection view, a balance formula might be:
=MAX(0,[@[Monthly Rent]]-[@[Amount Received]])
This is only an illustration. It works when the amount received applies to the same period and the balance is calculated against monthly rent alone. It does not account for prior balances, credits, late fees, partial-period charges, or other adjustments. If those are relevant, use a separate transaction ledger and calculate the balance from the appropriate records.
You can also calculate days until lease expiration:
=[@[Lease End Date]]-TODAY()
Consider flagging leases that expire within a chosen planning window. For example:
=IF(AND([@[Lease End Date]]>=TODAY(),[@[Lease End Date]]<=TODAY()+90),"Review","")
The number of days is a planning choice, not a legal deadline. Set reminders in line with the lease and local requirements.
5. Use data validation for status fields
Excel’s data validation feature can provide a drop-down list for occupancy and payment statuses. This helps prevent entries such as “Vacant,” “vacant,” and “Empty” from being treated as unrelated values in filters or summaries.
6. Add conditional formatting thoughtfully
Conditional formatting can draw attention to:
- Leases approaching expiration
- Vacant units
- Balances due
- Missing lease dates or rent information
Use color as a prompt to review a record, not as a replacement for checking the underlying data.
7. Create a summary section
A summary at the top of the workbook or on a separate worksheet might include:
- Total units
- Occupied units
- Vacant units
- Contracted monthly rent
- Rent collected for the selected period
- Total balances due
- Leases expiring within the next 90 days
Make sure the summary describes exactly what it measures. For example, “contracted monthly rent” is not necessarily the same as actual cash collected or a property’s net operating income.
A Simple Rent Roll Example
Here is a simplified illustration:
| Property | Unit | Tenant | Status | Lease end | Monthly rent | Amount received | Balance due |
|---|---|---|---|---|---|---|---|
| Oak Street | 1A | Jordan Lee | Occupied | 2027-03-31 | $1,650 | $1,650 | $0 |
| Oak Street | 1B | Morgan Patel | Occupied | 2026-12-31 | $1,725 | $1,200 | $525 |
| Oak Street | 2A | — | Vacant | — | $0 | $0 | $0 |
This example shows why it helps to keep occupancy and payment information distinct. Unit 1B is occupied but has a balance due, while Unit 2A is vacant and has no current tenant rent to collect. A real workbook should also identify the reporting period for payment figures, such as “Amount received for November 2026.”
Common Rent Roll Mistakes to Avoid
Treating scheduled rent as collected income
A lease may call for a particular amount, but that does not mean the tenant paid it. Keep contracted rent and cash received in separate fields or reports.
Forgetting the reporting period
A payment amount without a month or date range is ambiguous. Label the reporting period prominently, or maintain a separate rent roll for each period.
Leaving old information in place
When a tenant moves out or renews, update the record carefully. Preserve prior leases and payment history in your document-management or accounting system rather than overwriting important records without an audit trail.
Mixing charges and deposits with rent
Deposits, reimbursements, fees, and other charges may be treated differently from rent for accounting, legal, or reporting purposes. Record them separately and follow the applicable rules.
Relying on the spreadsheet as the only record
A rent roll is not a lease, receipt, bank statement, or complete tenant ledger. Keep source documents and transaction records, and reconcile the spreadsheet regularly against them.
Sharing sensitive information too broadly
Rent rolls contain personal and financial information. Limit access to people who need it, use appropriate file permissions, and avoid sending unprotected copies when a safer sharing method is available.
Best Practices for Maintaining Your Rent Roll
- Update it consistently. Choose a routine, such as updating after payments are posted and leases are signed or amended.
- Reconcile it to source records. Compare payment figures with your bookkeeping system and bank records.
- Use a clear file-naming convention. Include the property name and reporting date, such as
Oak-Street-Rent-Roll-2026-11.xlsx. - Keep historical copies. Save dated versions or use a system that preserves change history.
- Protect formulas and headers. Worksheet protection can help prevent accidental edits, though it is not a substitute for secure storage.
- Back up the workbook. Keep backups in a secure location and follow your organization’s data-retention practices.
- Review formulas before sharing. Check that totals, date flags, and filters include all relevant rows and that summary labels are accurate.
A Rent Roll Is Not the Same as a Profit-and-Loss Statement
A rent roll helps answer questions such as, “What rent is scheduled for each unit?” and “Which leases are nearing expiration?” A profit-and-loss statement tracks income and expenses over a period. It may include items such as repairs, insurance, taxes, utilities, and management fees.
Likewise, gross scheduled rent should not be confused with collected income, net operating income, or property value. Those measures depend on additional information and calculation methods. If you are preparing financial statements, tax filings, financing materials, or a property valuation, confirm the required definitions and supporting records with the relevant professional or institution.
Conclusion
An Excel rent roll template can make rental-property information easier to organize and review. Start with one row per unit, use consistent fields for lease and payment information, and keep rent separate from other charges. Add formulas and formatting only where they clarify the data, and reconcile the spreadsheet against leases and accounting records on a regular schedule.
Most importantly, treat the rent roll as a useful summary—not as the entire record of a tenancy or the property’s financial performance. A current, carefully defined spreadsheet can support better decisions, while accurate source documents and good recordkeeping provide the foundation.
Sources
- Microsoft Support, Overview of Excel tables — information on using tables to organize and manage spreadsheet data.
- Microsoft Support, Apply data validation to cells — guidance on restricting entries and creating drop-down lists.
- Microsoft Support, Use conditional formatting to highlight information in Excel — guidance on highlighting values based on rules.
- U.S. Department of Housing and Urban Development, Fair Housing — federal fair-housing information; property managers should also check applicable state and local requirements.
- Internal Revenue Service, Publication 527: Residential Rental Property — U.S. federal tax guidance for residential rental property. This publication is not a substitute for advice about your specific tax situation.