How to Build a Rent Roll in Excel for Rental Properties

A rent roll in Excel is a single table that lists every unit of a rental property with its tenant, rent, lease dates and status. It answers the questions an owner, a property manager or a lender asks first: how much rent is coming in, how much is lost to empty units, and which leases end soon. This guide builds one step by step with EDATE, SUMIF and COUNTIFS, using a small seven-unit building you can copy.

What is a rent roll?#

A rent roll is a snapshot of a property's income on one date. Each row is one unit, and each column is one fact about it. It is not an accounting ledger: it doesn't record every payment ever made, only the current position.

A typical rent roll includes:

• Unit and tenant name (blank for a vacant unit).
• Monthly rent: the rent in the lease, or the asking rent for a vacant unit.
• Lease start, term in months and lease end.
• Status: Occupied, Vacant or Notice.
• Paid this month and the balance owed.

Keep vacant units in the list. Leaving them out hides the rent you could be earning, which is the number buyers and lenders look for.

Step 1: Set up the rent roll columns#

Type the headers in row 1 and one unit per row below. Here is the example building, with a report date of 1 November 2026 in cell K1 (=DATE(2026,11,1)):

A few setup tips make the formulas easier later:

• Type unit numbers as text if they have letters or leading zeros (like 01A), so Excel doesn't change them.
• Use a drop-down list for Status (Data > Data Validation > List), so "Occupied" is never typed as "occupied " with a stray space.
• Press Ctrl+T (Mac: ⌘T) to turn the range into a table, so new units pick up the formulas automatically.

Step 2: Calculate lease end dates with EDATE#

EDATE adds a number of whole months to a date. A 12-month lease that starts on 1 March ends the day before the same date a year later, so subtract 1. In G2:

=IF(E2="","",EDATE(E2,F2)-1)

For unit 101, EDATE(1 Mar 2026, 12) is 1 Mar 2027, and minus 1 gives 28 February 2027. The IF leaves the cell blank for vacant units with no start date. EDATE handles short months for you: a lease that starts on 31 January and runs 1 month gives EDATE of 28 February in a normal year.

If the result shows a number like 46446, format the column as a date (Windows: Ctrl+1; Mac: ⌘1). The EDATE function guide lists the syntax, and EDATE and EOMONTH explained covers month-end tricks.

Step 3: Total the rent by status with SUMIF#

SUMIF adds the rent of the rows that match one condition. Below the table, build a short summary:

SUMIF reads as "check the Status column, match Occupied, add the Rent column". The occupied rent and the vacancy loss should always add up to the total: 7,525 + 2,775 = 10,300. That is a quick check that no unit has a misspelled status. The SUMIF function guide has the full syntax.

The two occupancy figures differ because vacant units have different rents. Unit occupancy counts doors; the rent share weighs each unit by its rent.

Step 4: Flag leases that expire soon#

Leases ending soon need a renewal call, so count them and flag them. To count leases ending within 90 days of the report date in K1:

=COUNTIFS(G2:G8,">="&$K$1,G2:G8,"<="&$K$1+90)

The result is 2: unit 201 ends on 30 November 2026 and unit 203 on 14 January 2027. Blank lease ends for vacant units aren't counted, because a blank is not a date after K1.

For a flag on each row, add a column with:

=IF(G2="","",IF(G2<$K$1,"Expired",IF(G2-$K$1<=90,"Expiring","")))

The $ signs keep K1 fixed when you fill the formula down. To highlight the flagged rows, add a conditional formatting rule on the Status or Flag column. The same expiry logic drives a subscription tracker in Excel, where renewals replace leases.

Step 5: Track payments and balances#

Add two columns: Paid for what each tenant paid this month, and Balance for what is still owed. In the Balance column:

=IF(H2="Occupied",D2-I2,0)

If unit 201 paid 800 of its 1,600 rent, its balance is 800. Total the column with SUM to see the rent outstanding. When balances run over several months, sort them by how late they are with an aging report in Excel, and track your own property bills with a bill tracker.

Common rent roll mistakes#

These errors make a rent roll look healthier, or worse, than it is:

1. Leaving vacant units out. The total rent then looks like 100% occupancy. Keep every unit.
2. Status typed in different ways. "Occupied", "occupied " and "Occ" are three different values to SUMIF. Use a drop-down list.
3. Lease end without the −1. =EDATE(E2,F2) gives the first day of the next lease, so a lease looks one day longer.
4. Using TODAY() in a filed report. The flags change every day you open it. Put the report date in one cell.
5. Mixing monthly and yearly rent. Keep one unit of time in the Rent column, usually monthly, and label it.

Rent rolls are a common task in property jobs, along with price per square foot and commission splits; see Excel skills for real estate.

Practise it#

A rent roll is mostly SUMIF and date maths, done carefully. Practise SUMIF in a live spreadsheet: you type the formula and it checks it instantly. Then practise EDATE, EOMONTH and WEEKDAY for lease and renewal dates, and work through the real estate listings case study, which asks you to analyse listings the way a property analyst would.