by Brian DeChesare Comments (14)

What’s in a Real Estate Private Equity Case Study? Part 2: A Value-Added Office Case Study

Value-Added Real Estate Private Equity Case StudyA few months ago, I posed a simple question here:

“Can you find anything online?”

And then I explained why it’s still hard to find information on some topics, like real estate private equity case studies, even today.

Nothing much has changed in the past few months, so today I wanted to share another REPE case study, the full video tutorial, the case study prompt, the Excel solution file, and the full investment recommendation.

This one’s similar to the first case study because both are based on real documents sent in by readers, but other than that they’re dramatically different:

  • The first case study was closely linked to market data, but this one is far more about the numbers and getting the Excel calculations right.
  • The first case study was based on a property in Seattle, whereas this one is based on a property in Boston, so you won’t be arrested if you don’t work at Microsoft or Amazon. Go diversity!
  • There is no real “trick” in this case where you have to modify the assumptions; however, a few of the Excel formulas could vaporize your brain. Be careful!

Let’s go:

The Video Walk-Through, Case Study Files, and More

Table of Contents:

  • 2:41: Part 1: The Types of RE PE Case Studies
  • 5:19: Part 2: This Case Study and What Makes It Tricky
  • 12:40: Part 3: Why Excel is Horrible for This Case Study
  • 16:59: The Scenarios in This Model
  • 17:51: Part 4: The Property Model and Returns Analysis
  • 26:39: Part 5: The Investment Recommendation
  • 28:37: Recap and Summary

And in text form:

Types of REPE Case Studies and Where This Fits In

Last time around, I laid out the different types of real estate investment strategies, which corresponded to the types of case studies you might receive:

  • Core or Core-Plus: The property exists. It’s stabilized. You acquire it. You don’t change it, or you change it very little. Then you sell it. Low risk, and low potential returns (5-10%).
  • Value-Added: The property exists, but it’s not “stabilized” because there’s some issue like a low occupancy rate, below-market rent, or a sorely needed renovation. You buy the property, spend money to improve it, and sell it. Higher risk, but also higher potential/targeted returns (10-15%).
  • Opportunistic: The property doesn’t exist, or it does exist but will be torn down and replaced. You’re building something brand-new, which may result in GREAT SUCCESS… or catastrophic failure. This one offers the highest risk, but also the highest potential returns (15-20%+).

This case study is firmly in category #2 because there are numerous problems with the property:

  • The occupancy rate is only 74% vs. the overall Boston market average of 85%.
  • The average rent of $36.72 is below the market rent of $38.00 per rentable square foot per year.
  • The property is in need of a renovation; it was first constructed in 1893. That’s so old that Excel won’t let you enter it as a date!

On the other hand, the property has a lot going for it: it’s in downtown Boston, and it has AAA tenants like Google, Athenahealth, and various pharmaceutical companies:


And perhaps most importantly, its asking price is very low.

An $18 million USD asking price for this property translate to a $ per rentable square foot (RSF) value of $157 compared with a citywide median of $443.

The implied Going-In Cap Rate is also only 8.4% – 8.8%, against a median of 6.5% in the broader office market in Boston (The Cap Rate varies because each scenario has a different Year 1 NOI).

Even if you include the cost of the $2 million renovation, the $ / RSF is still only $174 and the Going-In Cap Rate is still only 7.5% – 8.0%.

These attributes have made your firm, Yi Ti Capital Partners (YTCP), take notice of the property.

The firm usually invests in the Jade Sea region of the world, and it has significant holdings in the gold mines of Asshai and the spice production facilities in Yin and Jinqi.

But recent calamities there, such as the Grey Death and at least one dragon loose in the west, have caused YTCP to turn its attention to the safer environment of Boston.

Case Study #1 vs. Case Study #2: Show Me the Numbers, Please

You might think that this case study is more about the numbers because it’s a value-added deal with a renovation…

…But you’d be wrong.

The real reason is that the analysis becomes much more granular when the property has fewer tenants.

With a multifamily property or a hotel, there’s rarely a reason to go into granular detail because there are hundreds of rooms in each property, and the terms, room rates, etc. are all similar.

You might separate the rooms into studio vs. 1-bedroom vs. 2-bedroom, or Small Room vs. Big Room vs. Awesome Baller Room for hotels, but even that is unnecessary in most cases.

But take a look at the tenant base here:


If one tenant fails to renew its lease, you might be looking at a 10% loss in in-place revenue.

Plus, each tenant might have different start and end dates, different contract lengths, and so on.

So you need to analyze tenants individually.

This approach is standard for office and retail properties.

ARGUS vs. Excel: Who Wins in a Death Match?

