there is no standard REPE model. sure you have to understand the underlying cash flows of the asset which will be different for office, retail, residential, student housing, self-storage, hotels etc. to be honest we use argus to get office, retail and industrial property level CFs done and export to excel. for more operationally focused assets there are standard ways to model them (i.e. look at HVS hotel reports to see the standard pro forma for a hotel asset).

once you have the property level cash flows it depends on what you are investing in - debt, equity, mezz, development, a recapitalisation/restructuring, LBO, JV etc. i've not seen one model that does all of these and it is not that difficult to model out each of these scenarios if you understand what they are.

 
Relinquis:
I agree with RE_Banker on this... For REPE, make sure you understand the asset level DCF first, then add, debt and partnerships/corporate structures as appropriate.
Second (or third) this. For a beginner, it doesn't have to be related to private equity in any way - for simplicity's sake, what would it look like if I just bought a hotel, held it for X yrs, and sold it? quick example found with google https://www.reoworkout.com/pdf/property/info119_4.pdf (scroll to pg 25-27)
 

Received this related question recently via PM:

Have u ever seen the damordan RE Model? If so is it fairly accurate to work off of? http://pages.stern.nyu.edu/~adamodar/New_Home_Page/spreadsh.htm Marked "Other Assets - reval.xls"
That model is decent as an overview and for the key concepts... most actual RE investing is done on an IRR basis to target a certain return on equity, so you'll have the actual debt terms modelled out (with fees and repayment schedule) and cash flows to equity more prominently displayed.

The biggest difference in real world modelling is that the actual leases terms would be modelled to get to the rental revenue figure and the reimbursements as they will differ lease by lease, then market assumptions for roll-overs at the end of the leases and for any vacant space that's leased up (usually this will be done in Argus for large properties with several tenants). You'll also have deductions for landlord's contribution to Tenant Improvements and Leasing Commissions (TIs & LCs) in addition to Capex in the line items before you get your Net Cash Flow.... Also, one would deduct brokerage commissions/sales transactions costs from the terminal value calculation.

Also, people don't usually have beta and stuff in their models if they're just buying a building.

 
Best Response

I am going to echoe the advice already given - there is no standard for modeling RE and its much more important to understand finance in the context of an RE investment (that means know going-in/terminal cap rate and un/leveraged IRR).

For an entry-level candidate, I dont expect much. I also recommend getting familiar with Argus, as it is the industry standard for projecting cash flows. Complex reimbursement methods wil be over your head, but you could at least find an offering memo off the 'net (usually from an investment broker) and try to model that in Argus for practice (that's what I used to do). Create an 11 yr run, export to Excel and play with it. You will get to see what an operating statement looks like. I wouldnt worry too much about partnership distributions and construction modeling yet.

My firm actually paid to send me to a ULI class for Excel modeling and I learned Argus on my own time.

Man made money, money never made the man
 

These should get you started.

http://www.docstoc.com/docs/68190435/Commercial-Model-DO-NOT-OVERWRITE

http://www.finance30.com/group/realestate/forum/topics/template-for-rea…

Real Estate Investment Analysis -Reworked.xls

