Description
This Course Is For You If You Want To
This Course Is For You If You Want To:
- Efficiently expand your understanding beyond basic debt and equity structures for acquisition and development transactions
- Achieve a better handle on how debt and equity stakeholders finance value-add acquisitions that are brought to stabilization through capital expenditures
- Accelerate your career advancement by becoming the go-to analyst or associate for modeling complex financing structures
- Acquire and own a modeling library of a dozen unique debt and equity scenarios, all of which can be used as is or customized by you to suit your needs on the job
Applicable Analysis Types
- Stabilized and Value-add Acquisitions
- Ground-up Development
- All Property Types
Details
In this advanced 7-lesson course from Real Estate Financial Modeling, you will learn a variety of sophisticated debt and equity financing and equity partnership distribution techniques for single property transactions. Case studies are used of the acquisition of a non-stabilized $80MM office property and the ground-up development of a $100MM commercial property.
Specifically, you will model:
- when ownership funds capital costs with debt drawn post-closing, and with funds escrowed at closing
- mezzanine debt with payment in kind (PIK) and participation
- refinancing of outstanding principal, and maximum loan proceeds (cash-out) refinancing
- development land loan, line of credit, and sponsor land equity structures
- cash flow waterfalls with IRR and profit share catch-ups
- an equity multiple hurdle-based waterfall
- a hybrid equity multiple/IRR hurdle-based waterfall.
Where applicable, project-level operating cash flows are kept constant to allow for comparative analysis across financing structures.
This course builds significantly upon and is intended to be studied after the completion of REFM’s Level 1, 2, and 3 Bootcamps.
1. Stabilized property acquisition with a single I/O senior loan funding fully at closing
2. Partially-vacant property acquisition with a single I/O senior loan funding both at closing and on a delayed draw basis for TIs and LCs as vacancy is absorbed
3. Same as 2 above, with refinancing by an amortizing loan after stabilization
4. Same as 2 above, but with a mezzanine loan with payment in kind and current interest funding as exclusive funding source of the delayed draws
5. Same as 2 above, but with mezzanine loan participating in post-financing property cash flow
6. Integrating an accruing or current-pay land loan into a development Sources & Uses of funds projection with 3 layers of equity and 2 layers of construction debt
7. Same as 6 above, but replacement of senior construction loan with a Line of Credit
8. Integrating sponsor land equity into a development Sources & Uses of funds projection with 3 layers of equity, a land loan, and 2 layers of construction debt
9. Advanced equity joint venture partnership waterfall distribution structures:
- IRR-based Priority Preferred Return Waterfall (Sponsor IRR Catch-up)
- IRR-based Priority Preferred Return Waterfall with Sponsor Profit Share % Catch-Up
- IRR-based Pari Passu Preferred Return Waterfall with Sponsor Profit Share % Catch-Up
- Equity Multiple vs. IRR Basics
- Equity Multiple-based Waterfall
- Equity Multiple- and IRR-based Waterfall.
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 7 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 7.2 hours across the 7 lessons. Watching all the video (replaying parts where desired) and completing the exercises will take approximately 10 to 15 hours, depending on the individual.
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.
You will:
- Gain a holistic understanding of 12 unique modeling scenarios and learn what needs to be reflected in each model
- Be able to approach any financing structure with a solid analytical framework
- Be confident in your ability to solve for both debt and equity performance metrics in any situation
What You Get
- Lifetime online access to an easily-navigated 432-minute (7.2-hour) video tutorial, playable on any device including PC, Mac, phones and tablets
- Accompanying fully-unlocked, annotated Excel file, compatible with both PC and Mac, which you can repurpose for future analyses
- PDF of the slide deck used in the course.
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
ASB Capital Management
CapitalOne
CBRE Global Investors
CCRE
Citibank
ColonyNorthstar
CPPIB
CreditSuisse
Crescent Communities
Crow Holdings
DDG
DLA Piper
Greystar
GTIS Partners
Hodes Weill & Associates
Jamestown
JBG Smith
JPMorganChase
Kennedy Wilson
Lubert-Adler
Macys
Park Hill
Peterson Companies
PGIM
PN Hoffman
Prometheus
QuadReal
Related
SEO
Skanska USA
Square Mile Capital
USAA Real Estate
CBRE Global Investors
Clark Enterprises
Comstock Homes
Conor Commercial
Crescent Communities
Federal City Property
Flournoy
Four Seasons
GTIS Partners
Howard Hughes
Hoffman
HPET
Hunt Companies
Insight Property Group
LaSalle Investment
MadisonMarquette
Markwood
Mesa Capital
Midway
Miller Valentine
Panoramic Interests
PEG Development
Renaissance Downtowns
Skanska USA
Snavely Group
Trammell Crow Residential
Trion Properties
Vornado
Sample Content
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
- Stabilized Property Acquisition Exercise
- Stabilized Property Acquisition Solution
- Partially-Vacant Property Acquisition With Delayed Draw Funding Exercise
- Partially-Vacant Property Acquisition With Delayed Draw Funding Solution
- Partially-Vacant Property Acquisition With Delayed Draw Funding and Post-Stabilization Refinancing Exercise
- Partially-Vacant Property Acquisition With Delayed Draw Funding and Post-Stabilization Refinancing Solution
- Partially-Vacant Property Acquisition With Delayed Draw Mezzanine Loan and Post-Stabilization Refinancing Exercise
- Partially-Vacant Property Acquisition With Delayed Draw Mezzanine Loan and Post-Stabilization Refinancing Solution
- Partially-Vacant Property Acquisition With Delayed Draw Payment in Kind (PIK) and Participating Mezzanine Loan and Post-Stabilization Refinancing Exercise
- Partially-Vacant Property Acquisition With Delayed Draw Payment in Kind (PIK) and Participating Mezzanine Loan and Post-Stabilization Refinancing Exercise
- Development Land Loan Exercise
- Development Land Loan Solution
- Development Line of Credit Exercise
- Development Line of Credit Solution
- Development Sponsor Land Equity Exercise
- Development Sponsor Land Equity Solution
- IRR-based Pari Passu Preferred Return Waterfall Exercise
- IRR-based Pari Passu Preferred Return Waterfall Solution
- IRR-based Priority Preferred Return Waterfall (Sponsor IRR Catch-up) Exercise
- IRR-based Priority Preferred Return Waterfall (Sponsor IRR Catch-up) Solution
- IRR-based Priority Preferred Return Waterfall with Sponsor Profit Share Catch-Up Exercise
- IRR-based Priority Preferred Return Waterfall with Sponsor Profit Share Catch-Up Solution
- IRR-based Pari Passu Preferred Return Waterfall with Sponsor Profit Share Catch-Up Exercise
- IRR-based Pari Passu Preferred Return Waterfall with Sponsor Profit Share Catch-Up Solution
- Equity Multiple-based Waterfall Exercise
- Equity Multiple-based Waterfall Solution
- Hybrid Equity Multiple- and IRR-based Waterfall Exercise
- Hybrid Equity Multiple- and IRR-based Waterfall Solution
Course Content
Lesson 1 – Stabilized Acquisition Review (37 min.)
In this lesson, you will do a 30-minute review of the requisite components of an acquisition analysis with and without the use of senior debt financing. You will look at key performance indicators for both lender and equity owner. This general modeling architecture will serve as the basis for the subsequent scenarios in the course.
Assumptions:
- Stabilized property is acquired with an interest-only senior loan.
- Property is sold valued on forward NOI.
Video chapter markers:
- Overview
- Operating Cash Flow Projection
- Workflow Plan
- Uses of Funds
- Sources of Funds
- Sale
- Unlevered Cash Flow
- Loan Schedule & Lender Cash Flow
- Levered Cash Flow
- Comparison of Debt and Equity Returns
Lesson 2 – Delayed Draw Funding (49 min.)
In this lesson, you will expand on the fundamentals that were put in place in Lesson 1 by increasing the model’s periodic granularity to monthly, and its flexibility to accommodate a non-stabilized property acquisition that requires significant capital expenditures after closing. Required capex takes the form of a common area/lobby renovation to attract new tenants, and for TIs and LCs. The former will be funded by general cap ex budget escrowed at closing, and the latter will be funded by “good news money” capacity in the senior loan.
Assumptions:
- Partially-vacant property is acquired with an interest-only loan with delayed draw capacity for the Year 1-2 TIs/LCs.
- Property is stabilized in Years 1-2, and sold at the end of Year 6, valued on forward NOI.
Video chapter markers:
- Introduction
- Overview
- NOI and CFBDS&T graph
- TIs and LCs
- Common Area Renovation
- Future Capital Uses and Sources
- Common Area Renovation CapEx Account
- Other Capital Reserves
- Cash Flow Before Debt Service and Taxes
- Uses of Funds
- Purchase Price
- Yield on Price
- Future Capital Items
- Total Eligible Loan Costs
- Total Uses of Funds
- Sources of Funds
- Future Funding
- Debt Contribution
- Equity Contribution
- Total Sources of Funds
- Final LTC
- Sale
- Forward NOI
- Disposition Net Sales Proceeds
- Unlevered Net Cash Flow
- Total Investment
- Cash on Cash
- IRR
- Multiple on Equity
- Acquisition Loan
- Draw
- Interest Payment
- Repayment
- Ending Balance
- Debt Yield
- Lender Net Cash Flow
- IRR
- Total Investment
- Multiple on Invested Capital
- Lender Summary Metrics
- Levered Cash Flow section
- Loan Proceeds
- Levered Net Cash Flow
- Total Investment
- Total Equity Draw
- Check on Total Investment
Lesson 3 – Refinancing After Stabilization (81 min.)
In this lesson, you will expand the analysis to include a refinancing (permanent, amortizing loan) that triggers after the building is stabilized.
Assumptions:
- Partially-vacant property is acquired with an interest-only loan with delayed draw capacity for the Year 1-2 TIs/LCs.
- Property is stabilized in Years 1-2, refinanced, and sold at the end of Year 6, valued on forward NOI.
Video chapter markers:
- General Overview
- First Loan Repayment Overview
- Refinance Loan Sizing Overview
- Outstanding Loan Balance
- Overview of 3 Sizing Tests
- Loan-to-Value Test
- DSCR Test
- Debt Yield Test
- Lesser of the 3
- Principal Borrowed
- Front-End Loan Fee
- Excess Proceeds to Equity
- First Loan Repayment
- LTV at Repayment
- Lowest DSCR
- Average DSCR
- Lowest Debt Yield
- Average Debt Yield
- Review of Graphs
- Second Loan Debt Schedule Overview
- Loan Calendar Month Counter
- Beginning Balance,Draw,Interest Payment
- Principal Payment
- Balloon Payment
- Ending Balance
- Front-End Loan Fee
- DSCR
- Debt Yield
- Lender Net Cash Flow
- IRR
- LTV at Funding
- LTV at Repayment
- Lowest DSCR
- Average DSCR
- Lowest Debt Yield
- Average Debt Yield
- Loan Points
- Debt Service
- Cash Flow After Financing Costs
- Loan Proceeds Overview
- Repayment of First Loan
- Excess Proceeds to Equity
- Refinance Loan Repayment
- Levered Net Cash Flow
- Cash Flow Excluding Capital Events
- Cash on Cash
Lesson 4 – Mezzanine Loan Funding of Capital Costs (37 min.)
In this lesson, you will expand the analysis to include a separate, mezzanine loan to fund the suite-specific capital costs that get the property to stabilization.
Assumptions:
- Partially-vacant property is acquired with an amortizing loan. An interest-only mezzanine loan is secured to fund the Year 1-2 TIs/LCs when they arise.
- Property is stabilized and then refinanced and operated until it is sold at the end of year 6, valued on forward NOI.
Video chapter markers:
- Overview
- Uses of Funds
- Sources of Funds
- Sizing of Mezzanine Loan
- Mezzanine LTC
- Acquisition Loan Schedule
- Draw
- Interest Payment
- Principal Payment
- Balloon Repayment
- Ending Balance
- Front-End Fee
- DSCR
- Debt Yield
- Lender Net Cash Flow
- Mezzanine Loan Schedule
- Beginning Balance
- Draw
- Interest Payment
- Repayment
- Ending Balance
- Front-End Fee
- DSCR
- Debt Yield
- Lender Net Cash Flow
- Initial LTC
- Repayment LTV
- Lowest DSCR
- Lowest Debt Yield
- Average DSCR
- Average Debt Yield
- NPV
- Financing Cash Flows Section
- Acquisition Loan Points
- Mezzanine Loan Points
- Mezzanine Debt Service
- Cash Flow After Financing Costs
- Acquisition Loan Proceeds
- Mezzanine Loan Proceeds
- Mezzanine Loan Repayment
- Refinancing Proceeds Repayment of Outstanding Balances
- In-Place Loans Outstanding Balances
- Levered Cash Flow
- Equity Draw
- Check on Investment Amount
Lesson 5 – Mezzanine Loan Payment In Kind and Participation (30 min.)
In this lesson, you will add complexity to the mezzanine loan by enabling payment in kind (accruing interest) as well as cash flow participation.
Assumptions:
- Partially-vacant property is acquired with an amortizing loan. An interest-only mezzanine loan is secured to fund the Year 1-2 TIs/LCs when they arise.
- A portion of the mezzanine loan interest accrues for a given time period; the mezz loan stays in place and participates in cash flow above a $ threshold after stabilization.
- Property is refinanced after stabilization; finally, it is sold at the end of year 6, valued on forward NOI.
Video chapter markers:
- Overview
- Avoiding Negative Cash Flow
- Current vs. Accrued Interest Inputs
- Financing Structure Review
- Participation Inputs and Structure
- Draw
- Current Interest
- Accrued Interest
- Cumulative Accrued Interest
- Repayment
- Ending Balance
- Graph Inspection and Loan Block Review
- DSCR
- Participation
- Debt Yield
- Mezzanine Net Cash Flow
- Big Picture
- Outstanding Loan Balance
- Participation
- Levered Net Cash Flow
- Big Picture Review
- Returns Review
- Insufficient Funds Validations
Lesson 6 – Development Land Loan, Line of Credit and Land Equity (58 min.)
In this lesson, you will switch gears to look at various ground-up development financing structures, both debt and equity, that enable full capitalization of the development project.
Video chapter markers:
- Development Land Loan Exercise Overview
- Uses of Funds Overview
- Positive Revenue After Cash Financing Costs
- Exercise Set-Up
- Equity Contributions Relationship
- Sponsor and Partner Equity Draw
- Third Party Investor Draw
- Mezzanine Loan Draw
- Senior Loan Draw
- Land Loan Beginning Balance
- Land Loan Draw
- Accrued Interest
- Cash Interest Payments
- Repayment and Ending Balance
- Accrued Interest formula correction
- Cash Financing Costs formula update
- Capitalized Financing Costs formula update
- Sponsor Equity Draw formula update
- Partner Equity Draw formula update
- Third Party Investor Equity Draw formula update
- Mezzanine Lan Draw formula update
- Senior Lan Draw formula update
- Total Sources of Funds
- Loan Repayments
- Interest Calculation Conventions
- Senior Loan Draw revisited
- Development Line of Credit Exercise Overview
- Floating Interest Rate
- Line of Credit Draw
- Funded Interest Reserve
- Cash Interest Payments
- Sponsor Land Equity Exercise Overview
- Contributed Land Value use of funds
- Land Equity Contribution source of funds
- Rebalancing Equity Shares
- Cash Financing Costs formula update
- Cash Financing Costs formula update
- Sponsor Direct Project Costs Draw formula update
- Sponsor Pro Rata Cash Financing Costs and Operating Deficit Funding formula updates
- Sponsor Total Funding formula update
- Partner Direct Project Costs Draw formula update
- Third Party Investor Direct Project Costs Draw formula update
- Mezzanine Loan Draw formula update
- Senior Loan Draw formula update
- Total Sources of Funds formula update
- Deleting the Land Loan block
Lesson 7 – Advanced Waterfall Structures (140 min.)
In this lesson, you will focus on 5 unique equity joint venture monthly-based waterfall structures:
- 3-tier IRR-based Priority Preferred Return Waterfall with Sponsor IRR Catch-up
- 3-tier IRR-based Priority Preferred Return Waterfall with Sponsor Profit Share % Catch-Up
- 3-tier IRR-based Pari Passu Preferred Return Waterfall with Sponsor Profit Share % Catch-Up
- 4-tier Equity Multiple-based Waterfall
- 4-tier Hybrid Equity Multiple- and IRR-based Waterfall.
Video chapter markers:
- Pari Passu Preferred Return Exercise Overview
- Tier 1 Overview
- Investor Beginning of Period Balance
- Investor Investment
- Investor Preferred Return Accrual
- Investor Preferred Return Distribution
- Investor End of Period Balance
- Investor Cash Flow
- Investor XIRR
- Sponsor Cash Flow
- Sponsor XIRR
- Tier 1 Remaining Cash to Distribute
- Tier 2 Overview
- Investor Distribution
- Sponsor Equity Distribution
- Sponsor Promote Distribution
- Tier 2 Remaining Cash to Distribute
- Deal Summary Overview
- Deal Total Investment
- Deal Gross Distributions
- Deal Levered Net Cash Flow
- Deal XIRR
- Deal Multiple on Equity
- Investor Summary/Net Cash Flow
- Investor Investment
- Investor Gross Distribution
- Investor Net Cash Flow
- Investor XIRR
- Investor Multiple on Equity
- Comparison of Deal vs. Investor Returns
- Sponsor Summary/Equity Investment
- Sponsor Equity Cash Flow
- Sponsor Promote Cash Flow
- Sponsor Net Cash Flow
- Sponsor Summary Values
- Sponsor XIRR
- Sponsor Multiple on Equity
- Check on Total Profit
- Review of Graphs
- Priority Preferred Return Exercise Overview
- Investor Preferred Return Distribution
- Tier 2 Sponsor IRR Catch-Up
- Sponsor Beginning of Period Balance
- Sponsor Investment
- Sponsor Accrual
- Sponsor Distribution
- Sponsor End of Period Balance
- Sponsor Cash Flow
- Tier 2 Remaining Cash to Distribute
- Comparison with Pari Passu Structure
- Pari Passu Pref and % Catch-up Exercise Overview
- Tier 1 Overview
- Investor Return of Capital
- Investor Preferred Return Profit
- Investor Cash Flow
- Investor Share of Cumulative Profits
- Sponsor Return of Capital
- Sponsor Preferred Return Profit
- Sponsor Cash Flow
- Sponsor Share of Cumulative Profits
- Total Profits Check
- Cumulative Profit Distribution Through Tier 1
- Tier 2 Overview
- Sponsor Preferred Return Profit Distribution
- Sponsor Catch-up Profit Distribution
- Share of Cumulative Profits
- Sponsor Cash Flow Through Catch-up Tier
- Sponsor XIRR
- Investor Catch-up Profit Distribution
- Investor Cash Flow Including Catch-up Distribution
- Investor XIRR as of Tier 2
- Total Catch-up Amounts Distributed
- Cumulative Catch-up Amounts Distributed
- Cumulative Profits Distributed through Catch-up Tier
- Initial True-up of Catch-up Distribution to Sponsor
- Tier 2 Remaining Cash to Distribute
- Tier 3 Residual Split
- Final True-up of Catch-up Distribution to Sponsor
- Priority Pref with IRR and % Profits Catch-up Structure Overview
- Sponsor Catch-up Profit Distribution Goal Seek
- Slide Discussion 1
- Equity Multiple Waterfall Structure Overview
- New Deal-level Cash Flow and Deal Timeline Characteristics
- Tier 1 Hurdle
- Tier 1 Investor Beginning of Period Balance
- Investor Equity Investment
- Tier 1 Hurdle Investor Accrual
- Investor Return of Capital
- Tier 1 Multiple Distribution
- Tier 1 End of Period Balance
- Investor Equity Investment and Distribution and Net Cash Flow
- Sponsor Equity Cash Flow
- Remaining Cash to Distribute
- Tier 2 Beginning of Period Balance
- Investor Equity Investment
- Tier 2 Investor Accrual
- Investor Return of Capital and Tier 1 Multiple Distribution
- Tier 2 Distribution
- Investor End of Period Balance
- Investor Cash Flow
- Sponsor Equity Cash Flow
- Sponsor Promote Cash Flow
- Remaining Cash to Distribute
- Tier 3 Beginning of Period Balance and Investor Equity Investment
- Investor End of Period Balance
- Tier 3 Investor Accrual
- Investor Return of Capital and Tier 1 Multiple Distribution and Tier 2 Distribution
- Tier 3 Distribution
- Tier 4
- Conceptual Simplicity Desirability
- Slides Introduction of Hybrid Structures
- Why Hybrid Hurdles Exist
- Hybrid Waterfall Excel Exercise Overview
- Cumulative vs. Compounded Nature of Equity Multiple and IRR
- Tier 1 Net Cash Flow and Equity Multiple Achieved
- Tier 2 Multiple Incremental Distribution Back-Solving
- Hybrid Tier General Approach
- Tier 2 Equity Multiple Hurdle Resultant IRR and Multiple
- Tier 2 IRR Hurdle Net Cash Flow and XIRR and Resultant Multiple
- Tier 2 Distribution Investor Cash Flow
- Tier 2 Distribution Sponsor Equity Cash Flow
- Tier 2 Distribution Sponsor Promote Cash Flow
- Tier 2 Remaining Cash to Distribute
- Tier 3 Overview
- Tier 3 Equity Multiple Hurdle Remaining Cash to Distribute and Net Cash Flow and Multiple Achieved
- Tier 3 IRR Hurdle Remaining Cash to Distribute and Net Cash Flow and Multiple Achieved
- Tier 3 Investor Cash Flow Distribution
- Tier 3 Sponsor Equity and Promote Cash Flow Distributions
- Tier 3 Remaining Cash to Distribute
- Tier 4 Residual Split
- Review of Dominant Hurdle Bases
- Review of Graphs
- Understanding the Nuances of Multiple Protections and Sponsor Hurdle Gaming Incentives
