How to combine property rent rolls into one portfolio rent roll
By the Analistable team · Updated · 5 min read
Give every property's rent roll the same unit-level columns (property, unit, tenant, status, annual rent, lease end, arrears), stack them with Power Query From Folder or VSTACK, and keep vacant units as rows with zero rent. A pivot by property then shows occupancy, passing rent and arrears, with portfolio totals calculated from the units rather than averaged.
Part of our guide: How to consolidate data in Excel
What a portfolio rent roll needs
Property managers often keep one rent roll per building, each laid out slightly differently. Owners and lenders want one table: every unit in the portfolio, who occupies it, what it pays, when the lease ends and what is owed. That is a stack of the building rolls at unit level, not a sheet of building totals copied across.
| Column | Example | Notes |
|---|---|---|
| Property | Mill Street | Added during the combine |
| Unit | Flat 3 | Unique within a property |
| Tenant | R. Douglas | Blank if vacant |
| Status | Let / Vacant | Typed, not colour-coded |
| Annual rent (£) | 11,400 | 0 for vacant units |
| Lease end | 31/03/2027 | Real date |
| Arrears (£) | 950 | From the rent ledger, as at a stated date |
Step 1: stack the property rolls
- Save each property's roll in one folder, named by property, or keep each as a tab in one workbook.
- Remove title blocks and subtotal rows from each roll; subtotals stacked with units are counted twice.
- In Excel, Data → Get Data → From File → From Folder, Combine & Transform, and turn Source.Name into a Property column.
- Standardise rent to one period. If some rolls hold monthly rent and others annual, multiply monthly by 12 in a new column rather than mixing them.
- Load the combined table and add a Data as at date.
For tabs in one workbook in Microsoft 365, =VSTACK(MillSt!A2:G30, Harbour!A2:G40) works once each tab has a Property column. The Power Query folder method is easier to refresh each month.
Worked example: three properties
| Property | Units | Let | Occupancy | Passing rent (£ pa) | Arrears (£) |
|---|---|---|---|---|---|
| Mill Street | 12 | 11 | 91.7% | 125400 | 1900 |
| Harbour House | 8 | 6 | 75.0% | 81000 | 3250 |
| Elm Court | 20 | 19 | 95.0% | 205200 | 600 |
| Portfolio | 40 | 36 | 90.0% | 411600 | 5750 |
Portfolio occupancy is 36 let units out of 40, 90.0%. The three building percentages averaged would give 87.2%, understating the portfolio because the small, weaker Harbour House would count as much as the 20-unit Elm Court. Always compute totals from the unit rows. Arrears as a share of passing rent is about 1.4% for the portfolio but 4.0% at Harbour House, which, with two empty units, makes it the property to look at.
In a pivot, put Property in rows, and add Count of Unit, Sum of Annual rent and Sum of Arrears as values; occupancy is a count of Status = Let divided by the count of units, which a COUNTIFS beside the pivot handles neatly.
Lease expiries and rent reviews
Once every unit is in one table, the lease event schedule comes for free. Filter Lease end to the next 12 months, or group by quarter, to see how much rent is exposed to renewal:
=SUMIFS(Roll[Annual rent], Roll[Lease end], ">="&DATE(2026,10,1), Roll[Lease end], "<"&DATE(2027,1,1))This gives the rent on leases ending in Q4 2026. Repeat for each quarter to build an expiry profile; a quarter with a large share of portfolio rent expiring is a concentration risk worth planning for.
Checking arrears against payments
The arrears column is only as good as the ledger it came from. Before reporting it, reconcile expected rent with payments received for the period; the step-by-step method is in reconciling a rent roll with rent payments. Typical gaps are payments recorded against the wrong unit, deposits mistaken for rent, and part-payments that leave small balances.
Analistable can load the combined rent roll and the payments export together and answer "tenants more than one month in arrears by property" or "rent expiring in the next six months", writing the SQL and running it in your browser. It reads the spreadsheets but doesn't edit your rent roll or accounting records. For the wider set of consolidation methods, see the consolidating data in Excel guide.
Common problems when combining rent rolls
- Units listed twice: a unit let to a new tenant mid-month can appear once for the outgoing and once for the incoming tenant. Decide whether the roll shows the position at a date (one row per unit) and filter to the current lease.
- Commercial and residential mixed: shops and offices are usually measured by floor area and rent per square foot or metre, flats by unit. Keep a Use column and an Area column so both views are possible.
- Service charge in the rent: some rolls include service charge or ground rent in the rent figure, others don't. Split them into separate columns before totalling passing rent.
- Rent-free periods: a unit in a rent-free period is let but paying nothing. Keep contracted rent and current cash rent as two columns so occupancy and income both read correctly.
- Different reporting dates: every roll should be as at the same date. A roll from last month alongside this month's makes arrears look inconsistent.
A short checklist sent with the template each month — same date, unit-level rows, vacant units included, annual rent — prevents most of these before they reach the combined table.
Frequently asked questions
- Should vacant units be included in a combined rent roll?
- Yes, as rows with a Vacant status and zero rent. Without them you can't calculate occupancy, and the unit count no longer matches the building.
- How do I calculate portfolio occupancy?
- Divide the total number of let units by the total number of units across all properties. Don't average each property's occupancy percentage. For commercial property, occupancy by floor area is often more meaningful than by unit count.
- Some rolls show monthly rent and others annual. What should I do?
- Convert everything to one basis in a new column before combining, and label it clearly. Mixing monthly and annual figures in one column is the most common rent roll error.
- How often should a portfolio rent roll be refreshed?
- Usually monthly, after rent has been collected and arrears updated. If it is built in Power Query, replacing the property files and refreshing takes minutes; keep a dated values-only copy each month for history.