Excel for Real Estate Certification Levels 1, 2 and 3 Bootcamps | Excel For Real Estate, Real Estate Finance and Single Transaction Equity Joint Venture Partnership and Waterfall Modeling

In this 3-hour course, you will learn 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.

Learning objectives include:

• Understand how and when to use XIRR vs IRR
• How to use VLOOKUP correctly, even when you have extraneous data
• How to use SUMPRODUCT correctly
• How to create a tally/summary report when there are one or two criteria
• How to quickly create a year header from month numbers
• How to prevent irrelevant negative values from being returned
• How to calculate the lender’s IRR with and without points
• How to use keyboard shortcuts to work most efficiently, and when shortcuts won’t work
• Understand the difference between deleting cell contents and an entire row
• How to use the IF function to project weighted average property vacancy
• How to nest multiple IF functions to allocate a soft costs budget on a non-straightline basis
• How to dynamically project two discrete Tenant Improvement allowance payments with possible overlap
• How to correctly look up a text value when there are similar heading labels
• How to apply custom suffixes to numeric values
• Formatting and formula writing best practices
• Understand the date serial number system and how to create date headers correctly
• How to run a data table, and how to recognize when a data table is returning corrupted values
• How to solve for the cumulative sum to solve for residual payment amounts
• How to apply the CUMPRINC function correctly when there is an I/O period
• Understand the danger of circular references
• The multiple ways to apply the SUM function
• How to build a traditional amortizing mortgage amortization schedule, and integrate an I/O period
• How to solve for interest in an amortization schedule in three different ways
• How to solve for the NPV of a cash flow stream

• 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

• 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

This is a hands-on-the-keyboard course. You will watch the video and pause it to complete exercises in the associated Excel; you will repeat this process.

Our approach to help you learn is to isolate individual techniques and tie them to real world applications. Examples are given and then exercises are given for you to complete. Solutions are in the Excel, and are reviewed on the video.

Rest assured that you will work very hard in this course programming dozens of formulas, and you will learn a lot about the real estate business in the process.

The video running time is 3 hours. Watching all the video (replaying parts where desired) and completing the exercises will take approximately 4 to 6 hours, depending on the individual.

The sample questions and 35-question Certification exam will take approximately an additional 2 hours to complete.

This course will prepare you to pass the associated 35-question Certification exam, which is available online 24/7/365,  and is open book. The exam has questions similar to these sample questions.

Passing the exam requires a score of 70% or better, and passing With Distinction requires a score of 85% or better. You can retake the exam if you are unsatisfied with your score.

Upon passing, you will be listed automatically on the Certification Holders page.

You are allowed one re-take of the test if you wish to take it again.

Upon successful completion of this course, you will:

• Have built confidence in your ability to apply the most commonly-used set of formulas, functions and techniques for CRE Excel-based modeling
• Be able to understand how outputs are generated on any spreadsheet you come across
• Be ready to build upon the technical foundations you have put in place

• Keyboard Shortcuts
• Spreadsheet and Formula Basics
• Cell Formatting
• Arithmetic
• Statistics
• Relative vs. Absolute References
• Insert and Delete Rows and Columns
• Dates
• SUMIF and COUNTIF
• Paste Special, Transpose
• VLOOKUP and HLOOKUP
• Conditional Statements
• Conditional Statements Solution
• Conditional Formatting
• TVM and Financial Functions
• Financial Functions Exercise
• Mortgage Payment
• Mortgage Payment Exercise
• Mortgage Payment Exercise Solution
• CUMPRINC and CUMIPMT
• Formula Auditing
• Data Source
• Data Tables
• Circular References
• Best Practices

Asset Living
CBRE Global Investors
Clark Enterprises
Comstock Homes
Conor Commercial
Crescent Communities
Federal City Property
Flournoy
Four Seasons
Grosvenor
GTIS Partners
Hines
Hoffman
Howard Hughes
HPET
Hunt Companies
Insight Property Group
Kettler
LaSalle Investment
MadisonMarquette
Markwood
Mesa Capital
Midway
Miller Valentine
Panoramic Interests
PEG Development
Renaissance Downtowns
Rockefeller Group
Skanska USA
Snavely Group
Trammell Crow Residential
Trion Properties
Vornado

In this approximately 2-hour course, you will learn about 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.

Learning objectives include:

• Understand a traditional basic transaction capitalization, as well as advanced structures
• Understand what special characteristics construction loans have vs. acquisition loans
• Assess the risk of a loan being underwater if you take an interest-only loan and markets sour
• Project future cash flows assuming growth and determine cash on cash return
• Solve for the present value of projected cash flows when the discount rate varies year to year
• Calculate unlevered and levered cash flow
• Compare acquistion loan options and how they impact levered cash flow
• Components of the theoretical discount rate, and how discount rate should change
• Understand the nature of, and limitations of, a pro forma
• Size a permanent loan to replace a construction loan subject to three sizing tests, and understanding refi risk
• Understand time value of money and what your options are when you see a negative NPV
• Model pari passu and staggered equity and debt funding
• Understand how percentage rent is calculated
• Calculate equity value when exiting as a refi vs. sale
• Solve for residual land value for a development site using a back of the envelope approach
• Detect mismatches in sources and uses of funds
• Calculate weighted average cost of debt
• Model direct project draws and deficit draws for multiple equity players

• 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

• Compounding and Discounting (FV, PV)
• Net Present Value (NPV)
• Conditional Statements (IF, AND)
• Cumulative Principal (CUMPRINC)
• Minimum (MIN)

This is a hands-on-the-keyboard course. You will watch the video and pause it to complete exercises in the associated Excel; you will repeat this process.

Our approach to help you learn is to walk through a series of mini case studies that illustrate key dynamics of real estate finance. In the interest of getting you to learn efficiently in your limited study time, we give you “skeletons” of the analysis set-ups and teach from that point of progress forward. You are of course welcome to re-build the spreadsheets from scratch at any time.

Rest assured that you will work very hard in this course, and you will learn a tremendous amount about real estate finance and financial modeling in the process.

The video running time is 1 hour 48 minutes. Watching all the video (replaying parts where desired) and completing the exercises will take approximately 3 to 5 hours, depending on the individual.

The sample questions and 39-question Certification exam will take approximately an additional 2 hours to complete.

This course will prepare you to pass the associated 39-question Certification exam, which is available online 24/7/365, open book, and may be completed in more than one sitting. The exam has questions similar to these sample questions.

Passing the exam requires a score of 70% or better, and passing With Distinction requires a score of 85% or better. You can retake the exam if you are unsatisfied with your score.

Upon passing, you will be listed automatically on the Certification Holders page.

Upon successful completion of this course, you will:

• Have gained a critical understanding of basic and intermediate capital structuring for real estate transactions
• Be able to project property cash flows, and discount them at both constant and variable discount rates to solve for the NPV
• Be able to back-solve for the residual land value of a site for a potential office building development
• Be able to model the funding and repayment of up to 5 equity and debt layers in a development transaction analysis context, and size a refinancing loan on an existing owned property

• TVM Timeline
• DCF Timeline
• DCF Exercise
• DCF Solution
• Residential Refi 1 – Exercise
• Residential Refi 1 – Solution
• Residential Refi 2 – Exercise
• Residential Refi 2 – Solution
• Residual Land Valuation Overview
• Office Industrial Development Back of the Envelope Exercise
• Office Industrial Development Back of the Envelope Solution
• Capital Structure 1
• Capital Structure 2
• Capital Structure 3
• Capital Structure 4
• Senior Construction Loan Overview
• Equity Exercise
• Equity Solution
• Mezzanine Loan Exercise
• Mezzanine Loan Solution
• Pari Passu Exercise
• Pari Passu Solution
• Maximum Loan Amount Exercise
• Maximum Loan Amount Solution
• Profit Sharing
• Waterfall #1 Graphs
• Double Promote
• Waterfall #2 Graphs
• Keyboard Shortcuts

In this 3-lesson course from Real Estate Financial Modeling, you will learn about the customary “dollars in” and “dollars out” dynamics of single transaction commercial real estate equity joint venture (JV) partnerships (not multi-transaction private equity funds), and the important rationales that underpin these dynamics.

Specifically, you will model:

• non-compounded and a compounded cumulative pari-passu preferred returns, with pari-passu residual cash flow splits (i.e., a 2-tier waterfall)
• a 3-tier IRR hurdle-based waterfall with residual splits including a sliding scale-based set of sponsor promotes
• 2 interlocking 5-tier waterfalls with unique sets of IRR-based hurdles and sponsor promotes (a double-promote structure).

