It's frustrating when you have a rent roll with all the information you want or need, but it's raw and unorganized. I've had a question recently:

"Do you have a tutorial handling a messy rent roll with multiple unit types and renovation standards?"

Today, I plan to teach you Excel skills to break down any complicated rent roll that exhibits:

• Confusing unit codes

• Buildings partially renovated

• Multiple (and random) charges (rent, concessions, parking, discounts)

• Charges are displayed in a different order for each unit

This article aims to teach you how to decode and interpret as efficiently as possible.

Tutorial Contents

1. Rent Roll with Lease Charges Primer

2. Rent Roll Analysis Learning Objective

3. Part I: Scrubbing Rent Roll Data For the Proforma

   • Video: Deciphering a MF Rent Roll with Lease Charges

4. Part II: Move-ins & Lease Expirations

5. Part III Concessions & Ancillary Income

The rent roll with lease charges looks like this:

It is my favorite format to get rental information due to the sheer quantity of relevant and timely data about the following:

• Unit Mix (Floorplans and Square Footage)

• Occupancy/Vacancy

• Unit Renovation Status

• Rental Rates

• Gain/Loss to Lease

• Timelines (Move-ins, Lease Starts, and Lease Expirations)

• Rental Concession Trends

• Ancillary Income Charges (Parking, RUBS, Storage, etc.)

Everything you need is here, but there is one big problem: You must reformat it to get any value from it.

I intend to show you how I dissect a rent roll in the "lease charges" format in under 20 minutes (and faster with practice). Once you learn these skills, you can easily recycle them on any property rent roll delivered in this format.

The methods will work whether a property is 20, 200, or even 1,000 units. The steps I will show you will be effective for all unit counts and no more time-consuming for larger complexes. 

Disclaimer: I use macros when I break down a rent roll, and I have slightly altered my process so viewers can adjust the rent roll using only formulas and simple Excel functions. This adjustment is not materially more time-consuming but is likely more suitable for most viewers.

We are looking at a 27-unit property that we want to input into Tactica's Value-Add Model on the "Renovation" tab.

Four things are required from the rent roll:

1. Unit Types/Floorplans/Renovation Status

2. Unit Count (by Floorplan)

3. Average Square Footage (by Floorplan)

4. Average Rent (By Floorplan)

Time Estimate Once Proficient: 10 - 15 minutes

Note: I always focus on the actual in-place leased rents and ignore the market rents, as market rents are generally subjective and make the analysis more confusing as we'd need to account for proforma loss-to-lease.

This property has undergone a partial renovation, so several renovated and original units exist. As we get the critical data listed above, we can also determine if renovated units see any rent premium vs. their unrenovated counterparts.

After getting the data from the rent roll that proforma requires, you can take the analysis further by looking at the time-sensitive data (move-ins, lease starts, lease expirations).

I think of a rent roll as a snapshot of property performance. It may have been different yesterday and could change tomorrow, but today, it gives you the most detailed depiction of the property's financial health.

You can unlock much more value if you are willing to extract and analyze move-in and lease expiration details for each unit.

The link above is a separate article describing the benefits of reviewing time-sensitive data such as:

• Move-Ins

• Lease Starts

• Lease Expirations

A tutorial video demonstrates how to extract and create visuals to interpret move-ins and lease expirations.

Concessions can be a massive financial burden for a real estate investor. It's crucial to extract all concession data and determine if historical concessions given to residents are:

• Upfront (better)

• Recurring (worse)

And determine your future exposure if you were to purchase the property.

We published two articles that encompass everything there is to know about rental concessions. 

Part I: A Real Estate Investor's Guide to Concessions: Acts more as a high-level primer, details the definitions, and shows basic examples of how you may experience concession data in the rent roll or T12 financials.

Part II: Analyzing & Underwriting Concessions: Dives into how to extract concessions information for the rent roll and input your finding into Tactica's proforma Value-Addd Model. There is a video tutorial that walks you through all the necessary steps.

Ancillary Income

You should also pay attention to what other items ownership charges residents for (parking, utilities, pets, storage, etc.), how much they are charging, and how this compares to the local comp set.

I have a separate article dedicated to ancillary “other” income charges,

Summarizing the Rent Roll with Lease Charges

This series included three articles and videos that take a raw, unorganized rent roll and extract all the valuable data. First, we took out only the essential piece of information to populate Tactica's Multifamily Value-Add Model:

• Unit Types

• Unit Type Count

• Average Square Footage

• Average Actual Rents

Then in Part II extracted and created insightful visualization to interpret the time-sensitive information:

• Move-Ins

• Lease Starts

• Lease Expirations

And finally, In Part III, we bifurcated rental concession data between:

• Upfront

• Recurring

Figured out which unit types were more prone to incentives and assessed future concession exposure. 

Lastly, we pulled out ancillary income charges (specifically utility bill backs) to quickly compare to submarket comps.