Get smart in Excel and stand out in the crowd

Since 2011, REFM’s Certification Program In Excel For Real Estate has measured proficiency in operating skillfully and efficiently in Microsoft Excel for the specific purpose of real estate transaction and partnership modeling analysis. Holding each subsequent Level of REFM Certification further validates one’s skills and demonstrates their seriousness about being an expert-level modeler.

The rigorous Certification Program teaches and then tests one’s ability to complete the following tasks using Excel:

• Forecasting property income and expense cash flows
• Building a mortgage payment schedule with an Interest-Only component
• Calculating IRR and NPV specific to actual calendar dates
• Quickly allocating construction costs according to bell-shaped curves
• Valuing a ground-up development site on a residual basis
• Forecasting an asset’s future stabilized Net Operating Income
• Accurately calculating loan interest for multiple layers of financing
• Determining exactly how much equity partners should be compensated
• Performing sensitivity analyses around the driving factors in a transaction

How long it takes to become Certified

• You can complete all three levels in a single weekend if you want
• Each level requires around 4 to 6 hours of study/exercises
• Each exam takes around an hour (Level 3 typically takes a little longer)

Test pricing and Certification benefits

• The tests come with the purchase of the Certification materials
• Upon passing each Level, you will receive a listing on the Certification Holders Page and a badge for your email signature
• You can also put your Certifications in your LinkedIn profile.

REFM’s Certification Program in Excel for Real Estate empowers real estate professionals and students with a way to demonstrate their level of expertise in solving analytical real estate problems with Microsoft Excel. The three Levels of Certification establish a uniform performance standard by testing for knowledge and for a thorough understanding of the principles, procedures and techniques requisite in performing real estate transaction and partnership analysis.

Each Level of Certification:

• Defines and develops competencies among potential job candidates
• Identifies those candidates who have acquired these attributes
• Raises the level of core competencies within an organization
• Promotes career development
• Indicates employee achievement, and
• Demonstrates a serious commitment to having marketable real estate financial analysis skills.

Eligibility: Anyone who wishes to pursue REFM Certification is an eligible Candidate.

How Long It Takes: You could learn all three Levels and take all three tests in a single weekend if you dedicated the weekend to becoming Certified for all three Levels.

Cost: Cost of Level 1 Self-Study product is $149; Levels 2 and 3 can be bundled for a total cost of $399. Academic pricing is lower. See individual product pricing for details.

Body Of Knowledge Tested: Review each Level’s contents below.

Requirements: You may take any Level Test you wish in any order you wish. In other words, you do not have to take the Level 1 Test to take the Level 2 Test, etc.

Format: Each Test must be completed in 60 minutes, and each is a combination of multiple choice, formula/function interpretation, and formula/function construction. Use of a blank Excel workbook and the Excel Help function is allowed, with the natural penalty for doing so being the loss of time to complete the Test in its entirety.

Testing Schedule: The Tests are available online, 24/7/365 (click on the links at left to try sample questions, and to take the Tests). Tests can also be specially administered on-site at corporate and educational facilities. Email testing@GetREFM.com to inquire.

Testing Conditions: Tests can be completed in more than one sitting. Candidates must not receive any outside assistance during the Test, and must not consult any resources other than the Excel Help function within a blank Excel workbook.

Test Preparation: The content tested at each Level is highly specific, and preparation is strongly encouraged for best results. REFM offers Self-Study Preparatory Materials to prepare Candidates for the Tests.

Passing Requirements: The Tests are designed to be challenging to match the rigor required in the workplace to perform analyses in a timely and efficient manner. The passing score is 70% and Passing With Distinction is 85%+.

Credentials: Upon passing each Level, Candidates will receive a Certificate of Completion and they may elect to have their name listed in the REFM Certification Database for verification by employers, and a badge for your email signature.

Excel for Real Estate Bootcamp Level 1 Certification

Level 1 Certification tests to ensure that the proper foundations are in place.  The following knowledge and skills areas are tested:

(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

Practice questions are here

Self-Study Preparatory Material may be purchased here

Real Estate Finance Bootcamp Level 2 Certification

Level 2 Certification tests more sophisticated analysis and use of functions.

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

Practice questions are here

Self-Study Preparatory Material may be purchased here

Single Transaction Equity Joint Venture Partnership Waterfall Modeling Bootcamp Level 3 Certification

Level 3 Certification tests the ability to model complex partnerships and waterfall distributions.

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
• Claw-Back overview and modeling
• Claw-Back Exercise.

Practice questions are here

Self-Study Preparatory Material may be purchased here