Where applicable, deal-level levered cash flows are kept constant to allow for comparative analysis across JV structures.

This course builds upon the content in REFM’s Level 1 and  2 Bootcamps.

Learning objectives include:

• Understand the design of sophisticated, institutional-style profit sharing structures
• IRR hurdle-based tier structure basics
• IRR definition, extreme case results, and investment back-solving
• Conventions of advanced structures
• Promote basics
• Preferred Return calculation over multiple periods
• How to integrate an acquisition fee into the waterfall
• Checking accrual calculations for accuracy
• Double-promote basics
• How cash-out refinancing impacts waterfall returns
• Hybrid waterfall structure with both IRR and equity multiple hurdles
• Return on capital
• Downstream tier accruals, distributions, and remaining cash to distribute

1. What is a joint venture and why does it exist?

2. Review of what happens on the cash flow tab

3. Dollars in considerations

4. Dollars out considerations

5. Rationale behind targeting “disproportionate financial reward” to the sponsor and how to achieve it

6. Trade-off for disproportionate financial reward / Preferred return

7. Residual cash flow splitting methods

8. Multi-tier waterfall structures

9. Which IRR serves as the trigger?

10. Sponsor promotes, and ambiguity of definition of the promote

11. How to isolate base and incremental dollars within an IRR

12. What if there are 3 equity players? / Double-promote structure

13. Common mistakes when modeling waterfalls

This is a hands-on-the-keyboard course. You will watch the video and pause it to complete exercises in the associated Excel; you will repeat this process through all 3 lessons.

Our approach to help you learn is to not force you to build the model from scratch. Rather, we provide you with pre-formatted, pre-labeled model “skeletons” so that you can focus on learning the business underpinnings of the formulas that you will be writing, and the formula mechanics themselves. Real estate is a business, and the business models, analytical conventions and operating realities must be understood intimately so they can manifest correctly in the Excel-based logic statements and calculations.

Our teaching and learning format not only allows you to learn more deeply, but also allows you to do so 20% faster than if you were to start from a blank worksheet.

You are of course welcome to re-build the spreadsheets from scratch at any time, but in the interest of getting you to learn efficiently in your limited study time, we choose to give you the skeletons and teach from that point of progress forward.

Rest assured that you will work very hard in this course programming dozens and dozens of lines to fully populate the model skeletons, and you will learn a tremendous amount about real estate and financial modeling in the process.

The video running time is 3.3 hours across the 3 lessons. Watching all the video (replaying parts where desired) and completing the exercises will take approximately 5 to 7 hours, depending on the individual.

The sample questions and 27-question Certification exam will take approximately an additional 2 hours to complete.

Through the direct experience of REFM’s principals in commercial real estate transactions, and REFM’s ongoing consulting to industry practitioners across the U.S. for the last decade, we have developed and have continually refined “The REFM Way” of teaching financial modeling.

To enhance your learning and help you to efficiently build a practical skill set, we:

1. Always set the stage by providing the big picture first, and keep it at the fore with graphs and sparklines
2. Speak in plain language and define industry terms on an ongoing basis
3. Give you the head start of having a model skeleton in which to learn
4. Provide a magnified formula bar so you don’t have to strain your eyes to see formula contents
5. Draw with “ink” on the worksheets to help you follow the lesson and grasp important points
6. Give you highly granular video chapter hyperlinks so you can find what you want quickly and easily.

This course will prepare you to pass the associated 27-question Certification exam, which is available online 24/7/365, open book, and may be completed in more than one sitting. The exam has questions similar to these sample questions.

Passing the exam requires a score of 70% or better, and passing With Distinction requires a score of 85% or better. You can retake the exam if you are unsatisfied with your score.

Upon passing, you will be listed automatically on the Certification Holders page.

Upon successful completion of this course, you will:

• Have gained a critical understanding of the business conventions that underpin the modeling of equity joint venture investment cash flow waterfalls
• Be able to distinguish between the various structural elements in waterfalls that are new to you
• Be able to model up to 5 waterfall tiers using IRR-based hurdles at a compounding frequency of daily, monthly, quarterly or annually, and be able to model a double-promote structure.

The Excel file is 100% unlocked, and you can re-purpose the tabs and comprising code as you desire.

