Preview of Advanced Single Transaction Debt and Equity Modeling Course

Overview

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.

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 2 – Delayed Draw Funding
• Lesson 3 – Refinancing After Stabilization
• Lesson 4 – Mezzanine Loan Funding of Capital Costs
• Lesson 5 – Mezzanine Loan Payment In Kind and Participation
• Lesson 6 – Development Land Loan and Line of Credit and Land Equity
• Lesson 7 – Advanced Waterfall Structures

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. Stabilized Property Acquisition Exercise
3. Stabilized Property Acquisition Solution
4. Partially-Vacant Property Acquisition With Delayed Draw Funding Exercise
5. Partially-Vacant Property Acquisition With Delayed Draw Funding Solution
6. Partially-Vacant Property Acquisition With Delayed Draw Funding and Post-Stabilization Refinancing Exercise
7. Partially-Vacant Property Acquisition With Delayed Draw Funding and Post-Stabilization Refinancing Solution
8. Partially-Vacant Property Acquisition With Delayed Draw Mezzanine Loan and Post-Stabilization Refinancing Exercise
9. Partially-Vacant Property Acquisition With Delayed Draw Mezzanine Loan and Post-Stabilization Refinancing Solution
10. Partially-Vacant Property Acquisition With Delayed Draw Payment in Kind (PIK) and Participating Mezzanine Loan and Post-Stabilization Refinancing Exercise
11. Partially-Vacant Property Acquisition With Delayed Draw Payment in Kind (PIK) and Participating Mezzanine Loan and Post-Stabilization Refinancing Exercise
12. Development Land Loan Exercise
13. Development Land Loan Solution
14. Development Line of Credit Exercise
15. Development Line of Credit Solution
16. Development Sponsor Land Equity Exercise
17. Development Sponsor Land Equity Solution
18. IRR-based Pari Passu Preferred Return Waterfall Exercise
19. IRR-based Pari Passu Preferred Return Waterfall Solution
20. IRR-based Priority Preferred Return Waterfall (Sponsor IRR Catch-up) Exercise
21. IRR-based Priority Preferred Return Waterfall (Sponsor IRR Catch-up) Solution
22. IRR-based Priority Preferred Return Waterfall with Sponsor Profit Share Catch-Up Exercise
23. IRR-based Priority Preferred Return Waterfall with Sponsor Profit Share Catch-Up Solution
24. IRR-based Pari Passu Preferred Return Waterfall with Sponsor Profit Share Catch-Up Exercise
25. IRR-based Pari Passu Preferred Return Waterfall with Sponsor Profit Share Catch-Up Solution
26. Equity Multiple-based Waterfall Exercise
27. Equity Multiple-based Waterfall Solution
28. Hybrid Equity Multiple- and IRR-based Waterfall Exercise
29. 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:

1. Stabilized property is acquired with an interest-only senior loan.
2. 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:

1. Partially-vacant property is acquired with an interest-only loan with delayed draw capacity for the Year 1-2 TIs/LCs.
2. 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:

1. Partially-vacant property is acquired with an interest-only loan with delayed draw capacity for the Year 1-2 TIs/LCs.
2. 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 fund the suite-specific capital costs that get the property to stabilization.

Assumptions:

1. 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.
2. 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:

1. 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.
2. 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.
3. 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