What it is

A self-study module that can make you more knowledgeable and skilled, improving your in-class performance and competitiveness as a job applicant.

This self-study module gives you:

 Online access to 160+ minutes of easily-navigated video instruction, playable on any device including PC, Mac, phones and tablets

 Accompanying fully-unlocked Excel files, compatible with both PC and Mac

  1 online Certification Exam, which bestows REFM Certification In Excel For Real Estate.

In this 80-minute video tutorial, you will learn waterfall modeling, the most valuable and desired set of financial modeling skills in real estate. You will learn advanced techniques and topics related to single property transactions, not multi-transaction private equity funds.

Topics covered include: 

• Rationale behind targeting disproportionate returns to the Sponsor
• How to achieve disproportionate returns through fees and partitioning of cash flows
• Preferred Return overview
• Preferred Return variations with respect to priority of payment
• Preferred Return in context (Payment Types A, B and C)
• Nature of Preferred Return (Non-compounded/compounded, non-cumulative/cumulative)
• Annual Preferred Return Exercise
• Monthly Preferred Return Exercise
• Waterfall Distribution overview, with Animation
• Promote Mechanism overview and modeling
• Look-Back Internal Rate of Return (IRR) Method
• 3-Tier Waterfall modeling
• Double-Promote, 5-Tier Waterfall modeling
• Double-Promote Exercise
• Alternate Compounding Periods: Monthly, Daily, Quarterly
• Sample Partnership Structures
• Claw-Back overview and modeling
• Claw-Back Exercise.

This product is the preparatory material for REFM Excel for Real Estate Level 3 Certification.

The Excel file contains the following tabs:

1. Preferred Return Non-Compounded Annual
2. Preferred Return Non-Compounded Annual Solution
3. Preferred Return Compounded Monthly
4. Preferred Return Compounded Monthly Solution
5. 3-Tier Annual Waterfall with Look-Back
6. Double-Promote Profit Sharing
7. Double-Promote Monthly Waterfall # 1 with Look-Back
8. Exercise – Rebuild Tier #3
9. Double-Promote Monthly Waterfall #2 with Look-Back
10. Double Promote Returns Summary Exhibit
11. Alternate Compounding Periods
12. Sample Partnership Structure 1
13. Sample Partnership Structure 2
14. Sample Partnership Structure 3
15. Claw-Back.

In this 84-minute video tutorial, you will learn big picture real estate private equity fund basics, the general legal structure of funds based in the U.S., how to set up a fund-level model, and considerations for running sensitivity analyses. The case study used is a Core-plus fund that acquires existing income-producing properties.

Topics covered include:

• Big picture fund basics:
  • Capital sources
  • Major types of funds
  • General legal structure
  • Rationale behind disproportionate reward to the General Partner/Fund Manager through a promote structure
• Modeling and then replicating a prototypical transaction starting at Net Operating Income and ending at Levered Cash Flow
• Weaving in and rolling up and modeling multiple transactions on the fund-level
• Considerations for running sensitivities
• Projection of management fees
• Individual transaction typical partner joint venture structure and modeling
• Fund-level clawback

Video Tutorial Contents

