Commercial real estate does not have a transparent market valuation like other publicly traded assets like stocks, bonds, and ETFs, so analysts must conduct extensive market research to develop assumptions and then construct models based on those assumptions to calculate the valuation and analyse if it’s worth investing.
In this case study, I’m going to show you how to build a model to value a commercial property step by step. In part I, I’m going to explain how to generate this office building’s financial statement to get the exit price and measure risk level. In part II, I will show you how to calculate this property’s IRR and conduct a sensitivity analysis, so we can decide if this is a good investment. I suggest you check the Excel financial model I’ve attached at the end while going through this article. Enjoy!
Case Scenario
Imagine You are an institutional investor contemplating the acquisition of an 85% leased, 251,743 rentable-square-foot office building in Irvine, California (“Culver Drive Tower”). Comparable properties have sold for between $300 and $340 per square foot (PSF), and you believe a fair price for this property is $320 PSF.
You believe the Irvine office market is set for a recovery over the next few years, and you believe that rents will rise at above-market rates while vacancy rates will fall. To take advantage of this trend, you plan to acquire the property, hold it for five years, and sell it.
Operating Assumptions:
Average Annual Rent per Square Foot: $38.40; 5% growth declining to 3% by Year 3.
Expense Reimbursements per Square Foot: $0.25; 3% annual growth.
Property Management Fees: 3.0% of Effective Gross Income.
Operating Expenses per Square Foot: $5.00; 3% annual growth.
Real Estate Taxes: 1.12% of property value initially; 2% annual growth.
Replacement Reserves per Square Foot: $0.40; 3% annual growth.
Capital Costs per Square Foot for New Tenants: $7.00; 3.0%.
Vacancy declines from 18% in the historical period to 15% by Year 5.
For the existing tenants, leases corresponding to 10% of the rentable square feet expire in Year 1, followed by 5% in Year 2, 10% in Year 3, 5% in Year 4, and so on.
It takes 6 months to find a new tenant following each expiration and each new tenant receives 3 free months of rent.
For simplicity, assume there are no renewals.
Now let’s type these assumptions into the Excel sheet and generate a financial statement for this property.
Building financial statement
To build a statement, we need to figure out the revenue, expenses, and net operating.
(1) Revenue
Let’s start with “base rental income”. This is the income we could get if we fully rented out this property at market rates and at 100% occupancy. Base rental income = rentable square feet * average annual rent per square foot. We assume the rent will increase by 5% (similar to the current US inflation rate) and then decrease to 3% by year 3.
Then, we need to do four adjustments before getting the effective gross income (EGI). Absorption & Turnover Vacancy is the expense to find new tenants after some leases expire and the old tenants choose not to renew the leases. In this case, in 2023, 10% of leases will expire and it will take half a year to get new tenants. So, Absorption & Turnover Vacancy = base rental income * expiring lease% * time required to find new tenants.
New tenants usually get a rent discount over the first few months. In this case, we won’t charge them the first-quarter rent. Therefore, we need to subtract “concession & free rent”.
Expense Reimbursements depend on the agreement between tenants and landlords. In this case, the landlord pays electricity, water, and Wi-Fi bills, and then receives reimbursement from the tenants. So, we need to add this part back when we calculate EGI.
Besides, we have an 18% vacancy rate in 2023 and then stabilize at 15%. So, we have to deduct the general vacancy from the base rental income.
(2) Expenses
Now we can move into “expenses”. Property management fees, operating expenses, and taxes are pretty straightforward. We also have reserves that are set aside for capital costs and are usually calculated by multiplying the rentable square feet of the property by replacement reserve psf. We calculate this way because usually as the property gets bigger, we will spend more and then need more reserves. Capital costs include capital expenditures, tenant improvements, and leasing commissions. Capital expenditures occur when we renovate the lobby area or do something else that affects the entire building. With tenant improvement, if a new tenant moves in and the tenant wants something custom, we often agree to pay for it to incentivize them to move in. Leasing commissions are what we pay to brokers who find new tenants.
These are all considered capital costs because they last for many years. These costs are not related to the day-to-day operation of the building but are used to attract new tenants to sign multi-year leases. Usually, we will set aside a reserve for these costs and then pay those costs out of this reserve because we don’t want to have a lumpy cash flow.
(3) Net Operating Income
Then, we subtract the total expense from EGI to get net operating income (NOI). The net operating income is important because it shows us the recurring cash flow from the core operations of this property. It’s like EBITDA for a normal company.
To get to the adjusted NOI, we need to think about the capital cost and how it works with the reserves. We assume the capital cost psf for new tenants is $7 and grow 3% each year. Then we calculate the annual capital costs based on this assumption. For the capital costs paid from reserves, we look at how much we actually have to pay first and then we’re going to compare that to the starting replacement reserve amount plus how much we put into the reserve.
Now let’s look at the acquisition and exit assumptions to calculate the exit price and our risk level.
Acquisition and Exit Assumptions
Assume an acquisition price of $320.00 per rentable square foot. Acquisition costs represent 1.0% of the gross price. A local bank has agreed to issue Senior Debt to support the deal, with the following terms:
Loan-to-Value (LTV) Ratio: 65%
Interest Rate: 3.50% (fixed)
Loan Amortization Period: 30 years
Loan Maturity: 5 years
Issuance Fee: 1.0%
Minimum Debt Service Coverage Ratio (DSCR): 1.9x; Minimum Debt Yield: 10%
Assume that you can sell the property in Year 5 for a 7.50% Cap Rate and that Selling Costs represent 2.0% of this exit price.
Cap Rate & Exit Price
First, we calculate the “implied going-in cap rate” by dividing the property’s NOI in 2023 ($5,077,491) by the acquisition price of $80,557,760. If the cap rate is lower, it means the property is more expensive. We divide stabilised NOI ($6,797,476) by the exit cap rate (7.5%) to get the exit price, which is the amount we can expect to receive from the sale of the property in five years.
In addition, we need to make a plan for the sources and uses of the money.
Generally, we need to pay for the acquisition and loan, which is $81,886,963 in total. Almost all real estate deals involve loans. For existing properties, the loan-to-value ratio (LTV) is 60% ~80%. In this case, our LTV is 65%. So, Senior debt accounts for 65% of funds, with equity investors providing the remaining 35%.
Debt Service & Risk Level
Then we check our debt service and assess our risk level.
We're using the IPMT and PPMT functions in Excel here to ensure that the debt service each year stays the same. They automatically split it up so that Interest + Principal Repayment = the Same number each year. After figuring out the interest expenses and principal payments, we can calculate cash flow to equity investors by subtracting these payments from adjusted NOI.
Now we can calculate some metrics based on NOI and Debt. Debt yield = NOI/initial debt amount of $52,362,544. Interest coverage ratio = NOI/interest expense. Debt service coverage ratio= NOI/ (interest expense + principal payment). These metrics give us a measure of how much risk is involved with a deal. For the debt service coverage ratio (DSCR), the higher number means there’s more NOI available to service the debt, so, the lenders take on less risk. We request a minimum debt yield of 10% and a minimum DSCR of 1.9x. The risk level for the first year is not ideal because of the 1.78x first-year DSCR and 9.7% debt yield. I'll use sensitivity analysis in part II to further evaluate the risk level.
What's next ...
Now we finished the first part. In part II, I’m going to show you how to calculate this deal’s IRR and further analyse this deal’s risk.
Comments