Preview of Excel for Real Estate Certification Level 3 Bootcamp | Single Transaction Equity Joint Venture Partnership and Waterfall Modeling Course

This course includes:

• Chaptered video of 3 lessons totaling 3.3 hours
• Fully-unlocked Excel file with solution set
• PDF of course slide deck
• Lifetime access, even on mobile devices
• Access to the online Certification exam, which bestows Level 3 Excel for Real Estate Certification upon passing

Overview

In this 3-lesson course from Real Estate Financial Modeling, you will learn about the customary “dollars in” and “dollars out” dynamics of single transaction commercial real estate equity joint venture (JV) partnerships (not multi-transaction private equity funds), and the important rationales that underpin these dynamics.

Specifically, you will model:

• non-compounded and a compounded cumulative pari-passu preferred returns, with pari-passu residual cash flow splits (i.e., a 2-tier waterfall)
• a 3-tier IRR hurdle-based waterfall with residual splits including a sliding scale-based set of sponsor promotes
• 2 interlocking 5-tier waterfalls with unique sets of IRR-based hurdles and sponsor promotes (a double-promote structure).

Where applicable, deal-level levered cash flows are kept constant to allow for comparative analysis across JV structures.

This course builds upon the content in REFM’s Level 1 and  2 Bootcamps.

Instructions

Download the Excel from the Materials tab above. Then watch the videos, and follow along and perform the exercises in the Excel.

Video Sample

• Lesson 1 - Foundations and Pari-Passu Waterfall Modeling
• Agenda
• Big picture: two investors
• Multiple investor cash flow 3-tier waterfall
• Dollars out considerations
• Which priority of payments is best?
• Pref Non-Compounded Exercise overview
• Cash flow available for distribution types B and C
• Summary of cash flows and returns through Distribution Type B
• Tab 6 Pref compounded solution monthly overview
• Tab 7 Monthly vs Annual Preferred Return Calculation Analysis of Differences
• Lesson 2 - Sliding Scale Residual Cash Flow Splitting Using IRR-based Hurdles and Promotes
• Waterfall tiers overview
• Example of interim and final multiple achieved
• Tab 9 IRR Dollar Component Calcs 1
• Tier 2 of 3-Tier Waterfall
• Sensitivity Analysis on Hurdle Rates and Promotes
• Refinancing Impact on Waterfall
• Lesson 3 - The Double-Promote
• What if there are 3 equity players?
• How $20MM in equity might break out
• Double-promote structure for 3 equity players
• Double-promote Waterfall #1
• Waterfall #1 Tier 3
• Waterfall #2 points of connection to Waterfall #1

Excel File Tabs

The Excel file is 100% unlocked, and you can re-purpose the tabs and comprising code as you desire.

1. Hyperlinked Table of Contents
2. Preferred Return Overview
3. Non-compounded Pari-Passu Preferred Return Exercise
4. Non-compounded Pari-Passu Preferred Return Solution
5. Compounded Pari-Passu Preferred Return Exercise
6. Compounded Pari-Passu Preferred Return Solution
7. Compounded Pari-Passu Preferred Return Solution – Monthly
8. Compounded Pari-Passu Preferred Return Solution – Monthly vs Annual
9. IRR Explanation
10. IRR Dollar Component Calculations 1
11. IRR Dollar Component Calculations 2
12. 3-Tier Waterfall with Promote Exercise
13. 3-Tier Waterfall with Promote Solution
14. 3-Tier Waterfall with Promote With Refi Solution
15. Double Promote Diagram
16. Double-Promote Waterfall #1 Exercise
17. Double-Promote Waterfall #1 Solution
18. Double-Promote Waterfall #2
19. Double-Promote Returns Exhibit
20. Alternate Compounding Periods
21. Partnership Structure 1
22. Partnership Structure 2
23. Partnership Structure 3

Course Content

