Expert guide

Rent Roll Spreadsheet in Excel

A rent roll that answers the owner's questions and survives a lender's due diligence

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: keep one row per unit in an Excel Table with property, unit, size, tenant, lease dates, contract rent, market rent, deposit, other charges, balance and status. Add formulas for days to expiry, loss-to-lease and annualised rent, a summary of physical and economic occupancy, a lease expiry schedule by quarter, and an arrears ageing. Save a dated snapshot every month. For portfolios that need a payments ledger and lease history, see Excel to database solutions.

A rent roll looks simple until someone else relies on it. An owner wants to know what the property earns; a lender wants to know what it will earn and how secure that is; a buyer wants to find every discount and every lease ending next year. Most landlord spreadsheets answer the first question and fail the others, usually because lease dates, market rents and balances are not kept consistently.

The Rent Roll Columns

ColumnNotes
Property, Unit, Type, SqFtOne row per unit, including vacant units
TenantBlank when vacant
LeaseStart, LeaseEndReal dates; month-to-month leases flagged separately
ContractRentCurrent monthly rent per the lease
MarketRentYour current asking rent for that unit type
ConcessionMonthly value of any discount or free rent
OtherChargesParking, storage, pet, utilities recovery
DepositHeld, for liability reporting
Balance, LastPaidFrom the payments ledger
StatusOccupied, Vacant, Notice given, Eviction, Model/Down

Calculated Columns

Days to lease end:
=IF([@LeaseEnd]="", "", [@LeaseEnd] - TODAY())

Lease status:
=IF([@Status]<>"Occupied", [@Status],
    IF([@LeaseEnd]="", "Month-to-month",
    IF([@LeaseEnd] < TODAY(), "Expired (holdover)",
    IF([@LeaseEnd] - TODAY() <= 90, "Expiring 90 days", "Current"))))

Effective rent (after concessions):
=[@ContractRent] - N([@Concession])

Loss to lease (monthly):
=IF([@Status]="Occupied", [@MarketRent] - [@[Effective rent]], 0)

Rent per sq ft (annual):
=IF([@SqFt]>0, [@[Effective rent]] * 12 / [@SqFt], "")

Summary: The Numbers Owners and Lenders Ask For

Total units:              =ROWS(tblRentRoll)
Occupied units:           =COUNTIFS(tblRentRoll[Status], "Occupied")
Physical occupancy:       =Occupied / Total

Gross potential rent:     =SUM(tblRentRoll[MarketRent])
In-place rent:            =SUMIFS(tblRentRoll[Effective rent], tblRentRoll[Status], "Occupied")
Economic occupancy:       =In-place rent / Gross potential rent
Loss to lease:            =SUM(tblRentRoll[Loss to lease])

Annualised in-place rent: =In-place rent * 12
Deposits held:            =SUM(tblRentRoll[Deposit])

Economic occupancy will always be lower than physical occupancy when there are concessions or below-market leases. The gap between the two is one of the first things a buyer or lender will look at.

Lease Expiry Schedule

Units expiring in a quarter (quarter start date in A2):
=COUNTIFS(tblRentRoll[LeaseEnd], ">=" & A2, tblRentRoll[LeaseEnd], "<" & EDATE(A2, 3))

Rent expiring in that quarter:
=SUMIFS(tblRentRoll[Effective rent], tblRentRoll[LeaseEnd], ">=" & A2,
        tblRentRoll[LeaseEnd], "<" & EDATE(A2, 3))

Share of rent expiring:  =B2 / In-place rent

Lay out eight quarters ahead. A quarter where a large share of rent expires is a concentration risk to manage with staggered renewals.

Arrears Ageing

Balances belong in a payments ledger, not typed onto the rent roll. A simple ledger table (Unit, Date, Type: Charge or Payment, Amount) lets you calculate both the balance and its age.

Balance per unit:
=SUMIFS(tblLedger[Amount], tblLedger[Unit], [@Unit], tblLedger[Type], "Charge")
 - SUMIFS(tblLedger[Amount], tblLedger[Unit], [@Unit], tblLedger[Type], "Payment")

Last payment date:
=MAXIFS(tblLedger[Date], tblLedger[Unit], [@Unit], tblLedger[Type], "Payment")

Arrears bucket (by days since last payment, approximate):
=IF([@Balance] <= 0, "Current",
    IFS(TODAY() - [@LastPaid] <= 30, "0–30",
        TODAY() - [@LastPaid] <= 60, "31–60", TRUE, "60+"))

Monthly Snapshots

  • At month end, copy the rent roll as values to a sheet named RR_2026-11 (or append to a history table with a SnapshotDate column).

  • A history table lets you chart occupancy, in-place rent and arrears over time.

  • Lenders frequently ask for trailing 12-month rent rolls; snapshots make that a filter, not a reconstruction.

Common Rent Roll Mistakes

  • Leaving vacant units off the list, which inflates occupancy.

  • Typing balances manually, which drifts from the bank.

  • Not recording concessions, which overstates rent.

  • Leaving lease end blank for month-to-month tenants without flagging them.

  • Mixing several properties without a Property column, which makes portfolio reporting impossible.

Where AI Helps a Landlord

AI can extract lease start, end, rent, deposit and special clauses from signed lease PDFs to populate or check the rent roll, the same technique as extracting PDF data into Excel with AI. It can also draft renewal offers from the row data. Always verify extracted lease terms against the signed lease.

When the Spreadsheet Is Not Enough

A rent roll in Excel is fine for one person and a modest portfolio. When several staff record payments, you need lease history per unit, or tenants and maintenance requests need tracking too, move the data into related tables. Our custom Excel spreadsheet work covers well-structured workbooks, and Excel to database solutions covers the move to Access or SQL Server when the time comes.

FAQ

Rent roll spreadsheet FAQ

Columns, occupancy and lender reporting

A rent roll is a list of every unit in a property or portfolio showing the tenant, lease start and end dates, monthly rent, deposit, current balance and status (occupied, vacant, notice given). Owners use it to manage income, and lenders and buyers ask for it to value a property.

Property, unit, unit type and size, tenant name, lease start, lease end, monthly rent, market rent, deposit held, recurring charges (parking, storage), balance owed, last payment date, status and notes. Lenders often also want rent per square foot and lease expiry schedules.

Physical occupancy is occupied units divided by total units: =COUNTIFS(Status,"Occupied")/COUNTA(Unit). Economic occupancy is rent actually collected divided by potential rent at full occupancy, which also captures discounts, concessions and unpaid rent.

At least monthly, after rent is posted, and immediately whenever a lease is signed, renewed or ended. A stale rent roll is one of the most common problems found during property refinancing or sale due diligence.

When you manage enough units that tenant payments, maintenance requests and lease renewals need separate workflows, or multiple people update the file. Many small landlords keep the rent roll in Excel and add a structured payments ledger and lease tracker before moving to full property management software.