I thought I would share my proforma spreadsheet for analyzing commercial properties. This is a basic tool, but it’s one I use frequently. You can get the spreadsheet by subscribing to my newsletter.
There are some incredibly in-depth tools out there for analyzing commercial properties. The spreadsheet that’s given as part of the CCIM 101 course is incredible. It’s able to forecast 10-years down the road, but it is so detail heavy that it can make your eyes cross, your hands sweat, and truly wonder if commercial real estate is anything you want to get involved in. Please understand that I’m not making light of a tool of that magnitude. In fact, I will use it when analyzing million-dollar shopping centers.
However, there are times when I just want to quickly analyze a property to understand if it is worth digging into deeper. That’s where this little proforma comes in. It isn’t mean to be an end-all/be-all. It’s just a starting point.
The spreadsheet was given to me by my investing partner who said I was free to tweak it and share it.
Without further ado, let’s take a quick walk-through.
The first thing to notice is the tabs. There are three: welcome, proforma, and return.
The “welcome” tab is just a quick hello from me.
The “proforma” and “return” tabs are where the magic happens.
Let’s start with the proforma tab first.
After entering the basic information about the property, it’s time to show the tenant mix. Feel free to add or delete rows as necessary.
It’s important to get as true an understanding of a tenant’s rent as possible and that is more than just the monthly amount paid. If you only know the tenant is paying a certain dollar amount ($5,000/month) but don’t know the square footage of their space, you could greatly misjudge where the tenant sits within the market.
For example, a tenant paying $5,000/month in a 10,000 SF space pays $6.00/SF annually.
($60,000/year ÷ 10,000 SF)
while a tenant paying the same $5,000/month in 7,000 SF space is paying $8.57/SF annually
($60,000/year ÷7,000 SF)
Perhaps $6/SF is too low or $8.57 is too high. Seeing this immediately will help you know where these tenants are next to similar tenants in like-sized spaces.
The “lease expiration” column should be explanatory. The longer a lease term, the less risk for a landlord. The shorter the lease term, the more risk a landlord is assuming. The tenant may move which results in lost rent (vacancy), commission expense, and turnover costs.
Or perhaps that’s what you, as a purchaser, wants. Maybe you want the current tenant to leave since you have a better tenant (national credit, more rent, etc.) that is willing to come in afterwards. Quickly appraising the length of the lease term(s) is valuable.
The type of lease is important if you want to ensure your property is either all “gross” (landlord covers all common area costs), “modified gross” (landlord covers most of the common area costs), or “Triple-Net” (tenant is responsible for the common area costs).
There is a line item in the spreadsheet for “recoverable expenses.” This has to do with the term “Triple-Net” or “NNN”. Triple-Net leases are those wherein a landlord recovers the costs associated with common area maintenance.
A thing to note is the vacancy factor. Regardless if the property is full, any investor should apply a vacancy factor to their calculations. Depending on the history and type of the property, same may apply a smaller vacancy factor (3%-5%) while others may apply a larger factor (10% or greater).
This number is yours to change as you look ahead. There is no crystal ball so be realistic in your assumptions.
The categories should be self-explanatory. Feel free to tweak, add, delete as necessary. The thing to note here is that you only need to enter the annual amount for all categories (except property management which is a based upon a percentage).
The spreadsheet will calculate immediate how much, per square foot, the property is spending per category. You’ll also see what the property is totaling for expenses. This is valuable information as you can quickly tell if a property is under or over market averages on expenses.
As an example, in my market (Spokane County/Eastern Washington), $/SF on expenses for NNN properties is currently falling somewhere between $4/SF-$6/SF.
A property that is “under market” with their expenses may show that it is not being taken care of correctly, the property taxes have not been reassessed/reset for some time, or all the expenses have not been reported by the seller.
A property showing “over market” expenses per square foot may be bleeding cash due to mismanagement, undiscovered problems in utilities, etc.
You really want to dial these numbers in as tightly as possible.
NET OPERATING INCOME
Total income – Expenses = Net Operating Income (NOI)
This is the number that we’ll use to determine the value of the property by applying a cap rate to it.
As a reminder, the cap(italization) rate is the return and investor is expecting on the project. At the bottom of the spreadsheet is a series of numbers. The lower the number, the less risky the project is assumed to be.
For example, a 6% cap rate (return) is considered a very safe investment.
Conversely, a 10% cap rate would be thought to be incredibly risky.
You will have to determine these numbers or speak to a broker in your market to help dial in what an expected return would be surrounding a property you are evaluating.
The spreadsheet will calculate the numbers automatically for you and give you the expected value of the property at the sought after returns.
As a reminder,
NOI ÷ cap rate = a property’s value.
Want more discussions of cap rates?
THE RETURN OF THE RETURN
There is a tab for another worksheet which allows you to enter your financing information. If you haven’t got this dialed in with the bank, you can at least guesstimate what you’re working with.
Most of it should be explanatory and only a few items should be changed on this worksheet:
1. Enter the Loan to Value percentage (LTV). This is how much the bank is willing to lend on the project – 75/25, 60/40, etc.;
2. Enter the interest rate;
3. Enter the term in months (10 years is 120 months, 20 years is 240 months and 25 years is 300 months, and 30 years is 360 months).
Everything else is calculated and gives you a different way to look at your project.
One thing to note is “Cash on Cash.” This is the return on the money invested into the project (as opposed to Cap Rate which is a return on Net Operating Income).
Its formula is:
Cash Flow = NOI – Debt Service
Cash on Cash Return = Cash Flow ÷ Initial Cash Invested
There you go. A quick and easy proforma for you to value your properties.
I’d love to hear your thoughts.