But there is a significant downside to this granular analysis: the Excel formulas can get incredibly confusing and complicated.

Take, for instance, one of the formulas used to calculate “free months of rent” for tenants (a typical concession in an office lease contract):


In case you can’t see images, the formula is as follows:

=IF(AND(H$39>$E110,H$39<=EOMONTH($E110,$D110)),-H85*$H21/Months,IF(AND(H$39> $F110,H$39<=EOMONTH($F110,$D110)),-H85*$H21/Months,0))

And it gets more confusing once you move into the formulas for the Tenant Improvements and Leasing Commissions.

Truthfully, Excel is not suited to these formulas and you’re much better off using ARGUS for this exercise.

In these models, you typically assume probabilities for the renewal status of each tenant: there’s a 70% chance tenants will renew, and a 30% chance tenants will not renew and that you will have to find new tenants instead.

If there’s only one “renewal point” for each tenant, you can do this in Excel because you just plot out two separate paths and probability-weight each path.

So you take the rent, free months of rent, general vacancy, TIs, and LCs from the “renewal” case and multiply all those by 70%, and then do the same for the non-renewal case and multiply by 30%.


This setup gets unwieldy once you have multiple “renewal points.”

For example, if it’s a 10-year model and each tenant has a contract lasting 2 years, the complexity multiplies: you’d have to handle 2^5, or 32, separate cases.

Just for fun, here’s the chart that James, who worked with me on this case study, created:


After seeing that, I simplified the entire case study by including only longer-term contracts and ensuring that each suite had at most one renewal point.

In real life, however, this gets so complicated that you’ll often use ARGUS to generate the Pro-Forma Model.

And then in Excel you’ll build in the acquisition/exit assumptions, debt schedule, Sources & Uses, the returns analysis, and so on.

Part 1: Market and Rent Roll Analysis

First off, the Boston office market has a lot of positive qualities:

  • The median per-capita income is 30% above the national median, with 2-3% labor-force growth and a household formation rate double the population growth rate.
  • Unlike in Seattle, there’s more diversity in the job market, with employment gains split between tech and life sciences and other industries.
  • The overall vacancy rate for office properties is 12%, with a median $42 / SF asking price for Class-B offices.

We have no reason to doubt the provided assumptions since the market data confirms that the assumed occupancy rate and rental rate increases are feasible.

From the rent roll analysis, we can draw a few conclusions:

  • Over 50% of the in-place revenue expires within the first 4 So we need to think about what happens if the current tenants do not renew, or if it takes much longer to find replacement tenants.
  • There’s unusually high turnover in Year 3 (30%) – what happens if the Turnover Downtime between tenants is greater than expected?
  • On the other hand, tenant concentration is on the low(er) side since no tenant represents more than 8% of the rentable space in the building.


As a result, we’ll have to sensitize around a higher-than-expected number of Downtime Months, higher concessions, and a higher lease cancellation rate.

Part 2: Setting Up a Lot of Ugly Excel Formulas

These formulas take hours to explain in the step-by-step case study in our Real Estate course, so I can’t go into them in detail here.

But here is the basic idea:

First, we split everything into two cases: 1) All the tenants renew, or 2) None of the existing tenants renew.

If all the tenants renew, the rents increase each year according to the assumed rates, and upon renewal, rent changes to the going market rate at the time.

Also in this case, the free months of rent are granted when the initial lease ends… since the same tenant renews and gets those free months.

The EOMONTH formulas check to ensure that we stick to the specified number of free months.

“General Vacancy” means that no tenant occupies the suite, and it’s not a case where an existing tenant has failed to renew.

So the 8 suites at the bottom are all in that category all the way across.

“Absorption” refers to the difference between the market rent and the in-place rent paid by the tenant, and therefore only applies to occupied suites.

We sum up everything at the bottom to get the Effective Monthly Rents in this scenario.

The other case – No Existing Tenants Renew – is similar, but there are a few differences:

  • We need to assume Turnover Downtime right after each lease ends. These 6-9 months represent the time it takes us to find a new tenant for the suite.
  • Free Months of Rent still exist, but come into play when these new tenants move in after the end of that downtime.
  • Absorption and General Vacancy still apply in the same way.

Then at the bottom, we roll up everything to get the typical items in a Pro-Forma Model for an office: Base Rental Revenue at Market Rates, Absorption & Turnover Vacancy, Free Rent (Abatement), and General Vacancy.

All of those, plus Expense Reimbursement Revenue (simple calculation done later on), give us the Effective Gross Revenue for the property.

