Why Do Spreadsheets Win?

Real estate is a business. Hands-on transformative and operational work is rewarding, but tracking the numbers associated with it is essential. Otherwise the long-term plan falls apart in the face of short-term ideas.

This is why spreadsheets are ubiquitous. They help track, measure, and manipulate data quickly and with quality to support the business plan.

The most common are Google Sheets, Apple’s Numbers, and the far and away most used – Microsoft Excel.

If you didn’t get trained in spreadsheets in school, navigating and using them is similar to learning another language. However it’s worth learning! They are SO helpful for breaking down and tracking data that it is essential to learn the basics. Just like with another language, it’s useful to start with what you’ll use a lot and build from there.

(Side-note, I LOVE spreadsheets. If you want to talk through any of this, I’m here for you!)

For today, let’s cover some tips and tricks for understanding rent rolls!

[Example Rent Roll]

I use the desktop version of Excel, though these also apply to Google Sheets.

  • Quick Note: Don’t trust a summary provided by someone else. Make your own before going under contract (or have your team make one per your standard process). Some reasons why:
  1.  The data they used for their summary could be old or incorrect
  2. They could be using Market Rent instead of Actual Rent
  3. They could be counting for vacancy differently than you want to (are units under notice or eviction “occupied”?)

Tip 1) Instantly List All Unit Types

Rent rolls have similar information all set up in different ways. Different property managers use different software, and different owners prefer different labels of everything from unit type to how to label “vacant” units. This means to understand what the rent roll is telling you, you’ll need to break it into your own common assumptions. You can outsource this. However it is helpful to know how to do a few quick things on your own.

  • Example: Unit Types differ between properties but not at the same property so I start there.
    • =UNIQUE(All cells with Unit Types): This is really helpful as it instantly gives you a list of all the different types of units in the rent roll which you can then use to find out other compiled data about. Many times they are marked if they are renovated or not too so you can look at how much renovated units really are going for over classic.

Tip 2) IFs Are Your Friends

  • Now that you have all unit types, you can use the different “IF” functions to get max, min, average, count, etc. for each type. This is helpful to understand just how the current property is operating as a comparison to where you think the market is (or what the broker/owner is telling you about it)
  • Examples:
    • =AVERAGEIFS: Used to find average rents, fees, square footage and/or delinquencies for each unit type.
    • =COUNTIFS: Used to count number of units, number vacant, number on notice, number down or under eviction by unit type
    • =MAXIF & =MINIF: Used to find the max or min rent for each unit type

Tip 3) SUM it up

  • For quick calculations you can simplify your new table. Just don’t forget to do weighted averages since the number of units per type can skew your results!
  • Example:
    • =SUM & =SUMIFS: Used to double check total number of units, vacant units, run weighted average or determine how many are delinquent or under eviction

These handful of functions turn a rent roll into a more useful table like below. Since this is based on your own assumptions you can trust it for underwriting (though in due diligence you still need to verify if the underlying data is correct!)

To…
BONUS:

Sometimes Unit Type or BD/BTH or another column are combined in the same cell. This makes it tricky to analyze. But not with the following text manipulation functions:

  • =TEXTSPLIT() & =TEXTJOIN(): With these awesome functions you can split text apart using a space, -, or any other character on your keyboard. Then join the parts you want back together! Also very helpful for T-12s.
  • =RIGHT() & =LEFT(): These two give you back text from the beginning or ending part of a bit of text.
  • Example: Say you have “Pine 2BD1BTH Reno” as a type in Cell B2. You can use TEXTSPLIT(B2,”)  to break it apart by spaces  into cells B3, B4 & B5. Then for B4 (which now has “2BD1BTH” in it) use LEFT(B4,3) to get 2BD and RIGHT(B4,3) to get 1BTH. Now you can drag this down for all the units, letting you clearly see what each unit bed and bath is, as well as which are renovated or not!

With these quick formulas you can quickly analyze even large property rent rolls without hiring or expensive software (Google Sheets is free!). Then you have confidence in how in-demand the property is NOW so you can decide on a proper business plan and what it’s really worth to pay for.

Soon you’ll be creating charts and visuals of the data which is a powerful way to understand data trends and communicate with the team. Below you can see 32R vs 32 has a much bigger delta than 22R to 22. So I’d focus renovations on the 3BD/2BTH first in a business plan.

Add a Comment

Your email address will not be published. Required fields are marked *