Case Study Challenge

[Lev]

This is the thread for the very first Leveraged Breakdowns Real Estate Private Equity Case Study Challenge. Once finished, please post a filesharing link to your finished case in this thread. We will review your case if submitted before midnight on April 30, 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 look forward to reviewing your submissions!

Also, feel free to discuss any questions or comments regarding the case in this thread. We will actively monitor discussions during the challenge period.

[Jeowong Jeowong]

For the management fee, is that after considering interest expense?

[Lev]

Management fees are charged on effective gross revenue. This does not include any interest. A manager is incentivized to maximize revenue, thus they are compensated on that metric. Interest is not correlated to a manager’s performance and is otherwise an unrelated metric.

[Jeowong Jeowong]

Below is a rough draft. Feel free to provide comments/feedback:

https://uofi.box.com/s/79ahnif78ug61kx3z4sbam3dctlsnpsf

[Caringpineapple]

A few assumptions (please correct me where I am wrong):

For expenses:

• I am assuming operating expenses are only applicable to occupied units?
• I am assuming CAPEX will be applicable to 100% of units, occupied or not?

For revenue:

• I am assuming other income comes from only occupied units? (this would be a contribution towards utility payments, condo fees, etc irl I am guessing?)

Thanks!

[Lev]

OpEx should be applied to total units, not just occupied units. Don’t dig into this too much, it’s just a high level assumption on average. Generally speaking, it’s hard to scale the majority of OpEx items up and down with unit-level occupancy (personnel, repairs, building services, taxes, insurance, etc.)

 

CapEx per unit should be multiplied by all units, yes.

 

Yes, feel free to occupancy-adjust other income.

 

jeowong—I’ll aim to review your model tonight.

[Lev]

OpEx should be applied to total units, not just occupied units. Don’t dig into this too much, it’s just a high level assumption on average. Generally speaking, it’s hard to scale the majority of OpEx items up and down with unit-level occupancy (personnel, repairs, building services, taxes, insurance, etc.)

 

CapEx per unit should be multiplied by all units, yes.

 

Yes, feel free to occupancy-adjust other income.

 

jeowong—I’ll aim to review your model tonight.

[Lev]

For Posterity, here is a link to the model that I reviewed for Jeowong.

Jeowong,

Great model, it shows a lot of promise. Some nits and observations below.

1. Row 27: break out your rent revenue between GPR & vacancy. Real estate investors like to see what your GPR & vacancy assumptions are separate from one another.
2. Row 28: Similar comment for Other Revenue — show the vacancy loss separately
3. Rows 30, 32: This is very minor. The mathematically proper way to turn an annual growth rate into a monthly growth rate would be =(1+r)^(1/12)-1. This is a bit more accurate than dividing an annual rate by 12.
4. Row 33: NOI is always inclusive of management fees. Also, REPE investors only ever use Economic NOI for multifamily, which is inclusive of capital maintenance reserves (the $400/yr metric quoted in the prompt). If you don’t know what Economic NOI is and why it’s important, refer to this post from Green Street Advisors, the premier real estate research outfit.
5. Rows 47 thru 53: Great find on the CBRE cap rate source. However, you should apply the cap rate to the economic NOI (inclusive of capex). Also, I’ve just updated my post to include an exit cap rate assumption to simplify since I figure it may be too much to ask students to derive their own cap rate like you did. Refer to the latest post, which reads:
   1. The T0 market cap is 4.5%, expanding at 5bps per year. The inflated exit cap is what you apply to your NTM exit NOI for GAV.
