Watch Me Model a Real Estate Private Equity Technical Interview Exercise in Excel

[@adventuresincre]

See the Notes section of the Video Description for corrections/clarifications.

[@bryani4894]

Spencer Burton Hi there, anyone know why in the actual excel file the LTC % in cell C25 changed to a formula instead of an input. During the video I saw when 60% was typed in cell C25 but the actual file says C25/C22 which is just loan/total project cost. This is confusing me to me as there would be no way to know before hand that dividing the loan by total project cost would yield 60% as the LTC. And if we divided an empty cell by total project cost it gives back 100%. Can someone help please. Everything else is fine

[@richhh7772]

i love how this information is free on the internet thank you.

[@pv0315]

None of your videos are any bit boring. All awe-inspiring stuff. You have changed covers (thumbnail) of all the notebooks. Thumbs up.

[@bilalkhokhar8497]

excellent content , i could not believe that the exercise file had an option pay what ever you want to and even free . May God reward you for this deed

[@Dalmutiii]

These "watch me mode"l videos are amazing! This one and the multifamily. More of this!!!!!

[@pv0315]

your laughter at 1:07:07 is awesome. God bless you. Thank you for this amazing knowledge share.

[@reganlandrigan7172]

Nothing but the best in these videos. Thank you

[@robertcarpenter8077]

These modelling exercises you're doing are superb ! One thing I wondered about was the timing of the TI and LC payments stipulated in the problems assumptions. It would seem to have made more sense to start the tenant improvements 6 months before the tenant moves in while waiting to pay the leasing commissions the day of move in rather than the other way around.

[@williammaroon4568]

Incredibly useful, not sure how you only have that many views, everyone in the field should use this if not for a little review and refresher. But thank you for putting this together.

[@sebaesquetini]

This is so great! Thank you Spencer, I love your videos.

[@jeebus2121]

I have no idea why I'm watching this but it's curiously interesting. [And I have no background in real estate or financial modeling]

[@JVZBrowser]

Dude you’re a hero

[@MC-fe8ih]

Hi Spencer Firstly thanks for this walkthrough model - very useful. I had a question on the S&U part of the downloaded excel (v1.3) file, specifically cell D25 (LTC) - this should be a 60% hardcode assumption. Why is this taking the loan / total project costs? This creates a circularity with cell C25? Let me know if I am missing something

[@nataliasarpi5075]

Great video!!! Extremely useful "Watch me" series. Seems there is circular reference in the calculation of the construction interest, perhaps you can fix it.

[@laurayt3]

your channel is amazing! Thank you for sharing your knowledge!

[@DevInvest]

Excellent as per usual. Thank you, As always.

[@himmatsingh4251]

Hi, thanks for providing much needed knowledge, could u please resolve this issue, as i have prepared the spreadsheet as mentioned in the video by u, but when i go back to specific cell containing formula of calculation, it disappear the result and show 0 or dash instead of the outcome. Although when i downloaded ur sheet too, same thing happening, hope if u resolve this error, thanks again.

[@Jbsito]

Got my spreadsheet off course at 23:10. Once I plug in the construction interest, all my calculations become off by a small amount, with my Month 8 balance being $5,356, Month 9 being $17,407, and the rest of the numbers being messed up. Not sure why this is the case. Been trying to work it out for hours -- all my other calculations are identical.

[@Elee4555]

Thank you. Quick question for you - why do you use a negative sign in front of total before interest to calculate the unlevered cash flow?

[@닝닝닝]

Hi Spencer, I'm wondering how we could assume breakeven as Month25? Could you elaborate more on this? Thank you

[@leventis1991]

Hello to all, I have a question regarding the structure of the exercise. Wouldn't be correct if the the loan was calculated after the unlevered CF? I mean that shouldn't the loan be calculated by adding the unlevered cashflow's shortfalls and not the total costs? The purpose of the loan isn't to cover the lack of funding ? I mean that the structure that you demonstrate is i)Costs -> ii)Loan structure->iii)revenue->iv) Unlevered cf ->... Whereas the way i propose is i)Costs -> ii)revenue->iii) Unlevered cf ->iv)Loan structure-> v) levered cf ->...

[@mn3784]

Great video！thank you ！

[@jonahchang6545]

My calculations start messing up when calculating Const. Interest around 22:50. I've played around with doing iterative and non iterative calculations and things aren't adding up correctly. Can anyone please advise?

[@MnMsg2Wrld]

same problem. Not sure if we are supposed to get some deviation based on the order Excel calculates between different version of the spreadsheet. Anyone have a clue?

