Watch Me Build a Multifamily Real Estate Model

Watching others model is a great way to learn new tricks, and improve your own modeling abilities. I turned on my screen recorder and built a basic multifamily real estate model.
00:00 - Introduction and Model Overview
02:04 - Rent Roll Development
05:00 - Financial Setup
08:38 - Income Statement Preparation
15:09 - Effective Gross Income Calculation
20:04 - Cash Flow and Operations Analysis
23:04 - Risk and Return Metrics
28:12 - Return on Investment Analysis
34:30 - Model Finalization and Review
37:50 - Conclusion and Practical Application
Download the completed model here: www.adventuresincre.com/watch...
Learn to build institutional-quality models from scratch: www.adventuresincre.com/accel...
Learn more about the author: www.spencerburton.org

[@adventuresincre]

Checking up on this video, now almost four years later and I'm embarrassed by the sound quality! But otherwise, the concepts taught are sound. I updated the source files (as of Feb 2020) to include a pre-formatted template in case you want that head start. You can download the source files free here: www.adventuresincre.com/watch-build-multifamily-real-estate-model/. Thanks for watching!

[@cantona33]

Just watched it. Audio seemed great to me. Very helpful!

[@adventuresincre]

@@cantona33 Glad it was helpful!

[@YehudaWolfset]

Spencer, your Watch Me Build videos are great! Can you do a more complex Watch Me Build Multifamily Real Estate Model? You reference in the original video many other metrics that could be incorporated into the model...I was hoping you could expand on them. Thank you!!

[@mahimabhargava207]

Thank you for taking the time to put this together! It was very helpful!

[@TeddySmithDev]

Finally stumbled on your channel after wading thru so many garbage videos! You're the man, Spencer!

[@wannawatchu66]

Who made the garbage videos?

[@juliaborrosch1176]

Thanks so much for sharing this Spencer. Extremely helpful. I added additional items to allow for costs at acquisition like agent fees, debt origination, real estate tax/stamp duty and due diligence costs..The model is built up so elegantly that it's easy to play around with the numbers. Big Thanks!

[@paulhernandez108]

Great stuff Spencer! Thank you for such clear and concise instruction.

[@sdwivedi27]

Great video - so thorough and more helpful to others than you may realize. Thanks for sharing your in-depth knowledge of the subject!

[@ownership_gratitude]

I cannot thank you enough Spencer. I am prepping for a technical exam using your material!

[@foreverraining1522]

This was AWESOME!!!!! I just learned so much!!!! Thank you very much!!!

[@harrisondrucker9660]

This was huge. Thank you!

[@abujafor8423]

Thank you very much! It is a great video. I've learned a lot of things.

[@aownmehdi4537]

Good work, I truly appreciate it.

[@MrNicfeller]

Great job! Great preparation for an interview

[@BradKaellner]

Love this, great info on building a multifamily investment model!

[@MrRichBravo]

Great information Spencer!!!

[@SherryXShi]

wonderful! I was looking for a way to track all the numbers in investment projects. I have learnt so much. Thanks Spencer.

[@6superdave]

Resize Column: Ctrl + Spacebar (select column) Alt + H +O +I (resize column) Works for several columns selected at once! Amazing video, thank you :)

[@adventuresincre]

+Dave Cameron Thanks for the tip! I've started to use it.

[@adventuresincre]

+Dave Cameron Dave - a follow up from a month ago when you suggested ALT+H+O+I to resize columns. It is now a part of my regular repertoire - I use it all the time! Thank you for the suggestion.

[@ScaredGames2000]

Very good video. Awesome and simple underwriting.

[@Michael20]

This is really good especially the Excel shortcuts. Appreciate this as an Analyst

[@GlennMariano]

This was great, and really helpful. Thank you!

[@amanyrajab7675]

this is amazing! thank you so much

[@tellemanndergaertner]

Thank you for sharing your knowledge 😄

[@cosladacity1]

amazing video!!

[@mathiasmnsted3248]

Great video! Thank you

[@philipdeverna9312]

Awesome video, thank you so much! Also - the error you mention - the CapEx value in cell E26 should be formatted black because it is a calculation - not blue

[@bradthomas3188]

That was great - thank you!

[@pv0315]

Amazing Sir. Thanks , thanks and many thanks. :)

[@robws12]

Spencer, awesome stuff, thanks so much for posting this. My question is - if you wanted a summary or top sheet for all of the investments or potential investments, how would you go about putting it together? Would you copy / paste this same tab for properties 2..n and replace the inputs and have a master tab that looked up the same cell on each of the different sheets? Or would you go with another approach? Thanks again!

[@jeffreybaase3495]

very good stuff.

[@shayklempner363]

Thanks for the great video! BTW, the keyboard shortcut you are looking for at 12:58 is ALT+H+O+I. Cheers.

[@Zenas521]

Awesome video sir.

[@damienrance5183]

Hi Spencer, Great quality content, truly appreciate it. I was wondering if you could link the excel short-cut add on please. Thanks

[@rheadj]

This is very helpful. Thank you! Where would you place the rehab budget if there was one?

[@kevinyang3773]

