[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
TBH this isn't too difficult to build, especially for a top RE fund. Any specific questions on how to approach?
This is pretty straightforward and you’re not even going to attempt it before asking someone to do it for you? Real Estate job market must be really tough for employers right now
As someone that is starting to reach out for prospective jobs, is this the typical modeling tests at top REPE firms? I'd feel a lot better if that were the case.
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
That makes me feel more confident. Hoping to land a spot coming from EU to US at a REPE firm and I do this everyday, so hopefully I get a question like this.
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.
The above model is not for the interview I am having. This test was for a different asset manager which i found online and it seemed relevant
I mean it's a great test. Tests for concepts in the simplest way.
Feel bad for whoever is considering hiring this guy...pt 2
What’s with the random IB/PE anonymous user names providing 0 value to the discussion
Rerum omnis totam amet voluptatem consequatur sed. Cupiditate est aut vel dolores veniam. Pariatur quo error ipsum sit velit velit commodi. Iste debitis tenetur est. Molestiae nobis tempora perspiciatis eveniet delectus ut. Sint officia sapiente voluptatum possimus.
Veniam laboriosam ut dolore. Amet temporibus maxime et explicabo dicta. Tenetur temporibus veniam dolorem dolorem et nam tempora.
Esse tenetur voluptas error quia ut vel. Quia quam unde quas occaecati dicta quo est consectetur. Quas officiis aperiam possimus doloribus fuga mollitia.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...