Looking for Multifamily model with Loan Sizing Constraints

FinancialBarn's picture
Rank: Orangutan | 254

Hello,

Does anyone have a MF model with loan sizing constraints? I have an interview with a large nyc firm that said I will essentially have to build out a mf model and then figure out the loan amount given loan constraints? Has anyone encountered this or at least built a model that tackles this?

Any advice is helpful.

Thanks.

Comments (26)

Feb 15, 2020

I was told I was going to be provided the rent roll in this case.

Feb 16, 2020

Array

    • 3
Feb 16, 2020
lkjhg:

https://www.adventuresincre.com/watch-build-multif...

This is definitely a great model, however it doesn't show modeling with any loan constraints.

I am happy to search but do you know if he also models a MF model with loan constraints?

  • Investment Analyst in PE - Other
Feb 16, 2020

Should be some very basic min/max functions you really can't figure this out...?

    • 1
    • 2
Feb 16, 2020
Investment Analyst in PE - Other:

Should be some very basic min/max functions you really can't figure this out...?

I am sure I could, however I believe what I will be tested on is using something like dscr or LTV to figure out what the appropriate loan balance should be. While the concept isn't too complex, I think it would be nice to see it done in a neat way already (I tend to learn better rebuilding already built models).

Funniest
  • Investment Analyst in PE - Other
Feb 17, 2020

Damn....feel bad for whoever is considering hiring this guy

    • 9
    • 3
Feb 18, 2020

You only need to know basic arithmetic to do this...

Most Helpful
Feb 17, 2020

While I don't agree with the other guy putting you down, you are acting a bit entitled. A big part of working in real estate and finance in general is the ability to tackle problems that you've never seen before, and this is a fairly simple problem. That being said, there was a point in time where I didn't know how to do this either, so I'll help you out.

Build out your income statement to arrive at year 1 NOI, or just make up an NOI number and use that.

Assumptions: it's a 5% interest only Loan to make things easy, use a 5% cap rate to arrive at value, a minimum DSCR of 1.20x, and a max LTV of 75%

Divide NOI by the DSCR Minimum to find the max amount of interest expense. Divide this interest expense by the interest rate to arrive at the max loan value based on DSCR constraints. If you want to use an amortizing loan rather than an interest only loan, you'll have to use the =PMT formula but let's ignore that for now

Divide NOI by your cap rate to arrive at value of the building. Then multiply by your Max LTV to find the max loan value based on LTV constraints.

Take the minimum of the two Max Loans to arrive at your answer

All you have to do is remove the IRR calculations in an equity model, add the calculations I described above, and boom you've got a debt model

    • 12
Feb 17, 2020
JSmithRE2010:

While I don't agree with the other guy putting you down, you are acting a bit entitled. A big part of working in real estate and finance in general is the ability to tackle problems that you've never seen before, and this is a fairly simple problem. That being said, there was a point in time where I didn't know how to do this either, so I'll help you out.

Build out your income statement to arrive at year 1 NOI, or just make up an NOI number and use that.

Assumptions: it's a 5% interest only Loan to make things easy, use a 5% cap rate to arrive at value, a minimum DSCR of 1.20x, and a max LTV of 75%

Divide NOI by the DSCR Minimum to find the max amount of interest expense. Divide this interest expense by the interest rate to arrive at the max loan value based on DSCR constraints. If you want to use an amortizing loan rather than an interest only loan, you'll have to use the =PMT formula but let's ignore that for now

Divide NOI by your cap rate to arrive at value of the building. Then multiply by your Max LTV to find the max loan value based on LTV constraints.

Take the minimum of the two Max Loans to arrive at your answer

All you have to do is remove the IRR calculations in an equity model, add the calculations I described above, and boom you've got a debt model

I had no intention of coming across as entitled, perhaps my use of verbiage was not the best. I was simply looking to see if someone had tackled this in the past or had something readily available.

This is helpful, thank you.

Feb 17, 2020

You're right, this is pretty basic and OP should know. If OP reads this use a 1.25x dscr but you should be able to explain it. And ask about LTV, but if no answer then stick to 65%.

