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!
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
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!
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.
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.
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.
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.
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?
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!
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/
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%.
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/
@@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
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?
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.
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.
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
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?
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.
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?
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)
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/
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!
This is brilliant! I'm an experienced real estate investment specialist and still I find these videos so helpful!
This was... absolutely excellent. Thank you. My LPs are going to be so fluent in reading their financial reports!
I love when I find such elegant and to the point resources/videos. Thank you very much for this!! Keep them coming!
Your amazing, i love making these models and you teach this skill so well. Thank you.
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
It is a fantastic Video illustrating everything in details
Thanks!!
Thank you for putting this out. Looking forward to the accelerator program!
Spencer thank you for sharing your knowledge. Outstanding video!
Thank you very much for this helpful video :)
Thank you so much! This is extremely helpful
Thank you so much! This was really helpful!
I don't work in PE (yet) but this is a nice quick refreshment. Thank you!
These are great videos you provide to the public. We appreciate it!
Thanks so much for sharing this highly valuable instructional vid. Your tutoring is spot-on!
This is the second video I've watched and they are superb. Thank you so much!!
You are the best!
Thank you!
I really enjoy the watch me build videos.
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!
Awesome stuff, love it, thank you!
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.
Great video! This was very helpful!
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.
Could you please share how you upgraded it to account for -ve CF in year 1
Your teaching is amazing! Thank you so much.
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.
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.
Add the loans proceeds to the cashflow. Make sure it’s cashflows after debt service.
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?
This great....did mike do a video for a waterfall based on equity multiple?
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!
In Hurdle 2, shouldnt the IRR of LP be
Amazing stuff. Did u set up a VBA code for automatically blue font for hard key?
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/
@@adventuresincre You are the best. Planning on watching all of your CRE modeling. Thank you so much !
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.
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%.
absolutely brilliant thanks
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?
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/
@@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
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?
Sponsor only receives his/her proportionate share if IRR is 8% or less
you are correct. it should. Their equity placed into the deal should qualify for the same pref treatment.
Why didn't you input an IRR value for Hurdle 3 under the partnership structure?
Fantastic video! thank you
What does the AMT column header denote within the partnership structure? Also, This was a great, rudimentary tutorial. Thank you!
Per my understanding, it is the amount contributed by the Sponsor and the LP.
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.
Where would you add in the proceeds from a sale in the last year? split 50/50
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.
what should one do if the error check for LP in hurdle 2 ends up being 1% less than expected IRR
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.
Can u share the link to the model excel sheet please for download
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/
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
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?
Why do you only model it our to year 5?
How do you know when you reach a hurdle?
how do you get the equity multiple as a multiple "2.07X" with the x?
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"
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?
How would you change this for monthly CF?
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.
You are like Beethoven
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?
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)
so helpful!!!
Isn’t the sponsor the limited partner?
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/
wonderful.
I hope he is alright
You're a goat