What you’ll learn
- The ability to build a functional, dynamic real estate financial model from scratch.
- The skill set to model real estate transactions for multiple different asset types and solve for the most essential investment metrics evaluated by large institutions and private equity groups.
- A substantial increase in speed and efficiency in Microsoft Excel by learning powerful tools and functions.
- A mastery of the foundational analytical and financial modeling tools necessary to break into the real estate private equity industry and model complex commercial real estate
Introduction
Welcome & What We Will Cover
Who This Course Is For
About The Instructor & Student Success Stories
Download Me First!
Disclaimer
The Property Cash Flow Statement
The Property Cash Flow Statement Overview
Effective Gross Revenue – Part 1
Effective Gross Revenue – Part 2
Operating Expenses
Net Operating Income (NOI)
Capital Expenses
Debt Service
Purchase, Sale, & Financing Metrics
Unlevered & Levered Cash Flow
A Note About Income Taxes
Real Estate Financial Modeling Fundamentals
Setting Expectations
Real Estate Financial Modeling 101 & Dynamic Modeling
Absolute & Mixed References
Custom Formatting
Drop-Down Lists
Real Estate Financial Modeling Color Codes
Gridlines & The “Steak Sauce” Cell
Real Estate Financial Modeling Excel Hotkeys & Shortcuts
Navigating Worksheets & Workbooks
Controlling Columns & Rows
Text Formatting
Font Style
Borders
Alignment
Excel Function Shortcuts
Copy, Paste, & Paste Special
Real Estate Financial Modeling Excel Functions & Formulas
IF Statements
AND & OR
SUMIF & SUMIFS
COUNTIF & COUNTIFS
AVERAGEIF & AVERAGEIFS
VLOOKUP & HLOOKUP
INDEX & MATCH
Weighted Averages (Using SUMPRODUCT)
CAGR (Using RATE)
ROUND, ROUNDUP, & ROUNDDOWN
DATEDIF
YEARFRAC & EOMONTH
PV
FV
NPER
RATE
PMT
Modeling Property Operations
GPR, Vacancy, & Credit Loss
Percentage Rent
The Pro Rata Share
NNN Reimbursements
FSG Reimbursements
MG Reimbursements
BYS Reimbursements
The Operating Expense Ratio
Renewal Probability Modeling
Cash Flow Modeling With Renewal Probability
Leasing Commissions
Tenant Improvement (TI) Allowances
Real Estate Debt Modeling
Loan Terms & Amortization vs. Term
Loan Functions & PMT
IPMT & PPMT
CUMIPMT & CUMPRINC
Building a Loan Amortization Schedule
Loan-To-Value (LTV)
Loan-To-Cost (LTC)
Debt Service Coverage Ratio (DSCR)
Debt Yield
The Loan Constant
Loan Sizing (LTV & LTC)
Loan Sizing (DSCR & Debt Yield)
Dynamic Loan Sizing Build-Out
Real Estate Investment Analysis
Key Real Estate Investment Analysis Metrics
Capitalization (Cap) Rates
Net Present Value (NPV)
Internal Rate of Return (IRR)
Equity Multiple
Cash-on-Cash Return
Positive vs. Negative Leverage
XNPV vs. XIRR
Real Estate Pro Forma Modeling
Real Estate Pro Forma Modeling Overview
Monthly Cash Flow Modeling
Monthly Growth & Toggle Triggers
Modeling NOI
Modeling Capital Expenses
Modeling Principal & Interest Payments
Modeling Purchase & Sale Metrics
Modeling Loan Proceeds & Loan Payoffs
Modeling Return Metrics
Data Manipulation & Scenario Analysis
1-Variable Data Tables
2-Variable Data Tables
Conditional Formatting
Goal Seek
Scenario Analysis
Private Equity Real Estate Interview Exam
PERE Exam Introduction
PERE Exam Instructions
Building The Pro Forma Timeline
Modeling GPR
Modeling Vacancy & Net Rental Revenue
Modeling Other Income & EGR
Modeling Operating Expenses & NOI
Modeling Capital Improvements, Reserves, & Cash Flow Before Debt Service
Modeling Principal & Interest Payments
Modeling Purchase & Sale Metrics
Modeling Loan Proceeds & Loan Payoff
Going-In Cap Rate & Loan Constant
Unlevered & Levered IRR
Unlevered & Levered Cash-on-Cash
Unlevered & Levered Equity Multiple
DSCR, Debt Yield, Exit LTV, & Expense Ratio
Running The Sensitivity Analysis
Wrapping Up
Where To Go From Here
Bonus Lecture
Bonus Lecture – The Next Step