Watch Me Build a Basic Real Estate Equity Waterfall Model with IRR Hurdles

Рет қаралды 120,327

Күн бұрын

In this video, I build a basic, 3-tier real estate equity waterfall model with IRR hurdles. Download blank worksheet together with the completed version, and follow along to learn one way I model real estate partnership cash flows in Excel.
00:00 - Introduction and Overview of Partnership Structure
05:31 - Equity Share, IRR Hurdles, and Property Cash Flow
11:38 - Modeling First Hurdle: LP's Capital Account and Sponsor Contributions
16:53 - Second Hurdle: Assumptions, Calculations, and Distributions
22:01 - Third Hurdle: Modeling Distributions and Final Review
30:34 - Conclusion and Upcoming Related Video Content
To download the models that go along with this video, visit: www.adventuresincre.com/watch...
To learn more about or to contact the author, visit: www.SpencerBurton.org

Пікірлер: 72

@josephallen5528 6 жыл бұрын

Everyone of these Watch Me Build videos is fantastic. Please keep these coming! Really appreciate it. (Construction draw/basic ground up development model "watch me build video" would be amazing!

@rauljimenez5485 5 жыл бұрын

I love when I find such elegant and to the point resources/videos. Thank you very much for this!! Keep them coming!

@conquistador3649 4 жыл бұрын

Your amazing, i love making these models and you teach this skill so well. Thank you.

@duedmanbra402 6 жыл бұрын

These are great videos you provide to the public. We appreciate it!

@charlottebarlowco 4 жыл бұрын

Spencer thank you for sharing your knowledge. Outstanding video!

@axelcramer3964 Жыл бұрын

This was... absolutely excellent. Thank you. My LPs are going to be so fluent in reading their financial reports!

@connorlw1 6 жыл бұрын

Your teaching is amazing! Thank you so much.

@MissOlgaLondon 3 жыл бұрын

This is brilliant! I'm an experienced real estate investment specialist and still I find these videos so helpful!

@cnmills715 4 жыл бұрын

Thanks so much for sharing this highly valuable instructional vid. Your tutoring is spot-on!

@krdavis121 5 жыл бұрын

I really enjoy the watch me build videos.

@Adam_G556 2 жыл бұрын

Thank you for putting this out. Looking forward to the accelerator program!

@PeacherLiz 6 жыл бұрын

I don't work in PE (yet) but this is a nice quick refreshment. Thank you!

@davidgordon6418 2 жыл бұрын

Awesome stuff, love it, thank you!

@harjith.d.bubber 6 жыл бұрын

This is the second video I've watched and they are superb. Thank you so much!!

@CanadianTips 3 жыл бұрын

It is a fantastic Video illustrating everything in details Thanks!!

@newyorkcrop966 2 ай бұрын

Thank you so much! This was really helpful!

@Burg_38 3 ай бұрын

You are the best! Thank you!

@jolinchang2002 9 ай бұрын

Thank you so much! This is extremely helpful

@markgiarelli 5 жыл бұрын

Fantastic video! thank you

@jainigam4028 3 жыл бұрын

Great video! This was very helpful!

@hempcrete4757 3 жыл бұрын

absolutely brilliant thanks

@gayaa6490 2 жыл бұрын

Thank you very much for this helpful video :)

@xiaohangli2495 5 жыл бұрын

so helpful!!!

@pv0315 5 жыл бұрын

wonderful.

@vosk875 10 ай бұрын

This is very nicely done, thank you. In this scenario it does not seem the GP is earning their pro-rata share of the cash flow (10% * 75%) in the second hurdle. Is the deal structured structured differently? Thank you

@plimps101 5 жыл бұрын

Am having an issue in a similar model where the LP's profit share % at each hurdle is being diluted by the sponsor's share, because the sponsor pulls a promote at each step. Any ideas on how to fix this?

@madisonpoole4908 5 жыл бұрын

Does this model account for a pref return going to the GP for his % of capital committed as well? Not just the uneven split after LP reaches their pref? Thanks.

@realdavitgharibyan 5 жыл бұрын

Fantastic video, Spencer I have a question regarding distributions. Does every waterfall distribute the capital along with returns. Some syndicators, e.g. property earned 13%irr that year and the pref is 6%, distribute the entire 13% as a return on investment and not capital return. Is this the case? Can you clarify it? Your modeling makes lots of sense but they pitch it as they have distributed 13% this year and don’t mention the capital return

@TheDylantaylor08 Жыл бұрын

Super helpful video, thank you so much! One thing I noticed is that the model you built isn't set up to handle negative cashflow in year one as you started with a positive cashflow. I had to build in a couple of IF statements to make it flow properly.

