REFM self-study modules provide students with an out-of-class resource to help them punch above their weight in Excel.
Since 2009, REFM has trained undergraduate and graduate students on how to solve problems with Excel so they can get the most of our their courses and prepare to be top performers in the workplace. Lecture-based and hands-on modeling training for university groups also prepares students for the REFM Certification Program In Excel For Real Estate, which students use to strengthen their resumes.
Training content authorship
Training content is created by REFM founder Bruce Kirsch. Mr. Kirsch is the co-author of the Fifth Edition of the top graduate-level real estate finance textbook, Real Estate Finance and Investments: Risks and Opportunities, along with Dr. Peter Linneman, the founding chairman of The Wharton School’s Real Estate Department.
Mr. Kirsch was previously an Adjunct Faculty member in Real Estate at Georgetown University, and he holds an MBA in Real Estate from The Wharton School and a BA in Communication from Stanford University. Prior to founding REFM, Mr. Kirsch worked in analytical roles for two top real estate platforms and a global mutual fund manager.
In many cases, students have little or no real estate or finance knowledge base, nor any of the Excel-based financial modeling skills that are valued so highly by employers. As time during the semester is severely limited, the thorough hands-on teaching of hard financial modeling skills cannot always be accommodated in class. This is where the UCSP adds value.
View the video below to learn more.
The purpose of the program is to:
- Improve student Excel-based problem-solving and analysis skills, resulting in quicker work turnaround and fewer mistakes
- Enable stronger student performance on projects, assignments, and in interviews
- Increase student confidence in advanced sensitivity analysis abilities
- Improve student financial modeling knowledge, understanding and customization capabilities
- Sharpen student presentation of transaction financials
- Heighten student credibility with and marketability to potential employers and advanced degree-granting programs
Teaching and Learning Format
The teaching and learning are intended to be a supplement to, not a replacement for, faculty-taught content. Engaging in the work is to occur on the student’s own time, at their own pace, outside of class.
REFM recognizes that there can never be a substitute for constructing financial models from scratch, and recommends that all students replicate as many of the program models as they can starting from a blank spreadsheet.
The minimum suggested engagement level for students is as follows:
- Student accesses program content via a university-branded portal webpage
- Student plays video-based tutorials (samples below) on their computer, following along in the accompanying unlocked Excel file
- Student performs hands-on exercises in the Excel file, and reviews solutions
- Student takes Completion Confirmation exams online on their own schedule.
The materials contained in the full program include:
- Over 16 hours of detailed online video instruction across 13 content modules
- 13 accompanying unlocked Excel files with hands-on exercises and documented solutions
- 3 online Completion Certification exams, which bestow REFM Certification In Excel For Real Estate
All self-study modules contain rigorous Excel-based hands-on exercises and solutions, so there is in effect continuous testing integrated into the program.
In addition, there are three formal online Completion Certification exams that the students may take at any time. These exams bestow REFM Certification In Excel For Real Estate. Scoring is automatically, instantaneously reported back to the students, and it can be reported back to the university faculty if desired. Sample questions can be found here:
The program body of knowledge comprises how to apply Excel functions, construct formulas, and build and operate advanced transaction and joint venture partnership financial projection models. The program also includes three online Completion Confirmation tests that students may take at any time.
Detailed module descriptions are found below. The three main categories of content are:
- Excel and real estate finance modeling foundations
- Modeling development and acquisition transactions
- Modeling joint ventures and private equity funds
This module is for those with little or no prior exposure to Microsoft Excel. Topics covered include:
- What Excel is, and what versions are in use
- Launching Excel and saving a new file
- The spreadsheet grid and worksheets
- Basic formatting
- Basic interface navigation using the ribbon tabs
- Saving alternate file versions
This module teaches the basics of operating in Excel, as well as advanced techniques, specifically as they relate to real estate analysis. All topics include exercises. Topics covered include:
- Arithmetic and Rounding
- 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
- 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
In this module, students will come to truly understand the nature of the IRR calculation and see why it reacts the way it does in the various acquisition and development cases that are presented in spreadsheet form. Topics covered include:
- Time value of money crash course: Present Value, Future Value and Net Present Value
- The nature of the IRR calculation, and how the IRR value changes during a transaction
- Equivalent Annual IRR rates for quarterly-, monthly-, and daily-based IRR calculations
- Why a monthly-based IRR is more accurate than an annual-based IRR
- Relationship between the IRR and the Equity Multiple
- What IRR tells us, and what it doesn’t tell us
- IRR for acquisitions and development examples
Excel functions taught include:
- PV – Present Value
- FV – Future Value
- NPV – Net Present Value
- IRR – Internal Rate of Return
- XNPV – NPV for non-periodic cash flows with known dates
- XIRR – IRR for non-periodic cash flows with known dates
In this module, students learn the answers to the following questions:
- What is a capitalization (cap) rate?
- What composes a cap rate?
- Why do cap rates change?
- How do you calculate a cap rate?
- How do you select one when performing a property valuation?
- What should you think when people quote cap rates verbally?
- Where can I find cap rates for a certain property in a certain geography?
In this module, student learn the answers the question: how does a real estate developer know what to pay for a piece of developable land?
Students will learn the basics of real estate development residual land valuation for both income-producing assets and unit sales assets, as well as the principles of valuation through comparable sales (comps).
Topics covered include
Residual Land Valuation Basics:
- Allowable Building Density/FAR
- Ground-Floor Retail Square Footage
- Primary Building Use Gross and Net Square Footages
- Calculation of Building Parking Requirements
- Development Cost Excluding Land
- Sources of Funds For Development
Specific To Income-Producing Assets (Office/Industrial/Retail, or Apartments):
- Stabilized Net Operating Income From Primary Use and Parking
- Future Stabilized Yield On Cost
- Solving for Residual Land Value
Specific To Unit Sales Assets (Condominiums or Housing Subdivisions):
- Net Sales Proceeds From Housing Units, Parking & Storage
- Gross Valuation and Net Sales Proceeds of Retail Component
- Solving for Residual Land Value
In this module, students learn the foundations of real estate finance as well as advanced techniques and topics. Topics covered include:
- The Time Value of Money Model (includes Exercise #1)
- The Discounted Cash Flow Model (includes Exercise #2)
- Internal Rate of Return (IRR)
- Mortgage Payment Calculation
- Refinancing/Interest-Only Scenario Payment Calculation (includes Exercise #3)
- Refinancing Case Study
- Maximum Loan Amount Calculation (for income-producing property acquisition)
- Residual Land Valuation (includes Exercise #4)
- Future Net Operating Income Calculation
- Transaction Capital Structures
- Financing Development Transactions
- Multiple Equity Investors Discussion (includes Exercise #5)
- Profit Sharing Discussion
- Preferred Return, Internal Rate of Return Waterfall and Promote theory
The developer’s analysis is conducted assuming a mixed-use prototype with ground floor retail and income-producing parking for multi-family, office/industrial and condominiums/housing subdivisions. All topics contain exercises. Topics covered include:
- Site and building information
- Rentable/salable square footage allocations across uses
- Project timing elements
- Capital structure
- Uses and sources of funds
- Cash flows and returns
In this module, students learn advanced techniques and topics related to joint venture partnerships and investment waterfall modeling for single property transactions. Topics covered include:
- 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
In this module, students will learn 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. A property-level partnership analysis is also addressed. Topics covered include:
- 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
Ready to get more information?