Real Estate Financial Modeling Tools

Mixed-Use Apartment/Multi-Family Development Financial Modeling Self-Study Video Tutorial with Excel File

$199.00

-

Academic / U.S. Military Price – $99.00

To get discounted pricing, click Credentials Verification in the header and follow the instructions. Once you are approved, the discounted pricing will show.

Sample Content


TRANSCRIPT SAMPLE

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. Also applies to acquisition of foreclosed development projects.

Product 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

SelfStudy750pxwide

 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

What You Will Learn

A mastery of technical pro-forma (financial projection) modeling skills for the ground-up development of a rental apartment building, including:

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

Video Tutorial Abbreviated Table of Contents

Video 1 – Module Overview, Excel Settings, Footprint and Building
Video 2 – Unit Mix, Capital Structure, Conditional Statements
Videos 3a and 3b – Sources and Uses of Funds
Video 4 – Cash Flow and Returns Summary, NOI and Valuation
Video 5 – Profit Sharing, Waterfalls
Video 6 – Exhibits and Supporting Schedules

Video Tutorial Expanded Table of Contents

Video #1

– REFM Product Module Orientation
– Important Excel Settings

Tab: Apartment Module
– Apartment Module Introduction

Tab: FAR, Footprint and Massing
– FAR, Footprint & Massing Introduction
– FAR
– Lot Coverage
– Massing

Tab: Profile, Lot and Building Info.
– Profile, Lot & Building Info Tab Introduction
– Project Profile
– Lot & Above-Grade Building Description
– Below-Grade Parking Garage Description

Video #2

Tab: Apartment Unit and Mix Details
– Apartment Unit and Mix Details Introduction
– Table Overview
– Table Details
– Studios

Tab: Capital Structure 1
– Capital Structure 1 Tab

Tab: Capital Structure 2
– Capital Structure 2 Tab

Tab: Capital Structure 3
– Capital Structure 3 Tab

Tab: Capital Structure 4
– Capital Structure 4 Tab

Tab: Conditional Statements Introduction
– Conditional Statements Introduction Overview
– Control Panel
– Timeline Compression Discussion
– Pre-Construction Period
– Construction Start Month and Duration
– First C of O
– Construction End Month/Final C of O
– Post-Construction Leasing Duration
– Retail Tenant TI Payment
– Retail Tenant Rent Commencement
– Time Coding Overview
– Calendar Month
– Counting Numbers
– Construction Month #
– Construction Status
– Cell Reference Locking
– Calendar Year
– Development Fee Example

Video #3

Tab: Sources and Uses of Funds
– Sources and Uses Overview
– Land and Acquisition Costs
– Hard Costs Bell-Shaped Curve Description

Tab: Construction Bell-Shaped Curve
– Bell-Shaped Curve Tab

Tab: Sources and Uses of Funds
-Hard Costs Allocation
– Tenant Improvements
– Soft Costs
– Customizing Soft Costs
– FF&E Costs
– Financing Costs Overview
– Sources of Funds
– Equity Capital
– Sponsor Draw
– Partner Equity Draw
– Third Party Investor Draw
– Mezzanine Loan
– Senior Loan
– Financing Costs

Tab: Mezz Int Graph
– Capitalizing Interest

Tab: Mezz Int Graph – C v. Non-C
– Capitalizing Interest, continued

Tab: Sources and Uses of Funds
– Capitalizing Interest, continued
– Operating Deficit
– Checks on Sources & Uses

Video #4

Tab: Cash Flow and Returns Summary
– Cash Flow & Returns Summary Introduction
– Apt Rent Assumption
– Apt Operating Expenses Assumption
– Apt Real Estate Taxes Assumption
– Apt Rentable SF
– Apt Rent Concessions &
– Leasing Commissions Assumption
– Apt Vacancy Assumption
– Apt Parking Rent Assumption
– Apt Parking Vacancy Assumption
– Apt Credit & Collection Loss Assumption
– Apt Capitalization Rate
– Apt Selling Costs Assumption
– Retail Assumptions Overview
– Retail Rent Assumption
– Retail Operating Expense &
– Real Estate Tax Assumptions
– Retail Rentable SF
– Retail Vacancy Assumption
– Retail Capitalization Rate
– Retail Selling Costs
– Apt Units Leased
– Apt Cumulative Units Leased
– Apt Cumulative % Units Leased
– Apt Income
– Apt Amenity Fee/Misc. Income
– Parking Income
– Retail Net Operating Income
– Gross Rental Income
– Apt Rent Concessions & Leasing Commissions
– Apt Vacancy
– Parking Vacancy
– Credit & Collection Losses
– Effective Gross Revenue
– Apartments & Parking Sale, Net