6. The index(range,COLUMN(cell),#) forumla is fancy, but could break if somebody added a column to the left of your model. Can you consider a safer way to build this formula?
7. Nothing explicitly wrong, but I prefer to separate my purchase & sale rows

Best,

Lev

[Jeowong Jeowong]

Hi Lev,

I’ve updated the model based on your feedback. It should be the same link as before: https://uofi.box.com/s/79ahnif78ug61kx3z4sbam3dctlsnpsf

Thanks.

[Caringpineapple]

Okay, Here’s my model!

https://drive.google.com/open?id=1j1hwJFofJNLoaNUddesfDcNlu7N59IeF

There is one thing which I am uncertain about and it regards both the purchase and sale price of the property. I was looking for the answer online for a while but I figured it would be easier directly asking you guys.

• For purchase price:
  • Is the NOI I use forward-looking or historical? Do people within REPE generally work with the upcoming year’s NOI projections when making a bid or is a bid generally tied to the historical YTD NOI?
• For sales price
  • Same question essentially. Do I use the historical YTD NOI for sale price or do I use a stabilized year to create an ask?

Thanks, look forward to your feedback

[Lev]

Your purchase price should be a plug that you solve for to achieve your IRR, as discussed in the REPE starter kit LBO module. You imply the entry cap rate using NTM NOI — this is standard practice across all CRE, not just REPE.

Your sales value is calculated by applying your exit cap rate to NTM NOI. For this case, we make no next-buyer adjustments. In Breaking Down REPE, we do cover a more nuanced case where taxes reassess at exit. Again, it’s industry standard to cap NTM NOI.

[Caringpineapple]

Thanks Lev.

That clears up my question.

[Caringpineapple]

I’ve made a couple of tweaks off of what you said in your previous comment. New Link:

https://drive.google.com/open?id=1UOslUEUi44gcTRgYChyjn-iP2SENyFsD

Thanks again.

[Lev]

Thanks for sharing. I’m jammed up today but will review in the next day or two.

[Lev]

For posterity, here is a link to the case study that I reviewed, submitted by Caringpineapple.

Elements I Appreciate

1. I like your use of (+) & (-) to indicate addition and subtraction. That improves the readability of this model, which is an important judgment criterion.
2. You’ve made a great effort to visually isolate different sections using colored headers, borders, and font style.
3. Your math is generally correct and well laid out. I apprecaite your demonstration of the growth formulas under the respective cash flow lines.
4. I like the left-side margin you create with column A. It would make it even sexier if you made a similarly-wide column at the end of the page too, and delineated the print range with alt+p+r+s. This visually confines the space.
5. It’s great that you included this mortgage debt schedule. many real estate private equity case studies I review often leave this out and it irks a lot of people, especially those with debt backgrounds.

Elements I would Suggest Changing

1. Monthly cash flows are hard to read as a manager. Over your real estate private equity career, your superiors will appreciate your ability to speed their review. Thus, I recommend you create an annual cash flow summary of the relevant cash flow line items. No need to annualize the backup build-ups that don’t directly generate IRRs. Of course, link to your monthly IRR calculation. More on the proper XIRR formula below.
2. Your use of borders is a bit noisy. If you start putting everything into some kind of box, then nothing is in a box. Perhaps a nice grey fill can indicate your T12 financials against the roll-up, or space. You have more visual elements in your toolkit that can separate information for your reader.
3. I’m personally against using multiple lines to name my cash flows. For instance, you put “Revenue” in column B and “(+) Base Rental Income” in column C.
4. Why am I against this? Imagine this single-asset model you’ve built was one of many within a portfolio roll-up model. If you want to find out what all of your assets’ property management fees are, you would probably use a SUMIFS or INDEX-MATCH. These formulas would need to be awkwardly retooled to lookup on two columns of names rather than one.
   1. The solution I prefer is to tab out your names using alt+h+6. This is good for two reasons. First, you achieve the same visual effect without messing up your column of potential lookup keys. Second, alt+h+6 is pure styling. Thus, you dont’ need to make your CF name ”    (+) Base Rental Income” (note the four spaces). Why this may seem small, your lookup key may not include those four spaces. In that instance, you would miss this item with spaces in its name. Hopefully you would catch this with a global tie-out check, but why run the risk?
   2. If you’re adamant about using two columns, and need to perform a lookup in the future, I recommend tagging your cash flows in column A (or adding an additional column on the far left) with standardized lookup names with no spaces, such as base_rental_income
5. You’re missing a unit mix. While it might seem stupid for an apartment with just one type of unit, your buy-side interviewers (or colleagues) are likely used to seeing unit mixes. It is visually easier for them to interpret that information, and will make you appear like an insider.
6. Every single one of your cash flow lines has a $ preceding the figure. All financial professionals, real estate private equity investors or otherwise, follow the following rules when formatting cash flows:
   1. The first in a block of lines starts with $
   2. Subsequent lines within the block do not start with a $, rather are just numbers with commas
   3. Sumlines always have $
   4. Anything afer a sumline does not have a $
7. Your IRR formula should just be XIRR. Look up the docs on how to use it, it’s pretty straightforward.
8. You’ve grown your cap rate with a hardcoded +0.0005. This would be better as its own input, perhaps right above the cap rate schedule. Most real estate private equity investors I’ve worked with stick to a linear expansion rate, but others may disagree. This isn’t a huge issue, the way you’ve done it is also common.
9. Your “NOI for the final year” formula is not flexible. Use a SUMIFS on the year #. This will allow you to flex the exit date. Same thing for your exit cap rate lookup, this could be achieved with INDEX or SUMIFS (and a few others).

Neat Formulas / Tricks You May Not Be Aware Of

1. You’ve hardcoded your “Hold Period Year.” I hope you didn’t manually type every single number, that would be a waste of time. If you weren’t aware of this trick, try typing this formula into cell E26 of your vanilla model (the first instance of “Hold Period Year 1”): =IF(MOD(D25,12)=0,D26+1,D26). Respond in this thread with what you think this formula is doing that makes it count the years properly. How would you use it to count quarters?
2. Real estate private equity investors like to know what year/month they’re reading as quickly as possible. Sure, you’ve labeled the “Hold Period Month” & “Hold Period Year” off to the left. But the first direction a reader’s eyes will go is up when they are looking to confrim the year/month. Thus, I recommend you style every cell  to identify which month/year it is with more detail. More specifcially, highlight all of your Year cells. Hit ctrl+1, tab, end, tab, type in “Year “0 (use the quotes like me, type everything from ” to 0). Enter. Repeat for the months. If you need to copy and paste style, hit ctrl+c (copy) then alt+e+s+t (paste only style).
3. Type alt+w+v+g to hide the grid. Do you see all the excess borders you copied & pasted over? This would reflect poorly during a real estate private equity case study interview. Make sure you don’t include noisy formatting like this.

[Author]

Posts