Joint Venture Real Estate Model: Including IRR Hurdles/Equity Splits/More

This is an fully functioning financial model for joint venture or individual cash flow analysis of a real estate deal. The power of excel is truly amazing. I have included two of my past excel templates within this real estate model. One of the previous templates you know as the amortization schedule. The other is not for sale, but is a fully functional depreciation schedule model with mid-month/quarter/year conventions, bonus depreciation, and all types of depreciation methods.

Update of Model: IRR Calculation



General Overview




Pricing
Once purchased, you will get a download link. The purchase of this model also gives you free access to this waterfall model. No extra charge. The download link will contain both not matter which one is purchased.

Anyway, I am here to talk primarily about the real estate model. I have done a lot of these from scratch in the past for clients but never just for myself. It is simple enough to handle a broad range of scenarios but still have all the major complexities needed to evaluate a real estate deal that involves sponsors (developer) and investors.

Quick Specs:
  • Pick the number of years (2-30).
  • Can be adjusted for any building sale date from 1-30 without any data issue.
  • Includes financing variables if needed.
  • Compare pre-tax cash flows for investor/sponsor (monthly/annual).
  • Adjust IRR hurdles/sponsor promote all in one easy-to-use table.
  • Revenue growth, expense growth assumptions (adjust up to 3 times over 30 years)
  • Includes capital gains tax at sale and closing fees as a % assumption.
  • Total return with and without the effect of IRR promote as well as cash-on-cash % return (i.e. you invested x amount and how much profit did you make from that investment).
  • Includes useful depreciation schedule and amortization schedule.
  • Dynamically pulls data from the amortization schedule based on the financing inputs (so running balance effects cash flow if building sold before the loan is paid off.
  • Simply plug in all your assumptions and see the results of your deal structure.
  • All formulas are unlocked and this will be given ready to use/learn from.
  • A visual chart has been added to display sponsor vs. investor cash flow/returns.
  • Has been updated to show investor units ("x" number of total units available is now an input)
  • Discounted Cash Flow Analysis has been added to this for overall project and per sponsor/investor.
Other Updates: DCF (Discounted Cash Flow Analysis)/Charts/Conditional Formatting


The excel template that has been built allows for a full scale list of assumptions that range from financing to building sale (terminal cap rate or annual appreciation) and all the essential inputs that are needed to get the cash flows moving and grooving.

After the net operating income has been reached, I show the breakout of debt service and then the breakout between each party (sponsor/investor) based on a base equity split that can be changed. All the revenue/vacancy/expenses/resulting cash flow of building sale/financing/capital gains has been put on one page for ease of analysis and to make it granular enough, the cash flows show on a monthly sheet as well as an annual summary (both with the same complexity of data).

The main summary tab out front is truly awesome and it allows the user to change any of the inputs and see the resulting cash flows in the same place. Also, to make the IRR hurdles nice and clean, you can adjust the sponsor promote hurdles as well as % amounts in an easy to use table. The cash flows will all auto update and it shows the effect of this promotion against the cash flows if there had been no promote.

The point of having an IRR promote is to motivate the developer to sell the building for more and quicker. The higher the price and the quicker it is done, the more of a share they will get of the profits regardless of how much they put in initially. This is what the investor gives up for better performance.

My favorite part about this model is how deep you can go with the assumptions and the ability to see the net cash flow return right there. There is quite a bit of coding that has gone on here, but is all logical and easy to follow.

I would be happy to assist you in getting to know how this real estate model works at its inner most level no problem and include a lot of adjustments/additions free of charge that are included in the initial price.

Disclaimer: I am not a financial advisor and am not liable for the effects this financial model might have on your money. Please use at your own risk and fully understand what is going on before using.

Also see: Preferred Equity and Preferred Return models.