A rent roll is one of the first things a lender, investor, or buyer asks for when evaluating a property. It's also the document most property owners spend too much time maintaining because they never set it up properly in the first place. Here's how to build one in Excel that's clean, accurate, and easy to keep current.
What a Rent Roll Needs
A rent roll is a snapshot of every unit in a property (or portfolio): who's in it, what they're paying, when their lease started, and when it expires. At minimum, your columns should be: unit number, tenant name, lease start date, lease end date, monthly rent, security deposit, and status (Occupied, Vacant, Notice Given, Month-to-Month).
For a portfolio with multiple properties, add a Property column at the front so you can filter and summarize by property.
Calculating Key Metrics
Add a Days Until Lease Expiry column:
=IF(D2="", "", D2-TODAY())
Where D2 is the lease end date. Negative means expired. Apply conditional formatting: red for expired or negative, amber for 0–60 days, green for 60+. This gives you an instant view of upcoming lease renewals.
Add a Monthly Rent per Square Foot column if you track unit size:
=E2/G2
Where E2 is monthly rent and G2 is square footage. This lets you compare rent efficiency across units and spot below-market leases.
Portfolio Summary
Add a summary section at the top or on a separate sheet:
- Total units: =COUNTA(A2:A100)
- Occupied units: =COUNTIF(H2:H100, "Occupied")
- Vacant units: =COUNTIF(H2:H100, "Vacant")
- Occupancy rate: =COUNTIF(H2:H100, "Occupied")/COUNTA(A2:A100)
- Total monthly rent (occupied): =SUMIF(H2:H100, "Occupied", E2:E100)
- Potential gross rent (all units at current rate): =SUM(E2:E100)
- Vacancy loss: =SUMIF(H2:H100, "Vacant", E2:E100)
Format occupancy as a percentage. These six numbers are what investors and lenders want to see immediately.
Leases Expiring in the Next 90 Days
Use COUNTIFS to count leases expiring soon:
=COUNTIFS(D2:D100, ">="&TODAY(), D2:D100, "<="&TODAY()+90, H2:H100, "Occupied")
And SUMIFS to show the rent at risk:
=SUMIFS(E2:E100, D2:D100, ">="&TODAY(), D2:D100, "<="&TODAY()+90, H2:H100, "Occupied")
These two numbers — leases expiring and rent at risk — are what your asset management team needs to prioritize renewal conversations.
Rent Roll for Lenders and Investors
When sharing externally, create a clean export tab. Use formulas to pull only active leases, sorted by unit number, with only the columns the recipient needs. Hide the working columns. This gives lenders a professional document without exposing your internal tracking data.
The Easy Way: Using SheetXAI in Excel
Example 1: You have unit and lease data already in the spreadsheet.
"I have a unit list on Sheet 1 with tenant names, lease dates, and monthly rent. Build a rent roll with occupancy rate, total rental income, leases expiring in the next 90 days, and conditional formatting to flag expired or soon-to-expire leases."
SheetXAI reads your data, builds the summary metrics, adds the expiry flags, and applies the conditional formatting.
Example 2: Your lease data lives in a property management system.
"Pull lease data from our property management system and build a rent roll showing all units, tenant names, lease terms, monthly rent, and an occupancy summary for each property."
SheetXAI connects to your system, pulls the data, and builds the full rent roll.
Try SheetXAI free and see what it builds for you.
Published May 2026. See also: How to Build a Real Estate Deal Pipeline in Excel, How to Build a Budget vs. Actuals Report in Excel, and Google Sheets AI Guide.
