If you are using the Fifth Edition (and not Edition 5.1), be sure to download the PDF in the Fifth Edition Substitute Chapter Pages section below to substitute for the same numbered pages in the printed text.

Overview

Listen to this narration if you prefer

 

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

Summary

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

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

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

Questions

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

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

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

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

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

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

Explained: Figure 5.18 vs. Figures 6.2/.7/.8 After-Tax Cash Flow

Audio Interviews

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

 

BRUCE KIRSCH: You learn the industry standard structures for overlaying a financial projection framework onto the actual mechanics of real estate transactions for income producing assets.

And we start at the very top line of gross potential rent, and take it all the way through the bottom line of the post income tax cash flow to equity. And there’s a lot in between.

What students sometimes don’t understand is while making these projections is unfortunately an exercise in projecting the wrong numbers we still do need to strive for as much precision as possible. Why is that important if the numbers are going to be wrong anyway?

DR. PETER LINNEMAN: Yeah, it’s an important point. I played a lot of sports over my life. And you never know what’s going to really happen in the game conditions except that you know it will be generally very different than you prepared for, than you planned for, than you practiced and drilled for.

But if you have not practiced, and drilled, and prepared, and thought about it ahead of time in controlled environment– in an environment where as little as possible is moving, where you have as much time to concentrate as possible– you’ll never execute well when chaos is occurring around you, and things have to be made.

And so in sports you go through drills, practice drills, exercise drills. Endlessly. So that you’re prepared for what should happen and have built in instincts on what happens when things go awry, which they certainly will.

And in real estate I think it’s the same way. In business it’s the same way. If you don’t make yourself think about what’s going to happen? Why should it happen? How will it happen? If it doesn’t happen, what might? Why would it might happen that way?

If you don’t make yourself do that– not just before you make an acquisition, after you make an acquisition. Because as you know, these are ongoing, living beasts that you’ll do it whether it’s a budget, or a plan– a business plan– as you proceed through the life of the property. If you’re not doing it in the quiet and forcing yourself to think about these questions you’re going to be unprepared to deal with the reality of what comes up.

So before you’re buying a property you’re going through this discipline of line, by line, by line. What, why, and why do I believe that? And why could it be? And you’re having those discussions with your partners, and your investors, and your management team.

And it’s all to make yourself go through the exercise of what could happen? Why it could happen? What am I really anticipating? And as part of that drawing on your experience base saying, and if it goes wrong what will I do? Am I willing to live with it if it goes wrong? Am I really willing to bet on outstripping inflation by 4% for three years?

So by forcing yourself you’re really imposing the thought discipline of thinking through your business strategy. And as part of that what will you do if your business strategy is wrong?

So that’s why it’s critical. It’s not as critical in the sense of, and the numbers show that your NOI will be four million as, what is it as a real business matter that makes me believe income could be four million? And what would keep it from being four million? And how would I react if it does?

BRUCE KIRSCH: Right. And it’s often hard to impress upon students or folks who are new in the business the ripple effect of every individual input. Every individual assumption. In our models we color code them so that they jump out off the page in a bold blue font.

And every one of these inputs in isolation looks totally innocuous. But when taken in concert with one another you’re creating this, as you said, this living organism that is taking direction from you that rents are going to grow at x percent, and the expenses are going to grow at x minus 2%. And everything has a ripple effect.

DR. PETER LINNEMAN: And one of the mistakes that people make– and I mention it in the book– is they look at the line items in abstraction from one another. And they don’t operate in abstraction.

I mean you saw it in the downturn. The downturn of 2009 not only hurt occupancy, it hurt rents. It froze– instead of thinking that your empty space would lease up quickly it leased up slowly. Instead of your full space being full it was empty. Instead of rents going up they went down. Instead of your marketing budget going down it went up. Cap rates went up.

And you go, they’re all reacting to the same phenomenon, which is a very weak economy and no confidence. And your loan that you assumed was going to roll can’t roll. They’re all linked to the same phenomena, which is a bad economy and terrible capital market confidence.

So if all you did was say, well, what if the cap rate was 50 basis points higher, but my rents are the same, and so forth. You miss the reality of the question you should be asking is, what if the economy were really bad? What if the capital markets really did horrible things? As a whole and work through what they would mean for each cell in your spreadsheet.

Now you’re still going to be wrong. But you need to think about it not of, what if this cell is 2% higher or 2% lower? But, what if the world is a very different world? What cells does it change, and in which ways would it change them?

BRUCE KIRSCH: Right. Exactly. I was teaching chapter 4 once to a class and a student asked me, all right so now we understand everything that drives the property level pro forma. How do we model in for the general economy?

And I was a little dumbfounded, and I guess I did a poor job of teaching. I didn’t make it clear that every one of these assumptions that informs the property level has opinions and estimations about the general economy baked into it.

DR. PETER LINNEMAN: Yep. And my building relative to other buildings– what if somebody builds a new building? What does it mean for my building? And when does it mean it?

So my experience is ask big questions. And think them through about the whole spreadsheet rather than small questions like, what if occupancy is 5% higher or 5% lower? Think about the big question, and then try to answer what it means for the whole page.

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

BRUCE KIRSCH: When you’re new in the business, if you end up being in finance, you spend what I like to call a lot of quality time in spreadsheets, sometimes late at night. And when you’re working in these things for a long time, and when you’re tired, physically tired, and your eyes are tired it’s easy to get lost in the numbers, because they’re all glowing equally bright from the screen. And when you print them out, they all look equally authoritative. And unfortunately when we’re working in software, such as Microsoft Excel or another platform, the platform is not going to tell us, it’s not going to raise a red flag necessarily if we do something wrong, because generally speaking these soft-wares are simply carrying out inputs or instructions that we have coded into cells as formulas. And so this is really dangerous, because if the program is simply carrying out your instructions, and your instructions, unbeknownst to you, are faulty, you’re going to get something coming out the other end which is faulty as well. And so how can students and people who are newer to the business, or newer to working in spreadsheets, protect themselves and avoid making these big mistakes?

