Real Estate Investment Case Study Challenge #1

-

Introduction

 

This post will challenge you to build a real estate private equity investment case study. The challenge prompt is at the bottom of this post. We will review your case if submitted to our case study help forum before April 21, 2019. One accurate solution exists, but your Excel could look different than any other participant's Excel. We will judge you on your use of simple formulas, style and arrangement, trustworthiness, and correctness.

We Offer Free Professional Review


We will professionally review cases submitted to the relevant thread in our Case Study Help forum before the deadline of April 21st at 11:59pm

Various Methodologies Are Valid


No two excel models are ever quite the same. Your approach may look and feel a bit different than another participant's approach. When building your case and referencing educational materials, focus less on the exact design and implementation of the solution. Where you may choose an INDEX-MATCH, another might prefer a SUMIFS. Rather, focus on the investment thesis and cash flow story that drives the model.

One Solution Is Correct


Although your implementation may vary, only one accurate solution exists for this particular real estate investment case study challenge. We provide you with all relevant inputs, removing assumption uncertainty. Because one solution exists, we are able to judge the correctness of your model. Bear in mind, this challenge tests your mechanical ability and general awareness of investment fundamentals. Subsequent, more difficult challenges will expand to test your performance when facing uncertainty.

Our Judgment Criteria


Criteria #1: Simple Formulas

Real estate private equity investments professionals spend more time reading Excels than they do writing Excels. Thus, long, spaghetti formulas that follow winding paths are a needless headache. Write clear, short formulas that repeat consistent patterns. Your future self, and those that audit your model, will thank you.

None of this:

=IF(B2>97, "A+", IF(B2>93, "A", IF(B2>89, "A-", IF(B2>87, "B+", IF(B2>83, "B", IF(B2>79, "B-",  IF(B2>77, "C+", IF(B2>73, "C", IF(B2>69, "C-", IF(B2>57, "D+", IF(B2>53, "D", IF(B2>49, "D-", "F"))))))))))))

Excel Spaghetti Functions

Criteria #2 Style and Arrangement

Real Estate Private Equity Excel Models are created to convey information. If your model is hard for an outsider to access or quickly read, then your model is bad. Good style and arrangement directly impact your model's ability to convey information. Imagine your boss calls you at 2AM on Saturday demanding urgent information from a model your colleague built who is currently out of town. Compare the two examples below and imagine which you would prefer to be stuck with.

Bad Style Example

Reproduction of Top YouTube Result for "Real Estate Private Equity Model"

Good Style Example

Monthly Cash Flows in the Verdant Apartments LBO Model from Breaking Down REPE
 

Criteria #3: Trustworthiness (Checks and Testing)

You need to convince other investors to trust your model works correctly. Further, your readers do not need to review your model's functionality if your checks are designed well. Building trust and saving your readers time is a win-win. Every model needs robust checks. Any model without obvious, robust built-in checks is an immediate failure.

Criteria #4: Correctness

Beyond building a model that is easy to use, read, and trustworthy, you also need to accurately forecast the cash flows. Our specific prompt removes uncertainty and paints the same picture for all participants. If your math is correct, you will arrive at the same result as our team of professional real estate private equity investors.

Relevant Educational Materials

To learn the fundamental skills necessary to complete this case challenge, check out our Real Estate Private Equity Starter Kit. This course takes several hours.

If you need to prepare for a real estate private equity interview or want to dive deeper into the nuances of investing, then check out our flagship course, Breaking Down Real Estate Private Equity. This course takes several weeks.

The Case

 

Below is a high-level description of an apartment building that is for sale. Given a target gross levered return of 11.0%, what would you pay for this building? To answer this question, build a monthly cash flow model that meets the four judgment criteria listed above.

Unlevered Assumptions
  • 350 Total Units at $X per month
  • $50 of other income per month
  • 95% run-rate occupancy
  • $1,250 of total opearting expenses per unit per month (every expense, inclusive of RE tax, insurance, utilities, etc.)
  • $350 of capital reserves per unit per year
  • Property management fee is 2.5% of effective gross revenue (EGR)
  • 10 year investment hold period
  • 3% income growth rate
  • 2.5% expense growth rate

Levered Assumptions
  • Property is offered free and clear
  • Your fund's leverage constraint is 50% LTV
  • Issue floating rate debt, assume the market rate is L+200bps
  • Debt terms are 60 months I/O with an amortization term of 360 months

Useful External Resources

You can find the LIBOR curve on Chatham Financial's website

Learn more about modeling amortizing debt at Excel Easy

Comments

Post a Comment

Popular posts from this blog

Real Estate PE Technical Interview Questions I Ask at My Megafund

-
This post includes technical questions that I ask when interviewing candidates at my real estate private equity megafund. These questions are softballs meant to test your basic grasp of easy concepts. If you struggle with these real estate PE interview questions to any degree, then you’re not yet up to par. If you need to brush up, I highly recommend you check out Leveraged Breakdowns , the premier resource for real estate private equity education. Practice Until These Technical Questions Feel Like Baby Math Your interviewer considers the following technical real estate private equity interview questions to be easy, baseline checks of your skill. Practice until you’re comfortable in a high-stress interview. Also, make sure your mental math skills are tight. If not, there’s plenty of basic apps and websites out there that will help you practice. What is NOI? Question: What is NOI? What are the most common items included in NOI? Answer: Net operating income (“NOI”) is the st
Read more

REPE Fund Profile Series - Part Nine: BentallGreenOak

-
Welcome to Part Nine of our Fund Profile Series. So far, our series has worked its way through the first eight entries of the top 50 global real estate private equity (REPE) firms, according to the 2020 PERE 100 list. Here, each of our Fund Profiles briefly summarizes each fund’s history, as well as examining the details of the funds themselves. And we also outline a few of each firm’s key real estate equity investments in recent years to help you prepare for your upcoming REPE technical interview (s).  Following on from Part Eight of the series focused on the Rockpoint Group, today’s post will take a closer look at BentallGreenOak. Key facts about BentallGreenOak The BentallGreenOak group of companies was formed in 2019 after the North American real estate asset management arm of Sun Life Financial Inc (at the time), Bentall Kennedy, purchased a majority stake in GreenOak Real Estate from the Tetragon Financial Group. After the deal closed, BentallGreenOak became part of SLC Ma
Read more

Don’t Look Stupid When Discussing Yield Maintenance

-
The key to landing your first job in real estate private equity is to crush the interview. As an outsider to the industry, though, you run the risk of looking like an outsider if you don’t know the terminology forward and backward. No one wants to see that look on the interviewer’s face when you just said something that proves that you don’t know what you’re talking about. At Leveraged Breakdowns , we are committed to helping you become fluent in the language of real estate private equity and common interview questions. We also give you hands-on training with our real estate investment analysis online courses . Why isn’t it Just A Prepayment Penalty? Lenders love to throw in penalties in the event a loan pays off early. The early payoff could be due to the borrower refinancing or it could be due to sale of the property. The lender doesn’t really care. When lenders deploy capital, they do not want to see it come back early. They are managing cash flows and earning the spread be
Read more