How To Ace a Real Estate Financial Modeling Excel Exam

The more institutionalized the real estate industry becomes, the more is expected from people entering the business today. And this is especially true at the analyst and associate levels.

To make sure these companies are getting what they’re paying for, more and more firms are including Excel financial modeling assessments during the interview process itself, to test a candidate’s Excel knowledge specifically applied to real estate, and to verify what a candidate says they can do on their resume.

If you’re applying for analyst or associate roles in the real estate industry, this is a really important step in the process, especially since this is often one of the final steps before receiving an offer. Getting dinged here would mean a lot of time and effort wasted during the many other steps of the interview process.

Not good.

If you’re trying to break into commercial real estate for the first time, or trying to land a new role at a top-tier real estate investment or brokerage firm, there are a few key things that you can do to set you apart from your competition during an Excel exam.

And in this article, we’ll break down exactly what those things are, and how you can use them to feel confident heading into your own exam, and what you can do to make sure you ace whatever is thrown your way on interview day.

If video is more your thing, you can watch the video version here:

What Do Real Estate Financial Modeling Excel Exams Include?

The real estate hiring process tends to be a pretty long road, with multiple rounds of phone screenings, in-person interviews, and meetings with people you may not even end up working with on a day-to-day basis.

And at the end of this process, to make sure your skill set is everything that was advertised, companies will often throw in some variation of a timed Excel exam.

In general, most real estate Excel interview assessments involve some variation of a sample case study scenario, and the requirement to build out pro forma cash flows based on some key assumptions given.

And in almost all cases, these assumptions are going to fall into four main categories:

Purchase Assumptions

These will include your purchase price, closing costs, and potentially the exact date that the property is acquired.

Operating Assumptions

These will include your operating revenue, operating expenses, and potentially some construction cost or re-leasing assumptions.

Sale Assumptions

These will include your exit cap rate, costs of sale, and your hold period.

Financing Assumptions

These will include your loan proceeds, interest rate, amortization period, and your loan payoff date.

Build a Real Estate Pro Forma Model and Answer Questions

From that information, most exams will ask you to do two things.

The first is to create a pro forma cash flow statement to project out cash flows into the future.

And the second is to answer questions regarding return metrics or risk metrics based on those pro forma cash flows that you’ve created.

There are a lot of different ways to build a model, but there are a few “must haves” in these exams that many people miss when they’re working through these assessments.

Getting these things right can make a huge difference in how you present yourself to a potential employer, and whether they decide to go with you or someone else in the running for the role.

Make Your Real Estate Pro Forma Dynamic

First, and probably most importantly when working through a real estate financial modeling interview exam, you’ll want to make sure your model is as dynamic as possible, even if it’s not requested in the exam for you to do so.

To the extent you’re able to do this is going to be dependent on how much time you have to complete the exam.

However, at the minimum, you should be able to change your purchase price, your rents and expenses, your loan amount, your interest rate, your amortization period, and your exit cap rate, and your cash flows should change automatically in your model to reflect those assumption adjustments.

Even if the exam prompt doesn’t specifically ask for this, the expectation of you as the analyst or associate at a real estate firm is going to be that you can create models that can be used more than once. This means building models that can be used to analyze various different scenarios or to run stress tests on different operating metrics that might come up on a deal, without having to make massive customizations on every new transaction.

By making your model dynamic, you’re essentially showing your potential employer that you can make their lives easier with a model they can use to change one or two assumptions, and quickly see the changes to cash flows and return metrics as a result of those changes.

Model Cash Flows on a Monthly Basis (Unless Directed Otherwise)

Creating a dynamic model is important, but within the model that you build, you’ll also want to make sure that your cash flow timing is realistic and accurately reflects a sample scenario you might see on a live deal.

And with that, the next point on this list is to make sure that your cash flow model shows cash flows on a monthly basis (unless directed otherwise), ideally with the specific dates associated with each of those cash flows.