Spencer, thanks for the awesome content. Quick question - Under the unlevered and levered cash flow, how come the free and clear return (cell D47) and cash-on-cash return (D58) does not take the net cash flow proceeds from sales into account? Currently both returns only take into account the cash flow from operations but not the cash flow from sales in year 10. Is this an industry standard to calculate cash-on-cash returns without taking into account cash from sales?

[@kamalmammadov3206]

The explanation was pretty obvious Thank you for the video. But I have a question.... Do you have Real Estate Feasibility Study Calculation Model? Because I couldn't find that type of video in your explanation. Thank you very much for your attention

[@billbojenkins705]

Great vid! Thank you! However, I did notice something when calculating Management costs. You simply copy over the formula of the previous year value times inflation instead of keeping the management formula. Is this correct? (It may be, I am new and trying to figure out the math too.)

[@AlbertJrLopez-ks8oz]

Liked the video. I'm intermediate in RE modeling and looking to improve. Will you do a multifamily with mix use (retail) development model?

[@adventuresincre]

+Albert, Jr. Lopez A great suggestion Albert. I'll put that on my to do list. Thanks for watching!

[@sidharthchugh1392]

Hi Spencer, Thank you for the video. Can you tell me where can I find this excel template

[@davidlazarus1866]

do you have a link to the shortcut for ctrl+shift+B for the color add in? Thanks

[@aaronszekely5142]

Spencer, great spread sheet, super helpful thank you. How did you come up with the cap rate 5.5%? how should I determine my cap rate?

[@TK-ne2yo]

Largely depends on where the asset is located. For example Manhattan NY versus Nashville TN are going to have widely different cap rates. Generally the more urban the location the lower the cap rate and more rural/suburban is higher cap rate.

[@hkncgds]

hello, thank you for this great video. i just want to ask a question about, discounted cash flow how can i calculate DCF for this model? many thanks

[@thomassardelli591]

Where do you account for acquisition costs (upfront costs), upfront capex (if complex needs small rehab) and lender fees? How would these flow through the model? Thanks!

[@landonschannel6769]

At 9:14 how did you get excel to fill in the numbers? I slowed the video to 0.25 and you did it so fast I still couldn't catch what you did

[@dangreen4163]

This is a beautiful model. I was wondering if u have model that goes through the entire development process from land purchase, construction to sell... Thank you so much for this

[@adventuresincre]

Dan Green - here's a link to all of our development models: www.adventuresincre.com/re-modeling/excel-models/development/. You can browse the entire library here: www.adventuresincre.com/library-real-estate-excel-models/. I'd imagine there's a model in there that would work for you.

[@dangreen4163]

Spencer Burton thank you so much for your fast response. Also do u have a package for someone who truly wants to develop a solid R/E modeling knowledge? I am working on my capstone at NYU and would like to speak to someone about the project I am working on.

[@adventuresincre]

Hi Dan, happy to help. We don't have RE modeling packages, but here is a resource that might be helpful: goo.gl/rct68H . Best of luck on your capstone!

[@rohitchopra23]

Hello! Just wanted to ask you, should vacancy not be a function of only rental income ? Here vacancy figure is calculated using the total potential gross income. Thanks RC

[@dongdong403]

Thanks very much for this awesome video! Here is a question from 34:53 minute of your video concerning the calculation of cash on cash return: why are you using "before tax cash flow/equity" instead of "total leveraged cash flow/equity"?

[@adventuresincre]

Christal Zhong - Hi Christal, thanks for watching and great question! Cash-on-cash return is used to measure your return on equity invested in a given period, irrespective of an investments IRR. Think of it this way: you buy a building for $10 and each year that building throws off $1 of cash flow after financing - you've earned a 10% return that year on your invested equity and you can compare that 10% return to other investments that offer regular cash flows (e.g. bonds, savings account, dividend stocks, other CRE investments, etc). Leveraged cash flow, on the other hand, is used to measure an investment's internal rate of return (among other metrics). And so while it includes cash flow after financing as one input, it also includes other cash flow streams such as loan fundings/payoff, equity investment/residual sale proceeds, etc.

[@dongdong403]

Hi,Spencer,Thank you very much for your explanation! completely solved my question!

[@Aaron6715]

Great video! Aren't these after tax cash flows? Thanks

[@dionnestokes8862]

Spencer, how do I add the add on to my excel format?

[@Anthony-me4ck]

Thanks so much! Can you do a cash flow summary model that includes a value-add component? Lease up at increased rates, renovation cost and downtime, etc. Also what happens if there is a capital event (refinance) 5 years into the 10 year cash flow, considering the investment is worth more? Do you have a model on this already completed? Thanks!!!

[@adventuresincre]

Anthony - Thanks for watching! While I haven't shared a Watch Me Build of a Value-Add Model on KZbin, I have shared a Value-Add Apartment Acquisition model that does everything you describe. You can find it, together with a series of tutorial videos on using the model, here: www.adventuresincre.com/a-cre-value-add-apartment-acquisition-model/

[@dantorres9420]

What is the most efficient way to learn this? this is exactly what I’m looking to gain strength in.