1. Hyperlinked Table of Contents
2. Preferred Return Overview
3. Non-compounded Pari-Passu Preferred Return Exercise
4. Non-compounded Pari-Passu Preferred Return Solution
5. Compounded Pari-Passu Preferred Return Exercise
6. Compounded Pari-Passu Preferred Return Solution
7. Compounded Pari-Passu Preferred Return Solution – Monthly
8. Compounded Pari-Passu Preferred Return Solution – Monthly vs Annual
9. IRR Explanation
10. IRR Dollar Component Calculations 1
11. IRR Dollar Component Calculations 2
12. 3-Tier Waterfall with Promote Exercise
13. 3-Tier Waterfall with Promote Solution
14. 3-Tier Waterfall with Promote With Refi Solution
15. Double Promote Diagram
16. Double-Promote Waterfall #1 Exercise
17. Double-Promote Waterfall #1 Solution
18. Double-Promote Waterfall #2
19. Double-Promote Returns Exhibit
20. Alternate Compounding Periods
21. Partnership Structure 1
22. Partnership Structure 2
23. Partnership Structure 3

In this lesson, you will learn the critical foundations to understanding the basis of waterfall modeling, and proceed to model two different Preferred Return waterfall structures, both of which are Pari-Passu.

Video chapter markers:

• Agenda
• Spreadsheet formatting notes
• Big picture: spreadsheet tab relationships
• What happens on the cash flow tab
• If it’s a unit-sales project
• Big picture: spreadsheet tab relationships revisited
• Big picture: a single investor
• Big picture: two investors
• Multiple investor cash flow 3-tier waterfall
• JV partnerships: multiple parties teaming up
• Joint venture partnership cash flow splitting
• Dollars in considerations
• Dollars out considerations
• Disproportionate targeted reward to the sponsor is typical
• To motivate the creation of a larger pie for all
• How to drive the disproportionate financial reward to sponsor
• Trade-offs for disproportionate financial reward
• Variations of priority of distributions
• Which priority of payments is best?
• Preferred return distributions in context
• Tab 2 Pref Non-Compounded Exercise overview
• Equity investment and sponsor portion
• Annual preferred return (non-compounded)
• Combined invested capital account balance
• Review of non-compounded but cumulative waterfall structure
• Calculated preferred return (accrual)
• Preferred return distributions to sponsor and investor
• Review of progress
• Cash flow available for distribution types B and C
• Tab 4 Pref Compounded Exercise overview
• Annual preferred return (compounded)
• Investor capital invested
• Preferred return interest accrual
• Invested capital balance
• Interest account balance
• Payment of accrued interest
• Repayment of invested capital
• Check on IRR
• Sponsor capital invested
• Preferred return interest accrual
• Payment of accrued interest
• Repayment of invested capital
• Invested capital balance
• Interest account balance
• Review of progress
• Residual cash flow for distribution
• Comparison of non-compounding and compounding
• Summary of cash flows and returns through Distribution Type B
• Investor summary
• Investor investment
• Gross distributions
• Net cash flow
• Gross distributions total
• Investor investment total
• Investor IRR
• Investor Multiple on equity
• Investor Share of profits
• Sponsor equity investment
• Gross distributions
• Net cash flow
• Gross distributions total
• Sponsor investment total
• Sponsor IRR
• Sponsor Multiple on equity
• Sponsor Share of profits
• If residual profits were distributed pari passu
• Investor net cash flow
• Sponsor net cash flow
• Deal summary
• Total investment
• Levered cash flow
• IRR
• Multiple on equity
• Review of graphs
• Tab 6 Pref compounded solution monthly overview
• Monthly IRR and accruals
• IRR Check
• Review of graphs
• Tab 7 Monthly vs Annual Preferred Return Calculation overview
• Analysis of differences

In this lesson, you will expand on the fundamentals that were put in place in Lesson 1, integrating promoted interest dynamics (the “promote”) to make possible a disproportionate sharing of profits based on cash flow performance.

Video chapter markers:

• Review of concepts covered
• Purpose of Distribution Type C
• Residual cash flow splitting methods
• Waterfall tiers
• Which IRR serves as the trigger?
• Sponsor Promotes
• Ambiguity in definition of Promote
• Is there a promote in Tier 1?
• How do you isolate cash flows within a tier?
• Isolating base and incremental dollars within IRR
• How to think of the reported IRR
• Example of transaction interim and final IRR achieved
• Example of interim and final multiple achieved
• Isolating base and incremental dollars within IRR
• IRR unique dollar component calculations
• Tab 9 IRR Dollar Component Calcs 1
• Tab 10 IRR Dollar Component Calcs 2
• Tab 11 3-Tier Waterfall Exer overview
• Highlighting of differences in waterfall format
• Tier 1 Beginning of Period Balance
• Investor Injections,Investor Accruals
• Tier 1 Accrual Distribution
• Tier 1 End of Period Balance
• Investor Cash Flow,Sponsor Equity Cash Flow
• Tier 1 Remaining Cash to Distribute
• Investor IRR Check
• Lack of display of Sponsor investment cash flow
• Tier 2 overview
• Changing of inputs as a learning method
• Tier 2 Beginning of Period Balance
• Investor Injections
• Investor Accruals
• Tier 1 Accrual Distribution
• Tier 2 Accrual Distribution
• Tier 2 End of Period Balance
• Investor cash flow
• Sponsor Equity Cash Flow
• Sponsor Promote Cash Flow
• Tier 2 Remaining Cash to Distribute
• Tier 3 overview
• Tier 3 Investor Cash Flow
• Tier 3 Sponsor Equity Cash Flow
• Tier 3 Sponsor Promote Cash Flow
• Tier 3 Remaining Cash to Distribute
• Deal Summary
• Investor Gross Distributions
• Comparison of Deal and Investor returns
• Sponsor Summary,Sponsor Investment
• Sponsor Equity Cash Flow
• Sponsor Promote Cash Flow
• Sponsor Net Cash Flow
• Cash Flow Check
• IRR Checks
• Sensitivity Analysis
• Discussion of graphs and relative performance of players
• Share of Cash Flows by Tier
• Tab 13 3-Tier Waterfall Sol w Refi overview
• Refi Impact on returns
• Accruals and Tier 1 Remaining Cash to Distribute summaries
• Refi Repayment of All Investment

In this lesson, you will learn about how to model two unique interlocking waterfall structures among 3 equity entities, review frequency of compounding, and see some sample structure set-ups that you can re-purpose.

Video chapter markers:

• What if there are 3 equity players?
• Top-level sponsor example
• Teaming up with third party capital
• How $20MM in equity might break out
• Double-promote structure for 3 equity players
• Tab 15 D-P Waterfall #1 Exer overview
• Tier 1 review
• Tier 2 review
• Tier 3 overview
• Beginning of Period Balance
• Investor Injections
• Investor Accruals
• Tier 1 and 2 Accrual Distributions
• Tier 3 Accrual Distribution
• End of Period Balance
• Tier 3 Investor Cash Flow
• Sponsor Equity Cash Flow
• Sponsor Promote Cash Flow
• Tier 3 Remaining Cash to Distribute
• Tier 4 Beginning of Period Balance
• Investor Injections
• Investor Accruals
• Tier 1 and 2 and 3 Accrual Distributions
• Tier 4 Accrual Distribution
• Tier 4 End of Period Balance
• Investor Cash Flow
• Sponsor Equity Cash Flow
• Sponsor Promote Cash Flow
• Tier 4 Remaining Cash to Distribute
• Tier 5 Cash Flows
• Tier 5 Remaining Cash to Distribute
• Deal Cash Flow Check
• IRR Checks
• Review of returns graphs
• Share of Cash Flows by Tier
• Tab 14 Double Promote Diagram tab revisited
• Why 2 or 3 or 5 Tiers?
• Tab 17 DP Waterfall #2 Exercise overview
• Differences vs Waterfall #1
• Equity Investment Connection to Waterfall #1
• Top-Level Sponsor Investment Connection to Waterfall #1
• Cash Flow to Top-Level Sponsor Entity from Waterfall #1
• Promote to Top-Level Sponsor Entity from Waterfall #1
• Partner Tier 1 Accrual Distribution
• Review of returns across all entities
• Review of returns graphs for Waterfall #2 players
• Tab 19 DP Returns Exhibit
• Tab 20 Alternate Compounding Frequencies
• Monthly Compounding
• Daily Compounding
• Quarterly Compounding
• Sample Partnership Structure Set-Ups