The Advanced Real Estate Pro Forma Modeling Master Class
Last updated 1/2023
MP4 | Video: h264, 1280x720 | Audio: AAC, 44.1 KHz
Language: English | Size: 8.12 GB | Duration: 14h 22m
Last updated 1/2023
MP4 | Video: h264, 1280x720 | Audio: AAC, 44.1 KHz
Language: English | Size: 8.12 GB | Duration: 14h 22m
The Complete Guide To Creating An Institutional-Quality Commercial Real Estate Pro Forma Model From Scratch In Excel
What you'll learn
Learn how to build dynamic, institutional-grade commercial real estate pro forma investment models from scratch for office, industrial, and retail properties
Learn how to model commercial lease structures and reimbursements all in Excel (without expensive software)
Learn how to create institutional, professionally formatted commercial real estate pro forma models to present to investors and partners
Requirements
Access to Microsoft Excel for Mac or PC, Mac Numbers, or Google Sheets
A basic understanding of real estate finance and financial modeling is recommended
Description
Want to learn how to build professional, dynamic, institutional-quality commercial real estate pro forma models from scratch for office, retail, and industrial properties in Microsoft Excel? This course will take you by the hand and walk you step-by-step through the entire process of building a complete, dynamic, eight-tab commercial real estate pro forma model, starting with a blank Excel workbook ending with a complete commercial real estate pro forma acquisition model that YOU'VE created, from scratch. When I was first learning real estate financial modeling, I would download real estate investment models or real estate investment calculators on the internet, only to feel overwhelmed and frustrated by the complex functions and formulas everywhere in the Excel file. And once I felt like I was getting used to one of these things, I still was petrified to touch or change anything because I didn't want to "break" the model. And honestly, even if I understood it and I believed the model was working correctly, I still had no idea if I could even trust the calculations in the first place. If you've ever felt any of these things, this course will pull back the curtain and uncover the "Black Box" that most real estate financial models appear to be. By the end of this course, you'll be able to build a dynamic, professional-quality commercial real estate pro forma model from scratch, and by the time you finish the last lecture, you'll have an institutional-quality model that you've built from start to finish.This course will teach you how to build a commercial real estate pro forma investment model the way the largest and most sophisticated private equity real estate firms look at deals. At the end of this course, you will be able to:Build an institutional-quality, dynamic commercial real estate pro forma investment model for office, retail, and industrial properties from scratch (without expensive software)Model advanced real estate financing structures like mezzanine debt, future funding for renovation projects, prepayment penalties, dynamic loan sizing based on multiple constraints, and more.Create a dynamic sources and uses table that tracks equity investments throughout the project, so you'll know exactly how much you'll need to fund a deal up-frontBuild dynamic working capital accounts to automatically fund operating shortfalls to prevent unexpected equity "capital calls" to investorsModel complex commercial real estate lease structures in Excel including NNN, FSG, MG, and BYS reimbursement structures, custom rent escalation schedules, percentage rent, and moreCreate dynamic re-leasing scenarios based on renewal probability and re-leasing assumptions to create future lease cash flows on autopilotModel investor distributions on a monthly, quarterly, and annual basis and be able to switch between the three options quickly and easilyCreate custom charts and graphs that automatically populate in your model, that you can use to present more clearly and professionally to investors, lenders, and partnersBuild formula "checks" to error-proof your work and feel confident your calculations are accurate and can be trustedThis course is perfect for you if:You're a college student or graduate student looking to break into real estate investment after graduation, and you're looking to add the key technical skill sets to your arsenal that will put you head and shoulders above the competition and allow you to land a lucrative career opportunity in the fieldYou're an existing real estate professional looking to advance your career, increase your compensation, and break into the real estate investment industry.You've have a basic understanding of real estate finance and financial modeling, and you're looking to take your real estate financial modeling skill set to an expert level to land a better job, make more money, or do more profitable real estate deals.Here's what our students have had to say:★★★★★ "This course really has it all. I model deals for experienced private equity real estate investors, and the principals I work for had never used a proforma as dynamic, versatile, and visually appealing as this model. If you need to analyze a commercial deal you should absolutely take the time to work through this course. Thanks, Justin! Already looking forward to the next course."★★★★★ "Best class I've taken, truly builds a phenomenal model with institutional quality complexity. Take this class!"★★★★★ "Amazing, above expectations!"If you have a solid understanding of real estate finance, and you're looking to apply that knowledge to analyze commercial real estate investment opportunities, enroll now and let's get started building this model together today. Looking forward to having you in the course!
Overview
Section 1: Introduction
Lecture 1 Introduction
Lecture 2 A Sneak Peek Into What You'll Build
Lecture 3 Who This Course Is For
Lecture 4 About This Course & Student Success Stories
Lecture 5 Key Terms & Definitions Cheat Sheet
Lecture 6 Excel Shortcuts & Hotkeys Cheat Sheet
Lecture 7 Disclaimer
Section 2: The Advanced Real Estate Pro Forma Model
Lecture 8 The 8 CRE Pro Forma Model Tabs
Lecture 9 Building Out The Summary Dashboard
Lecture 10 Modeling Dynamic Commercial Real Estate Leases
Lecture 11 Modeling CRE Operating Expenses
Lecture 12 Building Out The Monthly Cash Flow Statement
Lecture 13 Modeling The Annual Cash Flow "Roll-Up"
Lecture 14 Building Out The Financing Tab
Lecture 15 Construction Budget Modeling & Automatic "Checks"
Section 3: Advanced Real Estate Pro Forma Modeling Fundamentals
Lecture 16 How To Get The Most Out Of This Course
Lecture 17 Excel Financial Modeling Best Practices & Color Coding
Lecture 18 Removing Gridlines
Lecture 19 The Entire Sheet Formatting Trick
Lecture 20 Workbook & Iterative Calculations
Lecture 21 Advanced Excel Settings
Lecture 22 Downloading Macros (Optional)
Section 4: Part 1 - Building Out The Summary Dashboard
Lecture 23 Building Out The Property Details
Lecture 24 Formatting The Property Details
Lecture 25 Building Out Parking Information
Lecture 26 Modeling Key Acquisition Inputs
Lecture 27 Modeling Sale Information
Lecture 28 Investor Distribution Frequency & Project-Level Fees
Lecture 29 Building Out The Summary Details
Lecture 30 The Capital Budget Breakdown
Lecture 31 Sources & Uses - Part 1
Lecture 32 Sources & Uses - Part 2
Lecture 33 Building Out Unlevered & Levered Return Metrics
Lecture 34 Modeling Sale Details & The Formula Build-Out
Lecture 35 IRR Partitioning
Lecture 36 Sensitivity Analysis Build-Out
Lecture 37 Combining Dynamic Text Values
Lecture 38 Creating Dynamic Hold Period Intervals
Lecture 39 Sensitivity Analysis Finalization
Lecture 40 The Consolidated Rent Roll
Lecture 41 Modeling The Weighted Average Lease Term (WALT)
Lecture 42 Model Download & Review
Section 5: Part 2 - Dynamic Commercial Real Estate Lease Modeling
Lecture 43 Building Out The CRE Leases Tab
Lecture 44 In-Place Tenant Details
Lecture 45 Formatting The In-Place Tenant Details
Lecture 46 Building Out The Expense Reimbursement Structure
Lecture 47 Modeling The Rent Escalation Schedule
Lecture 48 Re-Leasing Assumptions
Lecture 49 Re-Leasing Costs
Lecture 50 Modeling Percentage Rent
Lecture 51 Tenant Cash Flow Timeline Build-Out
Lecture 52 Timeline Formatting & Tenant Set-Up
Lecture 53 Modeling In-Place Base Rent
Lecture 54 The In-Place Operating Expense Build-Out
Lecture 55 Modeling In-Place Reimbursements
Lecture 56 Modeling Percentage Rent
Lecture 57 Percentage Rent Update
Lecture 58 Modeling Up-Front Free Rent
Lecture 59 Modeling Re-Leasing Base Rent
Lecture 60 Modeling Re-Leasing Free Rent
Lecture 61 Modeling Re-Leasing Reimbursements
Lecture 62 Modeling Up-Front Tenant Improvements
Lecture 63 Modeling Up-Front Leasing Commissions
Lecture 64 Modeling Re-Leasing Tenant Improvements
Lecture 65 Modeling Re-Leasing Leasing Commissions
Lecture 66 Total Suite Cash Flow Finalization
Lecture 67 Creating Additional Leases
Lecture 68 Creating Additional Lease Cash Flows
Lecture 69 Formatting & Pro Rata Share Calculations
Lecture 70 Grouping Tenants
Lecture 71 Model Download & Review
Section 6: Part 3 - CRE Expense Modeling
Lecture 72 Building Out The Expenses Tab
Lecture 73 T-12 & Hold Period Operating Expense Modeling
Lecture 74 Modeling Operating Expense Assumptions
Lecture 75 Property Tax Information Inputs
Lecture 76 Modeling Credit Loss & Property Tax Growth Rates
Lecture 77 Property Tax Modeling
Lecture 78 Monthly Expense Modeling & Formatting
Lecture 79 Modeling Monthly Property Tax Cash Flows
Lecture 80 Modeling Insurance
Lecture 81 Modeling CAM & Other Expenses
Lecture 82 Model Download & Review
Section 7: Part 4 - Building Out The Monthly Cash Flows
Lecture 83 Introduction To The Monthly Cash Flow Tab
Lecture 84 Building Out The Monthly Cash Flow Timeline
Lecture 85 Modeling Revenue & Free Rent
Lecture 86 Modeling Credit Loss & Net Rental Income
Lecture 87 Modeling CAM Reimbursements & Percentage Rent
Lecture 88 Modeling Operating Expenses
Lecture 89 Modeling TIs & LCs
Lecture 90 Modeling Capital Reserves
Lecture 91 Modeling Acquisition Information
Lecture 92 Modeling Sale Information
Lecture 93 Building Out The Loan Information Section
Lecture 94 Model Download & Review
Section 8: Part 5 - Modeling Advanced Financing Structures
Lecture 95 Introduction To The Financing Tab
Lecture 96 Creating The Loan Information Table
Lecture 97 Building Out The Loan Metrics
Lecture 98 Interest Rate Modeling
Lecture 99 Loan Periods & Prepayment
Lecture 100 Sizing The Acquisition Loan - LTV & LTC
Lecture 101 Sizing The Acquisition Loan - DSCR
Lecture 102 Sizing The Acquisition Loan - Debt Yield
Lecture 103 Sizing The Mezzanine Loan - LTV & LTC
Lecture 104 Sizing The Mezzanine Loan - DSCR & Debt Yield
Lecture 105 Sizing The Refinance - LTV & LTC
Lecture 106 Sizing The Refinance - DSCR & Debt Yield
Lecture 107 The Acquisition Financing Build-Out
Lecture 108 Dynamic Acquisition Loan Balance Modeling
Lecture 109 Modeling Additional Loan Proceeds
Lecture 110 Naming Cells For Faster Modeling
Lecture 111 Chatham Forward Curves (Link)
Lecture 112 Chatham Forward Curves
Lecture 113 Building In The Floating Interest Rate Index
Lecture 114 Creating Dynamic Fixed & Floating Interest Rates
Lecture 115 Building Out The Total Loan Payment
Lecture 116 Total Loan Payment Formula Update
Lecture 117 Modeling Principal & Interest Payments
Lecture 118 Modeling The Acquisition Loan Payoff & Prepayment Penalty
Lecture 119 Creating Acquisition Financing "Checks"
Lecture 120 Mezzanine Financing Build-Out
Lecture 121 Modeling Mezzanine Debt Payments
Lecture 122 Refinance Build-Out
Lecture 123 Modeling Additional Refinance Loan Proceeds
Lecture 124 Modeling Total Refinance Loan Payments
Lecture 125 Modeling Refinance Principal & Interest Payments
Lecture 126 Modeling The Refinance Loan Payoff & Prepayment Penalty
Lecture 127 Refinancing Prepayment Penalty Formula Update
Lecture 128 Total Payment Formula Update
Lecture 129 SUMIF Trigger Build-Out
Lecture 130 Model Download & Review
Section 9: Part 6 - Creating A Dynamic Construction Budget
Lecture 131 Introduction To The Construction Budget Tab
Lecture 132 Building Out The Construction Budget Line Items
Lecture 133 Construction Budget Costs & Timing
Lecture 134 Straight-Line Construction Cost Modeling
Lecture 135 Construction Budget Checks & Formatting
Lecture 136 Construction Subtotals & MAX and MIN IF
Lecture 137 Modeling Contingencies
Lecture 138 Model Download & Review
Section 10: Part 7 - Building Out The Monthly Cash Flow Tab Part 2
Lecture 139 Modeling Management Fees & Construction Expenses
Lecture 140 Partnership Expense Modeling & Acquisition/Refinance Loan Proceed Funding
Lecture 141 How To Use SUMIF To Model Additional Loan Proceeds
Lecture 142 Building In Debt Service & Modeling The Loan Payoff
Lecture 143 Modeling Loan Fees
Lecture 144 Creating Dynamic Working Capital Funding
Lecture 145 Investor Distribution Modeling - Part 1
Lecture 146 Investor Distribution Modeling - Part 2
Lecture 147 Investor Distribution Modeling - Part 3
Lecture 148 Cash Flow SUM & Formatting Finalization
Lecture 149 Model Download & Review
Section 11: Part 8 - Creating The Annual Cash Flow "Roll-Up"
Lecture 150 Introduction To The Annual CF Tab
Lecture 151 The Annual Cash Flow "All-In-One" Formula
Lecture 152 Modeling Unlevered Cash-on-Cash Returns
Lecture 153 Modeling Levered Cash-on-Cash Returns
Lecture 154 Model Download & Review
Section 12: Part 9 - Dynamic Chart Creation & Finalizing The Advanced Pro Forma Model
Lecture 155 Modeling Asset Management Fees
Lecture 156 Property Tax Finalization - Dynamic Assessed Value Modeling
Lecture 157 Modeling Tenant Health Ratio
Lecture 158 Modeling DSCR, Debt Yield, & Total Cost
Lecture 159 Capital Budget Modeling
Lecture 160 Sources & Uses Finalization
Lecture 161 Modeling Levered Return Metrics (IRR, Equity Multiple, & Cash-on-Cash Return)
Lecture 162 Modeling Unlevered Return Metrics (IRR, Equity Multiple, & Cash-on-Cash Return)
Lecture 163 Building Out The Property Sale Details
Lecture 164 Building Out The Consolidated Rent Roll
Lecture 165 Modeling In-Place Rent
Lecture 166 Reimbursements & Lease Expirations
Lecture 167 Partitioning The IRR - Part 1
Lecture 168 Partitioning The IRR - Part 2
Lecture 169 XNPV Formula Update
Lecture 170 Creating The "IRR Partitioning" Chart
Lecture 171 Creating The "Base Rent Makeup" Chart
Lecture 172 Creating The "Dynamic Operating Metrics Over Time" Chart
Lecture 173 Dynamic Operating Metric Cash Flow Build-Out - Part 1
Lecture 174 Dynamic Operating Metric Cash Flow Build-Out - Part 2
Lecture 175 The Dynamic Operating Metric Chart Build-Out
Lecture 176 Creating The "DSCR Over Time" Chart - Part 1
Lecture 177 Creating The "DSCR Over Time" Chart - Part 2
Lecture 178 Running The Sensitivity Analysis
Lecture 179 Creating The "Checks" Tab
Lecture 180 Building Out The Model Checks & Conditional Formatting
Lecture 181 How To Use What You've Built
Lecture 182 Operating Shortfalls & IRR Partitioning Finalization
Lecture 183 Final Model Updates
Lecture 184 Model Download & Review - We're Done!
Section 13: Wrapping Up
Lecture 185 Where To Go From Here
Lecture 186 A Final Word
Section 14: Bonus Lecture
Lecture 187 Bonus Lecture - The Next Step
Real estate investors and professionals who want to learn how to create dynamic, institutional-quality commercial real estate pro forma models from scratch for office, industrial, and retail properties,Current real estate professional who want to become an expert in real estate financial modeling to advance their career and make more money,Aspiring commercial real estate investment professionals who want to begin a lucrative career in commercial real estate investing