[@gnordg3731]

whats the name of the add in?

[@gnordg3731]

and where can i get it?

[@reedadams1702]

Why is depreciation not factored in? thanks!

[@Anthony-me4ck]

Can you do one with the following:

[@jeanctaveras]

I wonder how this would work on a Mac with shortcuts

[@dustincarreon]

Hi Spencer, Would you have a pro-forma model for Build to Sell - Multifamily Housing Units?

[@adventuresincre]

Sure. You might check out our Condo Development Model: www.adventuresincre.com/condominium-development-model/

[@carllucamuenkel1248]

Hello! This video is teaching me a lot but I don't understand two things. 1. Why is the monthly payment for the loan * the amortization (360) so much higher than the loan value (11,923,448 > 6.9 mil) with 4% interest? 2. Why is the loan balance only decreasing by around 180k when the debt service is about 400k? Thank you!

[@chisanen8293]

sorry could you explain the math for the monthly payment?

[@scottf440]

it is a TVM function with PMT as the unknown variable

[@ahmadrowe4805]

having troubling putting the add on. Could be the reason my total is different from yours. I have $4,550 oppose to your 73,500. are my number off because I don't have the add-on?

[@albxdotcom]

do you have a download for this spreadsheet ?

[@adventuresincre]

Absolutely. You can download the model on my blog at: www.adventuresincre.com/watch-build-multifamily-real-estate-model/

[@albxdotcom]

Thanks so much !! Happy Thanksgiving

[@albxdotcom]

BTW - have you raised equity for any projects using crowdfunding. If so, I would love to pick your brain!!

[@matthewmcfadgen7831]

what is the add-in he uses for the additional shortcuts?

[@adventuresincre]

It's the Excel 4 CRE addin that we created. You can download it for free at www.Excel4CRE.com

[@joshuaahdoot393]

What is a recovery ?thanks

[@flaskwater44]

Yeah, I wasn't sure what that means. Perhaps it means you recover lost rental income by by some means.

[@adventuresincre]

Josh Ahdoot - sorry for the late response! In multifamily underwriting, recovery income (RUBS) is revenue generated by charging tenants for reimbursement of certain utility expenses (trash, sewer, water, and sometimes electrical).

[@SulaimanShah]

The decimal removal shortcut is not working for me and it's annoying. I'm trying ctrl + alt + , Is this correct? and I think I have the newest excel version.

[@adventuresincre]

That shortcut is not a standard Excel shortcut. Rather, it is made possible using a free Excel add-in we created called 'Excel 4 CRE': www.adventuresincre.com/custom-excel-addin-for-real-estate/

[@SulaimanShah]

@@adventuresincre Thank You. That was frustrating.

[@OwenRULESSS]

Why break out debt service from expenses and put it after cashflow?

[@arsenalfan13]

Great video, but I'm struggling with the PMT formula. It always returns #VALUE! on the monthly payment cell. Trying to find a good fix for this. Thanks!

[@adventuresincre]

No problem! What's the formula that's resulting in a #VALUE error? Paste the formula here and I'm happy to help you troubleshoot it.

[@arsenalfan13]

Yeah sure. It's the same formula you used in the video. =PMT(J4/12,J5,-J3) for monthly payment

[@adventuresincre]

The result will be a #VALUE! Error when any non-numeric value has been passed in the PMT function formula. So ensure that the values you have in J3, J4, and J5 are numeric.

[@arsenalfan13]

@@adventuresincre Ok, got it. Managed to get it fixed. Thanks!

[@mohammedanas8571]

can someone please explain it to me why in 20:08 we are multiplying by 200?

[@AE-lo4jz]

It’s an assumed dollar amount of CAPX per unit; in other words $200/unit of CAPX

[@George4Greece]

You're a wizard

[@razerfish]

Why no depreciation?

[@adventuresincre]

Hey Razerfish - thanks for watching. No depreciation was modeled, because all analysis was done on a pre-tax basis. After-tax analysis is best performed by a licensed CPA.

[@carlpassmore]

At 12:23 I had to take my earbuds out because I thought I heard my son crying. Ha! Not mine.

[@janebeckett4927]

Not me thinking this was a physical model build 😂

[@1rvjs]

A window on my computer screen has appeared "How likely are you to recommend this device to a friend or colleague?" How do GET RID OF THIS????!!!

[@arunkumaracharya9641]

My suggestion is to skip keyboard shortcuts; mac and windows have different short cuts, so are the versions of excel. That would be less distracting; otherwise great content!

[@aladamarketing]

You are going so fast, it's hard to follow you, so I always kept going back, and that is VERY IRRITATING, especially when I had to go too far back!!!!! Please slow down, considering that first time users have trouble to follow you, while they are coping you for the practice..... THANK YOU!

[@adventuresincre]

Sergy Shamshura - Hi Sergy, I appreciate the honest feedback. Interesting enough, I'm asked to speed up almost as much as I'm asked to slow down. The beauty of KZbin is, while we all go at varying paces, the platform gives us the ability to pause, go back, or fast forward; unlike a live classroom setting. Best of luck and thanks for watching!