@Alekstoday12345 9 ай бұрын

Could you please share how you upgraded it to account for -ve CF in year 1

@YogeshSharma-ox8zf 3 жыл бұрын

This great....did mike do a video for a waterfall based on equity multiple?

@rikuxoblivion 4 жыл бұрын

In Hurdle 2, shouldnt the IRR of LP be

@dyonesios 2 жыл бұрын

An example in which you had a construction loan would be super helpful. Been struggling for several days to build a waterfall in which there's a construction loan, a takeout loan post stabilization, and then a sale well into the future.

@veritaxverity8567 Жыл бұрын

Add the loans proceeds to the cashflow. Make sure it’s cashflows after debt service.

@ivanabunjevacki4935 Жыл бұрын

Hi there, thanks for the great video! I have one question: is it possible not to have preferred return as a given in the model, but start from the first hurdle? I'm asking this because I saw a job post on the internet to create a waterfall model and the person provided his data, but the data on preferred return is missing? (it is just the hurdles data that he provided) Thanks in advance!

@lucaslu6268 8 ай бұрын

Thanks for sharing your templates, they've been super helpful! I am building a model for a vacation rental property, it will be a partnership structure with a GP (sponsor) and LPs, holding period of 5-7 years. Question about row 34 (LP Req'd Return to hit Hurdle 1) - why is that being added to calculate the Ending Balance of LP account on row 37? (in addition to Distributions to LP on row 36) Before the sale of the property in year 5, all cash flows available for distribution are going to come from monthly rental income, curious why row 34 is also being included in the running Ending Balance of LP account. Thanks!

@jonnathanagudelo8025 4 жыл бұрын

what happens if the NET Project Cash Flow is Negative? What does that mean? if so, what do i need to look at to make it positive?

@veritaxverity8567 Жыл бұрын

Hey just a quick question know those equity contributions it looks like you’re using the equity of the cashflow to come up with a contributions. What if we are using the lp to raise equity for the debt. How can we track with gets paid back in that case?

@LiveInOC Жыл бұрын

How would you extract average CoC return for each (the GP and LP) considering the profits from sales event is worked into the distributions? The average annual return considers the all cashflows including the capital events, but CoC only includes the CF from operations.

@MrBobw53 3 жыл бұрын

Where would you add in the proceeds from a sale in the last year? split 50/50

@csk 3 жыл бұрын

Hi Spencer. This video is amazing. One question, at 7:35, how do you know that the sponsors IRR is greater than the property level IRR? Thanks a lot.

@enricosanchez3289 2 жыл бұрын

This is because the sponsor has a promote on the deal, meaning they get a percentage of cash flow over their 10% share. The levered IRR is 17.3% which is well over the preferred return of 8% so it can be assumed that the GP/sponsor will have an IRR over 17.3% (as they benefit from the promote structure) and the LP investor will have an IRR under 17.3%.

@realestatedadspodcast Жыл бұрын

Why in Hurdle 2, does it say prior distributions $157,500 when $175K was distributed within Hurdle 1? 90/10 with $157,000 going to LP and $17,500 going to Sponsor totaling $175K. Hurdle 2 starts with $157,500 distributed though $175K actually went out. It looks like there is an error in row 51 for Prior distributions @ 21:05, which only include the 90% LP distributions as opposed to the additional 10% paid out to the sponsor also. If i've made an error please let me know. I love the content though if I'm mistaken I would love to know.

@vtscrogers 3 жыл бұрын

What does the AMT column header denote within the partnership structure? Also, This was a great, rudimentary tutorial. Thank you!

@rocknroll9918 3 жыл бұрын

Per my understanding, it is the amount contributed by the Sponsor and the LP.

@navidbayanfar3371 2 жыл бұрын

Spencer this is so great - I even ordered one of your models recently. Quick question - on the Hurdle 1, is the sponsors equity also accruing a pref return? it should, no?

@XCALIBURSHOES 2 жыл бұрын

Sponsor only receives his/her proportionate share if IRR is 8% or less

@realestatedadspodcast Жыл бұрын

you are correct. it should. Their equity placed into the deal should qualify for the same pref treatment.

@Fliper2K7 2 жыл бұрын

Would you do this for something you're flipping as soon as the project is complete? Looks like you're holding this for 5 years.

@MrBobw53 3 жыл бұрын

How do you know when you reach a hurdle?

@webuyhousesnjfast9308 6 жыл бұрын

Isn't the IRR for the Sponsor in the Hurdle 2 section 19%? You forgot to include an "IRR Error Check" but when I calculate it myself that's what I get, however it should be 12%, I may be missing something?

