Chapter 6 | Financial Modeling

Overview

Financial modeling is the systematic analysis of expected outcomes for an investment and is used to assist in evaluating risks and opportunities.  Many get bogged down in the numbers and do not realize that their analysis is only as good as the quality of the information and judgment behind them.  Students should be skeptical of their financial model, especially when they are inexperienced in the business.

Summary

Students should avoid meaningless sensitivity analysis where they alter one variable without pausing to consider what the world needs to look like for such a change to happen.  If the strength of a local economy is expected to weaken, several variables will change.  A property may see lower rents, higher vacancy, lower ancillary income (fewer tenants, fewer dollars; existing tenants spend less), slightly lower variable operating expenses (from the vacancy), higher leasing commissions and higher tenant improvement dollars.  Additionally, in weaker times, owners will try to defer capital expenditures until property performance improves.  Deferring necessary capital expenditures may lead to large capital requirements in the future.

Sales assumptions are a critical part of a financial model, as a dominant share of a building’s value derives from disposition (as opposed to its operation).  The gross sales price is generally estimated by capping future stabilized NOI.  Analysts often mistakenly use the same cap rate at exit as for their purchase.  The problem with this approach is that the building is older and perhaps not as competitive at sale compared to when it was acquired.  It also can result in wishful and nonsensical pricing if the going-in cap rate was not based on stabilized NOI.  For a stabilized property, the exit cap rate is generally higher than your going-in rate, reflective of it being an older and less sought after building. But there are always exceptions, such as if the property was purchased when it was not stabilized and is sold after stabilization.

Net sales proceeds are what is left after selling costs, income taxes on accumulated depreciation and capital gains, and any mortgage amount to be repaid are deducted from the gross sales price.

Questions

These are the types of questions you’ll be able to answer after studying the full chapter.

1. Is the mathematical relationship between NOI cap rates and NOI sales multiples linear or exponential?

2. If a local economy is expected to strengthen, how would rent, vacancy, ancillary income, operating expenses, leasing commissions, tenant improvements, and capital expenditures be affected?

3. Why is it important not to blindly conduct sensitivity analysis?

4. Givner Apartments were acquired five years ago by an investor for $10MM. The investor decided not to lever the investment (use debt), and annual adjusted NOI is stable at $1MM.  If the investor sells the property at an 8% cap rate, what would be the investor’s net sales proceeds?  Assume the following:

  • accumulated depreciation on the property is $1MM
  • the Tenant Improvements and Capital Improvements over 5 years have been $300,000
  • 21% tax rate on accumulated depreciation
  • 15% tax rate on capital gains
  • 2% selling costs.

Audio Interviews

Why we strive for precision in making projections (7:16)

 

How to avoid big financial modeling mistakes (7:33)

Excel Figures

Download Excel

Key Terms

To view the definition, click or press on the term. Repeat to hide the definition.

The systematic projection and analysis of expected outcomes for an investment.

Transaction KPIs include net cash flow (net profit), internal rate of return (average compounded annual return), net present value (value created in discounted dollars), multiple on equity (a.k.a. equity multiple and multiple on invested capital; KPIs are viewed in concert with one another; each KPI tells you something different about the performance of your invested capital.

An equity KPI that tells you how many times you get your investment back. A multiple on equity of 1.0x means you simply broke even and did not make any profit. A multiple of 2.0x means you doubled your money.

Marketing techniques that property owners employ to get a tenant to sign a lease, such as free rent, lower security deposits, free toasters, etc.

Property gross revenues less vacancy.

Periodic (typically monthly) principal and interest payments made on a loan.

Annual NOI divided by annual Debt Service Expense.

Property operating expenses that change in proportion with the level of building occupancy. Examples include:

– electricity
– telephone service
– sewer rents and charges
– water and oil/gas
– repairs and maintenance
– janitorial and cleaning supplies and service
– elevator maintenance contracts
– shuttle bus service and maintenance
– fitness center equipment maintenance and replacement
– accounting fees for reimbursements
– common area snow removal (variable due to unpredictability)
– common area landscaping and plants.

Property operating expenses that do not change based on the level of building occupancy. Examples include:

– management fees
– management office rental value
– common area electricity
– supplies, uniforms, dry cleaning
– insurance premiums
– security
– concierge
– painting of common areas
– building employee wages and benefits
– payroll taxes
– legal fees
– window cleaning
– maintenance contracts for boilers, HVAC and other mechanical, plumbing and electrical equipment
– emergency generator service and maintenance
– licenses and permits
– trash removal/recycling
– pest control.

Funds set aside that provide for the periodic major repair or replacement of building components that wear out more rapidly than the building itself. Senior lenders require minimum capital reserves be set aside and maintained to ensure continued property competitiveness.

Before-Tax Levered Cash Flow less Income Tax Liability.

Estimated property sale value; theoretically captures the value of all future cash flows beyond the point of sale; to estimate the sale value you will generally apply a cap rate to a stabilized NOI.

The negotiated property sale price before any potential deductions.

The cap rate, expressed as a percentage, at which a property’s sale is valued, which is equal to the year 1 NOI yield on the Purchase Price the buyer will receive.

A property buyer’s year 1 yield of NOI on the Purchase Price.

Property sale proceeds remaining after Selling Costs, Income Tax Liability, and any outstanding mortgage balance have been deducted from the Gross Sales Price.

Primarily comprised of the sales Brokerage Commission, but also include jurisdictional property transfer and recordation taxes.

A property’s Gross Sales Price less Selling Costs.

The commission an investment sales broker receives for closing a property purchase and sale transaction.

A valuation method for a subject property in which one or more recently sold similar assets are used to help determine the value of the subject property.

Chapter Headings

  • What is Financial Modeling?
  • Things Change for a Reason
  • Leslie Court Apartments
  • Base Rental Revenue
  • Vacancy
  • Ancillary Income
  • Operating Expenses, Replacement Reserves and Cap Ex
  • TIs and Leasing Commissions
  • Sale Value
  • Gross Sale Price and Net Sales Proceeds

The Top Quarterly Publication for Commercial Real Estate Investors

 

 

Excel for Real Estate Certification

 

 

Bisnow / Linneman Education Videos

 

 

Investment Strategy Specialists

 

 

Looking for investment analysis software?

valuate app logo

 

 

Financial Modeling Self Study Courses

 

 

 

Quarterly Capital Markets Webinar Slides