How to Create a Rent Roll in Excel

A rent roll is not simply a list of tenants.

It is a snapshot of the units, leases, and rental income attached to a property or portfolio.

The most important distinction is this:

A rent roll tells you what is contractually expected. A payment ledger tells you what actually happened.

Keeping those two concepts separate makes rental records more reliable and helps landlords understand the difference between expected income and actual collections.

What Belongs in a Rent Roll?

A practical rent roll can contain:

• Property ID
• Unit
• Tenant
• Lease start date
• Lease end date
• Contractual monthly rent
• Security deposit
• Occupancy status
• Other recurring charges
• Estimated market rent, if tracked

For example:

PropertyUnitTenantMonthly rentLease endStatusP0011AJordan Lee$1,65006/30/2027OccupiedP0011BTaylor Reed$1,80003/31/2027OccupiedP0022A—$1,900—Vacant

This table gives the landlord an overview of the portfolio's current leasing position.

For more detailed reporting, you can also include lease type, renewal status, rent frequency, and the date of the last rent adjustment.

What Does Not Belong in the Core Rent Roll?

Detailed payment history should be maintained separately.

Suppose Taylor's contractual rent is $1,800 per month, but they pay $900 twice during the month.

The rent roll should still show:

Contractual monthly rent = $1,800

The payment ledger should show:

DateAmount receivedOctober 1$900October 8$900Total received$1,800

This separation allows the landlord to see both the contractual rental income and the actual collection performance.

It also preserves a transaction history that can be reconciled against bank records.

Add Lease-Expiration Analysis

One of the most useful things you can do with a rent roll is sort leases by expiration date.

Create these reporting buckets:

• 0–30 days
• 31–60 days
• 61–90 days
• 91–180 days
• More than 180 days

Then calculate the contractual monthly rent represented by each group.

For example, if $14,000 of monthly rent is attached to leases expiring within 90 days, that is more useful operationally than simply reporting that three leases expire soon.

The amount of rent at risk matters, not just the number of leases.

A useful report should show both the number of expiring leases and their combined monthly rent. This helps landlords prioritize renewals, marketing, and vacancy planning.

Current Rent vs. Market Rent

You can extend the rent roll to compare contractual rent with estimated market rent.

UnitCurrent rentEstimated market rentDifference1A$1,650$1,750$1001B$1,800$1,900$100

This comparison can help identify units worth reviewing when a lease comes up for renewal.

However, treat market rent as an analytical estimate, not a replacement for contractual rent.

An estimated $100 difference does not automatically mean you can increase the rent by $100. Lease terms, local regulations, notice requirements, and applicable rent-increase rules still matter.

Keep the Rent Roll Current

Update the relevant records whenever:

• A tenant moves in.
• A tenant moves out.
• A lease is renewed.
• Contractual rent changes.
• A unit becomes vacant.
• A lease is terminated.

Do not recreate the rent roll from scratch every month.

Maintain the current unit and lease records, preserve historical changes where needed, and generate reports from the underlying data.

This reduces duplication and makes it easier to identify when information was last updated.

Why a Rent Roll Becomes Valuable as a Portfolio Grows

With one property, you may remember most of the relevant information.

With 20 properties, relying on memory becomes much less practical.

A structured rent roll lets you answer questions such as:

• What is the portfolio's current contractual monthly rent?
• Which units are vacant?
• Which leases expire soon?
• How much monthly rent is associated with upcoming renewals?
• Which properties have the greatest lease-expiration exposure?
• How does current rent compare with estimated market rent?

These answers help landlords plan renewals, anticipate potential vacancies, and understand the portfolio's leasing position.

The rent roll becomes an operational tool rather than just another spreadsheet.

Rent Roll vs. Payment Ledger: Keep Them Separate

The rent roll describes the current leasing position. The payment ledger records transactions.

Neither replaces the other.

The rent roll helps answer, "What rent should be charged under the current lease records?"

The payment ledger helps answer, "What money was received, when was it received, and how was it allocated?"

Combining both into one table can make partial payments, arrears, lease changes, and historical reporting harder to manage.

Keeping the records separate gives landlords a clearer picture of both expected income and actual performance.

Sources

IRS — Publication 527: Residential Rental Property

https://www.irs.gov/publications/p527

This publication provides broader guidance for U.S. rental-property owners on rental income, expenses, and recordkeeping.