Real Estate Financial Modeling Interview Exam Questions – What You Need To Know
When applying for a real estate analyst or associate job at a private equity real estate or brokerage shop, just writing “proficient in Microsoft Excel” on your resume usually won’t cut it these days.
And during the interview process, to test your knowledge in real-time, many commercial real estate firms will give potential analyst hires some sort of real estate financial modeling interview exam, where the candidate will have to demonstrate their knowledge of Excel, and their ability to calculate real estate investment returns and metrics for various scenarios.
The exact format of these exams can vary quite a bit from company to company, but even when the format changes, the core fundamental knowledge-base most that most firms are looking for tends to be pretty consistent across the board.
So in this article, to help you prepare for interview day and ace whatever exam is thrown your way, let’s walk through five of the most commonly tested topics included real estate financial modeling interview exams, and the specific terminology and calculations you should know within each.
If video is more your thing, you can also watch the video version of this article here.
1. Basic Excel Functions
In a real estate analyst or associate role, the vast majority of your workday (at most firms) is going to be spent in Microsoft Excel.
And with that, a big part of being successful in these types of roles is coming into the position with a solid understanding of the core functions and formulas used by CRE analysts on a regular basis.
Here are some of the most commonly used functions that I’ve seen included in real estate financial modeling interview exams, in no particular order:
HLOOKUP & VLOOKUP
These functions allow for dynamic retrieval of values within a table array based on a specific column or row reference value and a column or row reference number.
The IF function allows a user in Excel to create binary “If-then” scenarios, where a user can dynamically test if the value in a certain cell is less than, equal to, or greater than another value, and return a certain value as a result of whether or not the result of that test is true or false.
PV & FV
The present value (PV) and future value (FV) calculations measure the time value of money. Specifically, these functions measure the impact of a given discount rate or interest rate on an expected value in the future, or a current value today.
IRR & NPV
The internal rate of return (IRR) and net present value (NPV) functions allow a user to easily calculate the time-weighted, annualized returns on a series of cash flows, and these two functions are used extensively on a day-to-day basis at the vast majority of real estate private equity and brokerage firms.
Data tables aren’t technically Excel functions, but they are a functionality in Excel that allows users to sensitize the effects of various manual input drivers on outputs in a model. In a real estate context, this allows investors to see the effects of things like differing purchase prices, exit values, rent growth figures, and debt levels on the key return metrics of the deal.
2. Real Estate Finance Modeling
Obviously Excel is important, but just knowing Excel itself won’t put someone at the top of the candidate stack when applying for real estate analyst or associate positions.
In addition to Excel knowledge, real estate investment, brokerage, and lending firms are also going to be looking for candidates with a strong fundamental knowledge of real estate finance, specifically, including the following topics:
Employers are going to be looking for a strong working knowledge of how to calculate things like monthly debt service given a loan amount, interest rate, and amortization period, the loan-to-value (LTV) and loan-to-cost (LTC) ratios of a deal, and maximum loan proceeds based on a minimum required debt service coverage ratio (DSCR) and/or debt yield on the asset.
Employers are going to be looking for a strong working knowledge of how to calculate key return metrics on a deal, including the cap rate, internal rate of return (IRR), equity multiple, and cash-on-cash return.
Aside from the key finance concepts used to analyze commercial real estate deals, employers are also going to be looking for candidates that understand how to model various growth rates into a model to adjust things like rent growth, expense growth, property tax growth, or capital expense growth over time.
3. Commercial Real Estate Lease Modeling
This is where things start to come together, and how an employer can make sure that your Excel skill sets and real estate finance knowledge play nicely together. These skill sets include:
Modeling Weighted Average Renewal Cash Flows
For modeling purposes, when an in-place lease matures, commercial real estate firms need to make assumptions around what those cash flows will be for that particular suite, even after that lease expires. This requires the ability to model weighted average values for things like downtime between leases, tenant improvement (TI) allowances, leasing commissions, and market rents, all based on the probability that the in-place tenant renews its lease (or vacates).
Modeling Operating Expense Reimbursements
For firms that work on commercial real estate deals (really anything other than multifamily or hospitality), the ability to be able to quickly identify and model common operating expense reimbursement calculations is critical for CRE analysts. Before heading into an Excel interview exam, make sure you’re clear on the definition of a triple-net (NNN) lease, full service gross (FSG) lease, modified gross (MG) lease, and base year stop (BYS) lease, and how revenue will be impacted by each structure.
Modeling Tenant Improvements and Leasing Commissions
Similar to the point above, this is generally only going to be important for firms that invest in office, industrial, and/or retail properties. With that said, being able to calculate a tenant improvement (TI) allowance based on a market amount per square foot, as well as leasing commissions as percentage of total contractual base rent over the lease term, will be extremely helpful in preparing for a real estate financial modeling interview exam at most CRE private equity and brokerage firms.
4. Commercial Real Estate Cash Flow Modeling
Once an employer knows that you understand Excel, have a strong foundation in real estate finance, and understand how commercial leases work in practice, from there, most firms will want to see you put all of that knowledge together to create a complete, dynamic pro forma model to analyze a deal based on given assumptions.
For the purposes of an interview exam or case study, this usually consists of three steps:
Build a Complete Pro Forma Model Based on Given Assumptions
Step number one of this process is going to be to build out a dynamic pro forma cash flow model based on assumptions given to you around things like market or in-place rent values, rent and expense growth, operating expense ratios, vacancy rates, loan metrics, and an exit cap rate.
Ideally, this model should be dynamic, meaning that if you change any manual input assumptions in the model itself, the rest of the cash flow calculations will change automatically. This is big in showcasing your abilities in Excel, and your ability to quickly model various deal scenarios for senior members of the firm to evaluate.
Calculate The Key Return Metrics on the Deal
This step includes taking the pro forma cash flows you’ve created based on the assumptions you’ve been given, and then calculating the return metrics we talked about earlier in this article.
The most heavily scrutinized metrics are generally going to be the cap rate, IRR, equity multiple, and cash-on-cash return, but you will likely also be asked to calculate debt metrics like the DSCR, debt yield, LTV ratio, or loan constant, as well.
Value The Property Based on Target Return Metrics
Many real estate financial modeling interview exam case studies will require the candidate to take everything they’ve built to come up with a valuation on the property based on a target IRR, equity multiple, cash-on-cash return, or mixture of all three.
And this is very similar to what an analyst would do on a day-to-day basis on the job, coming up with offer prices on new deals in an acquisitions role, or creating broker opinion of value (BOV) documents in an investment sales analyst position.
5. Working With ARGUS
Again, this is usually only applicable for retail, office, and/or industrial-focused firms, but if commercial leases exist in the company’s portfolio, it’s likely that they use a software called ARGUS in some shape or form.
And while an interview exam doesn’t generally require you to work within ARGUS directly, many exams will require you to build a real estate financial model based on another Excel file that has been exported from ARGUS, directly.
In these scenarios, you’ll often be expected to solve for similar metrics as mentioned in point number four on this list, with the IRR, equity multiple, and cash-on-cash generally being the most widely tested.
However, this portion of the exam may have some additional “cash flow interpretation” questions based on the ARGUS cash flows provided, including when the property achieves stabilization, what types of operating expense reimbursement structures most tenants have, and what an exit cap rate would need to be to hit a desired sale price.
What If I Don’t Know All of These Things?
Ok, so it might seem like I just gave you a lot of homework.
And if you aren’t familiar with most of these topics, then that is true – I would recommend taking the time to learn these concepts, even if it might take some time to get the hang of things.
I would rather give you homework now than have you unprepared for exam day, going through 3-5 rounds of the interview process only to make (very) avoidable mistakes on an Excel assessment, and potentially losing the position.
Ain’t nobody got time for that.
So, if you want more help with preparing for a real estate financial modeling interview exam than I can give you in a short blog post, here are two great options to get you started.
The Real Estate Financial Modeling Interview Exam Guide Course
This class will walk you through each of the five topics discussed in this article in detail, with dozens of practice interview exam questions and walkthroughs of the solutions to each. You can find all the details by heading over to our Courses page here.
Break Into CRE Academy
A membership to the Academy will give you instant access to all Break Into CRE courses, our entire library of practice interview exams and case studies, and additional, one-on-one, email-based career coaching to help you navigate the application and job search process and land your dream job in the real estate industry.
If you’re about to head into a real estate financial modeling interview exam soon, I hope this helps. Good luck!