0% Complete
0/14 Steps

Preview of Real Estate Private Equity Fund Modeling Tutorial

Lesson
Materials

Overview

In this lesson, you will learn about 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. Also includes modeling of individual transaction partner promote structure, and a fund-level clawback.

Instructions

1. Download the files from the Materials tab above
2. Watch the video, and follow along and perform the exercises in the Excel file.

Video Sample

Topics Covered

  • 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
  • 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)

REFM_Private_Equity_Fund_Modeling_v_12_0

REFM_Private_Equity_Fund_Modeling_Slides_v12_0