Foundations of Real Estate Financial Modelling PDF Free Download
1 / 57
/57
100%
i
Foundations of Real Estate Financial Modelling
Now in its third edition, Foundations of Real Estate Financial Modelling equips a new generation of
students and professionals with a resource MillionAcres guarantees they’ll “use throughout [their] com-
mercial investing career[s] .” Designed to provide increased scalable basis of pro forma modelling for real
estate projects, this complete update and revision of the classic text offers a step- by- step introduction to
building and understanding the models underlying investments in properties from single- family rentals to
large- scale developments. Case studies drawn from the author’s storied investment career put models into
real- world context while problem sets at the end of each chapter provide hands- on practice for learners at
any stage of their real estate careers. This edition employs the innovative nancial metric P(Gain) to quan-
tify the probability of a Return of Capital, ensuring readers’ ability to answer the most fundamental question
of investing— What is the probability I’ll get my money back?
The fully revised and enhanced third edition is organized in three functional units: (1) Real Estate
Valuation Basics, Theory, and Skills, (2) Real Estate Pro Forma Modelling, and (3) Real Estate Pro Forma
(Enhancements). Chapters cover:
• Interest Rates (Prime, LIBOR, SOFR)
• Amortization (Cash- Out Renance modelling)
• ADC (Acquisition, Development, Construction) Module
• Rent Roll Module (including seasonality)
• Waterfall
• Hotel Consolidation
• Stochastic Modelling and Optimization
Additional chapters are dedicated to risk quantication and include scenario, stochastic, and Monte Carlo
simulations, equity waterfalls, and integration of US GAAP nancial statements. A companion website
provides the real estate pro forma models to readers as a reference for their own constructed models, www.
pgain llc.com.
An ideal companion in the classroom and the boardroom, this new edition of Foundations of Real
Estate Financial Modelling will make even novices the “experts in the room on [their] chosen asset class”
(MillionAcres).
NAMED ONE OF THE BEST COMMERCIAL REAL ESTATE BOOKS BY THE MOTLEY FOOL
“Staiger gives us the technical tools needed to build robust pro forma modeling around our real estate
assets.”— MillionAcres
Roger Staiger is an investor, author, and philanthropist. He is the owner of P(Gain), LLC, an inter-
national real estate investment and advisory rm that develops, owns, and manages real estate and oper-
ating businesses in the Washington DC metropolitan area and the Caribbean. His previous roles include
Managing Director for a Fortune 500 Energy Company’s commodity division, CFO for America’s
Best Mid- Sized Builder 2006, and a Senior Portfolio Manager for a large, commingled pension fund in
New York. Mr. Staiger currently holds faculty positions in the real estate departments at John Hopkins
University, University of Notre Dame, and Auburn University. He is also a faculty member in the MS
Finance Program at The George Washington University. In addition, he provides expert witness testimony
on issues related to Real Estate in US legal cases.
iii
Foundations of Real Estate
Financial Modelling
Third Edition
Roger Staiger
iv
Designed cover image: © Shutterstock
First published 2024
by Routledge
605 Third Avenue, New York, NY 10158
and by Routledge
4 Park Square, Milton Park, Abingdon, Oxon, OX14 4RN
Routledge is an imprint of the Taylor & Francis Group, an informa business
© 2024 Roger Staiger
All rights reserved. No part of this book may be reprinted or reproduced or utilised
in any form or by any electronic, mechanical, or other means, now known or
hereafter invented, including photocopying and recording, or in any information
storage or retrieval system, without permission in writing from the publishers.
Trademark notice: Product or corporate names may be trademarks or registered trademarks,
and are used only for identication and explanation without intent to infringe.
First edition published 2015 by Routledge
Second edition published 2018 by Routledge
Library of Congress Cataloging- in- Publication Data
Names: Staiger, Roger P., III., author.
Title: Foundations of real estate nancial modelling / Roger Staiger.
Description: Third Edition. | New York, NY : Routledge, 2023. |
Revised edition of the author’s Foundations of real estate nancial modelling, 2018. |
Includes bibliographical references and index. |
Identiers: LCCN 2022051458 (print) | LCCN 2022051459 (ebook) |
ISBN 9781032454597 (paperback) | ISBN 9781032458106 (hardback) |
ISBN 9781003378808 (ebook) | ISBN 9781032459486 (ebook other)
Subjects: LCSH: Real estate investment–Mathematical models. |
Real estate investment–Finance–Mathematical models. |
Real estate development–Finance–Mathematical models. |
Real property–Finance–Mathematical models.
Classication: LCC HD1382.5 .S72 2023 (print) |
LCC HD1382.5 (ebook) | DDC 332.63/24015118–dc23/eng/20221024
LC record available at https://lccn.loc.gov/2022051458
LC ebook record available at https://lccn.loc.gov/2022051459
ISBN: 9781032458106 (hbk)
ISBN: 9781032454597 (pbk)
ISBN: 9781003378808 (ebk)
ISBN: 9781032459486 (eBook+ )
DOI: 10.1201/ 9781003378808
Typeset in Times New Roman
by Newgen Publishing UK
Access the companion website: www.pgain llc.com
v
Contents
Preface ix
Acknowledgments xi
PART I
Real Estate Valuation Basics, Theory, and Skills 1
1 Real Estate Basics 3
What Is Real Estate? 3
Calculating Terminal Gross Sale Price at Period “n” 7
Basic Methods of Valuation 8
Modied Internal Rate of Return (MIRR) 17
Risk 21
Scenario Risk Quantication 23
Distribution Shape(s) 25
Probability of Gain (P(Gain)) 26
Asset Class Consideration(s) 33
MS Excel Formulae Utilized 42
2 Asset Valuation Dened, Characterized, and Visualized 43
Physical Repositioning 44
Physical Methods to Reposition, i.e. adjust the structure of cash ows 45
Cash Flow Magnitude 54
Cash Flow Timing 57
Riskiness 61
Finance Method to Reposition 62
Capital Structure 63
Interest Rate(s) Adjustment(s), e.g. Fixed vs Variable 66
Derivatives (Futures/ Forwards/ Options) 68
Waterfall/ Securitization (Bifurcating Cash Flows) 72
Securitization 74
Real Estate Characteristics Dened 77
Single- Family Residential (For Sale Property) 85
Retail 86
Ofce 88
Multifamily/ Apartment 88
Hotel 89
vi Contents
vi
Addendum—(XIRR vs IRR) 91
Addendum—(XNPV vs NPV) 92
MS Excel Formulae Utilized 94
3 Interest Rates 95
Simple Interest 96
Compound Interest 97
Future Value/ Present Value 98
Periodic Rates 99
Variable Rates of Interest (Prime vs LIBOR vs SOFR) 101
Prime Rate 102
LIBOR (at the time of publication, LIBOR is being retired due to the LIBOR scandal) 103
Secured Overnight Financing Rate (SOFR) 104
Risk- Free Rate(s) 104
Yield Curve Theory 104
4 Amortization 112
Constant Payment Mortgage (CPM) 113
Interest Only Mortgage (IO) 117
Custom Amortization 118
Amortization Schedules (The Combination) 120
Amortization Table Construction: Scalable, Modular, Portable 125
Addendum—HP- 12c versus Fundamental Amortization Table 136
MS Excel Formulae Utilized 165
PART II
Real Estate Pro Forma Modelling 171
5 Single- Family Rental (Single Sheet) 173
Pro Forma Construction (Basics) 173
Lending Metrics/ Valuation (Important) 186
Problem Sets (By Hand) 190
MS Excel Formulae Utilized 197
Case Study—Chester 197
Case Study—Chestnut 202
Case Study—Wimbledon Place (Author: Alic Kelso) 203
V. Uptown’s Appeal 210
6 Five- Unit Multifamily 231
Multifamily Construction 231
MS Excel Formulae Utilized 259
Appendix 259
Recapitalization 259
Case Study—Thames 268
Case Study—Stephen Street (Author: Alic Kelso) 269
Case Study—Bridgehampton (Author: Paige Winebrenner) 278
Contents vii
vii
7 “N”- Unit Rent Roll 292
Rent Roll Page Construction 292
“N”- Unit Rent Roll Scalability 292
Add Rent_ Roll Page 303
Bonus Section—Seasonality 310
Common Area Maintenance (CAM)— General Discussion for Retail and Ofce
Projects 313
MS Excel Formulae Utilized 313
Case Study—The Redwood (Author: Bob Rajewski) 314
8 ADC (Acquisition, Development, Construction) Page 317
ADC Page Construction 317
ADC Add- on to Existing Models 332
MS Excel Formulae Utilized 340
Case Study—The Overlook 341
9 Waterfall Structure 346
Waterfall Theory 346
Waterfall Construction 349
Waterfall Incorporation 363
MS Excel Formulae Utilized 372
Case Study—Hidden Lakes 379
10 N- Unit For Sale 388
N- Unit For Sale Construction 388
For Sale Page 389
MS Excel Formulae 399
Case Study—The Woodlands 400
Addendum—Pricing House Deposits (2007 Perspective) 403
11 Hotel Asset 409
Hotel Construction 409
MS Excel Formulae Utilized 422
Case Study—The Powell 422
PART III
Real Estate Pro Forma (Enhancements) 427
12 Accounting Statement(s) 429
Financial Statement Overview 429
Balance Sheet 430
Income Statement 433
Statement of Cash Flow 435
Model Structure and Accounting Stand- Alone File 436
Adding the Accounting Statements to Existing Models 437
Process Steps 438
MS Excel Formulae Utilized 460
viii Contents
viii
13 Stochastic Modelling (Crystal Ball) 461
Stochastic Modelling Overview 461
What Is Crystal Ball? 465
Crystal Ball Mechanics 467
Running the Real Estate Pro Forma Model with Crystal Ball 475
Incorporating Crystal Ball into an Existing Real Estate Pro Forma Model 477
14 Optimization (OptQuest) 489
Optimization Overview 489
Efciency Ratios 489
What Is Solver? 495
What Is OptQuest? 499
Using OptQuest for Real Estate Optimization 509
Adding OptQuest to an Existing Spreadsheet Model 515
15 Closed Architecture Modelling + P(Gain) 523
“Risk” Relative to Commercial Real Estate (CRE) Investing 524
Introduction 524
Environmental Risk 525
Political/ Legislative Risk 525
Market Risk (Vacancy) 525
Tenant/ Resident Risk 525
Physical/ Appeal Risk 525
Entry Price Risk 526
Exit Price Risk 526
Holding Period Risk 526
Forecasting (Benets/ Faults) 526
VAL Software (Closed Architecture Modelling) 527
Index 547
ix
Preface
Foundations of Real Estate Financial Modelling provides professionals the tools and skills for
constructing real estate pro forma models. This text starts with a fresh perspective on Real Estate
Finance and Valuation basics. Then, after the reader has a solid theoretical base, the text begins
modelling with a blank spreadsheet and provides the step- by- step foundation to construct any
real estate nancial model for any asset class. Finally, the text nishes with stochastic modelling
and optimization providing the professional true and accurate risk quantication and optimiza-
tion. The text is a practical and hands- on application of real estate nance, complete with case
study examples and problem sets, for professionals to challenge themselves.
This third edition is intended to be read linearly, i.e. starting at Chapter 1 and nishing with
the nal chapter. It was designed in this manner for best use as the standard text for real estate
nance and modelling for the real estate professional.
While this text does provide the structure for all real estate nancial models, customization for
individual projects will be required. It is intended that this text provide the required and neces-
sary skills for professionals to modify the real estate proforma models to particular uses and
projects. This is why scalability, modularity, and portability have been structured and designed
for all real estate nancial models.
The completed models from this text are located on the author’s website, www.pgain llc.com.
The models are provided as a nal check for the professional but are not recommended as the
starting point for projects. It is the professional’s own model that they construct themselves,
whose base forms are veried against the text base models, which must be utilized for all projects.
It is this model that is truly “owned” by the professional. The completed models built for this text
are provided on the website as a nal check and verication to the professional.
It is never recommended by this author that models be purchased from third- party sources.
The best models are those which have been constructed by the individual professionals for their
own corporate or personal use. That is why this text provides detailed steps for real estate pro
forma model construction.
For larger projects it may be prudent to hire a real estate professional if custom construction
of the real estate pro forma model or ongoing analysis is required and/ or desired. This author
recommends that only professionals possessing academic degrees in nance, real estate, or other
quantitative disciplines be considered. Further, it is best to hire individuals that have direct own-
ership and personal development experience both domestically and internationally in physical
and nancial real estate projects. Be wary of hiring individuals without these credentials and
hands- on proven experience. Nothing provides better perspective for an individual than investing
one’s own capital. If one truly understands real estate nancial modelling, one will own real
estate assets!
The real estate nancial models utilized in this text, as stated, are all located on the author’s
companion website, www.pgain llc.com. These models have been stress- tested and all have been
utilized in real deals. When customizing the models for particular projects, ensure that, prior to
x Preface
x
making nancial decisions involving real capital, the model(s) are stress- tested completely for
any customization. Do the results make sense? Are the results sane? What does your “gut” say?
Prior to investing in a real estate project or developing a new asset, the author goes through each
model, spot- checking calculations and relationships within the models with his hand held HP-
12C calculator. When real money is being invested, it is absolutely worth the effort and time to
check the calculations by hand. As such, as a bonus, this text provides the basics of how to use the
HP- 12C calculator for general use and specically for mortgage calculations. By understanding
both MS Excel nancial modelling and the HP- 12C calculator, professionals have an ability to
independently verify results prior to capital deployment. “Measure twice, cut once!”
As the text concludes, Oracle’s Crystal Ball has been incorporated within existing and new
real estate models. Stochastic modelling and optimization is incorporated within new models
vastly increasing the analysis capability of existing real estate spreadsheet models. This provides
the professional the ability to add “condence” to current analyses, e.g. “With what percent
condence will a hurdle rate be met?” Also, the professional can optimize assets held or units
constructed within single projects or portfolios. Optimal results are provided to the professional,
e.g. “What is the optimal unit mix for the project to maximize yield (or minimize risk, or some
combination)?”
xi
Acknowledgments
I am proud to have my name on the cover of this text representing the excellent consolidation of
many great contributions. If the cover included every contributor there would be an unending list
of names. I am grateful to each contributor.
This is the third edition and I am greatly encouraged by the global attention the rst two
editions have brought to Real Estate Financial Modelling and pleased to receive this opportunity
to author yet another edition. Having this book accepted as part of the curriculum at University
of Notre Dame, Johns Hopkins University, and Auburn University, to name just a few, is quite an
honor and recognition.
Within this third edition there has been consolidation and signicant improvement from the
second edition. The models are signicantly more scalable and have increased functionality,
e.g. cash- out renance, improved waterfall, and additional material regarding optimization. The
models covered in this edition are scalable for mixed- use and can accommodate institutional
funding and complex partnership structures.
I am grateful to everyone for the assistance, small and large, that they have contributed to
this text. Even the students that begin their questions, “This is a stupid question….”. What those
students do not realize is that an epiphany can result from a unique question that improves the
models for everyone. In truth, that is how the nancial metric P(Gain) was conceived, i.e. a lunch
with a student that was asking about real estate valuation.
It is an honor to publish this text and I hope everyone is aware that the seless hours contributed
directly and indirectly are appreciated and valued. While my name is on the title (and I am
honored) this text is truly a collaboration of all the students, peers, and clients over the years.
Thank you.
newgenprepdf
3
DOI: 10.1201/9781003378808-2
1 Real Estate Basics
What Is Real Estate? 3
Calculating Terminal Gross Sale Price at Period “n” 7
Basic Methods of Valuation 8
Modied Internal Rate of Return (MIRR) 17
Risk 21
Scenario Risk Quantication 23
Distribution Shape(s) 25
Probability of Gain (P(Gain)) 26
Asset Class Consideration(s) 33
MS Excel Formulae Utilized 42
What Is Real Estate?
“What is real estate?” While the question seems simple, the answer is complex. Real estate
is real property. This transforms the question to what is “property?” For the purposes of
this book, real estate is a dened investment(s) and/ or ownership in physical buildings and
land, combined or separate, or nancial products backed by physical buildings and land, e.g.
mortgage- backed securities (MBS). This text focuses on nancial spreadsheet modelling,
i.e., the quantication of value and risk of physical real estate assets rather than nancial
products such as mortgage- backed securities and/ or collateralized mortgage obligations. The
common visualization of real estate is a commercial building (ofce, retail, warehouse, condo-
minium, multifamily), residential home, or farm (raw land), i.e. a physical structure residing
on land or the land itself. However, when considering the nancial aspects, the “picture” of
real estate is quite different. Real estate must be considered like other assets (equity and debt
instruments), i.e. pictured as a probability distribution which clearly denes its risk and return
characteristics. Yes, the distribution shape is different from traditional investment vehicles but,
nancially, real estate, like other asset vehicles, must be quantied and visually transformed
into a probability distribution.
This transformation is a two- step process. First, the physical asset is modeled within a spread-
sheet to produce a cash ow diagram, i.e. real estate nancial pro forma. Second, the real estate
nancial pro forma is transformed into a probability distribution about the expected return of the
asset quantifying and visually demonstrating the expected return, risk, and P(Gain), i.e. the prob-
ability of returning the invested capital. This allows the investors to understand Return OF and
Return ON Capital (Figure 1.1).
Real estate is tangible: it can be seen, touched, felt, and at times of distress, kicked. Real
estate is also intangible as it is a nancial instrument which can be transformed physically and
4 Real Estate Valuation Basics, Theory, and Skills
4
nancially. Physical transformation through repositioning, e.g. ofce to multifamily. Financial
transformation is accomplished by taking the project return and bifurcating risk tranches
through a nancial method called a waterfall (waterfalls are explained and modeled in a later
chapter), i.e. nancial engineering. A simple qualitative example of a waterfall distribution is,
assuming two investors A & B, to provide investors A & B proportionately different returns at
different risk levels. Regardless, the value of real estate is the cash ow produced which is a
direct result of the tenants and leases, i.e. value derived from paper rather than physical struc-
ture. Conceptually, consider bifurcating the physical real estate from the tenant leases. The
physical real estate produces expenses while the tenant’s lease produces revenue (of course,
there are expenses associated with leases). The characteristics of these cash ows depend on
the type of real estate, i.e. asset class, the current economic environment, and, of course, the
location.
To consider real estate in a broader context, it is an asset class attracting capital investment. In
fact, real estate is the second largest asset class in the U.S. and was actually the largest asset class
in 2006, prior to the bubble bursting. To provide some context, in 2010 U.S. GDP was approxi-
mately $14.5tn with total U.S. asset values (aggregation of xed income, equity, and real estate)
of approximately $69.0tn. In 2010, the asset split was xed income ($32.5tn), equity ($18.0tn),
and real estate ($18.5tn). Therefore, real estate, in 2010, was approximately 27% greater than
U.S. GDP and the same percentage of U.S. asset value.
Exercise:
1. What are the relative sizes of the major asset classes in the U.S. and your respective
country currently? How have they changed since 2010, i.e. a low point of the Financial
Crisis?
Real estate in the context as an asset class is often separated into two classes: residential and
commercial. In the United States, Residential is approximately four times (4×) the size of the
commercial asset class and has been a leading indicator by 16 months (based on historic ana-
lysis of the Case- Shiller index versus RCA’s Commercial index as of 2007). Note: Residential,
as a leading indicator of commercial, reduced to 9 months after the nancial crisis. Residential
real estate is therefore approximately $14.8tn while commercial real estate is $3.7tn as of 2010.
(Note: Commercial real estate in 2007 was $6.0tn.)
Exercise:
2. What is the approximate size of commercial and residential real estate in the U.S. and your
respective country? How has it changed since the Financial Crisis? Using current indices,
what is the current lead/ lag between price changes when comparing commercial and resi-
dential real estate? Complete a graphic demonstrating the change and describe what this
means for the broader economy.
Figure 1.1 Physical to nancial
Real Estate Basics 5
5
While the differences between residential and commercial may be “obvious” it is important to
reclassify each to best describe their nancial characteristics, i.e. residential, for a homebuilder,
is a for sale product and commercial is income producing, i.e. perpetual. Of course there is cross-
over, e.g. for sale condominiums for commercial and single- unit rental residential. The lines are
movable and possibly blurred and certainly are not absolute. Commercial real estate is typically
purchased for ownership and future income purposes. Residential real estate is constructed and
sold by the builder or developer (note a developer sells nished lots to a builder and a builder
constructs homes for sale to the end buyer). Provided, in the case of residential, construction is
ongoing, the “sales” can be considered perpetual income when the builder continues to build and
the developer continues to develop. Note: At the time of publishing, for rent residential commu-
nities are increasingly growing in popularity and as an asset class.
The perspective of the tenant and/ or home purchaser is similar. Traditionally, personal, resi-
dential real estate, purchased as a primary home, is personal consumption, i.e. residential home.
To further extrapolate, personal residential properties are also quasi- income producing with
a consideration of rental expense that would be incurred if individuals were not in their own
homes, i.e. paying rent to an owner/ landlord. The purchase of residential real estate allows the
owner to participate in market improvements, i.e. increased equity, but also harms the owner in
times of distress, i.e. reduced equity. Business school students and graduates reference this diffe-
rence of rental expense “opportunity cost” and model as if it were an expense to determine price.
At the core, all real estate has the same cash ow diagram (Figure 1.2):
Figure 1.2 Untenanted real estate
Specically, real estate is unique as an asset class as, unlike equity and xed income secur-
ities, the purchase (denoted by the longer rst arrow on the left) is “rewarded,” not with income,
e.g. dividends (equity) and/ or coupon payments (xed income), but rather with invoices. These
invoices are maintenance, in the case of a physical structure, insurance, and once a year property
taxes, etc. Basically the “reward” for owning real estate, in its purest form, is expenses which are
both timely and unpredictable, e.g. roof collapse that requires immediate restoration. Of course,
the owner also has use of the property and owner’s rights to the property but it is the tenant leases,
nancial paper, that provide the income for the asset. The nancial expenses attributable to owner-
ship, i.e. not unexpected capital expenses (HVAC replacement), are known and can be accurately
forecast given historic maintenance records and tax forecasts. To some extent, even the unexpected
can be models, e.g. replacement reserves, as, at the portfolio level, even these can be predicted
with some accuracy. However, force majeure events, e.g. tornado damage, remain unpredictable.
Therefore, the nancial value of real estate, in purity, i.e. without tenant leases, is negative. It is
only through active management, i.e. leasing efforts, that real estate returns are positive.
Question: Why does real estate trade with positive pricing? Answer: Due to real estate’s
ability to attract cash ows, realized or not. Also, in many cases, the intangible, e.g. beautiful
scenic viewpoint which is emotionally valued. Of course there are other reasons as well, e.g.
ability to absorb large amounts of capital in singular transactions, community investment and
presence, hubris, etc., but in summary, it is the ability to actively manage the asset to produce
positive cash ows, i.e. revenue exceeding maintenance, insurance, taxes, and debt service.
Therefore, the value of real estate is created not only from the physical structure itself but also
the net rents, i.e. leases less expenses, the asset attracts or has the ability to attract.
6 Real Estate Valuation Basics, Theory, and Skills
6
There are a myriad of methods to value real estate. A real estate asset can be valued as a
single income source or as a plethora of sources, e.g. tenant leases. In the case of an ofce pro-
ject where multiple tenants occupy the asset, the value can be determined as the portfolio of
leases rather than as a single project, i.e. ofce asset. This addresses risk quantication at the
single- and multiple- asset perspective. A single- tenanted building, i.e. a master leased facility,
provides no diversication and exposes the owner to the same risk as the tenant. However, a
multi- tenanted building provides diversication, provided the tenant’s industries are lowly or
negatively correlated. Extrapolating further, a multi- tenanted real estate asset can be modeled
and considered a portfolio of Forward Rate Agreements (FRAs) rather than a single asset. This
is largely left untouched in this text and further understanding of FRAs is left to texts focusing
on derivative products. However, it is mentioned to provide the reader a broad overview and
perspective.
From a risk perspective, real estate assets can be viewed three ways:
(1) Single Asset, e.g. Multifamily, Ofce, Retail, Hotel
a. Single Asset with single cash ow
b. Single Asset with multiple cash ows from tenants
(2) Multi- Asset Contribution, e.g. marginal contribution to a larger portfolio
(3) Derivative product of Forward Rate Agreement represented by tenant leases
No single method is correct though some are more appropriate than others depending
upon the analysis and intended use of the asset. However, each must be comprehended and
understood.
An actively managed real estate investment, fully or partially leased, will have the more trad-
itional cash ow diagram associated with equity and xed income securities (Figure 1.3).
Figure 1.3 Tenanted real estate
The above assumes a terminal value at the end of the period, i.e. net sales proceeds. There are
two traditional methods of valuing a real estate asset: (1) assume cash ows continue into per-
petuity and (2) assume cash ows end upon sale, i.e. terminal value. For the purposes of this text,
a terminal value will be assumed in calculating yield, as the diagram demonstrates. Generally
this text will consider real estate assets held for periods of 10 years but later chapters will dem-
onstrate how to model a dynamic sale date, i.e. a sale date that varies with time. It should be
understood that the terminal value can be removed if the property is considered held, i.e. valued,
into perpetuity. In fact, Western nance typically values based on a nal sales period, while
Eastern nance, i.e. Asia, values based on perpetual holdings, i.e. multigenerational. There are
two methods for determining terminal gross sales price, the nal cash inow, i.e. the large arrow
at the right of Figure 1.3: (1) alternative and (2) traditional. (Note: Terminal gross sale price in
this text will be quantied using the alternative gross sale price methodology unless otherwise
noted.)
Real Estate Basics 7
7
Calculating Terminal Gross Sale Price at Period “n”
(1) Alternative Gross Sale Price Methodology (recommended and used within this text)
Sales PriceNOIr
CapitalizationRate
where
NOI
n
n
n
Final
=+
()
1
:
:YYear of Cash Flow ending at sale date
Escalation r:rrate for for next yearNOIn
Note: In the methodology stated above, it is recommended that if the sale is occurring in
period 120, i.e. the end of the 10th year, periods 109– 120 be added and then escalated one year.
However, an alternative is to take period 120, i.e. one month, multiply by 12 to annualize,
and then escalate one year. It is recommended that the rst method, i.e. summation of periods
109– 120, be utilized as it reduces the inuence of a single period. Note: When utilizing this
methodology of gross sale price, the model will not work if the sale period is less than one
year, i.e. immediate sale. Of course, adjustments to the model can be made to accommodate a
sale in the rst year but the analyst must understand the model and requirements.
Note: The Alternative Gross Sale Price Methodology reduces the complexity of larger models
and will be demonstrated in a later chapter. It is strongly recommended that it be considered
on larger, more complex, real estate nancial models to reduce complexity and sheet count.
(2) Traditional Gross Sale Price Methodology (not recommended)
Sales Price
NOI
CapitalizationRate
n
n
=+1
CRITICAL: The sales price in period “n” is quantied as the net operating income (NOI) at
period “n+ 1”, i.e. revenue less expense, rather than net income. Net income is then dened as NOI
less debt service, i.e. cost of nancing. So the standard structure is Revenue – Expense = NOI.
Then, NOI – Debt Service (Principal and Interest) = Net Income. Note that this is all pre- tax. This
is consistent with Modigliani and Miller’s propositions from corporate nance which state the
value of an entity is independent of nancing concerns.
An example for the alternative gross sale price methodology (RECOMMENDED) in year
10, i.e. future year 10, for real estate. A real estate pro forma is constructed and the revenue for
year 10 is $950,000. The same forecast for year 10 expenses is $712,500. Also, the blended escal-
ation rate for revenues and expenses is expected to be 5.00% in year 10. So, year 10 NOI, revenue
less expense is $237,500 ($950,000 – $712,500). The capitalization rate is 10%. The alternative
gross sale price is therefore $237,500*1.05/ 0.1 or $2,493,750, i.e. (Revenue – Expense)*(1+
RateEscalation)/ (Capitalization Rate).
An example for the traditional gross sale price methodology (NOT RECOMMENDED) in
year 10, i.e. future year 10, for a real estate asset. A real estate pro forma is constructed (many to be
constructed later in this text) and the revenue for year 11, i.e. 10 + 1, is determined to be $1,000,000.
The same forecast has expenses for year 11 as $750,000. So, year 11 NOI, revenue less expense, is
$250,000 ($1,000,000 – $750,000). The capitalization rate is 10%. Note, this is a reversionary cap-
italization rate, i.e. forward forecast. Capitalization rates represent the multiplier investors expected
to pay for net operating income. In business school parlance, it is an EBITDA multiplier. Of course,
it is not a multiplier but a divider, but the concepts are similar. The gross sales price in year 10 is
8 Real Estate Valuation Basics, Theory, and Skills
8
therefore $250,000/ 0.10 or $2,500,000. Typically the Gross Sales Price (GSP) is then reduced by
sales expenses, e.g. brokerage fees, and principal repayment. Therefore the nal sales price, CFn, is
net sales proceeds, i.e. gross sales price less expenses and principal repayment.
Basic Methods of Valuation
Western nance values/ analyzes projects utilizing two primary methods: (1) net present value
(NPV) and internal rate of return (IRR). Both are mathematically related via a single formula.
The strengths and weakness of these methods are discussed throughout the text. Net Present
Value is utilized in Western business schools, while Western professionals typically will use
the internal rate of return to value a real estate project. Both have merits and both have signi-
cant weaknesses. Neither should be exclusively and solely utilized to make a determination but
rather in conjunction with other metrics and basics for a project, e.g. cash- on- cash, debt service
coverage ratio (DSCR), equity multiple, etc.
Valuation methodologies, as introduced here or in any text, are guides to analysis and under-
writing. They are no absolutes. The true determinant to the valuation method is the capital provider.
“He who has the gold makes the rules.” This is absolutely correct in real estate analysis and valu-
ation and, in reality, for any analyses or valuations. While institutional funds investing in real estate
will largely evaluate using academic fundamentals, individuals and private investment funds are
free to evaluate using proprietary methodologies. These proprietary metrics may include duration
analysis, modied internal rate of return, return on cost, and, of course, P(Gain), etc.
Net present value (NPV) is the sum of present value (PV) for all future cash ows (CFts),
discounted at the appropriate market rate or the rate of alternative equivalent- risk investments,
less the initial cash outlay (CF0). The net present value rule, according to business schools, states
an investment is worth considering/ investing when the NPV is a positive, i.e. the investor is
compensated for the risk taken and earns a positive return above the anticipated risk prole. The
anticipated risk prole is referenced as the discount rate in the NPV formula. The net present
value formula is as follows (Figure 1.4):
Figure 1.4 Discounted cash ow
Analyst note: CF0 is shown in the equation as positive but is dened as negative, i.e. cash out-
ow. CF0 is the initial cash outow.
NPV
NPV
tt
=− ++
=− ++
=
∑
15 000 12 000
1005
15 000 12 000
1005
1
3
,,
(.)
,,
(.)1123
12 000
1005
12 000
1005
15 000 12 000
105
1
++++
=− ++
,
(.)
,
(.)
,,
.
NPV22 000
1 1025
12 000
1 1576
15 000 11 428 57 10 10 884 3
,
.
,
.
,,.,,.
+
=− ++NPV5510 366 05
17 679
+
≈
,.
$,Npv
Real Estate Basics 9
9
With substantial concern, NPV is utilized to determine the value of projects to the equity
holders, i.e. investors. Accepting projects with a positive NPV appears to benet the equity
holders, assuming an appropriate discount rate has been utilized. This is a very basic method-
ology of valuation utilized in Western business schools. The major strength is the simplicity to
calculate. The major weakness is the constant application of risk to each period, i.e. assuming
risk is constant throughout the time period for the project.
As an example, consider a three- period cash ow with initial outlay and a discount rate of
5.00%. CF0 is 15,000 and the three equal inows of capital, i.e. periods 1, 2, and 3, are 12,000.
Net present value is calculated and demonstrated in the example.
Analyst bonus: The two methods to quantify the net present value utilizing an HP- 12c nancial
calculator follow:
HP- 12c Method 1 HP- 12c Method 2
F Clx F Clx
12,000 pmt 15,000 chs g cf0
3 n 12,000 g cfj
5 I 3 g nj
0 FV 5 i
PV…– 32,678.98 F npv…17,678.98
15,000 +
…– 17,678.98
An additional net present value example compares two projects with different cash ow
streams (Figure 1.5).
Figure 1.5 Project A&B cash ow steps
The above discussed how to quantify the net present value by hand and utilizing the HP- 12c
calculator. Here we demonstrate how to calculate using MS Excel. CRITICAL: The “= NPV()”
formula in MS Excel is incorrect and must be adjusted! The “= NPV()” formula in MS Excel is,
in actuality, a present value (PV) calculation. Therefore, the future cash ows are present values
and the net cash outow, i.e. CF0, is addressed outside of the formula. The method to quantify in
MS Excel is demonstrated in Figure 1.6.
10 Real Estate Valuation Basics, Theory, and Skills
10
Figure 1.6 Project A&B MS Excel
Analyst bonus: The method to quantify the net present value utilizing an HP- 12c calculator for
Project A and B is as follows:
Project A Project B
F Clx F Clx
50,000 chs g CF060,000 chs g CF0
18,000 g CFj10,000 g CFj
6 g Nj23,000 g CFj
12 I 2 g Nj
F npv…14,885.97 25,000 g CFj
21,000 g CFj
12 I
F npv…11,438.89
When considering both personal investment and capital budgeting decisions for an organization,
a project with a larger net present value is considered to be of greater utility as it is expected to
provide a greater return in real dollars. For instance, considering an initial investment of $100, a
9% discount rate, and a 3- year investment horizon, the timing of such cash ows is highly rele-
vant to the present value of the investment and as a result, to the NPV.
NPV
t
=− ++
()
++
()
++
()
=− +
=
∑
100 80
1800
20
1800
20
1800 100
1
1
3
23
.% .% .% 1105 67
100 20
1800
20
1800
80
1800
1
1
3
2
.
.% .% .%
NPV
t
=− ++
()
++
()
++
()
=
∑33 100 96 96=− +.
As can be noted within the above example, two investments with equal undiscounted dollars (i.e. –
$100 + $120 = $20) and almost identical factors provided signicantly different net present values,
solely as a result of the timing of their cash ows. The rst example provided a positive NPV of 5.67.
The second example, whose largest cash ow return was at the end rather than the beginning, failed
to provide a positive NPV at all. Despite the nominal sum of all cash ows being equal to one another
at face value, their differences when discounted to present value are signicant, i.e. timing matters!
Real Estate Basics 11
11
Internal Rate of Return (IRR) is the discount rate calculated by setting the NPV equation equal
to zero for a series of cash ows. It is the premier Western method for evaluating real estate projects.
It provides an intrinsic value of a project, expressing the realized rate of return for future cash ows.
NPVCFCF
r
o
t
t
t
n
== ++
()
=
∑
01
1
where:
CFo = – initial cash outow
t = period or timing of cash ow
n = total periods of analysis
r = IRR (solved rate)
While rules of thumb can be dangerous, the typical rule of thumb for evaluating a real estate
project, i.e. investment project as dened by negative cash ow at the beginning and then posi-
tive cash ows at the end, is that if IRR exceeds the discount rate the project adds value. Note
that IRR does assume that the reinvestment rate, i.e. the rate funds reinvested once earned, is the
same as the quantied discount rate (IRR). (Future note: Modied internal rate of return [MIRR]
bifurcates the reinvestment rate and discount rate. MIRR is discussed later in this text.)
Similar to NPV, IRR has pitfalls and does not always determine which projects offer positive
value to investors unless completely understood and utilized in conjunction with other metrics
of valuation. IRR does NOT quantify risk, risk is a separate consideration and calculation. IRR
is a metric utilized to determine the central location of a project’s return. It is one of two parts
required to dene the probability distribution for a project, assuming the probability distribution
is modeled as a normal or lognormal distribution.
The following is a list of some, but not all, of the pitfalls for using IRR for real estate project
evaluation.
(1) Multiple IRRs
(2) Reinvestment rate = Discount rate (IRR)
(3) Investment vs nancing projects
(4) Scaling (mutually exclusive projects)
(5) Distribution
(1) Multiple IRRs
Multiple IRRs exist as the NPV formula is functionally a polynomial when set to zero and solving
for the discount rate, i.e. IRR. Therefore, for specic real estate cash ows, those changing from
negative to positive or positive to negative more than once, multiple IRRs, may mathematically
exist. The below example illustrates a series of cash ows, with six “ips” in sign, in which two
IRRs exist. Neither is correct and neither is incorrect. It is impossible to evaluate these cash ows
using the IRR method (see Figure 1.7).
Figure 1.7 Project (uneven) cash ows
12 Real Estate Valuation Basics, Theory, and Skills
12
When graphing the NPV vs discount rate, two IRRs are located at each location of an X- intercept
(approximately 3% and 58%). Both are correct and neither offers a true representation of the
value of the cash ows to equity holders (Figure 1.8).
Figure 1.8 Multiple IRR
The net present value, discounted at 10%, yields a positive value of $1,408. Since the NPV is
positive, the cash ows yield value to the equity holders. This would not be the conclusion if the
IRR approach were utilized since it could yield a value of 3%, which is below the discount rate of
10%. Therefore, an NPV analysis, in this case, is superior. This is, as discussed, a case where mul-
tiple methodologies are required to determine project viability. Also, note that this project does have
negative cash ows and therefore the individual cash ows must be considered as a capital call,
i.e. the injection of capital from an investor, will be required at points during the project’s lifespan.
(2) Reinvestment = Discount Rate (IRR)
A nuance of IRR is that it assumes that the reinvestment rate equals the discount rate. That
is, as cash ows are received, they are assumed to be reinvested at the quantied discount
rate. This is problematic over long time periods for projects where there may not exist the
opportunity to reinvestment the cash ows as the quantied IRR due to structural shifts in
interest rates in the global economy. The two rates are bifurcated using an abridged/ modied
version of IRR, i.e. Modied Internal Rate of Return (MIRR). (Note: MIRR is discussed later
in this text)
(3) Investing vs Financing Projects
The third issue with IRR analysis is the altering valuation criteria for investment vs nancing
projects. Consider the below two projects, A and B, and the respective cash ows:
Project A B
Initial cash – $100 $100
Cash @ T = 1 $130 – $130
Real Estate Basics 13
13
Project A is the typical real estate project, i.e. money is invested (cash outow) for a future
benet (cash inow). Project B is a nancing project similar to how universities, when using
part- time professors, receive their cash ows. The students all pay tuition prior to the semester
start and the university begins paying its part- time instructor part way through the semester, i.e.
after receiving cash ow from the students. (Note: The part- time professor provides a free loan
to the university.)
The Internal Rate of Return for each project is 30%; however, the NPV for project A is a
positive $18.2 and project B is a negative $18.2, respectively, at a 10% discount rate. The IRR
analysis would seem to state that both projects have equivalent yield but the NPV analysis clearly
demonstrates that only project A adds value to investors.
The difference is that project A (Figure 1.9) is an investment project while project B
(Figure 1.10) is a nancing project. The rule for acceptance under the IRR criteria is to accept
if the IRR is greater than the discount rate for investment type projects and accept if LESS
THAN the discount rate for a nance type project. See graphs below. This rule change for
project type is confusing, if misunderstood, for IRR assessment but nonexistent in the NPV
analysis.
Figure 1.9 Investment project IRR
Figure 1.10 Financing project IRR
14 Real Estate Valuation Basics, Theory, and Skills
14
(4) Scaling (Mutually Exclusive Projects)
A fourth pitfall for IRR analysis concerns mutually exclusive projects. Scaling is a critical issue
when two opportunities are available but only one can be executed due to limited capital avail-
ability or other strategic reasoning, i.e. the two projects are mutually exclusive. An example is
the ability to earn $50 on one investment and $100 on another. Consider the below two projects,
A and B, and their respective cash ows:
Project A B
Initial cash – $50 – $1,000
Cash @ T = 1 $100 $1,100
For the above example, assume the period for the investment to be short, virtually instantaneous,
i.e. timing effects can be ignored. The NPV for project A is $50 and project B is $100. The IRR
for project A is 100% and project B is 10%. Using IRR analysis project A should be pursued since
it yields the highest return to equity holders, however, NPV analysis yields project B since it
yields the highest value to equity holders. The IRR analysis is awed since it neglects to account
for the scaling issue that $100 is worth more to the equity holders than $50. Also, project B
absorbs more capital, i.e. puts more capital to use, than project A. There can be signicant port-
folio advantages to deploying greater (and less) capital.
(5) Distribution
Internal Rate of Return (IRR), like all metrics, follows a distribution shape. While the actual dis-
tribution shape will vary depending upon project risk/ return characteristics, the type of distribu-
tion does not, i.e. IRR is a continuous distribution. In this text, the normal distribution is utilized
to approximate valuation metric behavior and to calculate the nancial metric P(Gain). P(Gain)
quanties the probability of returning invested capital and answers the question, “What is the
probability of receiving the initial capital back?”
What is most important, and mathematically fundamental, about a continuous distribution
is that there is zero area underneath a single point. Translation: There is zero percent (0.0%)
probability any single value/ number will occur. Common parlance: There is a 100.0% prob-
ability that the Internal Rate of Return is WRONG, i.e. has 0.0% probability of occurrence.
Therefore, while IRR is widely utilized as an approximation of central location, a decision
based entirely and exclusively on IRR is a decision based upon a number that is mathematic-
ally WRONG!
The ve pitfalls above demonstrate the limitations and concerns of singularly using the IRR
analysis to evaluate cash ows to capital providers/ owners. IRR is benecial in stating the
intrinsic value of future cash ows. NPV analysis always yields singular conclusions but does
not state the intrinsic value. Both methods must be utilized together, and in conjunction with
other valuation methods, to ensure accurate valuation of cash ows.
An example of calculating IRR in MS Excel is shown. Consider the following cash ow
stream from the earlier example, i.e. project B, with an initial cash outlay of 60,000 and the
following inow for periods 1– 5: 10,000; 23,000; 23,000; 25,000; 21,000; respectively. In MS
Excel the IRR is calculated as follows (Figure 1.11):
Real Estate Basics 15
15
Figure 1.11 Project B MS Excel
Analyst bonus: The HP- 12c solution follows:
F Clx
60000 chs g CF0
10000 g CFj
23000 g CFj
2 g Nj
25000 g CFj
21000 g CFj
F IRR…0.187
The graphical depiction of the IRR calculation provides a visualization of the metric
(Figure 1.12).
Figure 1.12 IRR analysis
16 Real Estate Valuation Basics, Theory, and Skills
16
As a nal introduction to Internal Rate of Return it is recommended that the analyst under-
stand how to quantify the IRR metric by hand. (Note to real estate nance students: This is
always a question on ALL examinations.) As a demonstration, a two- period cash ow is provided
(Figure 1.13).
Figure 1.13 Two- period project cash ow
The calculation by hand is demonstrated:
Note that the equation is a quadratic. The quadratic has two possible solutions as it describes
a parabolic arc. The solution chosen is the positive resultant as there are greater nominal cash
inows than outows, i.e. the solution to the IRR cannot be determined without understanding
and visualizing the cash ows.
The MS Excel and HP- 12c solutions also follow (Figure 1.14):
Figure 1.14 Two period project cash ow (MS Excel/ HP- 12C)
Real Estate Basics 17
17
NPV Practical Application
1. Calculate the Net Present Value (NPV) for the following project. An outow of $14,000 in
year 0 followed by an inow of $6,000, $7,000 and $7,000 in one- year increments with a
discount rate of 8%.
a) $4,371.21
b) $3,712.21
c) $3,426.83
d) $3,113.75
2. Calculate the NPV for the following project. A outow of $10,000 dollars followed by an
inow for three years of $2,500 and a single inow in the nal year of $6,000 with a cost of
capital of 9%. (Hint: this is an annuity, nal year annuity payment is grouped in with nal
cash ow.)
a) $961.34
b) $972.34
c) $1,082.52
d) $1,124.35
3. What change to NPV occurs when the discount rate for both problems is reduced by half?
a) $4,526.10/ $2,123.21
b) $4,464.10/ $2,130.19
c) $4,464.12/ $2,130.19
d) $4,526.12/ $2,123.21
IRR Practical Application
1. Calculate the IRR for the following project. An outow of $14,000 in year 0 followed by an
inow of $6,000, $7,000, and $7,000 in one- year increments.
a) 12.5%
b) 18.5%
c) 20.0%
d) 19.6%
2. Calculate the IRR for a project with $10,000 dollars of initial cash outow followed by three
years of $2,500 cash ows and a single lump sum inow in the nal year of $6,000. (Hint:
this is an annuity, nal year annuity payment is grouped in with nal cash ow.)
a) 13.3%
b) 12.3%
c) 14.7%
d) 11.2%
Modied Internal Rate of Return (MIRR)
Modied Internal Rate of Return (MIRR) adjusts for some of the pitfalls of traditional IRR ana-
lysis which assumes all cash ows are reinvested at the calculated IRR rate, i.e. MIRR bifurcates
the reinvestment rate and the discount rate. MIRR addresses the second issue of IRR. Therefore,
traditional IRR analysis may misstate the implicit return for a project by failing to quantify the
18 Real Estate Valuation Basics, Theory, and Skills
18
effect that earned cash ows during the project are not reinvested at a project’s IRR but rather at
a corporate reinvestment rate or other market rate.
MIRR corrects this misstatement by converting a project’s cash ows to a zero coupon bond/
security. The project’s future cash ows are compounded to the nal period at the reinvestment
rate. Using the initial cash outow, the yield on the zero coupon security is then calculated
(MIRR). The equation for MIRR is as follows:
MIRR
CF r
CF
where
CF
s
ns
s
nn
s
=
()
+
()
−
=
−
=
∑1
1
1
0
1
:
Cash Fllows in period t
CF Initial Cash Flow (Cost)
nNumber of p
0=
=eeriods
rReinvestment rate
sCurrent period
=
=
An example of the Modied Internal Rate of Return for a series of cash ows follows (Figure 1.15):
Figure 1.15 MIRR future value
The compounding of the cash ows creates a zero coupon security maturing at time period n (Figure 1.16).
Real Estate Basics 19
19
Figure 1.16 MIRR zero- coupon bond
Note the price (CFO) for the zero coupon security is as follows:
P
M
yield
where
MC
Fr
yield MIRR
nPeriods
n
S
S
n
nS
=+
()
=
()
+
()
=
=
=
−
∑
1
1
1
:
Solving for MIRR yields:
MIRRM
P
n
=
−
1
1
An example of MIRR is demonstrated with the cash ow stream having a 12 initial outow
and the following annual periodic inows: 4, 7, 5, 6, and 15. The reinvestment rate is 4.00%
(Figures 1.17 and 1.18).
20 Real Estate Valuation Basics, Theory, and Skills
20
Figure 1.17 FV mechanics
Figure 1.18 MIRR discount
MS Excel Example/ Solution:
Real Estate Basics 21
21
MIRR Practical Application
1. Assuming 100,000 dollars are invested today, for the next three years 12,000 dollars are
returned annually and in the fourth year a lump sum of 80,000 dollars is provided. What is
the IRR of this series of cash ows? Assuming a reinvestment rate of 15%, what is the MIRR
of this cash ow?
a) IRR: 4.52%/ MIRR: 6.35%
b) IRR: 4.52%/ MIRR: 7.23%
c) IRR: 5.71%/ MIRR: 7.23%
d) IRR: 5.71%/ MIRR: 6.36%
2. Assuming 25,000 dollars are invested today, next year 2,500 dollars are returned and in the
second year the full 25,000 returned. What is the IRR of this series of cash ows? Assuming
that 2,500 is invested in a government T- bill with a one- year maturity and a rate of 0.77%,
what is the MIRR of this cash ow?
a) IRR: 4.23%/ MIRR: 5.63%
b) IRR: 4.92%/ MIRR: 5.12%
c) IRR: 5.63%/ MIRR: 4.23%
d) IRR: 5.12%/ MIRR: 4.92%
Risk
Denition: Deviation from an expected outcome.
Risk is “understood” largely by everyone but the true denition seems to be allusive. Before
a discussion of risk commences, risk must be bifurcated into project, i.e. single entity, and port-
folio, i.e. multiple assets held together. Single entity risk, which will be discussed here, quanties
deviation of an expected return for a single project, e.g. project A has an expected return of 18%
with risk, as measured by a standard deviation of 5%. Single entity risk assumes a project is held
in a vacuum and does not consider additional assets held together, i.e. diversication benets.
Project risk assumes assets are held together but considered as singular assets for risk quanti-
cation. Portfolio risk therefore not only considers individual project return and risk but also
the correlation, i.e. linear association of projects, as well as the respective weights of each asset
held in the portfolio. For discussion purposes, project risk will be discussed early in this book,
whereas portfolio risk will be discussed in later chapters, specically with regard to optimization.
Therefore, the discussion which follows is for single assets only.
The actual denition of risk is simple: the deviation or variation from an expected outcome.
Basically, risk is the range of outcomes from the expected value. If one draws “risk,” assuming the
distribution type is normal, risk, in its most basic form, is the deviation from the expected return on a
normal curve, i.e. see the red bar with arrows in Figure 1.19. Risk is generally quantied as a standard
deviation. The main reason for standard deviation being the measure of risk is that it is quantied as
base units and is the second of two parameters dening a normal distribution (Figure 1.19).
Figure 1.19 Probability distribution
22 Real Estate Valuation Basics, Theory, and Skills
22
It is essential to understand that while the above drawing of risk is a representation, it does
NOT represent ALL probability distributions, i.e. a normal distribution is one type of distribu-
tion categorized under one of the two types of distributions. The two types of distributions are
continuous, normal falls under continuous, and discrete. Fundamentally, risk is simply devi-
ation from an expected return/ outcome. This deviation is often represented by a normal dis-
tribution as humanistic and nature data are best represented as normal distributions. However,
risk can be modeled using a myriad of distribution types/ shapes including but not limited
to: hypergeometric, uniform, triangular, Poisson, etc. As stated previously, the family of
distributions includes both continuous and discrete. The actual characteristics of each must
be understood. For the purpose of this text, whose focus is real estate nancial modelling, the
normal distribution, which is a continuous distribution, will be utilized most. Note: There are
two general types of distributions, i.e. continuous and discrete. Continuous distributions have
zero probability associated with a singular point but only a probability within a range of points,
i.e. between two points. Discrete distributions, e.g. Poisson, have probabilities associated with
single points of occurrence.
There are three main methods to quantify a project’s risk: (1) variance, (2) standard devi-
ation, and (3) range. Understanding that the three are all related is essential. Further, each can be
utilized as an approximation for another quantitative measure through the use and understanding
of the below relationship:
σσ
=≈
2
6
Range
The approximation assumes the distribution is best described by a normal distribution and
assumes no outliers in the distribution, i.e. the empirical rule states + / – 3σ from a central location
represents approximately 99.7% of the data. Outliers are dened as data values falling outside
this range (Figure 1.20).
Figure 1.20 Project distribution P(Gain)
Understanding the approximation is critical for a simple calculation of risk for projects. A more
detailed quantication for risk must use a stochastic approach, i.e. Monte Carlo or Latin
Hypercube. These stochastic approaches are discussed in much greater detail toward the end of
Real Estate Basics 23
23
the text in the chapters covering Crystal Ball. The simple calculation for risk of a project uses the
best case IRR (yield) and the worst case IRR. The range is calculated by taking the delta:
IRRIRR Range
Best CaseWorst Case
−=
The “Best Case” will be the highest IRR possible for the project with all “bull” projections, i.e.
highest rents, lowest vacancies, etc. The “Worst Case” is the polar opposite for the project with
all “bear” projections, i.e. lowest rents, highest vacancies, etc. For example, if the best and worst
case IRR were 50% and – 10%, respectively, then the range is their respective delta. For example,
50% – (10%) = 60% (note: parentheses indicate negative value). Dividing the range by six yields
a result of 10%. Therefore the standard deviation, i.e. risk, of this project, assuming distribution
is normal, is therefore approximately 10%, i.e. σ≈10%.
Range
Risk
6
≈
()
σ
An example of which would be the identication of a best, most likely, and worst case scenario
with differing probabilities associated with each possible outcome. In this case, under the best
case scenario, the project yields a 12% IRR, under most likely 8% and under the worst case a
– 3% IRR. Under these conditions, the project yields a range of 15%, i.e. 12% – (3%) = 15%, and
a standard deviation
σ
()
of 2.5%.
Range ≈− ≈12 315%(%) %
Risk
Range
σ
()
≈≈≈
6
15
6
25
%.%
Scenario Risk Quantication
While a range divided by 6 is a simple calculation, it is also pedestrian and can be misleading
as it assumes the underlying distribution is normal. For real estate, this is often not the case.
While still a relatively simplistic risk quantication, scenario analysis is superior as it allows for
a nonsymmetrical distribution to be approximated. It also provides for the events, “i,” to have
different probabilities of occurrence. The basic equation for scenario risk (variance) quantica-
tion is as follows:
σ
22
1
=
()
−
()
=
∑Er Er p
ii
i
n
where:
E(Ri): return for event “i”
E(r): central location of the distribution (estimated by IRR)
Pi: probability of event “i”
While this equation may appear new to many, it is actually the broader denition of variance than
was provided in lower- level statistics education. The two equations most often demonstrated are
24 Real Estate Valuation Basics, Theory, and Skills
24
sample and population variance. The difference being “Pi” which allows for non- equal probabil-
ities for each event, “Ri.”
For example, the two equations for sample and population follow:
σµσ
Population
i
i
n
Sample
i
i
n
R
N
Rx
n
2
2
1
2
2
11
=−
()
=−
()
−
==
∑∑
and
wheere:
P and
iPopulation==
−
11
1N
P
n
iSample
In both cases, population and sample, the probability of each event Ri is equally probabilistic.
The reason for the difference between population and sample, i.e. the minus one (1), is that
sample uses
x
to estimate the population parameter µ. This approximation requires a degree of
freedom loss adjustment in the denominator, i.e. n – 1.
The scenario risk (variance) equation is therefore most applicable to generic cases where
the probability of each event is not equal, i.e. real life/ real estate. As a demonstration, assume
a real estate project has only three distinct returns, i.e. best, most likely, and worst, with the
corresponding probabilities of these events. Note that event probabilities are difcult to quantify
and often are utilized from past experiences on similar projects or projections based on known
events going forward. The return calculations for the real estate project were estimated using
IRR, however, the return could have been calculated utilizing numerous methods, e.g. MIRR,
average Cash- on- Cash (not yet discussed), etc.
The example below demonstrates a project with three states of nature only: (1) Best Case,
(2) Most Likely Case, and (3) Worst Case. The respective probabilities of each case are stated
in the column to the right. The expected return, i.e. central location, and standard deviation, i.e.
risk, are quantied below
E(Ri) P(i)
1) 40.0% 10%
2) 10.0% 60%
3) – 20.0% 30%
ER RP
ER
ii
i
()
=
()
=×
()
+×
()
+− ×
()
=+
=
∑
1
3
04 01 01 06 02 03 00400.. .. .. ..66006 004
04 0040101
22
1
3
22
−=
=
()
−
()
()
=−
()()
+−
=
∑
..
.. ..
σ
σ
ER RP
ii
i
00040602004 03 0 013 0 0022 0 0173
001
22
2
.. .. ..
..
.
()()
+− −
()()
=+ +
=
σ
773 0 0173 0 1315∴= =
σ
..
The project parameters are therefore:
r,
.%,.%
σ
()
()
4001315
Real Estate Basics 25
25
Note: Had each event been assumed equally probabilistic, the expected return, E(r), i.e. central
location point, would have been 10% and the risk 10%. This would result in signicant over-
estimation of expected return and under- estimation of risk.
Note: If the assumption remains that the distribution is normal, the empirical rule is used to deter-
mine the distribution’s characteristics. If a normal distribution is not appropriate, Chebyshev’s
formula must be employed (see basic statistics text for explanation of Chebyshev’s formula and
its use in non- normal distribution risk quantication).
The important point here is to recognize projects have both an expected return, i.e. mean,
and risk, i.e. standard deviation. Without the understanding of both for a project the most basic
question of investing cannot be addressed: What is the probability the initial capital will be
returned, i.e. P(Gain)?
Distribution Shape(s)
Returning to the denition of real estate with an understanding of return and risk, a visual best
describes the asset class. When considering real estate in the context of both equities and xed
income securities, the unique characteristics are exemplied. For instance, a direct investment
in physical real estate usually involves leverage, i.e. borrowed capital. A typical million dollar
purchase will have the traditional 80% debt and 20% equity capital stack. Note: 80% while
achievable in residential jumbo loans is high leverage for a commercial product for the debt
component.
The 20% equity is the “down payment” and represents capital injected/ provided by the owner,
i.e. CF0. The 80% is borrowed funds from an external source, generally a lender. Further, the 80%
debt may require an external guarantee from the owner, i.e. recourse. Finally, the ownership of
the physical real estate also exposes an owner to the liability(ies) associated with the real estate,
e.g. cleaning toxic environmentally unfriendly soils. Therefore, a physical real estate purchase
may expose the owner to considerably more nancial risk than the initial equity commitment or
even the entire capital stack. The total loss in a physical real estate investment may be unlimited.
For example, a $10.0m property which is leveraged 80%. If the property just disappears (not
realistic but this is theoretical), the $8.0m debt is still owed. Now, if the property has disappeared
AND the soils are toxic and must be remediated, the loss can be greater than the original purchase
price. Noting that soils are the responsibility of the owner and therefore can pose risks greater
than the asset’s original price.
Note: While not the topic of this text, the additional risk of real estate can be mitigated
through ownership structures, e.g. LLC ownership of the asset rather than personal. If personal
guarantees of the debt are required, these guarantees should be capped to a maximum amount,
if possible. At the extreme, corporate and/ or personal bankruptcy. Again, these are not the sub-
ject of this text but it is possible to legally reduce risk through different ownership structures.
Not only is it legally possible to reduce risk, it is prudent to understand, quantify, and miti-
gate risk.
For the moment, as a point of contrast, the return distribution for equity and xed income
securities limits the loss of investment to – 100.0% (Figure 1.21). In statistics parlance, this is
similar to a lognormal distribution where the loss is anchored at 100.0% and the gain trails to the
right, i.e. positive innity. With the rare exception of purchasing an equity below par value, the
maximum loss for an equity purchase is the total invested capital. As with equity, the same is true
for a xed income security. The maximum loss for xed income purchase is the total invested
capital as well.
26 Real Estate Valuation Basics, Theory, and Skills
26
Figure 1.21 Equity/ xed income probability distribution
Figure 1.22a Real estate probability distribution
Figure 1.22b P(Gain)
Returning to real estate, the maximum loss, in theory, is negative innity (Figure 1.22a). This
is largely due to the unlimited liability associated with the purchase of an asset, e.g. toxic soils.
While this can be mitigated through legal protections as noted earlier, i.e. purchasing the asset
in an LLC, bankruptcy, etc., the risk of an enormous loss remains real. The risk of a huge loss
is “real” in real estate as the 2007/ 08 Great Recession demonstrated. Using the Case- Shiller pri-
cing index as a guide to U.S. residential real estate, peak- to- trough differences exceeded 35%.
Therefore a $500,000 home, 80% leverage, purchased at the peak of the market and sold at the
trough required, using simple math, a $75,000 check to be written at time of sale to satisfy the
lender (ignoring principal pay down and the time period between peak- to- trough which was
about two years). Aye Caramba!!!
Probability of Gain (P(Gain))
How is a project best described? Most often a project is described by the yield alone, e.g. IRR.
This characterization is not only immature but dangerous. Yield fails to answer the most basic
question of investment, i.e. “Will I get my money back?” Stated differently, yield fails to answer
the question pertaining to Return OF Capital (Figure 1.22b). The question that yield does address
is how much money a project is expected to realize, i.e. yield, if all assumptions and forecasts
prove to be correct. Yield is therefore, fundamentally, Return ON Capital.
Real Estate Basics 27
27
To summarize, the three fundamental questions for investing are, in order of importance,
listed below. For the purposes of this text, only numbers 2 and 3 are covered and #2 will be
considered, as per this text, the most important question to address when investing. Legal
structures and issues with real estate ownership are left to texts which specialize and focus on
legal issues, both criminal and civil. However, it is important to notice that earning money is
actually the tertiary goal of investing and the least important despite being the main focus in
many texts.
1. Stay out of Jail
2. Return OF Capital
3. Return ON Capital
Note: In this text, as it is a modelling text, we will be focusing on Return OF Capital and Return
ON Capital. As such, this text will refer to Return OF Capital as often being the rst rule of
investing. However, an investor must never lose sight of the legal, moral, and ethical issues
involving real estate. Real estate is much greater than return, capital, and yield, it is also about
people and social consequences. However, these discussions are beyond the scope of this text.
To answer both the second and third goals for investing in real estate, a project’s probability
characteristics must summarize both return and risk, i.e.
x
si
n,
−
()
1
θσ
, where
x
is the project
expected return, i.e. central location, and
σ
the risk. It is important to quantify both of these
metrics and value projects, even though they are singular, as a portfolio consisting of one asset.
Therefore, from portfolio theory, it is understood that the goal is NOT to maximize return but
rather maximize the efciency. The equivalent is true for an individual asset held in isolation, the
goal is to maximize the efciency of the project.
What is efciency? Loosely dened, efciency is the point of maximum return for any given/
chosen level of risk, i.e. the ratio of risk and return. In human terms, consider dating in your
personal life. The goal of dating is to nd the individual that creates a two asset portfolio that
is most “efcient.” No matter who you are dating there is always a baseline “gruff.” The goal
of dating is therefore to maximize the return for the chosen level of “gruff.” If you are dating
someone with a lot of “gruff” then a high return is required. If dating someone with a low amount
of “gruff” a lower return is required. Efciency, while probably not quantied, is sought in
everyday life and human relationships. It is also found in investing in real estate assets.
How is efciency quantied? There are three main methods to quantify efciency:
(1) Coefcient of Variation (CV), (2) Sharpe Ratio (industry standard for portfolio management),
and (3) Treynor Ratio (mostly commonly found in academic journals). This text will focus exclu-
sively on the Coefcient of Variation metric which is used by Markowitz and the general key-
stone of Modern Portfolio Theory (MPT). The Coefcient of Variation, CV, quanties efciency
as follows:
CV
x
=
σ
Note that risk is in the numerator and return is in the denominator. While it may, at rst, be
counterintuitive, maximizing efciency is therefore minimizing the coefcient of variation, i.e.
greater efciency has a lower CV. This is dissimilar to the Sharpe and Treynor ratios whereby
greater efciency is achieved by higher values due to the calculations being reciprocal to CV.
How does the Coefcient of Variation answer the rst and most important question for
investing, i.e. Return OF Capital? Again, we start with the assumption that a project’s yield
28 Real Estate Valuation Basics, Theory, and Skills
28
follows a normal distribution. Further, we recall that a normal distribution is dened by two
parameters: (1) central location and (2) standard deviation. Both values have been quantied for
the project/ asset, presumably.
As the real estate project’s return and risk parameters most likely will not be zero (0) and one
(1), i.e. a standard normal distribution has central location of zero (0) and standard deviation of
one (1), a z- score is utilized. The z- score translates a non- standard normal curve, i.e. the project,
to a standard normal curve, i.e. having 0 central location and 1 standard deviation. This enables
the use of standard normal distribution tables and common use of the empirical rule.
The z- score equation is as follows:
Zscore xx
where
x
i
i
−=
−
=
=
=
σ
σ
:
Data Value
xAverage Return
Standard Deviation
For the purposes of analysis, the z- score which in statistics 101 was an abstract formula to be
used on an examination as a means to an “A” grade, is now a major contributor to real estate’s
most fundamental question, i.e. “Do I get my money back?!” When using the z- score to translate
a project’s performance,
x
is the E(return), i.e. E(r), and σ the project’s risk. Xi is the data value
whose location is being transformed from a non- standard normal curve via the z- score to the
standard normal curve.
To determine the Return OF Capital Xi = 0; this is the point where the project returns initial
capital and more, i.e. returns more than the initial invested capital. Therefore, a z- score using
Xi = 0 represents the point at which a project returns the initial capital and begins to earn posi-
tive yield, stated different, the area to the right of the z- score is the P(Gain) of the project.
Demonstrated more simply, setting Xi = 0 the z- score simplies to the following:
Zscore x
where
xi
−=
−
=
σ
:
0
The next step is recognizing the relationship between this special case of z- score and the CV.
Notice that the z- score is the negative reciprocal of the CV. Therefore, the area to the right of
the z- score, as calculated by the negative reciprocal of the CV, is P(Gain). Remembering that
P(Gain) addresses the rst basic question in investments, e.g. Return OF Capital. See below:
CV xCV
x
xzscore=∴−=−=−=−
σ
σσ
11
An example of this translation is the 15- year historical return of the Case- Shiller index, composite-
10 MSA. The historical year- over- year average return as calculated on a monthly basis is 5.38%
and the risk, i.e. standard deviation of returns, is 10.28% for the data ending July 2010. The
results are summarized as such:
Real Estate Basics 29
29
x=
=
538
10 28
.%
.%
σ
While the data do answer the second question of investing, “Return ON Capital,” i.e. 5.38%, it
does not, until transformed, address the rst and most important question, “Return OF Capital.”
To accomplish this the Coefcient of Variation, efciency, must be calculated. Once the CV is
quantied, it can be transformed through the use of the z- score to calculate P(Gain) (see below).
CV
x
== =
σ
0 1028
0 0538
1 9108
.
.
.
Transforming to the z- score as follows:
zscore xx zscore x
CV
i
−=
−⇒− =− =− =− =−
σσ
11
1 9108
0 5233
.
.
Therefore, the area to the right of – 0.5233 on a standard normal curve is the Probability of Gain,
P(Gain), for this investment as described by the composite- 10, Case- Shiller index.
Two methods to calculate P(Gain) can be utilized: (1) back- of- the- envelope and (2) use of
tables and/ or MS Excel (where tables are embedded within formulae). The rst method requires
an understanding of the empirical rule. The area to the right of zero (0) is 50%. The area between
+ / – one (1) standard deviation, according to the empirical rule is approximately 68%. Therefore
the area between negative one and zero is 34%. Half of the area between negative one and zero
is therefore 34%/ 2 or 17%. Assuming a linear relationship (note: it is not a linear relationship),
then the area to the right of negative 0.5, i.e. – 0.5, is 50% + 34%/ 2 or 67%. Again, this is a rough
approximation for P(Gain), i.e. P(Gain) = 67%.
The second method, using MS Excel, uses the “= NORMSDIST(z)” function to calculate the
value we determine P(Gain) = 69.94%, e.g. “= 1- NORMSDIST(- 0.5233)= 69.94%.” Stated dif-
ferently, there is a 30.06% chance that an investment in a project with the characteristics as quan-
tied by the historic 15- year performance of the Composite- 10, of returning less than the initial
capital invested. Therefore it is the efciency measure that, when transformed utilizing a z- score
and with the assumption of the underlying distribution being normal, provides the rst and most
important answer to the real estate investment question, P(Gain), i.e. Return OF Capital.
Two examples of P(Gain), i.e. Return OF Capital, are provided. These examples assume the
project distribution, i.e. return and risk, are known and/ or previously quantied.
Example 1
Project A has the following characteristics:
E(r) = 21%
σ = 20%
The probability of Return OF Capital, i.e. P(Gain), is therefore as follows:
z
x
=
−
=
−
=
−
=
µ
σ
µ
σ
0021
20
105(. )
30 Real Estate Valuation Basics, Theory, and Skills
30
For simplicity, the z- score will be rounded to (1.00). Therefore, the probability of Return OF
Capital P(Gain) is represented by the area to the right of (1.00) on the standard normal curve, see
below (Figure 1.23).
Figure 1.23 P(Gain) right of z- score
Remembering that approximately 68% of the data for a standard normal curve is between + / – 1σ
and that the distribution is symmetrical, i.e. 50% is above 0 and 50% below. Therefore, the area
to the right of – 1 is therefore 50% (area to right of zero) plus 34% (area between – 1 and 0; 68%/
2) or 84%, i.e. P(Gain) = 84%.
Therefore, for a project with E(r) = 21% and risk (σ) = 20%, the probability of a Return
OF Capital, P(Gain), is therefore 84%. (Note: this assumes the return probability distribution is
normal which is the base assumption for quantifying Probability of Gain.)
Example 2
Project B has the following characteristics:
E(r) = 15%
σ = 22%
The probability of a Return OF Capital is therefore as follows:
zx
=−=−=−=
µ
σ
µ
σ
0015
22
068(. )
The probability Return OF Capital is represented by the area to the right of (0.68) on the standard
normal curve, see Figure 1.24.
Figure 1.24 z- score relative to whole numbers
As 68% of 34% is 23.1% (0.68 * 34%); the area to the right of (0.68) is 50% + 23.1% or 73.1%
(this assumes a linear relationship). Therefore, for a project with E(r) = 15% and risk (σ) = 22%,
the probability of Return OF Capital, i.e. P(Gain), is therefore 73.1%. (Again note: this assumes
the return probability distribution is normal.)
Real Estate Basics 31
31
Please note in Example 2, linear interpolation was used to determine the area greater than
(0.68) for the standard normal curve. This is an approximation. The value as read from the
standard normal table yields 75.18% as the probability of Return OF Capital.
For example, suppose there are three potential scenarios, (1) Best, (2) Most Likely, and
(3) Worst. The best case scenario maintains a 15% likelihood of occurring and will provide a
21% IRR, the most likely case maintains a 65% chance of occurring and maintains a 13% IRR
while the worst case scenario provides a – 4% IRR with only a 20% probability of occurring. The
quantication of return, risk, and the Probability of Gain or P(Gain) is as follows.
ER Er Ep
ER
ii
i
n
()
.. .. .
=
() ()
()
=×
()
+×
()
+−
=
∑
1
021015 012065 004××
()
=+
−=
=
()
−
()
=
=
∑
02 003007 0 008 009
0
22
1
2
.... .
.
σ
σ
Er Er p
ii
i
n
221 009015 012009 065004 00902
22 2
−
()()
+−
()()
+− −
()()
.. .. ....
==+ +
=∴==
(
0 0021 0 000585 0 00338
0 006125 0 006125 0 0782
2
.. .
...
,
σσ
σ
r
))
()
90 782.%,. %
Now that we have arrived at the expected return (r) as well as the risk (
σ
) we will utilize this
information to nd the Coefcient of Variation as well as the z- score and nally P(Gain).
CV x
CV
zscore
CV
=
==
−=−=−=−
σ
782
90 86
11
86
1 156
.%
.% .
.
.
Utilizing the “= 1- NORMSDIST(z)” formula in MS Excel, P(Gain) is 87.64%.
Example 3
For the following investment with the ve states of nature, what is the P(Gain)?
State E(Ri) Pi
1) 26.0% 10%
2) 24.0% 55%
3) 9.0% 15%
4) – 7.0% 15%
5) – 28.0% 5%
32 Real Estate Valuation Basics, Theory, and Skills
32
E(R): 14.7%
Variance: 0.023
Standard deviation: 15.1%
CV: 1.02
z- Score: (0.98)
P(Gain): 83.50%
Example 4
For the following investment with the ve states of nature, what is the P(Gain)?
State E(Ri) Pi
1) 35.0% 15%
2) 24.0% 50%
3) 13.0% 20%
4) – 8.0% 10%
5) – 23.0% 5%
E(R): 17.9%
Variance: 0.022
Standard deviation: 14.8%
CV: 0.82
z- Score: (1.21)
P(Gain): 88.70%
Efciency and Probability of Gain (P(Gain)) Problem(s)
1. For the following investment with the ve states of nature, what is the P(Gain)?
State E(Ri) Pi
1) 35.0% 20%
2) 22.0% 45%
3) 10.0% 25%
4) 1.0% 5%
5) – 22.0% 5%
2. For the following investment with the ve states of nature, what is the P(Gain)?
State E(Ri) Pi
1) 38.0% 25%
2) 24.0% 30%
3) 5.0% 30%
4) – 3.0% 10%
5) – 29.0% 5%
Real Estate Basics 33
33
Asset Class Consideration(s)
5- Year S&P5000 VBMFX Re s RE
Avg Return 0.75% 5.19% – 2.95%
Std Deviation 21.3% 2.92% 12.12%
CV 28.49 0.56 (4.11)
z- Score (0.04) (1.78) 0.24
P(Gain) 51.40% 96.22% 40.38%
10- Year* S&P5000 VBMFX Re s RE
Avg Return 0.63% 5.32% 4.15%
Std Deviation 20.01% 2.72% 12.91%
CV 31.76 0.51 3.11
z- Score (0.03) (1.96) (0.32)
P(Gain) 51.26% 97.48% 62.61%
* Apr 10’- Dec 01’
An example of asset class distributions is the three main asset classes: (1) equity, (2) xed
income, and (3) real estate. To demonstrate an understanding of the differences, indices will be
used to approximate each, e.g. equity (S&P 500), xed income (VBMFX), and real estate (Case-
Shiller composite- 10). For the period ending July 2010, the following return/ risk characteristics
were calculated on a historic 5- and 10- year basis.
What is striking is the differences when comparing the P(Gain) for each asset class. Fixed
income, as expected, has a signicantly lower risk/ return ratio. Equity and residential real estate
have signicantly lower P(Gain)s, i.e. higher probability of losses. For both time periods of this
analysis the risk for equity is higher than real estate while the return, although higher in the 5-
year case, may not warrant the risk of investment.
Exercise:
3. Complete the comparison of asset classes, i.e. US equity, xed income, and real estate, at 5,
10, and 15 years for the U.S. and your respective country (depending on data availability).
While the returns can vary and the relationships between the asset classes adjust depending
upon the historical time period, it is important to view the investment, any investment, in any
asset class as a probability distribution, i.e. with return AND risk, rather than as simple return.
Decisions made on asset investment without risk quantication fails to address the most simple
investment premise, i.e. P(Gain): Does one get their money back?!
Further, an understanding of the return/ risk dynamic is essential for an asset reposition, as a
slight change in either can drastically change asset performance and appearance. It should gener-
ally be a rule that when risk can be reduced at a greater rate than return the asset becomes more
efcient.
34 Real Estate Valuation Basics, Theory, and Skills
34
Finally, understanding the risk/ return characteristics of each asset class is essential for basic
investing. This understanding is essential as a go/ no- go decision. Without understanding the
investment options available, how can one decide if a potential investment is superior to others?
A qualitative real estate example would be swapping out a single tenant in a building with
another single tenant for the same lease terms. The difference is that the initial tenant could be
a B- grade tenant while the new tenant is ExxonMobil, i.e. AAA rated. As the lease terms have
not changed, the expected return for the asset (building) is unchanged, however, the risk pro-
le has signicantly changed, i.e. risk has been reduced. This certainly will justify a change in
tenant and may even justify a slight $/ sf adjustment downward for the AAA credit tenant, e.g.
ExxonMobil, as acknowledgment of the higher quality tenant. As described, this is a very basic,
very successful asset reposition at the tenant level.
Another example is underwriting multifamily tenants as “essential” or “non- essential.” During
the COVID pandemic, non- essential workers could not report to work. However, essential
workers, e.g. grocery store employees, were required to go to work. Therefore, when approving a
lease, consider if the tenant’s employment is considered “essential” or “non- essential.”
COVID absolutely changed the risk prole to tenants for multifamily assets. Public servants
and “essential” positions reduced risk of a project while “non- essential” positions, e.g. adminis-
trative, increased risk.
Problem Sets
1. Draw cash ow diagrams
a. Investment type
i. Draw a cash ow diagram for an investment with an initial cash outow of 30,000
dollars followed by a three- year period with an inow of 5,000 dollars and a return
of capital in year four.
ii. Draw a cash ow diagram for a real estate investment without tenants.
b. Financing type
i. Draw a cash ow diagram for a loan made on a traditional xed income mortgage.
ii. Draw a cash ow diagram for an IO loan made to a development company.
2. Capitalization Rate quantication problems
a. Determine the Capitalization Rate for a property with a sales price of $600,000 and a
Net Operating Income (NOI) of $42,000.
b. Determine the NOI for a property with a Capitalization Rate of 7% and a sales price of
$740,000.
c. Determine the Sales Price for a property with a NOI of $24,000 and a Capitalization
Rate of 6%.
3. Net Present Value problems
a. Calculate the Net Present Value (NPV) for the following project. An outow of $110,000
in year 0 followed by an inow of $40,000, $45,000 and $50,000 in one year increments
with a discount rate of 8%.
b. Calculate the Net Present Value (NPV) for the following project. A outow of $8,000
dollars followed by an inow for two years of $2,000 and a single inow in the third
year of $6,000 with a cost of capital of 9%.
Real Estate Basics 35
35
4. Internal Rate of Return problems
a. Calculate the Internal Rate of Return (IRR) for the following project. An outow of
$8,000 in year 0 followed by an inow of $3,000, $3,500 and $3,500 in one- year
increments.
b. Calculate the Internal Rate of Return (IRR) for a project with $100,000 dollars of initial
cash outow followed by three years of $20,000 inow and a single lump sum inow
in the nal year of $65,000.
5. Modied Internal Rate of Return problems
a. Assuming $120,000 dollars are invested today, for the next three years $24,000 dollars
are returned annually and in the fourth year a lump sum of $70,000 dollars is provided.
What is the IRR of this series of cash ows? Assuming a reinvestment rate of 12%, what
is the MIRR of these cash ows.
b. Assuming $50,000 dollars is invested today, next year $15,000 dollars are returned
and in the second year $40,000 returned. What is the IRR of this series of cash ows?
Assuming that $2,500 is invested in a government T- bill with a one- year maturity and a
rate of 0.77%, what is the MIRR of these short- term loans?
6. Risk Calculations problems
a. Dene risk for a project with a (1) best case scenario providing a 20% return and a 10%
probability, (2) a most likely scenario consisting of a 12% return and a 60% probability
of occurring and, (3) a worst case scenario with a 20% likelihood of returning a – 6%
return.
b. Dene risk for a project with a (1) Best Case scenario providing a 27% return and a 15%
probability of occurrence, (2) a Most Likely scenario consisting of a 12% return and a
60% probability of occurrence and (3) a Worst Case scenario with a negative 13% return
and a 25% probability of occurrence.
7. Efciency problems
a. Utilizing the information from question 6 a, nd the Coefcient of Variation and the
z- score.
b. Utilizing the information from question 6b, nd the Coefcient of Variation and the
z- score.
8. Probability of Gain, P(Gain)
a. Utilizing the information from questions 6a and 7a, nd the P(Gain) and P(Loss).
b. Utilizing the information from questions 6b and 7 b, nd the P(Gain) and P(Loss).
Solutions
NPV Practical Application Answers
1. Calculate the NPV for the following project. An outow of $14,000 in year 0 followed by
an inow of $6,000, $7,000, and $7,000 in one year increments with a discount rate of 8%.
d. $3,113.75
2. Calculate the NPV for the following project. A outow of $10,000 dollars followed by an inow
for three years of $2,500 and a single inow in the nal year of $6,000 with a cost of capital
of 9%. (Hint: this is an annuity, nal year annuity payment is grouped in with nal cash ow.)
a. 961.34
36 Real Estate Valuation Basics, Theory, and Skills
36
3. What change to NPV occurs when discount rate for both problems is reduced by half?
c. 4,464.10/ 2,130.19
IRR Practical Application Answers
1. Calculate the IRR for the following project. An outow of $14,000 in year 0 followed by an
inow of $6,000, $7,000, and $7,000 in one year increments.
c. 19.6%
2. Calculate the IRR for a project with $10,000 dollars of initial cash outow followed by three
years of $2,500 and a single lump sum inow in the nal year of $6,000.
a. 13.3%
MIRR Practical Application
1. Assuming $100,000 dollars are invested today, for the next three years $12,000 dollars are
returned annually and in the fourth year a lump sum of $80,000 dollars is provided. What is
the IRR of this series of cash ows? Assuming a reinvestment rate of 15%, what is the MIRR
of this cash ow?
a. IRR: 4.52%/ MIRR: 6.35%
2. Assuming $25,000 dollars are invested today, next year $2,500 dollars are returned and
in the second year the full $25,000 returned. What is the IRR of this series of cash ows?
Assuming that $2,500 is invested in a government T- bill with a one- year maturity and a rate
of 0.77%, what is the MIRR of this cash ow?
d. IRR: 5.12%/ MIRR: 4.92%
Efciency and Probability of Gain (P(Gain)) Problem(s) (Answers)
1. For the following investment with the ve states of nature, what is the P(Gain)?
State E(Ri) Pi
1) 35.0% 20%
2) 22.0% 45%
3) 10.0% 25%
4) 1.0% 5%
5) – 22.0% 5%
E(R): 18.4%
Variance: 0.018
Standard deviation: 13.2%
CV: 0.72
z- Score: (1.39)
P(Gain): 91.71%
Real Estate Basics 37
37
2. For the following investment with the ve states of nature, what is the P(Gain)?
State E(Ri) Pi
1) 38.0% 25%
2) 24.0% 30%
3) 5.0% 30%
4) – 3.0% 10%
5) – 29.0% 5%
E(R): 16.5%
Variance: 0.031
Standard Deviation: 17.7%
CV: 1.08
z- Score: (0.93)
P(Gain): 82.35%
Answer Sheet:
1. Draw Cash Flow Diagrams
a. Investment Type
i. Draw a cash ow diagram for an investment with an initial cash outow of $30,000
dollars followed by a three- year period with an inow of $5,000 dollars and a return
of capital in year four.
ii. Draw a cash ow diagram for a real estate investment without tenants.
38 Real Estate Valuation Basics, Theory, and Skills
38
b. Financing Type
i. Draw a cash ow diagram for a loan made on a traditional xed income mortgage.
ii. Draw a cash ow diagram for an IO loan made to a development company.
2. Capitalization Rate quantication problems
a. Determine the Capitalization Rate for a property with a sales price of $600,000 and a
Net Operating Income (NOI) of $42,000.
CapitalizationRate
==
42 000
600 000 7
,
,%
b. Determine the NOI for a property with a Capitalization Rate of 7% and a sales price of
$740,000.
NOIx==740 000 751 800,%,
c. Determine the Sales Price for a property with a NOI of 24,000 and a Capitalization
Rate of 6%.
Sales Pricen
==
24 000
6
400 000
,
%
,
3. Net Present Value problems
a. Calculate the Net Present Value (NPV) for the following project. An outow of $110,000
in year 0 followed by an inow of $40,000, $45,000 and $50,000 in one year increments
with a discount rate of 8%.
NPV=− ++++++=100 000 40 000
18
45 000
18
50 000
18 53
123
,,
(%)
,
(%)
,
(%),008 9.
Real Estate Basics 39
39
b. Calculate the Net Present Value (NPV) for the following project. An outow of $8,000
dollars followed by an inow for two years of $2,000 and a single inow in the third
year of $6,000 with a cost of capital of 9%.
NPV=− ++++++=8 000
2 000
19
2 000
19
6 000
19 151 32
123
,
,
(%)
,
(%)
,
(%).
4. Internal Rate of Return problems
a. Calculate the Internal Rate of Return (IRR) for the following project. An outow
of $8,000 in year 0 followed by an inow of $3,000, $3,500 and $3,500 in one year
increments.
NPV
IRRIRR IRR
==−+
+
()
++
()
++
()
08000
3 000
1
3 500
1
3 500
1
123
,
,,,
IRR = 11.72%
b. Calculate the Internal Rate of Return (IRR) for a project with $100,000 dollars of initial
cash outow followed by three years of $20,000 and a single lump sum inow in the
nal year of $65,000.
NPV
IRRIRR IRR
==−+
+
()
++
()
++
()
0 100 000
20 000
1
20 000
1
85 000
1
12
,
,,,
33
IRR = 9.36%
5. Modied Internal Rate of Return problems
a. Assuming $120,000 dollars are invested today, for the next three years $24,000 dollars
are returned annually and in the fourth year a lump sum of $70,000 dollars is provided.
What is the IRR of this series of cash ows? Assuming a reinvestment rate of 12%, what
is the MIRR of these cash ows.
40 Real Estate Valuation Basics, Theory, and Skills
40
b. Assuming $50,000 dollars are invested today, next year $15,000 dollars are returned
and in the second year $40,000 returned. What is the IRR of this series of cash ows?
Assuming that the $15,000 is invested in a government T- bill with a one- year maturity
and a rate of 0.77%, what is the MIRR of these short- term loan?
6. Risk Calculations (problems)
a. Dene risk for a project with a (1) Best Case scenario providing a 20% return and a 10%
probability of occurrence, (2) a Most Likely scenario consisting of a 12% IRR and a
70% probability of occurrence and (3) a Worst Case scenario with a negative 6% return
and a 20% probability of occurrence.
ER Er xE pi
ER
i
i
n
()
.. .. .
=
() ()
()
=×
()
+×
()
+−
=
∑
1
020010 012070 06 ××
()
=
=
()
−
()
=−
()()
+
=
∑
02 09
020009 010
22
1
22
..
.. .
σ
σ
Er Er p
ii
i
n
0012009 070006 00902 0063
0 006 00
22
2
.. .....
..
−
()()
+− −
()()
=
=∴=
σσ
006 0 0795
90 795
=
()
()
.
,
.%,. %
r
σ
b. Dene risk for a project with a (1) Best Case scenario providing a 27% return and a 15%
probability of occurrence, (2) a Most Likely scenario consisting of a 12% return and a
60% probability of occurrence and (3) a Worst Case scenario with a negative 13% return
and a 25% probability of occurrence.
ER ER P
i
n
ii
()
=
()
×
=
∑
1
Real Estate Basics 41
41
ER
()
=×
()
+×
()
+− ×
()
=027015 012060 013025 008.. .. .. .
σ
2
1
2
=−
()
×
=
∑
i
n
ii
RE
RP
σ
2027008 015012 008060 013008 02500=−
()
×+ −
()
×+−−
()
×=.. .... ..
..
1174
σ
==0 174 0 1319..
r,.%,
.%
σ
()
=
()
8001319
7. Efciency problems
a. Utilizing the information from question 6a, nd the Coefcient of Variation and the
z- score.
CV x
CV
zscore
CV
=
==
−=−=−=−
σ
795
90 88
11
88
113
.%
.% .
.
.
b. Utilizing the information from question 6b, nd the Coefcient of Variation and the
z- score.
CV Er
=
()
==
σ
0 1319
0 0800 1 6488
.
..
Zscore
CV
−=−=
−=
−
11
1 6488
061
.
.
8. Probability of Gain, P(Gain)
a. Utilizing the information from questions 7a and 7b, nd the P(Loss) and P(Gain).
7a) z- Score = – 1.13
P(Gain) = area to the right; in MSExcel: = 1- NORMSDIST(- 1.13)= 0.8708 or 87.08%
P(Loss) = area to the left; in MSExcel: = NORMSDIST(- 1.13)= 0.1292 or 12.92%
7b) z- Score = – 0.61
P(Gain) = area to the right; in MSExcel: = 1- NORMSDIST(- 0.61)= 0.7291 or 72.91%
P(Loss) = area to the left; in MSExcel: = NORMSDIST(- 0.61)= 0.2709 or 27.09%
42 Real Estate Valuation Basics, Theory, and Skills
42
MS Excel Formulae Utilized
1. Net Present Value: = NPV()
2. Internal Rate of Return: = IRR()
3. Modied Internal Rate of Return: = MIRR()
4. Standard Deviation (Sample): = STDEV()
5. Variance (Sample): = VAR()
6. Normal Distribution Percentage: = NORMSDIST()
Single-Family Rental (Single Sheet)
Britt, R . (2016).
Here are the 10 most pro-business cities in America
. MarketWatch. Retrieved 9 June 2016,
from www.marketwatch.com/story/top-10-the-tight-horse-race-for-first-place-2015-05-12
Brown, S . (2015).
2016 forecast: Dipping job growth will slow Dallas-area real estate market
. Dallasnews.com.
Retrieved 26 May 2016, from www.dallasnews.com/business/residential-real-estate/20151016-2016-forecast-
dipping-job-growth-will-slow-dallas-area-real-estate-market.ece
Brueggman, W.B. & Fisher, J.D. (2008). Real Estate Finance and Investments. Boston: Mcgraw-Hill Irwin.
Carlisle, C. (2016).
3 reasons DFW’s housing market will be spared from energy slump – Dallas Business
Journal
. Dallas Business Journal. Retrieved 26 May 2016, from
www.bizjournals.com/dallas/news/2016/02/09/3-reasons-dfws-housing-market-will-be-spared-from.html
Carlisle, C . (2016).
Report: Home prices in DFW rise 9.5%; Texas sees new real estate heights – Dallas
Business Journal
. Dallas Business Journal. Retrieved 26 May 2016, from
www.bizjournals.com/dallas/news/2016/02/01/report-home-prices-in-dfw-rise-9-5-texas-sees-new.html
Carlyle, E . (2016).
Houston, Texas – In Photos: America’s Fastest-Growing Cities 2015
. Forbes. Retrieved 4
June 2016, from www.forbes.com/pictures/emeg45eegeg/3-dallas-texas
Comprehensive Housing Market Analysis Dallas-Plano-Irving, Texas . (2016). U.S. Department of Housing and
Urban Development. Retrieved 26 May 2016, from www.huduser.gov/portal/publications/pdf/DallasTX-comp-
16.pdf
Dallas/Fort Worth Energy Outlook . (2016). Us.jll.com. Retrieved 26 May 2016, from www.us.jll.com/united-
states/en-us/Research/US-Dallas-Fort-Worth-Energy-Outlook-2015-JLL.pdf?113a73b0-f47a-471c-85ec-
bd3debc1d35e
Dallas neighborhoods on Walk Score . (2016). Walk Score. Retrieved 6 June 2016, from
www.walkscore.com/TX/Dallas
Dallas Home Price Index . (2016). St. Louis Federal Reserve. Retrieved 26 May 2016, from
research.stlouisfed.org/fred2/series/DAXRNSA
Eldred, G . (2002). Value investing in real estate. New York: Wiley.
England, J ., Anderson, J ., & Shafer, L . (2016).
Katy Trail Archives | Candy’s Dirt
. Candysdirt.com. Retrieved
5 June 2016, from http://candysdirt.com/tag/katy-trail/
Four Top Uptown Dallas Apartments to MEET PEOPLE . (2014). Uptown101 – Uptown Dallas Apartments.
Retrieved 5 June 2016, from http://uptown101.com/four-top-uptown-dallas-apartments-to-meet-people/
Futures & Options Trading for Risk Management – CME Group . (2016). Cmegroup.com. Retrieved 26 May
2016, from www.cmegroup.com
Graham, B . & Zweig, J . (2003). The intelligent investor. New York: Harper Business Essentials.
GSPC Historical Prices | S&P 500 Stock – Yahoo! Finance . (2016). Finance.yahoo.com. Retrieved 26 May
2016, from finance.yahoo.com/q/hp?s=^GSPC+Historical+Prices
Guide to Oak Lawn – Places to Live, Things to Do and Restaurants in Oak Lawn | Neighborhood Guide .
(2015). Neighborhood Guide. Retrieved 20 June 2016, from
http://neighborhoods.dmagazine.com/dallas/central-dallas/oak-lawn/
Jaffe, A . & Sirmans, C . (1995). Fundamentals of real estate investment. Englewood Cliffs, NJ: Prentice Hall.
Kimmel, P ., Weygandt, J ., & Kieso, D . (2007). Financial accounting. Hoboken, NJ: John Wiley.
MarketFlash May 23 . (2016). Cbre.us. Retrieved 26 May 2016, from www.cbre.us/o/dfwmarket/real-estate-
trends/Pages/MarketFlash-May-23-2016-Cultivating-a-co-working-culture-for-the-regions-entrepreneurs.aspx
Month Indicators – April 2016 . (2016). North Texas Real Estate Information Systems Inc.. Retrieved 26 May
2016, from www.ntreis.net/documents/NTREISSTATS_255201611055.pdf
National Home Price Index . (2016). St. Louis Federal Reserve. Retrieved 26 May 2016, from
https://research.stlouisfed.org/fred2/series/CSUSHPINSA
Oak Lawn, Dallas, TX Crime Rates & Statistics . (2016). Areavibes.com. Retrieved 19 June 2016, from
www.areavibes.com/dallas-tx/oak+lawn/crime/
Osborne, J . (2015).
Texas jobs once again in growth mode
. Dallasnews.com. Retrieved 26 May 2016, from
www.dallasnews.com/business/headlines/20151016-texas-jobs-once-again-in-growth-mode.ece
Perez, C . (2016a).
Industry Experts on Dallas’ ‘Next Golden Age’
. DMagazine. Retrieved 28 May 2016, from
Perez, C. (2015). Industry Experts on Dallas’ ‘Next Golden Age’ Retrieved May 28, 2016, from
www.dmagazine.com/publications/d-ceo/2015/real-estate-annual/dallas-real-estate-golden-age
Perez, C . (2016b).
Why the Dallas Real Estate Market Is Hot
. (2016). Dmagazine.com. Retrieved 26 May
2016, from www.dmagazine.com/publications/d-ceo/2015/real-estate-annual/hottest-dallas-real-estate-market-
in-decades
Perryman, R . (2015).
Selected Data from the Texas Forecast | The Perryman Group
. Perrymangroup.com.
Retrieved 3 June 2016, from www.perrymangroup.com/forecasts-and-publications/current-texas-forecast-data/
Poorvu, W . & Cruikshank, J . (1999). The real estate game. New York, NY: Free Press.
Randall, T . (2016).
Dallas real estate market is going wild as homes are snapped up in days
. MarketWatch.
Retrieved 26 May 2016, from www.marketwatch.com/story/dallas-real-estate-market-is-going-wild-as-homes-
are-snapped-up-in-days-2016-05-23?link=sfmw_tw
Staiger, R . (2015) Foundations of real estate financial modelling. New York, NY: Routledge
U.S. Department of Commerce . (2016). Census.Gov. Retrieved 4 June 2016, from
www.census.gov/housing/hvs/files/currenthvspress.pdf
VBMFX Historical Prices | Vanguard Bond Index Fund Total Stock – Yahoo! Finance . (2016).
Finance.yahoo.com. Retrieved 26 May 2016, from http://finance.yahoo.com/q/hp?s=VBMFX+Historical+Prices
What Would Cubes Do?: If You Think Katy Trail Ice House Opening Up a Plano Location Is a Great Idea, Then
You’re Missing the Point . (2013). Whatwouldcubesdo.blogspot.com. Retrieved 5 June 2016, from
http://whatwouldcubesdo.blogspot.com/2013/05/if-you-think-katy-trail-ice-house.html
West Village – Uptown Dallas – Dallas’ most vibrant walkable shopping and dining district. (2016).
Westvillagedallas.com. Retrieved 5 June 2016, from www.westvillagedallas.com/
Young, M . (2015).
Population gain in Dallas-Fort Worth is eye-popping, census figures show
.
Dallasnews.com. Retrieved 26 May 2016, from www.dallasnews.com/news/metro/20150520-some-things-
really-are-bigger-in-texas-census-figures-show.ece
Zhou, J . (2010). Testing for co-integration between house prices and economic fundamentals. Real Estate
Economics,
38
(4), 599–632. doi:10.1111/j.1540-6229.2010.00273.x
Five-Unit Multifamily
Hart C . Washington, DC metro area. December 2014 housing market update.
http://www.rbintel.com/sites/default/files/Housing%20Market%20Update%20-
%20DC%20Metro%20December%202014.pdf. Updated 2015. Accessed January 31, 2015.
Fallen R . Real estate broker, personal communication, October, 2014. .
Fairfax County Department of Tax Administration, Real Estate Division .
http://icare.fairfaxcounty.gov/ffxcare/datalets/datalet.aspx?mode=profileall&sIndex=0&idx=1&LMparent=138.
Updated 2015. Accessed April 3, 2015.
Fannie Mae. Home affordable refinance (DU refi plus and refi plus) FAQs .
https://www.fanniemae.com/content/faq/harp-du-refi-plus-faqs.pdf. Updated 2015. Accessed February 7, 2015.
Federal Housing Finance Agency . HARP: Your best route to a better mortgage. http://www.harp.gov/. Updated
2014. Accessed January 10, 2015.
Green D . Harp loan program: Rates & guidelines 2015.
Freddie Mac . 30- year fixed- rate mortgages since 1971. http://www.freddiemac.com/pmms/pmms30.htm.
Updated 2015. Accessed April 3, 2015.
http://gcaar.com/uploadedFiles/PDF/ToolKit/Research/Home_Sales_Statistics/DC_Single_Family/dcsf1214.pdf.
Updated 2014. Accessed February 3, 2015.
Blinder A . Forecast: 6.9 million to live in D.C. region by 2040.
Washington Examiner
. February 14, 2013 2013.
Available from: http://www.washingtonexaminer.com/forecast-6.9-million-to-live-in-d.c.-region-by-
2040/article/2521497.
George Mason University Center for Regional Analysis. Update from the U.S. census: Population growth in the
Washington DC metropolitan area, 2013–2014, 2015.
United States Census Bureau. US Census Bureau State & County QuickFacts – Falls Church City.
http://www.census.gov/. Updated 2013. Accessed March, 2015.
Turner L . Urban turf: Delta associates. http://dc.urbanturf.com/articles/tag/delta_associates. Updated 2015.
Accessed February 12, 2015.
Wadsworth K . Senior mortgage loan officer, personal communication, February, 2015.
