0% Complete
0/14 Steps

Preview of Mixed-Use Apartment Building Development Modeling Tutorial

Lesson
Materials

Overview

In this lesson, you will learn how to model the speculative development and sale of a multi-family rental building with ground-floor retail and income-producing parking.

Instructions

Download the Excel from the Materials tab above. Watch the videos, and follow along and perform the exercises in the Excel.

Tutorial Videos

Video 1- Introduction and Lot and Building

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

REFM-Apartment-Module-Tutorial