By: Roger Staiger
This was a 550 page textbook over the methods of building and refining real estate financial modeling. It was dense with complex formulas within Microsoft Excel to build models for single family real estate, multifamily, retail, hotel, office, development, and so on.
We used this to refine our modeling and use simpler and more elegant formulas to make our models easier to view and make decisions.
I am going to save you from reading excel formulas. The following are the takeaways aside from formulas. They were predominantly simple definitions of terms within real estate modeling.
What is real estate? Real estate is real property. What is “property”? For the purpose of this book, real estate is a defined investment(s) and/or ownership in physical buildings and land combined or separate, or financial products backed by physical buildings and land, e.g. mortgage-backed securities (MBS).
This text focuses on the financial spreadsheet modeling, i.e. the quantification of value of physical real estate assets rather than financial products such as mortgage-backed securities and/or collateralized mortgage obligations.
Residential real estate is a leading indicator of commercial real estate.
Residential real estate, for a homebuilder, is a For Sale product and commercial is income producing, i.e. perpetual.
Sales Price = Net Operating Income / Capitalization Rate
Net Present Value (NPV) is the sum of present value (PV) for all future cash flows (CF), discounted at the appropriate market rate or the rate of alternative equivalent risk investments, less the initial cash outlay. The Net Present Value rule, according to Business Schools, states an investment is worth considering/investing when the NPV is positive.
The definition of risk is simple: the deviation or variation from an expected outcome. Basically risk is the range of outcomes from the expected value.
IRR(bestcase) – IRR(worstcase) = Range
The fundamental goals of investing:
- Stay out of jail
- Return OF Capital
- Return ON Capital
To answer both the second and third goal for investing in real estate, a project’s probability characteristics must summarize both return and risk.
The goal is to maximize the efficiency of the project.
What is efficiency? Loosely defined, efficiency is the point of maximum return for any given level of risk, i.e. the ratio of risk and return.
How is efficiency quantified? Using one of these 3 methods.
- Coefficient of variation (CV)
- Sharpe ratio (industry standard for portfolio management)
- Treynor ratio (mostly commonly found in academic journals).
A basic proforma consists of three sheets: Input/Summary, Debt Amortization, and Proforma.
Short summary of the steps to build a model:
- Construct the Inputs of the model (Sources/Uses, Operating Characteristics, Financial Assumptions, Exit Considerations, and Valuation parameters)
- Develop the Amortization Table
- Develop the Proforma page, capital inflows and outflows, Basic income statement to Net Operating Income
- Link to Debt output
- Complete Net Income calculation (NOI less Debt payment)
- Complete sales summary (Exit strategy), Quantify new sales proceeds (Gross sale less sale expense and principal repayment)
- Complete net free cash flow (Net Income plus net sales proceeds)
- Summarize proforma and net cash flow on summary page. Quantify return and risk calculation to determine project characteristics
Retail real estate is generally considered sites of commerce, i.e. where items are purchased and sold.
Two types of tenants:
- Anchor tenants: “Big Box Stores”. Leases exceeding 25 years. Critical to the center’s economics and drive traffic to the site.
- Inline Stores: Shorter leases typically around 5 years. Small clothiers, coffee shops, sandwich shops, etc.
Office is considered space for white and blue collar, administrative uses. Every company and/or corporation uses office space to house workers and, ultimately, produce products or services. Typically for intellectual purposes and back office support.
Multifamily is for rental properties. Rent rollover risk is omnipresent for this asset class.
Hotels are the most correlated to economic events and the most responsive to changes in pricing, attitudes, and preferences.
The first main global lending rate is Prime. The US Federal Reserve, the central bank of the US, has two goals: (1) target and maintain inflation at or near 2.00% and (2) promote growth.
The Prime Rate is the basic interest rate charged by lending institutions to retail customers. It is typically set as 300 basis points over the Federal Funds Rate.
The second main global lending rate is LIBOR (London Interbank Offering Rate). It is not a rate based on actual transactions but rather a survey of 16 large global banks.
Sources and Uses
Sources can be fairly static when modeled as a percentage of Uses, e.g. Debt is 80% of direct costs and equity fills the gap. However, Uses are far from straightforward. The uses depend upon development interest, which may be floating, soft costs which vary significantly, and development costs which, are in reality, an estimate at the beginning of a project.
Revenue and Expense.
Finance, Valuation, and Sale
These relate to interest rate of debt, which can be fixed or a floating rate, capitalization rate utilized in final gross sale price, and month of sale date which is far from certain.
I recommend this book if you are building your own financial real estate model.