[@wyndhamburney6933]

@@MnMsg2Wrld I ran into the same issue and this issue was addressed in the video description (a simple subtraction error was made)

[@epursimuove1633]

fantastic.

[@lollskandi9880]

Hi Spencer, Thank you for the video and notes - it is beyond helpful! Quick question: I'm curious to know how you would calculate Asset Management Fees (% p.a. gross asset value) in this model. I have a very similar example at hand, and asset management and acquisition fees were the only difference. Hope to hear from you soon. Thanks, L

[@MicahSpivey]

Would you do a video about how someone would have done this in 1977? Did people just write all the stuff out? It would be cooler to figure out how people did it in 1877. Pre calculator; this stuff is crazy.

[@БесланЦатиашвили]

they just built shit lol

[@NguyenLe-qc7qu]

You're so badass bro

[@eddiedutoit]

Hi Spencer - I would be interested to see how you would take on a phased high rise development on (2 phases)..for example, I have concluded the necessary Capex of the entire development, estimated the construction cost per gross construction areas. But, now to phase it so it affords the investor time to recover from the construction debt (hard costs) from the proceeds and closing of the mother bond is another story? How would you take this on?

[@veronicatestori]

Thank you very much for this. I just have a question. I don't understand why to use the IF formula and then to use the * method. Potentially I could use a IF formula for Vacancy - right?

[@rajjpatel177]

Hey, I am having difficulty getting the same loan balance around the 22:20 minute mark. Redid the spreadsheet twice now and still having same issue. Please advise, thanks!

[@rajjpatel177]

Also, I made the corrections in the bio but still not getting same balance.

[@brydensears6318]

Great video

[@adventuresincre]

Thanks Bryden

[@woodyahh2110]

I have no idea why I am watching this Thanks

[@medicalsupplycenter1079]

Could have made expense reimbursement much simpler - just set an if sttaement =if(x>0) to the operating expenses instead of linking it to mutiple lines . feel free to correct if im wrong thanks for a great video

[@kellywu9730]

Hi Spencer, Thanks for the tutorial. Just one question on the required return for LP based on 12% rate. Why is the formula (1+annual rate )^(1/n) -1, n being the number of months. Thanks!

[@kellywu9730]

Cell G88

[@pranjalvw2193]

so you want to ask, why considered CAGR where LP invest with IRR

[@tejasjahagirdar900]

I am getting a cross-reference error while adding the formula for construction interest. Since the cells are all interlinked. Kindly suggest

[@SamuelHoldworth]

in minute 13.27 can you explain how to lock in the column/row? I can use Fn F4 but it's not clear how to input the formula with shortcut. Your help would be greatly appreciated.

[@jeonjinseo2885]

Hi, thank you so much for the video. I have a question, regarding the debt, why are we linking to break even month instead of operation start month? I believe we should link it to Operation start month because we are paying interest anyway although we have not reached the break-even point yet. Anyone comment please! Thanks in advance.

[@jeonjay5710]

Thanks for the great video Spencer. Deeply appreciated if you could answer two quick questions, 1) is it correct to have the construction interest even after the end of construction period (from month 25 to 27)? 2) For the debt services on row 52, is it correct to take the total loan on cell 25 instead of the loan balance in the particular month (row 29)?

[@adventuresincre]

Glad it was helpful. And yes, it's common for construction interest to accrue beyond the construction period during the lease-up period where there is insufficient operating cash flow to cover the interest expense.

[@jeonjay5710]

@@adventuresincre Understood. Thank you very much for the prompt reply. Could you please kindly answer the 2nd question as well? Thanks a lot in advance.

[@adventuresincre]

@@jeonjay5710 - As it relates to Q2. That's a good catch. The current equation assumes the loan has fully drawn upon operating breakeven, and is correct when that is the case. But since there's a quirk in this particular case where the asset hits breakeven before the loan is fully drawn, the current equation misses two short months (months 28 and 29). I'll update the source file to calculate debt service from the row 29 rather than cell C25.

[@jeonjay5710]

Very helpful. Thanks a lot!

[@manishasangar6808]

What if rent growth rate differ in upcoming years instead of fixed rent growth rate, then how it can be adapted in the formula.

[@MichaelAnderson-fi1qg]

Hi Spenser - So helpful and thank you - one question I have and I'm hoping its simple... If I want to increase rent annually on a $ basis, how would i change my model? Starting at $43 sq ft and going up $1 annually for example. Thank you in advance

