5/4/12

Looking for some help - I'm a management consultant and CFA charterholder trying to prepare for a case study with a Real Estate Private Equity firm. I'm solid on financial modeling in general but looking for specifics to RE and ideally a template that I can review in preparation... would appreciate any thoughts

Comments (26)

5/4/12

Search GOOG for "real estate operating statement".

Also, I recommend downloading the trial version of Argus, enter some basic assumptions, export the CF to excel, then play with it. Figure out cap rate, cash on cash, IRR, etc.

Any other specific questions?

Man made money, money never made the man

Financial Modeling

5/4/12

there's been some great real estate discussions on the forum here recently:
http://www.wallstreetoasis.com/forums/real-estate-...
http://www.wallstreetoasis.com/forums/real-estate-qa

Please keep us updated on your search and your position. Most people come ask for help but never come back to update the rest of us!

5/4/12

if you have time, pick up a copy of peter linneman's book: real estate finance & investments. it's THE must read real estate book (don't worry it's an easy read).

thank me later.

5/4/12

In the first thread referenced by huethan I tried to outline some of the general components, themes, and questions I've observed in the real estate private equity modeling tests I've seen. As with any model, you need to get to unlevered cash flow (which is very easy with a property model, since you don't have to deal with a balance sheet and accruals). Where real estate modeling tests differ, and add some layer of complexity, is generally in how the debt is sized and then how levered cash flow is distributed to JV partners via a waterfall.

Feel free to follow up with more specific questions and I will try to provide answers.

5/4/12

RE Capital Markets mentions Argus and I want to comment on that. For those unaware, Argus is a tool used universally throughout the real estate world to model property-level cash flows. It's particularly useful for multi-tenant office and retail properties because you can plug in all of the existing lease terms into Argus, enter market leasing assumptions, and Argus will spit out cash flows based on those inputs. Real estate is not like other businesses, where you can just project growth rates in sales volume and unit prices to forecast revenues.

Despite the ubiquity of Argus, however, I have found that few RE PE funds include it in their modeling tests. I think this is because we tend to hire i-banking analysts, and the majority of i-banking analysts do not know how to use Argus (I didn't). It's more important, anyway, that a candidate is proficient in Excel. Argus is pretty easy to learn, though I've found it's pretty important to have someone walk you through it once. The help features and documentation are poor, so learning it on your own without a template is probably not worth the effort.

6/7/12
re-ib-ny:

RE Capital Markets mentions Argus and I want to comment on that. For those unaware, Argus is a tool used universally throughout the real estate world to model property-level cash flows. It's particularly useful for multi-tenant office and retail properties because you can plug in all of the existing lease terms into Argus, enter market leasing assumptions, and Argus will spit out cash flows based on those inputs. Real estate is not like other businesses, where you can just project growth rates in sales volume and unit prices to forecast revenues.

Despite the ubiquity of Argus, however, I have found that few RE PE funds include it in their modeling tests. I think this is because we tend to hire i-banking analysts, and the majority of i-banking analysts do not know how to use Argus (I didn't). It's more important, anyway, that a candidate is proficient in Excel. Argus is pretty easy to learn, though I've found it's pretty important to have someone walk you through it once. The help features and documentation are poor, so learning it on your own without a template is probably not worth the effort.

Do you think Argus is at all useful for analyzing distressed real estate investments?

Too late for second-guessing Too late to go back to sleep.

5/4/12

Thanks for all the advice.

@ RE Capital Markets - I downloaded the free trial version of Argus and will dive into it this weekend.

@ fez - looks like a great read but i just don't think i'll have the time for it, and it doesn't appear to be the best use of my time in terms of prepping for this case study.

@re-ib-ny - Can you give any additional detail around the levered cash flow distributions to JV partners? I'm not sure what a JV partner is. If you happen to have an excel template or example that you could share, that would be a huge help. Also - appreciate your thoughts on Argus

Financial Modeling

Best Response
5/4/12

Sure. Most real estate private equity funds buy real estate through joint ventures where the fund serves as the "capital partner" and some local asset manager / developer serves as the "operating partner." Levered cash flow to the venture gets split between the two, but not necessarily proportionate to their contribution of equity. The mechanism by which the cash flow gets split up is referred to as the waterfall.

One common waterfall structure might have each partner getting their proportionate interest in the cash flow until they've received a return of capital and a preferred return (pref) of, say, 10%. Thereafter, the operating partner might get a larger share than he would otherwise be entitled to (for instance, the operating partner contributed 1% of the equity, but gets 10% of the cash flow above a 10% pref). Sometimes the waterfall is multi-tiered, where the share going to the operating partner gets higher and higher as incremental IRR hurdles are achieved (1% of cash flow until a 10% IRR, 10% to a 15% IRR, 20% thereafter, etc.).

Another common structure is called a "catch-up." This is a bit trickier to model. In reality, this is not typically used by funds to pay their operating partners, but instead the structure under which fund managers get paid by their investors. For some reason, though, I've seen this on a number of modeling tests. In this example, the general/operating partner might contribute 1% of the capital and the limited/capital partner contributes 99%. The profit gets split 1/99 until the LP has received all his money back and, say, a 10% preferred return. Then, the profit might get split 50/50 until the GP has received 20% of the total profit (this is the catchup), after that the GP gets 20%.

I would share a sample, but I don't know how to upload or share files anonymously.

5/4/12

Thanks re-ib-ny, that's very helpful. I actually have a lot of experience dealing with the intricacies of management and incentive fees, so the catch up and waterfalls I'm feeling pretty good about, should I see them. Your mention of operating partners is helpful - my impression from hearing the term originally was that they are more focused on property management and less in bed with the equity, but it sounds from your description like they are commonly involved the equity as well. I'll send you a private message with my email address if you don't mind passing along a sample model.

5/4/12

I don't feel comfortable emailing at this point, but I was able to find a rather decent illustration of a waterfall on the internet. Here is the link. Hope this is helpful.

http://isites.harvard.edu/fs/docs/icb.topic940228.... Structure_Updated 11_2_11_Original_5_19_09.xls

5/8/12

re-ib-ny - thanks again for your comments above and for sharing the link to a waterfall. I found it very straightforward. My only question now is what else can I do to prepare for this case study? My thinking is to spend some time reviewing past financial modeling case studies i've seen (non Real Estate), in which i've had to construct projected IS, BS, and CF based on provided assumptions and sensitize for IRR. Would appreciate any thoughts on whether this is the best use of my time.

5/9/12

Spalding, have you already made it through the interviewing component of this recruiting process, such that at this point your entire focus is on the case study?

If so, one thing I would recommend is that you go to EDGAR and pull a few 10-Ks for public REITs. It sounds like you have a decent financial modeling background, but may not have much familiarity with real estate terminology and financials. While a REIT annual report won't give you any modeling skills, it will give you some sense for key terms, etc.

I would suggest you look at a lodging REIT (try Pebblebrook, PEB) and an office REIT (try Hudson Pacific, HPP). I've tried to think of younger REITs that I don't think have too many complex securities, joint ventures, or confusing structures on their books.

Here is a link to the website for the Brueggeman textbook: http://highered.mcgraw-hill.com/sites/0073377333/s.... There's an Excel workbook you can download that illustrates a few modeling examples. Frankly, these examples overcomplicate the reality of what you will be asked to create in a real estate case study, but it might be worth a read.

5/9/12

re ib ny - Thanks for checking in. I submitted the case study yesterday afternoon and waiting to hear back now. I started the other thread that you just posted on, with the RE Valuation question, regarding the major snafoo i ran into with the case study. In general i think my response was pretty solid, but would like to get a better understanding of how to consider depreciation in a valuation (in an event where it does matter to an owner)

5/10/12

There is only one reason why "accounting" depreciation would matter, and that is for taxes. Depreciation creates passive losses that a limited partner can apply against passive gains to reduce tax base. In private equity, for whatever reason I have never seen anyone in acquisitions consider this; I suppose it's because our investors are often tax-exempt entities and in any case allocate money to private equity for the cash IRR and multiple, not for tax protection.

If you do have to consider capital gain and income taxes, then things get pretty complicated. Take Levered Cash Flow and add back any principal amortization from the mortgage and any capital expenditures (which you capitalize rather than expense), then subtract depreciation (you usually straight-line the value of IMPROVEMENTS, not land, over 39 years for most commercial property, and 27.5 years for apartments). You can also deduct amortization of upfront mortgage points over the life of your loan. The result of this calc is your taxable income. Apply the appropriate investment tax rate and subtract the tax from your Levered Cash Flow to get Tax-Effected Cash Flow.

On exit, you have to figure out the net book value of the investment (purchase price plus all capex, minus depreciation) and subtract that from your net sale price to calculate your "gain." For a typical taxable investor, I believe the IRS will charge 15% on most capital gains, but a higher rate (25%?) on what they call "depreciation recapture," which is any gain that offsets previously claimed depreciation. So if you claimed $20MM of depreciation over the life of the investment, and book a $25MM gain, I believe the IRS will tax you at 25% on $20MM and then 15% on the remaining $5MM. It should be obvious, but I'll note it anyway, that I am not a tax expert and am providing modeling advice, not tax advice.

That is the long story of how depreciation matters. Again, it is purely an accounting concept that impacts your taxable income and treatment of gains. Absent taxes, the only depreciation that matters is "economic depreciation," or the aging of your building, and that is something you address in the model through a capital improvement and/or capital reserve budget.

6/7/12

It is useful for all real estate investments. However you really need to understand the specific ins/out when in distressed RE, so I would say much less so.

For example, if you want to model a 2015 lease as paying for the next six months, and going to CAM only through expiration, while selling a pad site in year 2, collect rent until then, and collecting CAM afterwards, Argus is going to have a tough time with that.

Distressed means being realistic and many times creating your own base-case aside from a 75% renewal probability.

6/8/12

To add to SK's comments, there's not much that really differentiates distressed real estate from non-distressed real estate, at least when it comes to what it is and how it makes money. Argus, as I mentioned before, is a useful tool for RE practitioners because it facilitates the projection of cash flows when the impact of many leases needs to be accounted for. This is true for any real estate.

Much of the complexity involved in acquiring distressed real estate is in the structuring of the acquisition itself, understanding the process and the risks related to foreclosure, bankruptcy, and loan workouts. Often key is figuring out what interests need to be acquired/assembled to take control of a property. On the operations side, distressed real estate is only different (and then, only sometimes) in that there's often more vacancy and a need to put additional capital into a building, which is part of the opportunity. But the way you approach this isn't materially different from any other real estate asset.

6/13/12

Re-ib.....what kind of sensitivity tables are you using when you model out you acquisitions?

6/13/12

Depends on the deal and what we think the key drivers of value are. Common sensitivities include purchase price vs. exit price (if purchase price is negotiable), exit timing vs. exit price, and leverage vs. exit price. You'll look at sensitivities in terms of how they impact IRRs and multiples on an unlevered and levered basis. In some cases you may also sensitize market rents and lease-up timing, or RevPAR and margins (if you're looking at a hotel).

6/13/12

anyone?

6/13/12

Hope the interviews went well. Would you be kind enough to share any of those materials via PM? Would be much appreciated- happy to reciprocate if you still require anything.

6/13/12

Would be very interested to hear what you find out--in similar position. PM me if possible--much appreciated.

Good Luck!

6/13/12

Would also like to know if possible. Thanks!

6/13/12
Add a Comment
WallStreet Prep Master Financial Modeling