[Semi-Urgent] Case Study from Top RE Fund [Modelling Help]

EDIT 1: Thanks for all the replies below and fair comments. I should have mentioned I joined in global markets (illiquid sales for 4+ years) but looking to move to RE but experience on RE modelling is severely lacking. Got the interview after bare networking given that my background is not RE and can't screw this up. Barely having time to prepare and was looking for a shortcut - not sure where to start but will give it a go and get back to you with specific question. Cheers!


Can some provide me with the excel solution to the below. I have an interview coming up in 10 days.

Intro:
Contemplating the acquisition of a vacant, 3-unit, 800,000 rentable-square-foot industrial property for $40.0 million. The business plan entails leasing the property to three tenants and selling after five years. Mr RE plans to acquire the property alongside an Operating Partner who will not contribute any equity to the transaction, run day-today operations and receive a 20% incentive promote once the project has achieved a 9% cumulative IRR.

You have 60 minutes to prepare an annual, dynamic, and well formatted cash flow model that calculates unlevered and levered returns to Mr RE

Acquisition Assumptions:
• 100% purchased and owned by Mr RE
• 800,000 sf vacant industrial building
• Bought on January 1, 2019
• Gross purchase price of $40.0mm
• 1% closing costs upon acquisition

Financing Assumptions:
• Mortgage proceeds based on 65% loan-to-cost
• 30-year amortizing loan with a 4.0% fixed interest rate

Revenue Assumptions:
• No existing tenants in the building
• 10-year lease is signed in January 1, 2019 for 50% of the building @ $4.00 per SF per year
• 10-year lease is signed in January 1, 2020 for 25% of the building @ $4.50 per SF per year
• 10-year lease is signed in January 1, 2021 for 25% of the building @ $5.00 per SF per year
• No rent escalations

Operating Expense Assumptions:
• Annual non-recoverable operating expenses represent 3% of annual gross revenue

Capital Expenditure Assumptions:
• Tenant improvements totaling $3.00 per SF per at each lease inception (one-time expense)

Exit Assumptions:
• Sell in year 5
• 6.5% capitalization rate based on forward net operating income
• 1% sales costs upon exit

Waterfall Assumptions:
• 100% of cash flow goes to Metropolitan until the project achieves a 9% cumulative IRR
• 20% promote paid to GP thereafter

Comments (11)

 
Feb 29, 2020 - 9:48pm

I've found that a lot of groups do "speed tests" rather than super complicated models. Like they'd give the above but you'd have thirty minutes to do it. For example, Starwood's is longer than the above, less time, but about the same level of complexity

Array

  • 1
 
Most Helpful
Feb 29, 2020 - 10:58pm

To be honest this is about the most complicated a modeling tests should be. If you have been in the industry for 4 years, it should be a cakewalk or something that maybe needs 1 week to study for. As an example I know how to do development draws but it would take me 5-10 to make a dynamic draw, but 5 seconds of copy paste from a previous model I have built out.

And, I'm just curious. ... How hard is it to bring a flash drive into one of these modeling tests?

10 minutes to model the shell
10 min to model the cash flows/TI.etc
10 min to model amort tabale
20 min for the waterfall.

No real curve balls, I couldn't finish in 30 min. But I could give a good product in 1 hour.

I may just test myself on a slow day.

To help out, the test is set up to be modeled yearly. for cash flows you should have a row of cells referring to year 0,1,2,3,4,5.etc. below that is the date year 2019,2020,2021 etc. Have the revenue correspond (if greater than 2019 then 4 psf * pct square footage.

I'm not sure if you have any exact questions, but you should know how to do an amortzation table, cash flows and a shell.

Start Discussion

Popular Content See all

Student-run private equity funds?
+36IBby Prospective Monkey in Investment Banking - Mergers and Acquisitions">Prospect in IB-M&A
I wish I was an airline pilot
+21OFFby Vice President in Investment Banking - Mergers and Acquisitions">VP in IB-M&A
BMO fired all of its Houston Bankers (~60 people)
+20IBby Intern in Investment Banking - Mergers and Acquisitions">Intern in IB-M&A
Zoom events are cringe as hell
+17OFFby Prospective Monkey in Investment Banking - Mergers and Acquisitions">Prospect in IB-M&A
Do I quit IB for YouTube
+14IBby 3rd+ Year Analyst in Investment Banking - Industry/Coverage">Analyst 3+ in IB - Ind

Total Avg Compensation

December 2020 Investment Banking

  • Director/MD (18) $713
  • Vice President (53) $337
  • Associates (275) $227
  • 3rd+ Year Analyst (39) $161
  • 2nd Year Analyst (153) $153
  • Intern/Summer Associate (141) $139
  • 1st Year Analyst (595) $130
  • Intern/Summer Analyst (568) $82

Leaderboard See all

1
LonLonMilk's picture
LonLonMilk
98.4
2
Jamoldo's picture
Jamoldo
98.3
3
Secyh62's picture
Secyh62
98.3
4
CompBanker's picture
CompBanker
97.9
5
redever's picture
redever
97.8
6
Addinator's picture
Addinator
97.6
7
frgna's picture
frgna
97.5
8
NuckFuts's picture
NuckFuts
97.5
9
bolo up's picture
bolo up
97.5
10
Edifice's picture
Edifice
97.5