@BadFriendsTelevision 6 жыл бұрын

He did not forget to include an IRR Error check; he specifically said that he won't be doing it after Hurdle 1. Think about it, Sponsor/LP contribution is 10/90, and Up to 8% (Hurdle 1), Sponsor/LP distribution is 10/90, so both Sponsor and LP IRRs would be 8%. But between 8% and 12% (Hurdle 2), while Sponsor/LP contribution remains the same, Sponsor/LP distribution becomes disproportionate (the whole reason for we are even doing this thing called waterfall). In Section 2, LP's IRR is capped at 12% in Hurdle 2 because there is more cash available for distribution after Hurdle 2 (even after the 1st disproportionate returns between Hurdle 1 and Hurdle 2), and LP's IRR just needs to meet the Hurdle (12%) in Hurdle 2 section, according to the formula set up for "Distributions to LP" in Hurdle 2 section -> Now, here is what's causing the issue. Because of this formula, LP may have taken 75% of the remaining cash flow after Hurdle 1, or just w/e needed to achieve 12% IRR in total, whichever is smaller (MIN function). In this case, LP took way less than 75% of the remaining cash flow to achieve 12%. However, Sponsor's distribution in Hurdle 2 takes whatever return LP took, and treats it as "75% of all returns to be paid out during the period", and takes 25% of "all returns to be paid out during the period" by dividing what LP took as its return by 75% and multiplying it by 25%. I suck at this too so Im not sure if I made sense but that should be the reason; play around with the formulas yourself to see how they are working (and dont get confused between hurdles and returns to be distributed)

@leonardoneumayer2066 2 жыл бұрын

Why didn't you input an IRR value for Hurdle 3 under the partnership structure?

@TheACG95 3 жыл бұрын

how do you get the equity multiple as a multiple "2.07X" with the x?

@Corey-qc3om 2 жыл бұрын

Go to the cell you want to edit: Click CTRL and 1 at the same time, scroll to "Custom" and in type box click "0.00", then type: 0.00"x"

@danni5374 Жыл бұрын

what should one do if the error check for LP in hurdle 2 ends up being 1% less than expected IRR

@adventuresincre Жыл бұрын

An IRR in the error check LESS than the hurdle is not unusually, as it simply means that the investment produced insufficient cash flow to hit the hurdle in that tier.

@tonyko4192 2 жыл бұрын

Amazing stuff. Did u set up a VBA code for automatically blue font for hard key?

@adventuresincre 2 жыл бұрын

Glad it was helpful! 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/

@tonyko4192 2 жыл бұрын

@@adventuresincre You are the best. Planning on watching all of your CRE modeling. Thank you so much !

@timothybracken1728 Жыл бұрын

How do you automatically format the cells blue as you go so quickly? What are the keyboard shortcuts being utilized if there are any at all?

@adventuresincre Жыл бұрын

Thanks for the comment! Spencer uses a custom Excel add-in that expands the keyboard shortcuts to include a shortcut to make font blue. It's a free Excel add-in we developed specifically for CRE professionals. You can find it here: www.adventuresincre.com/custom-excel-addin-for-real-estate/

@timothybracken1728 Жыл бұрын

@@adventuresincre Wow, thanks you guys are on a whole other level. Glad I'm in the course now. I'm learning so much and filling in the knowledge gaps left from undergraduate school! Awesome

@jackrembe5999 Жыл бұрын

Why do you only model it our to year 5?

@lucatintor4896 Жыл бұрын

You are like Beethoven

@connectlastuser6191 3 жыл бұрын

How would you change this for monthly CF?

@enricosanchez3289 2 жыл бұрын

This could depend on wether if there is an LPA in place that states wether if the Pref is calculated on a monthly basis, quarterly, or annual. If it states it is calculated on an annual basis you would just create another excel sheet and consolidate monthly CF into annual CF and then run the same model.

@mahesh.kapoor Жыл бұрын

Can u share the link to the model excel sheet please for download

@adventuresincre Жыл бұрын

You bet. You can download for free the completed file from this Watch Me Build by heading here: www.adventuresincre.com/watch-build-equity-waterfall/

@m.j.mcintear793 2 жыл бұрын

I hope he is alright

@michelepiserchia1403 3 ай бұрын

Isn’t the sponsor the limited partner?

@adventuresincre 3 ай бұрын

Thanks for the question! No, the Sponsor and Limited Partner are different parties to a real estate transaction. The Sponsor (or "GP - General Partner") is the firm or individual that leads (i.e. Sponsors) the investment. The Limited Partner is the firm/individual/group that provides the capital for the investment. You can learn more about Sponsors here: www.adventuresincre.com/glossary/sponsor/