Remember not to overthink it during the test. If you've done some MF modeling, in my experience, these tests are always easier than you think they would be. Follow the directions and don't go overboard.

Commercial Real Estate Developer
 

I guess a better question is what excel funtions should I prepare myself for. Obviously any model I have been working out of already has these equations in so theyre automatically calculated (ex. cap rate sensitivity table analysis, PMT). Things that actually take some financial knowledge, not like a vlookup where it doesnt involve anthing financial if that makes sense.

So essentially what functions do you think i should be prepared for in terms of the excel test?

 

Most MF models do not require a ton of financial excel functions. A lot of the work is making sure cells tie correctly and all of your formulas are correct. Entails a lot of general functions of excel (i.e. vlookup, sumif, if, ifs). Only strictly financial functions you would need are PMT, NPV, IRR etc. but those are only used in a handful of cells, whereas the general formulas apply nearly everywhere. Also is this timed? If so, you should be very comfortable with shortcuts. I think you're overthinking it. Do you have previous MF modeling experience?

 
Kedz:
Entails a lot of general functions of excel (i.e. vlookup, sumif, if, ifs).

In my experience there's a 50/50 chance you won't even have to use that on the test. It's more "Make sure you subtract expenses from income to get the NOI."

Commercial Real Estate Developer
 
Most Helpful

Took a test recently. The gave me the rent roll and all the various trending assumptions. For the debt they gave the rate, amortization, and DSCR limit on the debt and asked me to size the loan. Make sure you set up your inputs nicely and make the pro forma dynamic. When we test candidates I hate it when they burry the assumptions as hard coded values.

You also need to be comfortable with calculating the remaining principal on the loan for exit analysis. Every test I have taken only ask for one holding period scenario, but bonus points if you know how to make it dynamic.

They then gave a discount rate and asked me to come up with the value of the property. (Debt plus sum of PV of CFADS)

They then gave me another bid price and asked what the IRR would be on that. A good way to check these two answers is your IRR should equal the discount rate if you use the value you came up with as the purchase price in that calculation.

Good luck.

 

I've taken a couple of these and also seen a few others. Somewhere on this site is a prudential excel test floating around.

What they more often are testing, especially for acq, is your logic in evaluating a property. Thus you will often give you some type of old Broker tear sheet with property info, maybe an axiometrics demographics report, and an income statement.

oftentimes the question is as simple as "what is this property worth, would you buy it and why?".

Some other tests will get more specific with questions...i.e. they will give you an income statement, growth rate...and then ask you what the yield on cost is in year #.

One thing that they rarely ask about, which always surprised me, was debt metrics and info. If you think about it, most assets are leveraged at 65%+. So to have 65% of your asset tied up in a loan, I want my analyst to know every corner of the loan docs and how to model the term sheet. i.e. explain the meaning of certain covenants...simply how to build an amortization table for a loan with an IO period - you'd be surprised how many don't know how to do this.

just my two cents though.

 

If you can complete this test (https://imgur.com/a/W1HFd) comfortably in 2 hours, I think you'll be well set-up. I doubt they'll be looking for knowledge of any particular finance functions, but more so can you model Multifamily at a high level. Given this test includes lease-up, debt sizing, and a JV structure I can't see what else they may thrown in other than something like an extra credit cash sweep till you hit a certain threshold or similar.

 

Here is the 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

 

Qui nihil ut beatae eius doloribus. Libero nobis porro alias quo. Dicta cupiditate cum quia qui. Sit tenetur et exercitationem explicabo consequuntur rerum sed. Aliquid at laborum et velit atque qui cum.

Et quaerat autem non aut numquam quo nulla. Non omnis sunt perspiciatis quia numquam recusandae nulla praesentium. Molestiae et sapiente non.

Et fuga temporibus eaque repellat. Nisi voluptates itaque quia dignissimos et ad. Rerum animi ipsa cum et consequatur. Dicta voluptatem soluta perferendis rerum.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
Secyh62's picture
Secyh62
99.0
3
Betsy Massar's picture
Betsy Massar
99.0
4
BankonBanking's picture
BankonBanking
99.0
5
dosk17's picture
dosk17
98.9
6
DrApeman's picture
DrApeman
98.9
7
kanon's picture
kanon
98.9
8
CompBanker's picture
CompBanker
98.9
9
GameTheory's picture
GameTheory
98.9
10
Jamoldo's picture
Jamoldo
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”