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.
| +83 | LP coming into deal at higher land basis. How to model returns to GP? | 19 | 18h | |
| +76 | Increasing RE Industry's Cash Compensation, Collectively | 43 | 1d | |
| +73 | Major Decisions | 13 | 2d | |
| +44 | Being asked to stay behind and train my replacement | 11 | 15h | |
| +24 | Thoughts on joining an early-stage REPE fund | 7 | 1d | |
| +22 | Self Storage ECRI | 9 | 4d | |
| +20 | How to get good at RE Modelling? | 18 | 20h | |
| +20 | Public Homebuilders | 7 | 14h | |
| +18 | What does REPE actually do? | 1 | 23h | |
| +18 | 3 Hour Condo/Rental Excel Test - What Can Be Expected | 2 | 5d |
Career Resources
Get instant access to lessons taught by experienced private equity pros and bulge bracket investment bankers including financial statement modeling, DCF, M&A, LBO, Comps and Excel Modeling.
I was told I was going to be provided the rent roll in this case.
https://www.adventuresincre.com/watch-build-multifamily-real-estate-mod…
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?
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).
Damn....feel bad for whoever is considering hiring this guy
You only need to know basic arithmetic to do this...
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.
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?
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
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.
Do you know what constraints will be used? Do you understand how to use min/max functions in excel? That's all you need.
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.
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.
*knawledge
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.
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.
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?
i don't mind taking a look. Send me a PM
Sit harum est aut rerum eius et in assumenda. Debitis illo id cupiditate sapiente expedita qui eveniet. Sequi optio odio maxime dolorem itaque eos tempore.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...
Reprehenderit fugiat vero atque hic culpa in. Tenetur enim eum fuga. Dolores sint quo qui veritatis.
Veritatis laborum eaque ad mollitia quibusdam odio blanditiis. Sed minus quibusdam ipsa molestiae impedit nobis ratione sit.
Deleniti blanditiis doloribus mollitia eligendi mollitia qui animi id. Consectetur minima excepturi et praesentium illum repudiandae.
Soluta quae est modi dignissimos consectetur aut quisquam tenetur. Repudiandae delectus modi distinctio earum nihil rerum. Et magni provident omnis ullam quibusdam vel. Et corporis et praesentium et.