DR. PETER LINNEMAN: You have to grasp what you were just saying, which is sort of the weakest link theory. You can get 999 numbers out of 1,000 right, but if the thousandth is wrong the math is distorted by the weakest link. So you have to understand that and believe that. Second to that is a bit of humility, which is, even if you’re very good, what are the chances that you don’t make a mistake as a human being 1 out of 1,000 times. So if there are 1,000 inputs into a spreadsheet, what are the chances you didn’t make a mistake on one of them, that’s if you’re very good. And if you’re starting, and it’s late at night, and you’re tired, and you’re pissed off at the boss, and you’re thinking about your boyfriend, and all these things that make people people, the odds get down to maybe 1 in 50 or one in 100. Which means if there are 1,000 numbers you’ve made 10, 20 mistakes, and they’re not going to cancel one another, they’re real mistakes.

So it’s first understand, and take to heart the weakest link. And second, have a little humility, and understand even if you’re very careful, even if you’re very diligent, you make mistakes. Now how do you see them? And I’ve done this and I’m sure you’ve done this, and when I was young I did the reverse of it, you walk in, and you say here’s the answer. And the answer is, let’s look on an IRR basis for example, and you say the IRR is 19.7. Well, come on, if your boss has been around, and is a real professional, they know that buying a property at a 7 cap with 50% debt, where the debt is at 5%, and the income’s growing 1% a year, and you hold it for four years, they know that’s going to be around an 8% IRR. They just know that. Why? Not Because they’re doing it in their head, although some of them may be able to do it. It’s because they’ve seen it enough times in the past, that that’s what the math is, and their brain just says when you show me a 22% IRR, it’s wrong. And the young person says, no, no it’s right, it’s right. You go, no, no, I know it’s wrong. I can’t tell the difference in my head, in that scenario I said, between, is it 8.2 or 7.8, but I can tell the difference between an 8 and a 12%, and a 16%, and a 20%, and I know this is wrong. Similarly if you buy something at a 10 cap and you believe the income’s going to go up 5% a year for five years, and you believe you sell it around the same cap rates you got in at, and you’re using 60% debt at 4% interest rate over the time period, you know that that’s going to end up in the 20, 25% IRR range. I don’t know exactly where, but if you walk in and tell me it’s an 8%, I know it’s wrong. So there’s this sense of getting used to, almost like an SAT test, where you say, OK, what would come next in the pattern. If it’s red, green, blue, red, green, what should come next. You get used to these patterns, and get used to does it make sense that this could be the pattern. So weakest link, humility that even the best of us make mistakes, and is it remotely believable that if I’m making 10% a year that I only get an 8% IRR. You go, no not unless I think the value is falling a lot, but I said, the value is going up a lot, so if I come back with an 8% return it can’t be right. So it’s that building in the sensitivity, and unfortunately most of us learned the hard way, by getting beat up a little bit all along the way.

BRUCE KIRSCH: Right, one thing that I’ve learned the hard way is, if it looks too good to be true, then it’s probably not true.

DR. PETER LINNEMAN: That’s exactly true.

BRUCE KIRSCH: I mean it’s kind of a funny approach of saying, all right, so you’re in your first job out of school, and you’re working for a company, and into your lap falls the best looking deal in the history of real estate in your city. I mean, what are the odds that this is just going to grace you with its presence, and nobody else has seen this thing. The odds are very, very, very low because the market is pretty efficient at the end of the day, in terms of people finding excellent transactions.

DR. PETER LINNEMAN: And in fact, that’s not just a valuable point in terms of looking at spreadsheets, that’s a good point in business. When you’re offered a deal that looks too good to be true, you’re offered a resume for somebody to hire that looks too good to be true, when you’re offered financing that’s too good to be true, look both ways a lot of times, and start really doing your homework, because in my experience, as you say, things that look too good to be true generally are not true. It could be a scam. It could be you’ve not gotten the true information. It could be all sorts of things. When your instincts tell you it’s too good to be true, you should be digging deep and fast to figure out what’s really going on.

BRUCE KIRSCH: Right, and I find personally that usually it’s one of two things. It’s either you’re forgetting to pay back your loan, or you forgot to buy the land. Something of that nature.

DR. PETER LINNEMAN: Or if it’s outside of the spreadsheet world, where it’s an opportunity you’re being offered, or an employee you’re being offered, you may be the target of a scam, or somebody who’s lying about their credentials, or their property, or something. It’s the old, if the London Bridge is for sale, you’ve got to ask why are you the lucky one who can buy it for nothing.

Fifth Edition Substitute Chapter Pages

Download PDF

Excel Figures

Download Excel

Key Terms

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

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

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

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

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

Property gross revenues less vacancy.

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

Annual NOI divided by annual Debt Service Expense.

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

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

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

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

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

Before-Tax Levered Cash Flow less Income Tax Liability.

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

The negotiated property sale price before any potential deductions.

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

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

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

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

A property’s Gross Sales Price less Selling Costs.

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

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

Chapter Headings

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

The Top Quarterly Publication for Commercial Real Estate Investors

 

 

Excel for Real Estate Certification

 

 

REFAI Certification

 

 

Investment Strategy Specialists

 

 

Looking for investment analysis software?

valuate app logo

 

 

Financial Modeling Self Study Courses

 

 

Quarterly Capital Markets Webinar Slides

0:00
0:00