The TIs and LCs get more complicated, but the basic ideas are as follows:

  • They’re both triggered when an existing tenant renews in Case #1 (all tenants renew) or when a new tenant moves in in Case #2 (no tenants renew).
  • TIs are paid out according to the rentable square feet in the suite and the assumption for TIs per square foot.
  • LCs are based on the value of the lease… to simplify this, we just take the first year of rent and multiply by the # of years in the lease and then multiply by the commission percentage. And then this number is distributed over 12 months.

In real life, the terms for leasing commissions – what you pay to the real estate agent(s) that brought in the new tenants – vary widely. So this example is representative, not universal.

The Punch Line About These Formulas

I’ll tell you upfront: these formulas do not handle all cases perfectly.

They mostly work for the scenarios here, but you can find edge cases where some figures are off.

I did this intentionally because it represents what will happen to you in real life.

In a time-pressured case study, you cannot worry about minutiae – get the major points right, make sure your schedule works reasonably well, but don’t obsess over edge cases.

Part 3: Creating a Pro-Forma Model, Acquisition/Exit Assumptions, and a Sources & Uses Schedule

This section might be the easiest part of this model: the operating expense assumptions are simple, and the acquisition/exit assumptions are similar to the ones in the first case study.

You assume a purchase price, renovation costs, fees, and then an Exit Cap Rate and an exit date.

Here, we assume the Exit Cap Rate declines in each case because of the renovation and higher occupancy rate.

The biggest improvement happens in the Upside Case, so there the Cap Rate drops to 7.00%; it’s 7.25% in the Base Case and 7.75% in the Downside Case:


Cap Rates can increase over time if the property stays the same, but if the average rents, occupancy rates, or other property attributes improve, it’s reasonable to assume a lower Cap Rate upon exit.

The only tricky thing on the Sources & Uses Schedule is the “TI / LC Holdback.”

This feature is a way for lenders to “hold back” some of the loan funding until progress is made on the renovation.

Doing so reduces the risk for them since they don’t have to release all the funds right away, and it encourages the property owners to finish more quickly.

Then there’s the debt schedule below this, which we don’t have time to cover here – but it’s a standard schedule where the principal is repaid over time and the entire remaining amount is paid back upon exit.

Part 4: Calculating Returns and Building the Infamous Waterfall Schedule

In real estate and private equity, returns are often distributed according to how well the investment performed.

In other words, if one investor group contributed $100 million and one investor group contributed $900 million, you won’t necessarily split the proceeds 10 / 90.

Instead, it might be 10 / 90 up to a 10% IRR… and then 15 / 85 between 10% and 15%, and then 20 / 80 above 15%.

This structure is called the waterfall returns schedule, and it causes confusion because people often set it up poorly.

Our schedule here is straightforward because it’s for an existing property on an annual basis.

The basic idea is as follows:

  • Beginning Balance: This increases over time as you are progressively “owed” more with each passing year.
  • Returns Accrual: If you’re in the 10% IRR tier, you’ll multiply the Beginning Balance each year by 10% to get this. This number represents “how much in additional returns you’re owed” after this year passes.
  • Repayment: “How much of a return do we earn in THIS year, and how much does that offset what we’re owed so far?” In most cases, this is just Cash Flow to Equity Investors times the 80% or 20% or whatever percentage we get in this tier. But at the end, this gets linked to the proceeds from the sale of the property.


You keep going down like that and splitting the IRRs by tier: the next tier represents the returns that correspond to an IRR between 10% and 15%, and the last tier is for the returns above 15%.

And then you can aggregate everything at the end, as we do, and show the IRR, multiples, and annual yields for the entire property, the LPs, and the GPs, and even the stats on an unleveraged basis (ignoring debt).

You should do this because your recommendations should never depend on just one number.

You always want to look at a range of returns across different investor classes and scenarios because investing is inherently probabilistic.

In the sensitivity tables at the bottom, we look at all the typical assumptions: Exit Cap Rates, market rents, exit dates, and LTV ratios, but we also include lease renewal probabilities and downtime months.

These tables directly correspond to the risk factors from Part 1: one of the major risks is that 50% of in-place revenue expires within four years.

So what happens if the renewal probability declines? What happens if it takes us longer than expected to find new tenants? What if it takes a really, really long time to find new tenants?

If you’re making a positive recommendation, your sensitivity tables need to reflect the risk factors and also how you might mitigate them.

Part 5: Making an Investment Recommendation

In the end, we recommend investing in this property using a simple argument, which you can see in the investment recommendation presentation:

  • First and foremost, it meets our targeted returns: a 20% 5-year IRR and 2.5x multiple in the Base Case, and a 1.2x – 1.5x multiple in the Downside Case.
  • We believe it’s plausible to boost the property’s occupancy rate to 80-85% and to increase average rents to $38 / SF, given the data for comparable properties.
  • For the numbers not to work, something disastrous would have to happen: market rents would have to decline by 25%, the lease renewal rate would have to drop from 70-75% to 30%, etc.