• Introduction (30 sec)
• Big Picture Fund Basics (90 sec)
• Major Types of Funds (2.75 min)
• U.S. Legal Structure (90 sec)
• Rationale for Promoted Interest (90 sec)
• Preferred Return and Promote Structure (90 sec)
• Clawback Introduction (15 sec)
• Fund Modeling Approach (2 min)
• Spreadsheet Tab Ecosystem Overview (30 sec)
• Prototypical Transaction Tab Overview (1.25 min)
• Spreadsheet Formatting Conventions and Training Format (60 sec)
• Typical Transaction Tab (5 sec)
• Acquisition Assumptions (15 sec)
• Financing Assumptions (20 sec)
• Cash Flow Assumptions (30 sec)
• Exercise #1 and Solution (20 sec)
• General Expense Inflation and CapEx Assumptions (20 sec)
• Disposition Assumptions (30 sec)
• Timeline and NOI (90 sec)
• Capital Expenditures (40 sec)
• Financing Section (20 sec)
• Exercise #2 (20 sec)
• Exercise #2 Solution (20 sec)
• Ending Balance (20 sec)
• Total Debt Service and Debt Service Coverage Ratio (20 sec)
• Net Distributable Cash Flow (10 sec)
• Leveraged Yield on Equity (40 sec)
• Residual Proceeds (1.2 min)
• Unlevered and Levered Cash Flow (35 sec)
• IRR (15 sec)
• Manager/Fund Cash Flow (30 sec)
• Exercise #3 (40 sec)
• Exercise #3 Solution (90 sec)
• Modeling Approach Review (10 sec)
• Fund Tab Overview (40 sec)
• Fund tab (10 sec)
• General Fund Level Assumptions (60 sec)
• Acquisition Timing Assumptions (30 sec)
• Waterfall Hurdle Rate Assumptions (90 sec)
• Operating Assumptions (40 sec)
• Net Cash Flow to Fund (30 sec)
• Investment #1 Net Cash Flow to Fund (50 sec)
• Resetting the Transaction Tab (70 sec)
• Exercise #4 (75 sec)
• Exercise #4 Solution (80 sec)
• Fund Level NCF From Investments (5 sec)
• Cumulative Fund Level Net Cash Flow (20 sec)
• Fund Level Expenses (5 sec)
• Start Up Costs (10 sec)
• Acquisition Fee (75 sec)
• Exercise #5 (20 sec)
• Exercise #6 (35 sec)
• Exercise #5 Solution (75 sec)
• Exercise #6 Solution (20 sec)
• Fund Level Net Cash Flow After Expenses (25 sec)
• General Partner Cash Flow (30 sec)
• Remaining Cash Flow for LP and Fund Manager (10 sec)
• Management Fees (10 sec)
• Exercise #7 (75 sec)
• Exercise #7 Solution (2.5 min)
• Net Cash Flow For LP and Fund Manager (15 sec)
• Cumulative Cash Flow Net of GP Cash Flow (10 sec)
• Net Cash Flow To Fund (GP, LP and Fund Manager) (30 sec)
• LP Capital Account Beginning of Period Balance (10 sec)
• LP Equity Drawn (10 sec)
• Preferred Return Accrual (20 sec)
• Exercise #8 (5 sec)
• Exercise #8 Solution (45 sec)
• LP Pro Rata Cash Flow (60 sec)
• LP Account Ending Balance (1.5 min)
• Partnership Cash Flow Distribution (10 sec)
• LP Investments/Distributions (10 sec)
• Net Cash Available to LP and Fund Manager (20 sec)
• Fund Manager Promote Share (90 sec)
• Exercise #9 (90 sec)
• Exercise #9 Solution (3.5 min)
• LP Share of Net Cash (15 sec)
• GP Cash Flow (5 sec)
• Total Partnership Cash Flow (50 sec)
• Summary of Investor Cash Flows (30 sec)
• Summary of Equity Investment (80 sec)
• Checking Total Equity Investment Amount (50 sec)
• Fund Returns Summary (2 min)
• Individual Partner Promote Tab (25 sec)
• Individual Transaction Equity Capitalization (20 sec)
• Individual Transaction JV Structure (90 sec)
• Individual Transaction Partner Promote Exercise Tab (35 sec)
• Partner Waterfall Structure (1.2 min)
• Capital Contributions (30 sec)
• Gross Proceeds to JV (15 sec)
• Gross Proceeds Available to Manager/Fund (30 sec)
• Tier 1 (40 sec)
• Beginning of Period Balance (10 sec)
• Manager/Fund Injections (30 sec)
• Manager/Fund Preferred Return Accruals (10 sec)
• Tier 1 Manager/Fund Distributions (80 sec)
• End of Period Manager/Fund Balance (10 sec)
• Manager/Fund Cash Flow (20 sec)
• Partner Equity-related Cash Flow (40 sec)
• Partner Promote-related Cash Flow (25 sec)
• Tier 1 Remaining Cash to Distribute (1.2 min)
• Tier 2 (50 sec)
• Exercise #10 (25 sec)
• Exercise #10 Solution (1.3 min)
• Tier 2 Manager/Fund Cash Flow (5 sec)
• Tier 2 Partner Equity and Promote Cash Flows (5 sec)
• Tier 2 Remaining Cash to Distribute (15 sec)
• Tier 3 (20 sec)
• Returns Summary (70 sec)
• Clawback (4.5 min)