But question for you, why YR1 NOI? Most of our lenders do adjusted going in NOI. Do you get your lenders to buy off on your yr1 proforma assumptions? Like new other income revenue?

Feb 17, 2020

I just picked year one to make things easy. Most modeling tests don't have a T-12 like in the real world. But you're right, in my experience lenders (at least on stabilized properties) will use T-12 NOI not NTM NOI

Feb 17, 2020

Honestly this is really basic stuff. If you can't do this then, with all seriousness, you do not deserve the job.

    • 5
    • 2
Feb 17, 2020
Temujiin:

Honestly this is really basic stuff. If you can't do this then, with all seriousness, you do not deserve the job.

Interesting, perhaps I do not deserve an entry level position. I apologize that my attempt of trying to learn offended you. I hope all is well my man.

Feb 17, 2020

Do you know what constraints will be used? Do you understand how to use min/max functions in excel? That's all you need.

Feb 17, 2020

agree with everybody else. This is not having you work on Argus or build a waterfall distribution. This is very basic, so you dont need a previous model where you can plug and chug, once you get hired not everything will be handed to you on a silver platter, so you need to build one from scratch. Its excel and JRSMITH already walked through what you need to do, follow every step.

Feb 17, 2020
Brody92:

agree with everybody else. This is not having you work on Argus or build a waterfall distribution. This is very basic, so you dont need a previous model where you can plug and chug, once you get hired not everything will be handed to you on a silver platter, so you need to build one from scratch. Its excel and JRSMITH already walked through what you need to do, follow every step.

I think everyone may have misinterpreted what I need. I have no issue with a DCF/Basic Pro forma, I suppose I should've rephrased and asked only for a model with Loan sizing.

That being said it took me an hour and a half to model the first one all the way through with loan sizing constraints for DSCR and DY. I appreciate everyone's input, particularly those who actually chose to answer as opposed to state the obvious.

Feb 18, 2020

My $0.02 as well:

It's really good to struggle through problems to learn. Nobody is inherently born with this knowledge. Especially with excel, it's a good practice to build a model from scratch. It makes you critically think about what's going on and why. Not saying don't ask questions on this forum, but learn the major constraints and think about mathematically how you would achieve them. It helps you understand their purpose a bit more than memorizing a formula.

Sure, DSCR = NOI / PMT but why does this matter? Or DY = PMT / Loan Amount. Who cares? Figuring that out will make you more effective at your job.

"The three most harmful addictions are heroin, carbohydrates, and a monthly salary." - Nassim Taleb

    • 1
Feb 18, 2020
Malta Monkey:

knowledge

*knawledge

    • 1
Feb 18, 2020

Agreed. I am guessing the feedback here would have been a lot different had OP posted a model that he had built and asked for feedback on what he had done right or wrong. Even in the real world showing your work and asking for feedback is a much better approach than asking for help without doing any of the work. OP might have done some work, but we dont know that unless he shows us what he has done so far.

    • 1
Feb 18, 2020

OP - feel free to PM me your email. I am more than happy to send you a loan sizing exhibit that we send to clients and discuss these concepts in more detail offline.

I work in multifamily lending and while I too was a little taken aback by the simplicity of your questions, I respect how you have handled yourself and your desire to learn.

Array
Feb 18, 2020

I'm not sure why this guy is getting so much heat. His question is literally what this forum is for.

With that said, OP could you be more specific on what you want to see? The Loan Sizer in my model is one formula in a cell. You could build out inputs on the side I suppose (LTV, DSCR, Interest Rate, etc.) in a clean table if your looking for formatting examples.

    • 1
Feb 18, 2020
promoteseeker:

I'm not sure why this guy is getting so much heat. His question is literally what this forum is for.

With that said, OP could you be more specific on what you want to see? The Loan Sizer in my model is one formula in a cell. You could build out inputs on the side I suppose (LTV, DSCR, Interest Rate, etc.) in a clean table if your looking for formatting examples.

Appreciate the support, would you confidentially be able to shoot that over or would you glance at mine and see if you agree with what I have created?

Feb 19, 2020

i don't mind taking a look. Send me a PM

Feb 18, 2020
Comment