There are reasonable mitigants for all those risk factors: offer more rent-free months, higher TIs, or promise lower rent escalations in exchange for higher annual rent in the beginning.

The Punch Line: Could This Really Happen?

So this seems like a good deal… maybe even a great deal!

The only problem is that it’s unlikely we could find a property with these stats.

The downtown/CBD area of Boston is a prime area for real estate, so finding an office property with an asking Cap Rate of 8.0% – 8.5% when the going rate is 6.5% is highly unlikely.

Sometimes you do find hidden gems like this one, but they’re usually in much smaller cities and more “random” locations.

Unlike the first case study, this one is more “historical fiction” because I did not link it directly to a real property.

45 Milk Street is the address of an actual office building in Boston, but it’s occupied by a single tenant and its stats are different from the description here.

Up Next: One More REPE Case Study… and Possibly More

My goal is to give away more for free than what you would receive in most $10,000 seminars.

So in our third REPE case study, coming up later this year, we’ll cover a pre-sold condo development in São Paulo, Brazil.

That one will be a mix of “market” and “math,” and will be somewhere in between the first and second case studies in complexity.

You already have access to the full step-by-step case study if you have the Real Estate Financial Modeling course.

The course outline details everything else in there, including other RE development/acquisition cases and the REIT-related lessons.

We’re also revamping the rest of the course, and other upcoming cases include a hotel renovation in Dubai and an office/retail development in London.

Stay tuned.

Other Articles in This Series of Real Estate Private Equity Case Studies:

M&I - Brian

About the Author

Brian DeChesare is the Founder of Mergers & Inquisitions and Breaking Into Wall Street. In his spare time, he enjoys memorizing obscure Excel functions, editing resumes, obsessing over TV shows, traveling like a drug dealer, and defeating Sauron.

Break Into Investment Banking

Free Exclusive Report: 57-page guide with the action plan you need to break into investment banking - how to tell your story, network, craft a winning resume, and dominate your interviews

We respect your privacy. Please refer to our full privacy policy.


Read below or Add a comment

  1. Hi,

    many thanks for this insightful case. May I briefly ask whether you could show how to build the 2 driver sensitivity table?

    1. These are all just standard sensitivity tables, so I’m not sure what you mean. Enter the row and column values… link to the cell in the top-left corner… and select everything and press Alt + D + T to create the table.

  2. I’ve never commented on one of your posts before, but enjoy reading them and think they are very well written. I think this is a great case study, and plan on trying it out myself. I had the following questions:

    1. What time frame would you be expected to complete the case study in?
    2. Would you just get a blank excel doc. or would some of it be pre-formatted / have the tenants / comps tabs in there already?

    1. Please don’t go by anything in this case study, as it’s terrible and I hate it. We’re going to delete and re-do all the RE PE coverage on this site and make it more in-line with newer case studies.

      1. Do you know how soon that will be? I ask because I’m going to be going through REPE recruiting soon (1st year RE analyst in BB IBD) and want to be as prepared as possible.

        1. The second half of this year. No exact dates yet, but our Real Estate course already has the new, improved versions of all these case studies.

  3. So when a Real Estate P/E focuses on buying hotels in opportunistic scenarios to then renovate them, what case study would it more closely resemble out of the 3?

    1. This one, but really none of them because a hotel pro-forma is completely different from an office pro-forma since items like TIs, LCs, expense reimbursements, etc. do not exist and you do not create granular lease projections.

  4. Thanks for sharing this Brian. Really detailed with a lot of moving parts.
    Quick question regarding the 3% prepayment. Why do the investors pay a 15K prepayment on year 5 ?

    1. Because we assume that when the exit year is the same as the loan maturity year, the exit year takes precedence, i.e. you assume that the investors repay the loan because they have sold the property in that year, and as a result incur the prepayment penalty fee. You could obviously change that logic and do the opposite instead, but it makes such a small difference that it doesn’t really matter. And it’s only an issue in Year 5.

  5. Avatar
    Antonio Small

    Where did you get the structure for the income and expense categories in your pro forma? BOMA, IREM?

    I’d like to do some reading on it.

    1. It is based on internal documents from actual RE PE and RE lending firms. Please see:

  6. What part of the PE Structure does a Private Equity Associate/Analyst specifically work in?
    General Partner, Limited Partner, Placement Agent, etc.?

    1. Associates and Analysts at private equity firms are under the “General Partner” umbrella because GPs are the ones doing deals, managing investments, etc., i.e. what the private equity firms themselves do.

Leave a Reply

Your email address will not be published. Required fields are marked *