Lesson 1 – Foundations and Pari-Passu Waterfall Modeling (87 min.)

In this lesson, you will learn the critical foundations to understanding the basis of waterfall modeling, and proceed to model two different Preferred Return waterfall structures, both of which are Pari-Passu.

Video chapter markers:

• Agenda
• Spreadsheet formatting notes
• Big picture: spreadsheet tab relationships
• What happens on the cash flow tab
• If it’s a unit-sales project
• Big picture: spreadsheet tab relationships revisited
• Big picture: a single investor
• Big picture: two investors
• Multiple investor cash flow 3-tier waterfall
• JV partnerships: multiple parties teaming up
• Joint venture partnership cash flow splitting
• Dollars in considerations
• Dollars out considerations
• Disproportionate targeted reward to the sponsor is typical
• To motivate the creation of a larger pie for all
• How to drive the disproportionate financial reward to sponsor
• Trade-offs for disproportionate financial reward
• Variations of priority of distributions
• Which priority of payments is best?
• Preferred return distributions in context
• Tab 2 Pref Non-Compounded Exercise overview
• Equity investment and sponsor portion
• Annual preferred return (non-compounded)
• Combined invested capital account balance
• Review of non-compounded but cumulative waterfall structure
• Calculated preferred return (accrual)
• Preferred return distributions to sponsor and investor
• Review of progress
• Cash flow available for distribution types B and C
• Tab 4 Pref Compounded Exercise overview
• Annual preferred return (compounded)
• Investor capital invested
• Preferred return interest accrual
• Invested capital balance
• Interest account balance
• Payment of accrued interest
• Repayment of invested capital
• Check on IRR
• Sponsor capital invested
• Preferred return interest accrual
• Payment of accrued interest
• Repayment of invested capital
• Invested capital balance
• Interest account balance
• Review of progress
• Residual cash flow for distribution
• Comparison of non-compounding and compounding
• Summary of cash flows and returns through Distribution Type B
• Investor summary
• Investor investment
• Gross distributions
• Net cash flow
• Gross distributions total
• Investor investment total
• Investor IRR
• Investor Multiple on equity
• Investor Share of profits
• Sponsor equity investment
• Gross distributions
• Net cash flow
• Gross distributions total
• Sponsor investment total
• Sponsor IRR
• Sponsor Multiple on equity
• Sponsor Share of profits
• If residual profits were distributed pari passu
• Investor net cash flow
• Sponsor net cash flow
• Deal summary
• Total investment
• Levered cash flow
• IRR
• Multiple on equity
• Review of graphs
• Tab 6 Pref compounded solution monthly overview
• Monthly IRR and accruals
• IRR Check
• Review of graphs
• Tab 7 Monthly vs Annual Preferred Return Calculation overview
• Analysis of differences

Lesson 2 – Sliding Scale Residual Cash Flow Splitting Using IRR-based Hurdles and Promotes (75 min.)

In this lesson, you will expand on the fundamentals that were put in place in Lesson 1, integrating promoted interest dynamics (the “promote”) to make possible a disproportionate sharing of profits based on cash flow performance.

Video chapter markers:

• Review of concepts covered
• Purpose of Distribution Type C
• Residual cash flow splitting methods
• Waterfall tiers
• Which IRR serves as the trigger?
• Sponsor Promotes
• Ambiguity in definition of Promote
• Is there a promote in Tier 1?
• How do you isolate cash flows within a tier?
• Isolating base and incremental dollars within IRR
• How to think of the reported IRR
• Example of transaction interim and final IRR achieved
• Example of interim and final multiple achieved
• Isolating base and incremental dollars within IRR
• IRR unique dollar component calculations
• Tab 9 IRR Dollar Component Calcs 1
• Tab 10 IRR Dollar Component Calcs 2
• Tab 11 3-Tier Waterfall Exer overview
• Highlighting of differences in waterfall format
• Tier 1 Beginning of Period Balance
• Investor Injections,Investor Accruals
• Tier 1 Accrual Distribution
• Tier 1 End of Period Balance
• Investor Cash Flow,Sponsor Equity Cash Flow
• Tier 1 Remaining Cash to Distribute
• Investor IRR Check
• Lack of display of Sponsor investment cash flow
• Tier 2 overview
• Changing of inputs as a learning method
• Tier 2 Beginning of Period Balance
• Investor Injections
• Investor Accruals
• Tier 1 Accrual Distribution
• Tier 2 Accrual Distribution
• Tier 2 End of Period Balance
• Investor cash flow
• Sponsor Equity Cash Flow
• Sponsor Promote Cash Flow
• Tier 2 Remaining Cash to Distribute
• Tier 3 overview
• Tier 3 Investor Cash Flow
• Tier 3 Sponsor Equity Cash Flow
• Tier 3 Sponsor Promote Cash Flow
• Tier 3 Remaining Cash to Distribute
• Deal Summary
• Investor Gross Distributions
• Comparison of Deal and Investor returns
• Sponsor Summary,Sponsor Investment
• Sponsor Equity Cash Flow
• Sponsor Promote Cash Flow
• Sponsor Net Cash Flow
• Cash Flow Check
• IRR Checks
• Sensitivity Analysis
• Discussion of graphs and relative performance of players
• Share of Cash Flows by Tier
• Tab 13 3-Tier Waterfall Sol w Refi overview
• Refi Impact on returns
• Accruals and Tier 1 Remaining Cash to Distribute summaries
• Refi Repayment of All Investment

Lesson 3 – The Double-Promote (37 min.)

In this lesson, you will learn about how to model two unique interlocking waterfall structures among 3 equity entities, review frequency of compounding, and see some sample structure set-ups that you can re-purpose.

Video chapter markers:

• What if there are 3 equity players?
• Top-level sponsor example
• Teaming up with third party capital
• How $20MM in equity might break out
• Double-promote structure for 3 equity players
• Tab 15 D-P Waterfall #1 Exer overview
• Tier 1 review
• Tier 2 review
• Tier 3 overview
• Beginning of Period Balance
• Investor Injections
• Investor Accruals
• Tier 1 and 2 Accrual Distributions
• Tier 3 Accrual Distribution
• End of Period Balance
• Tier 3 Investor Cash Flow
• Sponsor Equity Cash Flow
• Sponsor Promote Cash Flow
• Tier 3 Remaining Cash to Distribute
• Tier 4 Beginning of Period Balance
• Investor Injections
• Investor Accruals
• Tier 1 and 2 and 3 Accrual Distributions
• Tier 4 Accrual Distribution
• Tier 4 End of Period Balance
• Investor Cash Flow
• Sponsor Equity Cash Flow
• Sponsor Promote Cash Flow
• Tier 4 Remaining Cash to Distribute
• Tier 5 Cash Flows
• Tier 5 Remaining Cash to Distribute
• Deal Cash Flow Check
• IRR Checks
• Review of returns graphs
• Share of Cash Flows by Tier
• Tab 14 Double Promote Diagram tab revisited
• Why 2 or 3 or 5 Tiers?
• Tab 17 DP Waterfall #2 Exercise overview
• Differences vs Waterfall #1
• Equity Investment Connection to Waterfall #1
• Top-Level Sponsor Investment Connection to Waterfall #1
• Cash Flow to Top-Level Sponsor Entity from Waterfall #1
• Promote to Top-Level Sponsor Entity from Waterfall #1
• Partner Tier 1 Accrual Distribution
• Review of returns across all entities
• Review of returns graphs for Waterfall #2 players
• Tab 19 DP Returns Exhibit
• Tab 20 Alternate Compounding Frequencies
• Monthly Compounding
• Daily Compounding
• Quarterly Compounding
• Sample Partnership Structure Set-Ups