Modeling question (from banks perspective)

Were you given any more information on format? How much time are they allocating for the test? What is the group's lending focus? What level is this job? A "modeling test" could cover anything from a basic request of just producing a cash flow/sizing a loan (Excel or ARGUS) to writing up a full investment memo on the deal.

Hey,
thanks for your reply. To answer your questions, the test is scheduled for 60min, so I expect (hopefully) nothing exotic or too in-depth!?
The job is an analyst role, although somewhere 2nd/3rd year.
They do not have a specific focus - could be anywhere b/w residential portfolio, office, hotel, shopping etc.
The test will be Excel based.

Thanks again for your input. Any feedback on what to expect is greatly appreciated.

My guess, based on similar experiences, is that it will depend heavily on what bank you are interviewing at. Are they in the construction lending business? If so it may be useful to understand how to takeout construction debt with a more permanent product. Are they generally just throwing a big chunk of perm debt on a deal? It would be pretty straightforward if you know how you would model that out.

Having worked at a real estate lending group at a major bank, at the end of the day their main concern is that they have covered all the potential risks associated with the investment and they are hitting their DSCR benchmark. They will typically look at a deal in a much more conservative manor.

I'd guess they will give you a proforma, some additional documentation, and a copy of their model to see what you can get done in 60 minutes. Most major banks (in my experience) have established models targeted for particular transactions and asset classes. The exception to this would be creating a new deal in Argus, which should be pretty straight forward. We never asked anyone to build a model from scratch as part of our interview process FWIW.

Hi picklemonkey,

thanks a lot for your reply. I am not quite sure if this bank is also into construction lending. I guess that this is at least not their core. I am a bit unsure whether I should just make a proforma with revenues, opex, NOI, capex, interest/amortization and calculate some ratios (DSCR, DY etc), or if I should build an income statement + cash flow statement.

I would go proforma + ratios. IS + CF is more what an acquisitions person would do vs. an originator.

I'm going to agree with you on this.

If you can tackle the BIWS models, you can definitely handle modeling from a lender's perspective. Should be simple by comparison.

Thanks all for your input. That definitely helps. :) Just on a sidenote and because at my current gig we do not do it, but how are you usually sensitize? I mean which variables? For the buyside it seems obvious for me - IRR as the result in dependence of e.g. LTV & cap rate. Any idea if there are some "standards" for the lending side? LTV and cap rate still sounds good, but what should they trigger?

Super quick sensitivity analysis - NOI and cap rate. That gives you the big metrics: debt yield, DSCR and LTV.

Guys, it's me again. I highly appreciated your input which definitely helped a lot. Coming back to the modeling test - I have feedback and questions for you :) Unfortunately, I did not pass the test, and I would like to know/see if there are any convenient solutions for my failure.

I had to model a fairly basic rent roll. The problem I had, and I would like to know if there are any "easy" solutions to that was that I had to model rents with explicit intra-year end, void periods, rent-free periods as well as re-rents with different rental value.

Example: Tenant A rents from 1.1.2014 to 4.5.2015 for $1,500 per month, then 3 months of void period. After 3 months void period, re-rent at 115% of previous rent.

I was actually surprised how I should model that within given time. Are there any "standard" formulas that one uses to show the rental income line? How in general would one model something like this? How do I trigger (i) end of rent, (ii) begin and end of void periods, and (iii) begin of re-rent, in a way that the model stays flexible?

Your help is again much appreciated :)

They were basically asking you to model a tenant rolling over in a space, assuming 3 months of downtime and a new lease rate of 115% of the past rent.

easiest way is to break this into two lines instead of doing it as one.

You model the initial lease on one line and the replacement lease on another. Add the two together.

the hard way is to setup everything as inputs, break the date into pieces using various functions and then a bunch of if formulas to test conditions.

Hey mrchees321,

many thanks for your input! :) Your point is exactly the problem. That "break date into pieces" thing broke me my neck during the test. I thought, that there is maybe some kind of industry standard formula for exactly that issue, as this kind of revenue modeling comes up often? (Although I ignored to learn that before my test as I assumed that such a thing is way to time consuming hahah - I was wrong ;) )

Year Ending 12/14 12/15 Rental Income
Tenant 1 $18,000 $4,685 Tenant 2 $10,152 Total Rental Income $18,000 $14,836

What did you do?

Sorry, something went wrong with the previous post:

Hi cre123

thanks for your input.

I tried it as well and came to: 2014: 18.000 2015: 14.400

I split it into 2 lines as suggested, one for the initial rent, the second line for the new rent. Problems that I faced: - I first built a monthly cash flow. I did not know how I can solve that directly on a yearly basis - For leases that begin/end during a month, I had to assume an end of month begin/end. I also did not know how else I could have solved that (= begin of lease; current month

Apologies for bumping this.

Can someone help we with the "exact" modeling? I still could not figure that one out...

Highly appreciated.

What do you guys do if there is a debt extinguishment notice required by a CMBS loans max 90 days min 60 days? What if the seller gives the CMBS guys the notice and then the deal gets killed and then they have to pay admin fees? These fees are quite large. How do you avoid this from happening? Put an extension clause in the P&S that after the DD period is completed, the close will happen a min of 60 days after the money goes hard (at this point the seller would then notify the CMBS guys of the termination of the loan)? What if the buyer doesn't like this clause. Seller will have to compensate buyer for that clause to be put in?

Thanks