To be effective, you really need to combine the two. The first for tenant info (see relinquis' comment) and the second for the rest. They both do a poor job of modelling individual tenant economics, but should get you in the general place for retail/office valuations. Keep in mind that these models tend to have a focus on a RE investing (talking balloon payments, etc) which may or may not fit your needs.

They are both xls files. Scroll down 2-3 posts in the forum to see the file on the second one. Feel free to post questions, but keep them in the thread (no pm).

 

Three out of four RE PE modeling tests I've seen have used a hotel as the base asset. This is because it's just not practical to have a candidate model leases without Argus in an interview setting. Getting down to operating cash flow is fairly straightforward, just know the main line items (e.g. ADR, Occupancy, RevPAR, F&B, FF&E Reserve, etc.).

Two or three complexities that are normally worked in are (1) debt sizing (be able to size debt by debt yield, LTV, and fixed charge coverage), (2) development budget (option to add additional rooms / square footage for a given cost), and (3) partnership waterfall. This last part is fairly important because it is relatively unique to real estate and can make or break a candidate. The two most common promote structures are a multi-tiered promote (where the GP's split of cash flow ratchets up based on IRR to the LPs) and a three-phase "catch-up" promote (where the cash flow is generally split pari passu until the LPs receive a target IRR, then the GP gets, say, 50/50 until it has received 20% of the profits, and then 20% thereafter).

 
re-ib-ny:
Three out of four RE PE modeling tests I've seen have used a hotel as the base asset. This is because it's just not practical to have a candidate model leases without Argus in an interview setting. Getting down to operating cash flow is fairly straightforward, just know the main line items (e.g. ADR, Occupancy, RevPAR, F&B, FF&E Reserve, etc.).

Two or three complexities that are normally worked in are (1) debt sizing (be able to size debt by debt yield, LTV, and fixed charge coverage), (2) development budget (option to add additional rooms / square footage for a given cost), and (3) partnership waterfall. This last part is fairly important because it is relatively unique to real estate and can make or break a candidate. The two most common promote structures are a multi-tiered promote (where the GP's split of cash flow ratchets up based on IRR to the LPs) and a three-phase "catch-up" promote (where the cash flow is generally split pari passu until the LPs receive a target IRR, then the GP gets, say, 50/50 until it has received 20% of the profits, and then 20% thereafter).

Don't try and scare the kid ;)

 

The top-line input to the waterfall is simply levered cash flow (which itself will be impacted by any changes to operating profit and exit assumptions).

Tier #1 should model a capital account for the limited partner(s). This will accrue at the rate of the preferred return, just like how you would model a tranche of amortizing debt. The amount distributed to the LP under the first tier will be their pari passu interest in the levered cash flow, until the account has been depleted. You can then very easily calculate the GP's share as the inverse percentage of whatever the LP received.

Tier #2 is the catch-up. You enter Tier #2 with residual levered cash flow after paying off Tier #1. Supposing the catch-up rate is 50%, you distribute the residual cash flow 50/50 but build in a MAX function that stops this tier once the sum distributed to the GP equals the target percentage of the total profit (usually, say, 20%).

Tier #3 is easy, just split 80/20 or whatever the target percentage is.

Let me know if you need me to further clarify.

 

Thanks for the response re-ib-ny! I never thought of the initial pari passu returns as a capital account, that's an interesting way to put it. I normally just think of it in terms of the NPV function. In regards to your suggestion about using the MAX function as a "stop", could you elaborate further?

I normally think of / have learned capping the GP share of the reversion CF in a catch-up based on the MIN function. For example, if the max GP reversion CF is $2MM based on some target profit or IRR and the catch-up rate is 36/64 with the remaining reversion let's say equal to $10MM, I would structure the catch-up such that the GP's incremental share of the catch-up can be no greater than $2MM-X, with X being the GP reversion CF before the catch-up. Therefore, the MIN function would be: MIN($10MM*0.36, $2MM-X) and could be sensitized based on lower or higher exit per pound and reversion.

Also, I'm curious as to how you view modeling in a catch-up that caps the GP's share of the promote at 20% of JV partner's profit, assuming a 70% JV and 30% GP interest? I'm thinking the maximum promote offered would be Total profits * 70% *20%? Sorry if this all seems confusing/ not explained properly. Either way, I'm just glad there are other RE guys on the site to bounce ideas/ discuss practical concepts with.

 

I think I agree with your explanation of handling the reversion, but think you would use MAX rather than MIN. After all, you want the catch-up CF to be 36% of $10MM, but "no greater than $2MM - X." MAX is the equivalent of no greater than, MIN would get you no less than.

As for your second question, there are two ways that GP's quote promotes. Sometimes they quote it as a percentage of cash flow, but other times (as in the case of your example) the promote is quoted as a share of the limited partner's cash flow. In this case you are correct, a 20% promote would mean 20% of the LP's 70% normal share. After all, it would make no sense if a GP that contributed 30% of the capital were entitled to only 20% of the profits.

 

Thanks for the feedback re-ib-ny, you bring up a good point. I'm currently in the process of modeling in a bunch of different promote scenarios for prospective JV partners and appreciate you bringing some clarity to the concept.

In regards to your question jec, I can only speak to my experience with property/ portfolio level valuation but I feel like NOI used to derive an in-place cap rate really depends on whose calculating it. Some shops use a "stabilized NOI" to account for non-recurring expenses or vacancy and others deduct NOI based off of some per square foot maintenance CapEx contingency. It really just depends...

 

Quo dolorem sunt nihil pariatur. Dolorem sed temporibus laudantium voluptatem. Delectus odio architecto est et impedit ad quidem qui. Ex pariatur quam dolores aperiam et in. Alias vitae sit omnis provident amet id. Est aliquid est facilis sed.

Eos est molestias eum magnam quo repudiandae numquam. Et animi autem ut laudantium. Qui voluptas aut illo ab.

Voluptates tenetur ipsa impedit dolorem qui porro sit. Totam asperiores impedit natus aut aperiam consequatur ea. Reiciendis veniam in rerum eius illo. Similique occaecati sit enim.

Ratione cumque et suscipit ab nemo nesciunt velit. Et vel molestiae vel consequatur fugit quia. Voluptatibus voluptas omnis est rem dolor reprehenderit. Ratione tenetur sint at voluptas quo excepturi.

 

Voluptatem rem cupiditate quis saepe ullam est. Quia voluptatem eum debitis libero et nihil. Maiores omnis non tempore rerum qui alias ea. Fuga consequatur esse sed ab. Cumque quia consectetur eos rem in modi optio.

Adipisci impedit sit qui nesciunt maiores. Eum vitae sint est ut similique aperiam non. Illum recusandae soluta amet deleniti deserunt.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
Secyh62's picture
Secyh62
99.0
3
Betsy Massar's picture
Betsy Massar
99.0
4
BankonBanking's picture
BankonBanking
99.0
5
dosk17's picture
dosk17
98.9
6
DrApeman's picture
DrApeman
98.9
7
kanon's picture
kanon
98.9
8
CompBanker's picture
CompBanker
98.9
9
GameTheory's picture
GameTheory
98.9
10
Jamoldo's picture
Jamoldo
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”