REPE Modeling Test Example
Saw a lot of posts on RE Modeling Tests & Case Studies and thought I'd give a shot on how I structured mine for interviews (on-site/take home) with anywhere between 30 mins to 1 hr of time allotted. The model enclosed typically takes ~30 mins to build (incl. formatting) with an extra ~5-10 for sensi's. To note, this is the exact model I sent in for a case study for an REPE MF (passed).
Link to Model:
REPE Modeling Test Example
Link to Image of Model:
https://ibb.co/KVtmLRy
*Simplified a lot of it (bells and whistles as well as P&L) but flexible for the time allotted
**Keep in mind Case Study had certain assumptions/objectives (Ex. all expenses reimbursed incl. vacancy loss), one size does not fit all. I wanted to provide a basic structure
***Missing waterfall but will make future post with one (Fees, Preff & Promote with IRR/EM Hurdles)
Let me know what you think and feel free to PM me with any questions (Also, have other models/case studies I could share). Would be interested to see other people's approaches too.
Thanks!
This is pretty good,
Personal opinion is that keep the inputs on top rows instead of to the right of cash flows.
Thank you and appreciate the feedback. I agree, for actual models everything is above
following, and appreciate you sharing so openly
Thanks for this! Could you maybe share some of your other models/case studies?
Glad you guys like it, I'll try to add the case studies sometime this week to this post. Hope you all find the model helpful and feel free to reach out with any questions.
Very nice! Can you share the initial data you had starting off the model so I can practice and build it myself with another approach? I'll post what I get afterwards.
Thanks.
Thanks for sharing. Is there any reason why you're not using the IPMT formula to calculate interest? As simply multiplying the rate by outstanding balance increases the total debt service over time (arguably not by much but still)
Great question, it honestly was just a preference and got you in the same ballpark range which is pretty much the goal of these models.
In an actual model, I tend to make my debt dynamic (fixed (0) vs floating (1)) and combine a rate/spread in combination with 1-mo FWD Libor. I do the same, where I calculate interest on BOP Balance. For me, easier to model floating rate debt + other terms and run sensi's off of.
Hope that was helpful
How is the sensitivity table at celll AG53 calculated? Was trying to figure out the inputs by following the logic but it looks like a function was used.
Thanks in advnace!
Just used data table function for all sensis.
I noticed that you applied your acquisition fee to the Total Costs to lever against - said another way, the 70% LTV was the purchase price and the acquisition fee.
Does this logic apply when you have debt origination fees, IS broker fees, etc.? I know the money has to come from somewhere, so it makes sense to loop them in.
Broker fee (Purchase price ) Origination (total loan amount) Sales costs (gross sale proceeds)
.
Thank you for sharing.
Can you share the case study and the additional models that you have?
Thank you for sharing. i was doing a modeling ex. earlier & encountered some difficulties.. would be grateful if someone could give me some insights!
thank you so much
Thank you for sharing. Can you share the initial case inputs?
Can you also send the additional models which you have?
Apologies for the late response, made a different account. Due to circumstance, I am not allowed to share the case study prompt.
Was pretty basic, a NNN industrial deal with some rollover/growth assumptions. Gave financing, cap rate, etc.
Asked you calculate Unlevered & Levered IRR, ROE, EMx, and Sensitivities.
Could you please provide the model? The original link does not work. Would be very appreciative.
Link to model does not work. Would you mind sending? I could use some guidance for this!
I agree - link does not work for me. Would love to access a modeling test.
Here is a modeling test for Equity Residential REIT, they give 90 minutes to complete
ANALYST CANDIDATE EXCEL EVALUATION
An opportunity exists to acquire a hypothetical 478-unit multifamily property in suburban Maryland. The property was built in 1999 and is fully stabilized. Build a dynamic real estate acquisition, operation, disposition, and financing model to evaluate the opportunity. Please provide an annual cash flow summary formatted to print.
General Assumptions:
● Acquisition Date: 4/1/2015
● Purchase Price: $110,000,000
● Stabilized Occupancy: 95%
● Rental Rate: See T-12 Financials Provided
● Revenue Growth: 4% annually
● Operating Expenses: See T-12 Financials Provided
o Management Fee = 2.5% of Total Income
o Tax’s Reassessed at Purchase: $1,600,000 Year 1, 3% growth thereafter o Replacements: $350/unit per year
● Expense Growth: 2.5% annually
● CapEx - $600/unit per year
● Hold Period: 10 Years
● Exit Cap Rate: 6.25%
● Sales Costs: 2.75%
Senior Mortgage
● 65% Loan to Cost
● Origination Fee: 45 bps
● Interest Rate: 3.5%, Fixed
● Amortization: 30 Years
Please provide a summary that includes the following metrics:
● Unlevered IRR
● Unlevered IRR Sensitivity Table (analyst choice of most relevant variables)
● Unlevered Return on Cost
● Levered IRR
I'd like to practice off of these assumptions but I don't see the T12 financials and operating expenses that are referenced in the post. are you able to provide those? or whatever documents were provided to you by the company that administered this excel test? thanks.
Download link doesnt work for me either. Would you mind sending through?
Download link still works for this.
Quo repellat amet non corporis. Dolorem aut amet fugit non. Repellat quos necessitatibus at nisi illo id quod. Eos iusto ut dolor.
Expedita dicta ratione aut dolorem iusto similique perferendis. Recusandae aut quia provident sit. Et aliquid perferendis deleniti perspiciatis dolorem est. Aut distinctio error ab voluptatum eius illum. Voluptatibus qui ab quia sint. Corrupti facilis quidem harum.
Magnam est esse sunt blanditiis quia impedit non sed. Eius a a praesentium aspernatur. Eum doloremque nemo ratione quia ea. Ex quo autem suscipit nulla labore asperiores. Qui aliquid laborum aut culpa et molestiae et cumque.
Ut enim est qui. Molestiae sint eius nemo rerum qui fugiat. Cumque laudantium quo eos sed a rem.
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...
Dolorem laboriosam dolore sed consequatur non. Non dolorem dolore qui et et ut. Voluptas non facere et fuga non ut. Corporis cum soluta occaecati et.
Rerum reiciendis reiciendis et autem ex fuga. Minima omnis eos hic quae et maxime hic. Consectetur quae aliquam quaerat sunt iste et voluptatem. Et aspernatur aliquid molestias pariatur libero modi. Molestiae pariatur et porro doloremque omnis. Aut nemo ut qui aut accusamus ducimus aliquam.
Commodi et perspiciatis qui est nemo ut recusandae itaque. Modi quia aperiam magnam eos quia enim maiores ad. Et molestiae eveniet commodi. Minima nulla ut quo officia doloribus.