Description
In this 3-lesson hands-on-the-keyboard Excel for Real Estate Certification course, you will learn techniques and topics related to joint venture partnerships and investment waterfall modeling for single property transactions. You will follow along in Excel and perform exercises to ensure you are grasping the concepts and mastering the skills being taught.
This product is the REFM Excel for Real Estate Level 3 Certification Preparatory Material.
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, as well as to 20+ practice questions with answer explanations to help you get ready
BONUS MATERIALS INCLUDED!
Proprietary data: Library of actual equity joint venture partnership waterfall structures across strategies and geographies
This Course Is A Good Fit If You Want To
This is a good fit if you want to:
- Gain proficiency in the most difficult, valuable and desired set of CRE financial modeling skills
- Pass an exam to add a known and valued skills Certification to your resume and LinkedIn profile
- Accelerate your career advancement by becoming the go-to analyst or associate for modeling equity joint venture waterfall structures
REFM’s Certification in Excel for Real Estate program has existed since 2011, and thousands of certifications have been granted. Certification holders use their REFM credentials to prove their skills and to support their career moves.
Applicable Analysis Types
- Stabilized and Value-add Acquisitions
- Ground-up Development
- All Property Types
Details
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.
1. What is a joint venture and why does it exist?
2. Review of what happens on the cash flow tab
3. Dollars in considerations
4. Dollars out considerations
5. Rationale behind targeting “disproportionate financial reward” to the sponsor and how to achieve it
6. Trade-off for disproportionate financial reward / Preferred return
7. Residual cash flow splitting methods
8. Multi-tier waterfall structures
9. Which IRR serves as the trigger?
10. Sponsor promotes, and ambiguity of definition of the promote
11. How to isolate base and incremental dollars within an IRR
12. What if there are 3 equity players? / Double-promote structure
13. Common mistakes when modeling waterfalls
This is a hands-on-the-keyboard course. You will watch the video and pause it to complete exercises in the associated Excel; you will repeat this process through all 3 lessons.
Our approach to help you learn is to not force you to build the model from scratch. Rather, we provide you with pre-formatted, pre-labeled model “skeletons” so that you can focus on learning the business underpinnings of the formulas that you will be writing, and the formula mechanics themselves. Real estate is a business, and the business models, analytical conventions and operating realities must be understood intimately so they can manifest correctly in the Excel-based logic statements and calculations.
Our teaching and learning format not only allows you to learn more deeply, but also allows you to do so 20% faster than if you were to start from a blank worksheet.
You are of course welcome to re-build the spreadsheets from scratch at any time, but in the interest of getting you to learn efficiently in your limited study time, we choose to give you the skeletons and teach from that point of progress forward.
Rest assured that you will work very hard in this course programming dozens and dozens of lines to fully populate the model skeletons, and you will learn a tremendous amount about real estate and financial modeling in the process.
The video running time is 3.3 hours across the 3 lessons. Watching all the video (replaying parts where desired) and completing the exercises will take approximately 5 to 7 hours, depending on the individual.
The sample questions and 27-question Certification exam will take approximately an additional 2 hours to complete.
Through the direct experience of REFM’s principals in commercial real estate transactions, and REFM’s ongoing consulting to industry practitioners across the U.S. for the last decade, we have developed and have continually refined “The REFM Way” of teaching financial modeling.
To enhance your learning and help you to efficiently build a practical skill set, we:
- Always set the stage by providing the big picture first, and keep it at the fore with graphs and sparklines
- Speak in plain language and define industry terms on an ongoing basis
- Give you the head start of having a model skeleton in which to learn
- Provide a magnified formula bar so you don’t have to strain your eyes to see formula contents
- Draw with “ink” on the worksheets to help you follow the lesson and grasp important points
- Give you highly granular video chapter hyperlinks so you can find what you want quickly and easily.
This course will prepare you to pass the associated 27-question Certification exam, which is available online 24/7/365, and is open book. The exam has questions similar to these sample questions.
Passing the exam requires a score of 70% or better, and passing With Distinction requires a score of 85% or better. You can retake the exam if you are unsatisfied with your score.
Upon passing, you will be listed automatically on the Certification Holders page.
You are allowed one re-take of the test if you wish to take it again.
Upon successful completion of this course, you will:
- Have gained a critical understanding of the business conventions that underpin the modeling of equity joint venture investment cash flow waterfalls
- Be able to distinguish between the various structural elements in waterfalls that are new to you
- Be able to model up to 5 waterfall tiers using IRR-based hurdles at a compounding frequency of daily, monthly, quarterly or annually, and be able to model a double-promote structure.
What You Get
Lifetime online access to:
- An easily-navigated 200-minute (3.3-hour) video tutorial, with captions available and playback controls up to 2x speed. Videos are playable on any device including PC, Mac, phones and tablets.
- Accompanying fully-unlocked, annotated Excel file, compatible with Excel on both PC and Mac, which you can re-purpose for future analyses.
- Access to the Certification exam, as well as to 20+ questions with answer explanations to help you prepare.
- PDF file of the 67 slides presented in the course.
Bonus Materials Included With Purchase
Proprietary data: Library of actual equity joint venture partnership waterfall structures across strategies and geographies
Meet Your Instructor
Since 2009, top companies and educational institutions have trusted REFM to train thousands of their employees and students.
New to REFM training?
Clients Include
Clients Include
Amherst
ASB Capital Management
Barings
BentallGreenOak
BrightSpire
Brixmor
Brookfield
CapitalOne
CBRE Global Investors
CCRE
Centennial Bank
Citibank
ColonyNorthstar
Conrex
CPPIB
CRC Companies
CreditSuisse
Crescent Communities
Crow Holdings
DDG
DLA Piper
Greystar
GTIS Partners
Hodes Weill & Associates
Houlihan Lokey
Howard Hughes
Jamestown
JBG Smith
JPMorganChase
Kennedy Wilson
Link Logistics
LivCor
Lubert-Adler
Macys
Oxford Properties
Park Hill
Peterson Companies
PGIM
PN Hoffman
Prometheus
QuadReal
Related
Revantage
SEO
Skanska USA
Square Mile Capital
USAA Real Estate
Asset Living
CBRE Global Investors
Clark Enterprises
Comstock Homes
Conor Commercial
Crescent Communities
Federal City Property
Flournoy
Four Seasons
Grosvenor
GTIS Partners
Hines
Hoffman
Howard Hughes
HPET
Hunt Companies
Insight Property Group
Kettler
LaSalle Investment
MadisonMarquette
Markwood
Mesa Capital
Midway
Miller Valentine
Panoramic Interests
PEG Development
Renaissance Downtowns
Rockefeller Group
Skanska USA
Snavely Group
Trammell Crow Residential
Trion Properties
Vornado
Sample Content
- 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.
- Hyperlinked Table of Contents
- Preferred Return Overview
- Non-compounded Pari-Passu Preferred Return Exercise
- Non-compounded Pari-Passu Preferred Return Solution
- Compounded Pari-Passu Preferred Return Exercise
- Compounded Pari-Passu Preferred Return Solution
- Compounded Pari-Passu Preferred Return Solution – Monthly
- Compounded Pari-Passu Preferred Return Solution – Monthly vs Annual
- IRR Explanation
- IRR Dollar Component Calculations 1
- IRR Dollar Component Calculations 2
- 3-Tier Waterfall with Promote Exercise
- 3-Tier Waterfall with Promote Solution
- 3-Tier Waterfall with Promote With Refi Solution
- Double Promote Diagram
- Double-Promote Waterfall #1 Exercise
- Double-Promote Waterfall #1 Solution
- Double-Promote Waterfall #2
- Double-Promote Returns Exhibit
- Alternate Compounding Periods
- Partnership Structure 1
- Partnership Structure 2
- 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