[@hughoake8812]

A cell entry to create an amount based on a daily % rate for that months entry? What would be a formulary for that?

[@w1nn19]

Hi Spencer! The Equity 42,941,653 (C26) is 400k less than GP+LP contributions 43,341,653 (D84). Could you please comment on this. Thx EDIT: NVM, found the 400k. Operating Loss of 400k (month 25-27) did not flow up/capitalized into Sources.

[@1OTDM]

How does he quickly change the font color to blue?

[@adventuresincre]

Great question! We created a free Excel add-in that adds numerous keyboard shortcuts, including the ability to automatically turn cell font blue. It's called 'Excel 4 CRE': www.adventuresincre.com/custom-excel-addin-for-real-estate/

[@thomashenry75]

Why do you input loan to cost as a formula instead of having it as an input? With the circular reference involved it does not come out to be the same.

[@paolobonja]

Any small apartment building new construction modeling Iink that you can post here?

[@MichaelSmith-lq6wd]

How do you know that break even is month 25?

[@jaekim5721]

Around the 26:48 minute mark, the construction interested changes from 1,723,501 to 0. Can you please comment on this change?

[@adventuresincre]

Thanks for pointing this out. I'm not sure what I'd done, but the formula in C21 that sums all Construction Interest for the entire development period [=SUM(G21:CR21)] got zeroed out somehow. I didn't realize this had happened until the 1:00:00 minute mark, when I saw my Levered Cash Flow line had equity contributions beyond when I'd expect. Adding the formul in C21 back, corrected the issue.

[@jk403]

Thank you. These are tutorials are fantastic and I cannot overstate how helpful they have been.

[@yizhang3964]

why the month is assumed to month 90? is there any explanation ?

[@pv0315]

Because project is getting stabilised only on month 30 . add to that the assumption for hold period - 5 years or 60 months. Total timeline - 90 months.

[@haroldk9369]

+1 banana

[@leventis1991]

Greetings to all, i need your help if you are kind enough. i can see that the model includes interest payments for the loan but i can't see where it counts for the principal of loan . can you please highlight what i am not getting? Thank you

[@starinthesky451]

How did you name the cell NRA?

[@Meyouletsgo]

I got a PE case study who can help me tomorrow ?

[@shushuchen6132]

I´d say that there is an error in Row 25 (Loan) calculation because you have taken TOTAL COST BEFORE INTEREST to substract EQUITY, instead of using TOTAL PROJECT COST (Incl.INT.).

[@adventuresincre]

Shushu - you are correct. I actually corrected the error later in the video but wasn't clear about it. You can find a further explanation of this, and a few other clarifications, in the video description. Thanks for commenting!

[@maiwenndoleac3112]

The link is invalid...

[@adventuresincre]

Fixed. Thanks for the heads up!

[@ctrouten1248]

It looks like Construction Interest (row 21) has circular reference errors, my model won't calculate these formulas because of this. I want to make sure I'm calculating all this correctly. By any chance would you be able to walk through how to fix the ref error? Or what the calc should be?

[@ctrouten1248]

Shouldn't the monthly loan pull from the "total before interest" instead on the "total project costs"? It seems to be double counting the interest the way you currently have it since we are getting the interest from the loan.

[@haroldk9369]

Enable iterative calculations under formulas in Excel settings

[@robmassopust4902]

Hard to read screen

[@adventuresincre]

Appreciate the feedback. The video was recorded at 1080p, but sometimes (depending on your internet speed or video player settings), the video will display at a lower resolution. If you're having a tough time seeing the screen, try clicking the 'Gear' icon in the lower right hand corner of the video, and setting the "Quality" to at least 720p.

[@lukew3224]

haha any WSO fan here?

[@MichaelAnderson-fi1qg]

Hi Spenser - So helpful and thank you - one question I have and I'm hoping its simple... If I want to increase rent annually on a $ basis, how would i change my model? Starting at $43 sq ft and going up $1 annually for example. Thank you in advance

[@MichaelAnderson-fi1qg]

Hi Spenser - So helpful and thank you - one question I have and I'm hoping its simple... If I want to increase rent annually on a $ basis, how would i change my model? Starting at $43 sq ft and going up $1 annually for example. Thank you in advance

[@MichaelAnderson-fi1qg]

Thats helpful but I'm still confused - Can you show me as you did with the last sentence how the math or the formula would work if i cahnged the increase to $2 dollars... I guess what I'm missing is where do i link the Ann Dollar Increase cell to that equation.?