Wednesday, January 6, 2016

Analyzing a Property's Cash Flow Statement & How to Create a Real Estate Pro Forma (part 2 of pro forma building series)

In the previous post we looked at the major categories needed to calculate down to net operating income. Those categories were "Income," "Income Adjustments," and "Operating Expenses." Now that we have the 1,000 foot view, we'll dive deeper into each of these categories, the line items in them, and look at actual calculations. Here's the link to the spreadsheet we're using to follow along: Example Pro Forma

Here's a list of everything we've covered so far:

Part 1 - Overview on how to calculate down to the NOI line item.
Part 2 - Intro to lease structures and calculating the gross potential revenue line item.
Part 3 - A look at reimbursement methods and how to calculate reimbursement income.
Part 4 - How to calculate the other income line item and an intro to income adjustments.
Part 5 - Rent abatements overview and calculation example.
Part 6 - Absorption and turnover vacancy explanation and intro to tenant improvements.
Part 7 - General vacancy allowance explanation and calculation example.
Part 8 - Operating expenses explanation.
Part 9 - Constructing a sources and uses table.
Part 10 - Building a debt schedule.
Part 11 - Calculating levered IRR.
Part 12 - DCF analysis.
Part 13 - Loan sizing.

Here's our simple example pro forma spreadsheet to follow along with as well.

Click this button to download the spreadsheet:


Commercial Real Estate Leases

Before diving into the individual line items, we need to briefly discuss a few things regarding commercial lease agreements:

  1. Lease Term - Similar to the lease an individual signs when leasing an apartment, a commercial lease must spell out when the lease begins and when it terminates.
  2. Rentable Square Footage - The lease must specify the square footage the tenant is effectively paying rent for.
  3. Base Rent - The lease must specify the amount the tenant pays per month in return for occupying their square footage in the building.
  4. Rent Increases - Landlords want to schedule rent increases throughout the lease term to combat inflation and keep up with the market. Any negotiated rent increases must be specified in the actual lease agreement.
  5. Expense Reimbursements - Commercial real estate tenants often reimburse the landlord for a portion of the operating expenses a landlord incurs on the property. Retail tenants typically have "Triple Net" (NNN) leases. NNN means the tenant reimburses the landlord their share of all operating expenses. Their share is calculated by their square footage divided by the total property's square footage, also know as the tenant's "Pro Rata Share." Notice in our example we have 2 tenants each occupying 25,000 square feet. Our total property square footage is 50,000 square feet. Each tenant's pro rata share is then 50%. Each tenant then pays 50% of all the operating expenses. The other most common reimbursement type is a "Gross," or "Full Service Gross" lease, often used for general office tenants. The definition and calculation methods behind these can get a little tricky. We'll leave it for now.

Gross Potential Revenue

Gross potential revenue is the base rent and scheduled rent increases the landlord would receive if all the suites were occupied and paying rent. In our spreadsheeet, on the "General Assumptions & Rent Roll" tab we specified in rows 19 & 20 the initial rent and rent growth for each tenant. Initial rent is the rent a tenant will pay according to their lease agreement, not factoring in any scheduled rent increases. The rent growth is the amount the base rent increases per year, according to the lease agreement. A typical increase structure is to increase the rent by 3% each year on the anniversary of when the tenant begins paying rent.

In our example we've assumed each tenant's initial rent is 25,000 per month. We've also assumed each tenant's rent increases the industry standard 3% per year. Year 1 gross potential revenue becomes quite easy to calculate. It's just each tenant's monthly rent amount multiplied by the number of months in a year, 12.

Calculating the gross potential revenue in future years is a bit more challenging, but still manageable. Tenant 1's rent is scheduled to increase by 3% in Year 2. To calculate Tenant 1's Year 2 rent, we must then take their Year 1 rent and multiply it by 1.03. Tenant 2's gross potential revenue is calculated exactly the same in this example. We then add up the two numbers and that's our Year 2 gross potential revenue.

Year's 3, 4, and 5 are calculated the same except to calculate Year 3, we need to multiply Year 2's gross potential revenue by 1.03. Year 4 we need to multiply Years 3's by 1.03, etc...

A common mistake is to think, for example, 2 years worth of 3% increases is equal to a 6% increase, and a quick shortcut to calculate Year 3 gross scheduled rent is just to multiply Year 1's gross scheduled rent by 1.06. Let's do some quick math to see why this is incorrect. 25,000 * 1.06 = 26,500. However, 25,000 * 1.03 = 25,750. 25,750 * 1.03 = 26,522.50. We can see that although the 1.06 method is close, it's not correct. The correct way to perform the shortcut is to multiply 25,000 by (1.03)^2, which you can see equals 26,522.50. If we wanted to know Year 5, we would use 4 in our exponent because the rent will have increased 4 times since Year 1. Note that the spreadsheet example uses this shortcut in a nifty formula to calculate the gross potential revenue.

Check back soon for the 3rd part of the series where we'll look at how to calculate the reimbursement income!

No comments:

Post a Comment