Level 1 Bootcamp – Excel For Real Estate (Level 1 Certification Preparation)
An interactive Video Tutorial that teaches the basics of operating in Excel, as well as advanced techniques, specifically as they relate to real estate analysis. You will follow along in Excel in real time and perform exercises to ensure you are grasping the lesson and are mastering the technical skills being taught.
This product is the preparatory material for REFM Excel for Real Estate Level 1 Certification.
Product includes the following items, which never expire:
Online access to an easily navigated 3-Hour Video Tutorial, playable on any device including PC, Mac, phones and tablets
Accompanying fully-unlocked Excel file, compatible with both PC and Mac
Topics Covered (All topics include Exercises and Solutions)
- Arithmetic and Rounding
- Statistics
- Relative vs. Absolute Cell References
- Inserting and Deleting Rows and Columns
- Dates and Timelines
- Conditional Statements
- Function Wizard
- Creating Summary Tables
- Formatting Numbers
- Conditional Cell Formatting
- Data Table Lookup Functions
- Custom Formatting
- Format Painting
- Paste Special
- Transpose
- Financial Functions for Compounding and Discounting
- Internal Rate of Return (IRR)
- Mortgage Payment Calculation
- Formula Auditing
- Data Tables for Sensitivity Analysis
- Circular References
- Keyboard Shortcuts
- Best Practices
Excel Formulas and Functions Taught
- SUM
- ROUND, ROUNDUP, ROUNDDOWN
- MAX, MIN, AVERAGE, MEDIAN
- Cell Anchoring using Dollar Signs
- EOMONTH, EDATE, DATEVALUE
- IF, AND, OR, and combinations thereof
- SUMIF, SUMIFS
- COUNTIF, COUNTIFS
- VLOOKUP, HLOOKUP
- COLUMNS, ROWS
- IFERROR
- RATE
- PV, FV
- NPV and XNPV
- IRR and XIRR
- PMT, PPMT, IPMT
- CUMPRINC, CUMIPMT
Video Chapter Headings
- Introduction to Table of Contents
- Keyboard Shortcuts Introduction
- Spreadsheet and Formula Basics
- Insert Function Button
- Formatting Conventions Used
- Format Cells Box
- Custom Formatting
- Format Painting
- Arithmetic/Addition
- SUM Function
- Subtraction
- Multiplication
- Division
- Exponents
- Order of Operations
- Evaluate Formula Function
- Rounding
- ROUND Function
- ROUNDUP Function
- ROUNDDOWN Function
- Alt Hotkeys
- Statistics
- MAX Function
- MIN Function
- AVERAGE Function
- MEDIAN Function
- Relative and Absolute References
- Relative Reference
- Absolute Reference
- Inserting and Deleting Rows
- Inserting and Deleting Columns
- Dates
- Annual Timeline
- Monthly Timeline
- EOMONTH Function
- EDATE Function
- SUMIF Function
- SUMIFS Function
- COUNTIF Function
- COUNTIFS Function
- Paste Special
- Transpose
- VLOOKUP Function
- COLUMNS Function
- HLOOKUP Function
- ROWS Function
- Conditional Statements/IF Function
- OR Function
- AND Function
- Nesting Conditional Statements
- Nesting and Multiplying by the IF Function
- Adding the IF Function
- Conditional Formatting
- Time Value of Money/Discounted Cash Flow Model Introduction
- PV Function
- FV Function
- NPV Function
- IRR Function
- XNPV Function
- XIRR Function
- Mortgage Payment Introduction
- Mortgage Amortization Table Overview
- Beginning Balance
- Monthly Payment
- Principal Portion/PPMT Function
- Interest Payment Portion/IPMT Function
- Ending Balance
- CUMPRINC Function
- CUMIPMT Function
- Formula Auditing
- Trace Dependents
- Trace Precedents
- Remove Arrows
- Data Tables
- Circular References
- Best Practices
Product Description
Level 2 Bootcamp – Real Estate Finance (Level 2 Certification Preparation)
An interactive Video Tutorial that teaches you the foundations of real estate finance as well as advanced techniques and topics. You will follow along in Excel in real time and perform exercises to ensure you are grasping the lesson and are mastering the technical skills being taught.
This product is the REFM Excel for Real Estate Level 2 Certification Preparatory Material.
Product includes the following items, which never expire:
Online access to an easily-navigated 1 hour and 48-minute-long Video Tutorial, playable on any device including PC, Mac, phones and tablets
Accompanying fully-unlocked Excel file, compatible with both PC and Mac
Topics Covered (All topics include Exercises and Solutions)
- The Time Value of Money Model
- The Discounted Cash Flow Model
- Net Present Value
- Residential Property Refinancing Feasibility Case Study
- Back of the Envelope Residual Land Valuation of Development Sites
- Acquisition and Development Transaction Capital Structuring
- Senior Construction Loan Mechanics
- Multi-Party Transaction Equity Structures
- Senior and Mezzanine Loan Funding and Repayment Dynamics
- Staggered and Pari Passu Equity Player Funding
- Maximum Refinance Loan Amount Calculation
- Equity Player Profit Sharing Introduction
- Preferred Return, Internal Rate of Return Waterfall and Promote Theory Basics
Excel Formulas and Functions Taught/Employed
- Compounding and Discounting (FV, PV)
- Net Present Value (NPV)
- Conditional Statements (IF, AND)
- Cumulative Principal (CUMPRINC)
- Minimum (MIN)
Video Chapter Headings
- Time Value of Money Theory Explanation
- Time Value of Money Example
- FV Function Review
- PV Function Review
- Case Study Assumptions
- Investment Cash Outflow
- Base Rent
- Escalations
- Percentage Rent
- Expected Return
- Year-Over-Year Growth
- Cash-On-Cash Return
- Present Value of Cash Flows
- NPV
- Discount Rate Selection
- DCF Exercise
- NPV With Different Discount Rates
- Residential Refi Exercise Case Study
- Case Study Outcome
- Underwater Property Options
- Underwater Property Exit Math
- Residual Land Valuation Overview
- Mixed-Use Office Industrial Back Of The Envelope Analysis
- Allowable FAR
- Ground-Floor Retail SF
- Efficiency Factor
- Parking Requirement
- ‘Current’ Stabilized NOI
- Goal-Seeking Land Cost
- Back Of The Envelope Exercise
- Capital Structure 1 Tab
- Equity
- Senior Loan
- Mezzanine Loan
- Senior Debt Collateral
- Mezzanine Financing
- Preferred Equity
- Senior Loan Interest Rates
- Mezzanine Loan Collateral
- Order of Funding
- Order of Return of Funds
- Capital Structure 2 Tab
- Sponsor Equity and Third Party Investor Equity
- Capital Structure 3 Tab
- Partner Equity
- Capital Structure 4 Tab
- Senior Construction Loan Overview Tab
- Loan Draws
- Interest Accrual
- Eligible Loan Costs
- Loan Composition
- Loan Size
- Senior Loan Cash Interest Payments
- Negative Amortization
- Equity Exercise Tab
- Financing Assumptions
- Analysis Timeline Compression
- Eligible Loan Costs
- Cash Financing Costs
- Capitalized Financing Costs
- Operating Deficit
- Total Development Costs
- Positive Revenue After Cash Financing Costs
- Sources of Funds
- Sources and Uses of Funds Equivalency Checks
- Sponsor Equity
- Exercise Set Up
- Simplified Sources and Uses of Funds
- Direct Project Costs Draw
- Cumulative Draw
- Cash Financing Costs
- Operating Deficit Funding
- Total Funding
- Total Equity Draw
- Senior Loan
- Interest Rate and Monthly Interest
- Variable Interest Rate Modeling
- Senior Loan Beginning Balance
- Senior Loan Draw
- Loan Fees – Front End
- Loan Fees – At Draws
- Senior Loan Funded Interest Reserve
- Senior Loan Cash Interest Payment
- Senior Loan Repayment
- Senior Loan Ending Balance
- Override of Senior Loan Size
- Partner and Third Party Investor Exercises
- Exercise Solution
- Mezzanine Loan Exercise
- Mezzanine Loan Solution
- Mezzanine Loan Beginning Balance
- Mezzanine Loan Draw
- Mezzanine Loan Fees – Front End
- Mezzanine Loan Cash Interests Payment
- Mezzanine Loan Repayment
- Mezzanine Loan Ending Balance
- Sources and Uses of Funds Equivalency Check
- Equity Pari Passu Funding Exercise
- Equity Pari Passu Funding Solution
- Maximum Refi Loan Size Exercise
- Loan-to-Value Test
- Debt Service Coverage Ratio Test
- Debt Yield Test
- Exercise Solutions
- Lender’s Cap Rate / Mortgage Constant
- Equity Profit Sharing Introduction
Product Description
Level 3 Bootcamp – Single Transaction Equity Joint Venture Partnership and Waterfall Modeling (Level 3 Certification Preparation)
- Teaches the most difficult, valuable and desired set of financial modeling skills in plain English
- Teaches you how to model out equity cash flow splits among up to three equity players
- You can integrate the included annual and monthly waterfall models with your existing spreadsheets
An interactive Video Tutorial that teaches you advanced techniques and topics related to joint venture partnerships and investment waterfall modeling for single property transactions (not multi-transaction private equity funds). You will follow along in Excel in real time and perform exercises to ensure you are grasping the lesson and are mastering the technical skills being taught.
This product is the preparatory material for REFM Excel for Real Estate Level 3 Certification.
Product includes the following items, which never expire:
Online access to an easily navigated 200-minute Video Tutorial, playable on any device including PC, Mac, phones and tablets
PDF of the 60-slide presentation
Accompanying fully-unlocked, annotated Excel file, compatible with both PC and Mac, with the following tabs:
- Preferred Return Non-Compounded Annual
- Preferred Return Non-Compounded Annual Solution
- Preferred Return Compounded Monthly
- Preferred Return Compounded Monthly Solution
- 3-Tier Annual Waterfall with Look-Back
- Double-Promote Profit Sharing
- Double-Promote Monthly Waterfall # 1 with Look-Back
- Exercise – Rebuild Tier #3
- Double-Promote Monthly Waterfall #2 with Look-Back
- Double Promote Returns Summary Exhibit
- Alternate Compounding Periods
- Sample Partnership Structure 1
- Sample Partnership Structure 2
- Sample Partnership Structure 3.
Topics Covered and Exercises/Solutions Included:
- 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.