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

Why Asset Underwriting Skills Are Necessary For Real Estate Private Equity Interviews?

-
Why are asset underwriting skills necessary for a real estate private equity career? Real estate private equity interviews demand serious preparation. Although no two funds are the same, each will expect a strong understanding of real estate underwriting. Traditionally, real estate underwriting has been hard to learn outside of a real estate private equity career. Thus, industry outsiders have struggled to acquire the basic investments skills necessary to underwrite even a simple single-asset acquisition. This post details why asset underwriting skills are beneficial and how Leveraged Breakdowns’ real estate private equity course will help you ace your next REPE interview . How asset underwriting helps you better understand real estate private equity funds? No two real estate private equity funds are the same, each varies across countless factors. Some of the most important factors to understand when searching for your real estate private equity career are: Size Sector Focus...
Read more

Cap Rates - Wicked Simple or Wicked Complicated?

-
Are cap rates wicked simple or wicked complicated? The snarky answer is “Yes.” What we mean is cap rates have an “everyday” application that is intuitive and simple. Its foundation, however, is much more complicated. As you move toward a career in a real estate private equity firm, you will need to learn to use cap rates in their simple form. But it never hurts to understand the origins of the concept.   What is a Cap Rate? To begin, the term “cap rate” is shorthand for “capitalization rate.” The term is well-defined in real estate appraisals, and is aimed at determining a market rate of return for the property being appraised (see more on appraisals here ). The appraisal breaks the cap rate down into three component parts: the risk free rate, the equity rate, and the debt rate. The sum of these three rates becomes the capitalization rate used to arrive at the property value based on the income approach (specifically, net operating income divided by the cap rate equals th...
Read more