“The New York Course” Self-Study Product

This product is a Self Study version of our popular 2-day intensive live training course that provides you with critical foundations and financial modeling skills specific to development and acquisition transaction analysis and equity returns analysis. Included in the product are:

Permanent online access to easily navigated, mobile-friendly Video Tutorials

Accompanying unlocked Excel files, compatible with both PC and Mac 

All the knowledge needed to attain REFM’s three Certifications

A job interview test Excel Solution Set and 1-page investment memo

What New York Course attendees have said

Why train with REFM?

• Time-tested training content second to none in terms of refinement, depth and breadth
• Efficient, student-focused teaching and learning format
• Potential to achieve the most highly-regarded Excel skills certification in the business

.

REFM is the trainer to these organizations:

Products Included

Level 1 Bootcamp – Excel For Real Estate (Level 1 Certification Preparation)

Description

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 the 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
• 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 and 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

Sample Content

.

Level 2 Bootcamp – Real Estate Finance (Level 2 Certification Preparation)

Description

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

Sample Content

.

Mixed-Use Apartment/Multi-family Building Development Modeling

Description

An interactive Video Tutorial that teaches how to model the speculative development and sale of a multi-family rental building with ground-floor retail and income-producing parking.

Includes the following items, which never expire:

 Online access to an easily navigated 180-minute Video Tutorial, playable on any device including PC, Mac, phones and tablets

 Accompanying fully-unlocked, annotated Excel file, compatible with both PC and Mac, which you can repurpose for future analyses

100-page Audio Transcript in PDF format

Topics Covered

Site and Building Information

• Calculation of allowable density using FAR multiple and site area
• Calculation of allowable building footprint using lot coverage ratio and site area
• Calculation of building height using slab-to-slab height and story count
• Calculation of apartment gross square footage given an assumed ground-floor retail square footage
• Calculation of apartment rentable square footage given an efficiency factor
• Calculation of parking requirements and required parking gross square footage given usable square footage loss factor

Apartment Unit and Mix Details

• Calculation of average rent/month given a monthly rent per square foot and average unit size
• Calculation of total rentable square feet of each unit type and total number of each unit type given average unit sizes, and percentage of total rentable square footage that each unit type comprises
• Calculation of monthly revenues and share of revenues by unit type

Capital Structure

• Calculation of sources of funds supplied by each of three equity sources, a mezzanine lender, and a senior lender
• Calculation of percentage of total development cost that each of the three equity investments comprise

Project Timing Elements

• Understanding of the relationship between the durations of the three phases of development (Pre-Construction, Construction and Post-Construction), and the timing and dependencies of the major milestones within each of those phases

Uses of Funds

• Quantification and timing of:
  • Land and Acquisition Costs
  • Hard Costs (based off of a bell-shaped distribution curve) and Tenant Improvement Costs
  • Soft Costs
  • Fixtures, Furnishings and Equipment Costs
  • Financing Costs, including Capitalized Interest Expense, and Operating Deficit

Sources of Funds

• Quantification and timing and interrelationship of Draws, Interest and Repayment, as applicable, of:
  • Developer Sponsor Equity
  • Developer Partner Equity
  • Third Party Investor Equity
  • Mezzanine Financing
  • Senior Financing

Cash Flows and Returns

• Income and Expense Assumptions
• Apartment Unit Lease-Up Schedule
• Calculation of Gross Rental Income, Effective Gross Revenue and Net Potential Revenue
• Calculation of Total Operating Expenses and Real Estate Taxes
• Calculation of Net Operating Income (NOI)
• Calculation of Current Annual Yield
• Calculation and Timing of Capital Expenditures
• Calculation and Timing of Non-Capitalized Interest Expense
• Timing of Financing Cash Flows
• Calculation of Project Levered Cash Flow
• Calculation of Multiple on Equity
• Calculation of Internal Rate of Return (IRR) on Equity
• Calculation of Profit Margin

Capitalized Valuation

• Calculation of “Current” Annual Net Operating Income
• Calculation of Future Stabilized Net Operating Income
• Calculation of Future Net Sale Amount

Profit Sharing/Internal Rate of Return Waterfalls

• Calculation of profit sharing based on an assumed Internal Rate of Return Waterfall structure with preferred returns and promotes