Tab: Net Operating Income; Valuation
– Net Operating Income; Valuation Tab Overview
– “Current” Net Operating Income
– Future Stabilized NOI Overview
– “Current” Annual NOI Calculation
– Capital Expenditures
– “Current” Annual NOI After CapEx
– Inflation to Future NOI
– Future Stabilized Yield On Cost
– Future Capitalized Value

Tab: Cash Flow and Returns Summary
– Apartments & Parking Sale, Net
– Retail Condominium Sale
– Total Net Potential Revenue
– Operating Expenses
– Real Estate Taxes
– Net Operating Income (Net Revenue, or NOI)
– Current Annual Yield On Cost
– NOI After CapEx Overview
– Capital Expenditures
– NOI After Replacement Reserves
– Interest Expense
– Senior Loan Interest Expense
– Mezzanine Loan Interest Expense
– NOI After Interest
– Levering the Cash Flows
– Financing Cash Flows Overview
– Equity & Loan Draws
– Senior & Mezzanine Loan Repayment
– Operating Deficit
– Project Levered Cash Flow
– Net Levered Cash Flow
– Returns Summary Box
– Net Levered Cash Flow
– Equity Investment
– Multiple on Equity
– Internal Rate of Return (IRR) on Equity
– Total Development Cost
– Profit Margin

Video #5

Tab: Profit Sharing
– Profit Sharing Overview
– IRR Waterfall Structure Introduction

Tab: Waterfall #1 Graphs
– Waterfall #1 Overview

Tab: Waterfall #1
– Waterfall #1 Detail
– Waterfall #1 Structure Box
– Monthly Timeline
– Total Project Invested Equity
– Total Project Distributions
– Third Party Investor Equity Investment
– Inter-Tier Relationship
– Waterfall Animation
– Tier 1 IRR Range
– Beginning of Period Balance
– End of Period Balance
– Third Party Investor Injection
– Accrual of Returns Assumption
– Investor Accruals Assumption
– Tier 1 Accrual Distribution
– End of Period Balance
– Accrual Distribution Revisited
– Partitioning Tier 1 Cash Flows
– Investor Cash Flow
– Top-Level Developer Cash Flow
– Developer Promote Cash Flow
– Tier 1 Remaining Cash to Distribute
– Tier 2 Overview
– Investor Injections
– Investor Accruals
– Tier 1 Accrual Distribution
– Tier 2 Accrual Distribution
– End of Period Balance
– Investor Cash Flow
– Developer Equity Cash Flow
– Developer Promote Cash Flow
– Remaining Cash to Distribute
– Tiers 3 and 4
– Tier 5 Overview
– Cash Flow Calculation
– Discussion of Tier 5 Cash Flows
– Waterfall Logic Illustration
– Waterfall #1 Review
– Returns Summary Overview
– Project Level Returns
– Investor Returns
– Top-Level Developer Returns
– Cash Flow Check
– IRR Check

Tab: Profit Sharing
– Waterfall #2 Overview

Tab: Waterfall #2
– Waterfall #2 Calculation
– Difference Between Waterfall #1 and Waterfall #2
– Project Level Equity Flows
– Developer Gross Cash Flow
– Developer Partner Equity Investment
– Tier 1 Remaining Cash to Distribute
– Checking Waterfall #2
– Returns Summary
– Cash Flow Check
– IRR Check

Video #6

Tab: Sources and Uses Exhibit
– Sources and Uses Exhibit

Tab: Supporting Schedules
– Supporting Schedules Overview

Tab: Land & Acquisition Costs Schedule
– Land & Acquisition Costs Schedule

Tab: Hard Costs Budget Schedule
– Hard Costs Budget Schedule

Tab: Soft Costs Budget Schedule
– Soft Costs Budget Schedule

Tab: Operating Expenses Schedule
– Operating Expenses Schedule

Tab: Returns Exhibit
– Returns Exhibit

Tab: Transaction Summary
– Transaction Summary Exhibit

Registration is open for April Excel Bootcamp Webinars Register Now
left to take 50% off with coupon code: yearend50