Cash generally flows in and out of a real estate asset every 30 days, whether that’s through collecting monthly rent payments, paying out employees and vendors as operating expense payments, or paying a lender monthly loan payments.

In most cases, this is what should be reflected in a modeling exam, as well.

This is especially important for calculating time-weighted return metrics, specifically the IRR and NPV, since monthly cash flows will produce different values than annual cash flows for these metrics and could skew your answers as a result.

Because both of these calculations factor in the time value of money, the IRR and NPV functions weigh cash flows received earlier in the analysis more heavily than those same cash flows received later in the analysis.

This means that if a property generates $10,000 in net levered cash flow per month, and $120,000 in net levered cash flow per year, showing twelve $10,000 cash flows each month is going to produce a slightly higher IRR and NPV value than showing one $120,000 cash flow at the end of the year.

As part of this, and to make sure your return metric calculations include this time-weighted analysis, you’ll also want to make sure you have specific dates associated with each of the cash flows you calculate.

These date values are going to be required to use the XIRR and XNPV functions, and even if a sample acquisition date or analysis start date isn’t given to you, using an acquisition date that’s 1-3 months in the future and then creating one cash flow in the last day of each month from there is a very reasonable assumption to make for exam purposes.

Make Your Real Estate Pro Forma Clean & Clear

Once your model is dynamic and you’ve created your monthly cash flows, you’ll also want to make sure that your model is clean and clear from a formatting perspective and your assumptions and thought processes are clearly explained.

If you put together sloppy cash flow projections without borders or shading, or it’s unclear where your assumptions end and where your cash flows start, this just shows your potential employer that the work product you put out might be sloppy or hard to understand, and you lack attention to detail.

And showing employers you’re lacking attention to detail is a huge blow to your chances of landing the job, because this is one of the biggest things that employers are looking for in real estate analyst and associate candidates.

Before you head into an exam, take the time to learn Excel formatting shortcuts to clean up your model quickly, and pick a consistent font style, shading color, and currency format to use across the board.

Also, make sure that all of your manual inputs in your model are in blue text, and all of your formulas are in black text, as this is the industry standard across the board in real estate financial modeling and should be used in any Excel document that you create.

And aside from just formatting, in an additional effort to make the model clear, you should also be making comments on your assumptions (when necessary) to help explain your thought process or why you did certain things that you did.

Often times you’ll find that the questions or prompts on these exams are kind of ambiguous, or leave you with incomplete information where you’ll have to make assumptions to answer the questions given to you.

And in those cases, you want to be as clear as possible as to what you assumed and why you assumed those things, just in case your interpretation of the question wasn’t the same as what the interviewer intended the interpretation to be.

Thought process matters a lot in these exams, and the more clarity you can give around why you took certain actions or made certain assumptions, the better you’re going to show. Again, the goal here is to demonstrate that your work product is clean, clear, and makes the hiring manager’s job easier at the end of the day.

Take The Pressure Off

Finally, as intimidating as these exams can be, keep in mind that you almost never have to get everything correct in order to land the job.

At the end of the day, these exams are created for companies to see you in action doing work that would represent things you might do on the job if you were hired, and the goal here is to see how you present your work product and how you think about problems presented to you.

With that, if you can create a dynamic, clean, clear monthly cash flow model, explaining your assumptions when necessary and showing your understanding of basic real estate metrics like the cap rate, IRR, equity multiple, and cash-on-cash return, you’re likely going to be in very good shape for interview day.

How To Prepare For a Real Estate Financial Modeling Excel Exam

If you’re preparing for an Excel exam during the interview process, or you just want to make sure you’re ready when the time comes, I hope this was helpful in giving you a framework to tackle whatever comes your way.

And if you’re looking for more interview exam practice, sample case studies, and guided Excel training to help you prepare for an upcoming assessment or the interview process in general, make sure to check out Break Into CRE Academy, which will give you instant access to sample case studies and Excel practice files, step-by-step walkthroughs of Excel interview exams and solutions, and one-on-one email-based career support to get your questions answered as you’re preparing for the big day.

Good luck!