Sample Content

.

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

Description

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.

• 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

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

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

Sample Content

Office Property Operating Projection and Acquisition Screening Analysis Modeling

Description

An interactive Video Tutorial that teaches:

• Typical line item set-up for office properties
• Cash flow projection modeling basics
• Building a multi-tenant property-level operating projection (also applies to single-tenant properties) assuming initial vacancy that is leased up, and no subsequent expirations during the analysis timeline
• Conducting a property-level acquisition equity returns screening analysis

You will model more than 130 rows across the two parts of the tutorial. This will likely take you 3 to 4 hours including watching the video tutorial.

Includes the following items, which never expire:

 Online access to an easily navigated 90-minute Video Tutorial, playable on any device including PC, Mac, phones and tablets

 Accompanying fully-unlocked, annotated Excel file, compatible with both PC and Mac, which you can repurpose for future analyses

Excel file tabs are:

• Office Cash Flow Set-Up
• Projection Formulas
• Projection Exercise
• Projection Solution
• Screening Analysis Exercise
• Screening Analysis Solution (see Bonus Content included below)

Topics Covered and Exercises/Solutions Included

Part 1

First, you will first learn the generic 20-line item set-up for an existing operating commercial office property, starting at Base Rental Revenue (Gross Potential Rent) and ending at Before-Tax Levered Cash Flow (cash flow to equity).

Then, a primer on projection formula construction is provided to get you ready to construct the 10-year annual property operating projection.

Next, the assumptions for projection modeling exercise are explained.

There are three Suites in the property. The assumptions to be used are that leases for Suites 100 and 200 were put in place at the start of the trailing twelve months, and that Suite 300 is vacant currently and will remain vacant through the end of Year 1. Suite 300 rent will commence at the start of Year 2 with Year 2 Base Year.

Suite 100 is a triple-net (NNN) lease, and Suites 200 and 300 are Gross leases (Base Year stop).

The following assumptions inputs are provided for you to key into the Exercise tab:

• Property-level Base Year (BY) operating expenses, real estate taxes and utilities PSF
• Base Year rents for all three suites
• general vacancy and credit loss
• Annual growth rates for rents for all three suites
• % of reimbursable and non-reimbursable expenses that are fixed
• Annual growth rates for:
  • parking and miscellaneous revenue
  • reimbursable expenses
  • real estate taxes
  • non-reimbursable expenses
  • capital reserves
• Management fees as a % of EGR
• Tenant Improvements and Leasing Commissions PSF for Suite 300

Projection formula mathematical descriptions are provided to guide your Excel formula construction.

After a couple of examples are performed for you, you will fill in the 30+ line items in the exercise. You can check your work in real-time against the fully-unlocked Solution set tab provided in the Excel file. Solutions for the lines are explained in the video.

Part 2

The second part of the tutorial takes the NOI line from the Part 1 Solution set tab, and weaves it into an acquisition analysis screening tab that integrates purchase, sale, debt and equity elements.

The sections of this analysis are:

• Uses of Funds
• Sources of Funds (Sponsor equity, Third Party Investor equity, Senior Acquisition Loan)
• Unlevered Projection Analysis
• Debt Schedule
• Levered Cash Flow
• Sensitivity Tables
• Levered Summary

After a few examples are performed for you, you will model more than 100 rows. You can check your work in real-time against the fully-unlocked Solution set tab provided in the Excel file. Solutions for the lines are explained in the video.

Data tables are used to provide a spectrum of outcome possibilities given simultaneous changes in two key inputs. A levered summary table is also provided to evaluate returns for multiple hold durations side-by-side.

BONUS EXCEL CONTENT INCLUDED! *

• Annual equity joint venture partnership waterfall between Sponsor and Investor
  • JV partnership structure: (Equity goes in 10% from Sponsor, 90% from Investor.) All equity gets a Preferred Return of 10%; after Preferred Return is distributed and Equity investment is returned, then the Sponsor gets a 20% promote, and the residual 80% goes to ALL Investors, pro-rata to cash investment.

* Bonus content is annotated only; there is no video tutorial footage associated with it.

Sample Content

.

Who Should Buy This Product

Beginner-, intermediate- and advanced-level professionals and students.