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

No video

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

Рет қаралды 135,633

Күн бұрын

Warning: This video is long and boring! But, if you're preparing for a real estate technical interview, you'll likely find value in it. Watch me complete what is an actual real estate private equity modeling test. Download the Excel file and follow along (see notes below for edits).
0:00 - Introduction, Model Setup, and Overview
4:31 - Model Development and Assumptions
10:00 - Project Costs and Equity Modeling
15:18 - Equity, Debt, and Interest Calculations
21:35 - Operations Setup and Rental Income Modeling
28:28 - Operation Months and Expense Recovery
36:38 - Operating Expenses and Cash Flow
44:00 - Finalizing Operations and Terminal Value
51:25 - Property Level Cash Flows and Waterfall Model
58:52 - Equity Contributions and LP/GP Cash Flow
1:07:10 - Finalizing Waterfall Analysis and Review
To download the Excel file that goes along with this video (both the finished version as well as a blank tab to follow along), visit: www.adventures...
Join our comprehensive real estate financial modeling training program - the A.CRE Accelerator: www.adventures...
A Couple of Notes/Corrections:
At 21:20, the formula for the Loan Cash Flow should be Total Project Costs (row 22) minus Equity (row 26), not row 20 minus row 26. This error was fixed around the 26:00 mark, but was not made clear.
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 formula in C21 back, corrected the issue.

Пікірлер: 81

@adventuresincre 6 жыл бұрын

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

@bryani4894 5 жыл бұрын

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

@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 6 жыл бұрын

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

@pv0315 4 жыл бұрын

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

@jeebus2121 4 жыл бұрын

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]

@robertcarpenter8077 6 жыл бұрын

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.

@nataliasarpi5075 6 жыл бұрын

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

@pv0315 5 жыл бұрын

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

@williammaroon4568 5 жыл бұрын

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.

@reganlandrigan7172 3 жыл бұрын

Nothing but the best in these videos. Thank you

@sebaesquetini 3 жыл бұрын

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

@himmatsingh4251 2 жыл бұрын

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.

@leventis1991 5 жыл бұрын

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 ->...

@medicalsupplycenter1079 2 жыл бұрын

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

@MicahSpivey 4 жыл бұрын

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.

@user-xd8jq4gy7y 3 жыл бұрын

they just built shit lol

@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

@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

@laurayt3 5 жыл бұрын

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

@DevInvest 3 жыл бұрын

Excellent as per usual. Thank you, As always.

@JVZBrowser 2 жыл бұрын

Dude you’re a hero

@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?

@NguyenLe-qc7qu 4 ай бұрын

You're so badass bro

@Jbsito 2 жыл бұрын

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.

@woodyahh2110 4 жыл бұрын

I have no idea why I am watching this Thanks

@lollskandi9880 5 жыл бұрын

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

@SamuelHoldworth 2 жыл бұрын

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.

@eddiedutoit 4 жыл бұрын

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?

@tejasjahagirdar900 10 ай бұрын

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

@jeonjinseo2885 6 жыл бұрын

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.

@manishasangar6808 2 жыл бұрын

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

@w1nn19 6 жыл бұрын

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.

@mn3784 3 жыл бұрын

Great video！thank you ！

@shushuchen6132 5 жыл бұрын

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 5 жыл бұрын

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!

@rajjpatel177 5 жыл бұрын

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 5 жыл бұрын

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

@aposteriori421 9 ай бұрын

fantastic.

@jonahchang6545 6 жыл бұрын

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 6 жыл бұрын

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 4 жыл бұрын

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

@MichaelAnderson-fi1qg 6 жыл бұрын

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

@jeonjay5710 2 жыл бұрын

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 2 жыл бұрын

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 2 жыл бұрын

@@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 2 жыл бұрын

@@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 2 жыл бұрын

Very helpful. Thanks a lot!

@hughoake8812 5 жыл бұрын

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

@kellywu9730 5 жыл бұрын

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 5 жыл бұрын

Cell G88

@pranjalvw2193 4 жыл бұрын

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

@thomashenry75 6 жыл бұрын

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.

@brydensears6318 4 жыл бұрын

Great video

@adventuresincre 4 жыл бұрын

Thanks Bryden

@ctrouten1248 6 жыл бұрын

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 6 жыл бұрын

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 6 жыл бұрын

Enable iterative calculations under formulas in Excel settings

@1OTDM 5 жыл бұрын

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/

@leventis1991 5 жыл бұрын

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

@MichaelSmith-lq6wd 2 жыл бұрын

How do you know that break even is month 25?

@paolobonja 5 жыл бұрын

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

@yizhang3964 6 жыл бұрын

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

@pv0315 5 жыл бұрын

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.

@jaekim5721 6 жыл бұрын

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

@adventuresincre 6 жыл бұрын

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 6 жыл бұрын

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

@haroldk9369 6 жыл бұрын

+1 banana

@starinthesky451 5 жыл бұрын

How did you name the cell NRA?

@Meyouletsgo 4 жыл бұрын

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

@robmassopust4902 6 жыл бұрын

Hard to read screen

@adventuresincre 6 жыл бұрын

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.

@maiwenndoleac3112 3 жыл бұрын

The link is invalid...

@adventuresincre 3 жыл бұрын

Fixed. Thanks for the heads up!

@lukew3224 3 жыл бұрын

haha any WSO fan here?

@MichaelAnderson-fi1qg 6 жыл бұрын

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.?

@MichaelAnderson-fi1qg 6 жыл бұрын