The Advanced Real Estate Development Modeling Master Class

Master real estate pro forma modeling fundamentals by turning land costs and lease-up assumptions into cash flow and return projections, using blue inputs, black formulas, green references, and red callouts.

Prepare to build an advanced real estate development model in Excel for Windows, focusing on a pro forma for office, retail, and industrial deals, with sample inputs.

Set up a clean, model-ready Excel workbook for advanced real estate development, configuring grid lines, fonts, calculation options, and iterative calculations for a reliable summary dashboard.

Create a property details tab and build a property details table including net leasable area, land square footage, street address, and city, state and zip. Format the header with center across selection, add an outside border, and apply a light gray background with white bold text, while using blue text for manual inputs and black text for formula-driven outputs, with sample values like 20,000ft² and 30,000ft² to verify formatting.

Build and populate the land acquisition information table, entering price, per acre and per square foot calculations, fees, closing costs, and escrow closing date with dynamic Excel formulas and formatting.

Build sale information and investor distributions tables in Excel, include costs of sale, exit cap rate, and hold period, and use data validation for monthly, quarterly, or annual distributions.

Model project-level fees—land acquisition, asset management, disposition, construction management—as percentages of purchase price, effective gross revenue, sale price, and construction costs; lease-up start and end months use a 'month' prefix.

Create a summary details table with stabilized yield on cost, DSR at refinance, debt yield at refinance, stabilized LTV at refinance, total project cost, all in basis per square foot.

Develop a development capital budget table in Excel, detailing hard costs, soft costs, contingency, tenant improvements, leasing commissions, per square foot costs, and construction timing for total capital cost.

Build a construction financing details table with separate senior and mezzanine inputs, covering LTC, loan amount, interest rate spread, fees, and terms in this lecture.

Model permanent financing after construction by selecting fixed or floating loans, sizing proceeds by LTV, LTC, DSR, or debt yield, and setting refinance month, cap rate, and amortization.

The sale details lecture builds a dynamic table that outputs projected sale date, exit cap rate, stabilized yield, sale price, and cost of sale with eomonth and iferror.

Create levered and unlevered return metrics tables to calculate IRR, equity multiple, cash on cash return, and total profit for a real estate development project.

Learn to build a construction-period sources and uses model, detailing senior and mezzanine loan proceeds, equity, and all cost categories from land to contingency, ensuring total sources equal total uses.

Explore how to model additional equity requirements and implement a circuit breaker within an advanced real estate development model, covering post-construction equity, cash extraction at refinance, and on/off data validation.

Set up the CRA leases tab to model lease-up assumptions and project cash flows for ten suites, detailing suite numbers, sf, tenant names, dates, pro rata share, and reimbursements.

Explore how to structure operating expense reimbursements for modified gross and base-year stop scenarios, including setting line-item triggers, base-year amounts, and Excel-based implementation.

Build a rent escalation schedule with start and end dates, end-of-month calculations, annual per-square-foot increases, and a 15-row model to capture escalating rents.

Model re-leasing assumptions in real estate development: renewal probability, lease terms, downtime, free rent, market rents for new/renewal, growth, and reimbursement structures including t allowance and leasing commissions.

Build a releasing costs table for advanced real estate modeling, configuring T per square foot for new and renewal leases, annual growth, leasing commissions, and percentage rent inputs.

Copy the releasing cost table to create the percentage rent table for the initial tenant, including five inputs: annual sales, health ratio, percentage rent yes/no, breakpoint percentage, and sales growth.

Create a tenant cash flow timeline to project per-suite cash flows across multiple suites over 30 years, incorporating inputs, leasing commissions on base rent, and dynamic month-ending dates.

Model dynamic releasing months in Excel to calculate multiple releasing periods in the cash flow timeline, using if, or, offset, round, eomonth, and weighted averages.

Create a dynamic tenant identifier by combining suite number and tenant name, then compute lease-up base rent from start/end dates using if and and with vlookup for monthly cash flows.

Model lease-up reimbursements by creating a dummy operating expense load, wiring month-end references, and applying 3% annual growth to taxes, insurance, cam, and other expenses.

Learn to model lease-up reimbursements in Excel using timing triggers, nested ifs, and sum product to calculate triple net, modified gross, base year stop, and pro rata shares.

model total percentage rent with an Excel formula that calculates overage above the breakpoint using annual sales, growth, and a percentage rent clause, including zero safeguards.

Implement upfront free rent with a conditional formula using lease start date, month ending date, and eomonth; return negative base rent during the free period and zero otherwise.

Explore releasing base rent calculations using lease end dates, downtime, renewal probability, and weighted average market rent growth for new and renewal leases.

Calculate releasing free rent in a real estate model by testing releasing month against down time and renewal probability, weighting rent for new and renewal leases to determine cash outflows.

Analyze releasing reimbursements in a real estate model by applying triple net, modified gross, and base year stop calculations with pro rata shares and operating expenses.

Model up-front tenant improvements for lease-up tenants in Excel. Calculate the upfront cash outflow using TE per square foot, square footage, and a 3% annual growth.

Calculate upfront leasing commissions for lease up using lease up base rent and upfront free rent, via a sumifs model tied to month ending dates and eomonth.

Learn to model releasing tenant improvements by applying weighted average tenant improvement allowance, timing logic around the releasing month, and renewal probabilities to calculate allowances and their cash flow impact.

Explore dynamic releasing leasing commissions in real estate development modeling, calculating base rent sums across lease years, weighting commissions by renewal probability and downtime, with advanced offset and sumif techniques.

Add cash flow codes like brr, cam, pr, fr, rr, t, and lc to group lease items, then use a sum if with the hold period to compute totals.

Duplicate tenant inputs to add ten suites, group and collapse inputs for a consolidated rent roll, and propagate dynamic cash flow formulas across suites, leading to an occupancy table.

The lecture demonstrates building dynamic occupancy calculations in Excel, tracking each suite's square footage and tenant name, using indirect and counta to auto-fill 16-row intervals and compute monthly totals.

Develop occupancy calculations in Excel by building a dynamic formula that returns tenant square footage based on lease up and releasing base rent, using countif, index, and match.

Finalize the pro rata share and health ratio calculations for tenants by building dynamic formulas using net leasable area, occupancy costs, lease up data, and projected annual sales.

Finalize the cre leases tab by cleaning tenant cash flow inputs, applying borders, and modeling three office suites with zeroed inputs, rent escalations, and renewal assumptions.

Create and format an expenses tab to model stabilized operating expenses, including property taxes, insurance, cam, and other costs with per-square-foot, growth, total, and fixed-percent assumptions.

Format real estate expense modeling in excel, define per square foot costs with cent-precision currency, use blue manual inputs, link net leasable area, and apply fixed versus variable expense rules.

build two tables for other assumptions and property tax information, including property management fees, annual expense growth, capital reserves per square foot, land value reassessment upon purchase and sale.

Build a year-by-year credit loss and property assessment growth rate model, adding annual assumptions from year one through year eleven, including a 1% credit loss, with clear formatting.

Build a hold period property taxes table up to year 11, incorporating the reassessed upon purchase logic, property tax rate, assessed value, fixed charge assessments, credits, and total taxes.

Tighten actual operating expenses by building an actual and stabilized operating expenses pair from the CRA leases data, then apply annual growth gradually to fixed and variable costs using hlookup.

Model operating expenses by tying property taxes to stabilized costs, calculating insurance with fixed percentage and occupancy, and applying a mix of CAM and other costs across construction and operation.

Create a construction budget tab that builds two tables for hard and soft costs and their timing over a ten-year hold period, using a copied timeline and timing inputs.

Model hard costs as a blue manual line item, format budgets in currency, compute per square foot from the summary tab, and configure S-curve or straight-line cash flows with checks.

Learn to model construction cash flows with S-curve and straight-line costs in Excel, using a bell-curve normal distribution and dynamic month testing. Implement checks and formatting to ensure accurate forecasts.

Create an Excel checks formula to ensure construction costs are funded in the construction period, using a 0.01 tolerance, absolute difference, dynamic ranges, and conditional formatting for ok and error.

Duplicate hard costs to create soft costs, then use monthly formulas for capitalized property taxes and insurance during construction, adding straight-line marketing and planning costs to finalize the budget.

Apply contingency modeling to the construction budget by adding a contingency line item, computing the total budget with formulas, determining month start and end values, and deriving the per-square-foot cost.

Develop a dynamic monthly cash flow tab and an annual roll up to project 11 years of real estate cash flows, including acquisition month zero and net operating income projections.

Develop a dynamic real estate model of net rental income by aggregating base rental revenue, free rent, and credit losses, including operating expense reimbursements and percentage rent.

Learn to model other income and compute effective gross revenue by incorporating operating expense reimbursements and percentage rent, using consistent Excel formulas.

Model property taxes within operating expenses using circuit breakers to manage circular references, and compute reassessed taxes after sale with hold-period assumptions.

Model monthly operating expenses and net operating income by zeroing insurance during construction, incorporating capitalized costs, and applying cam and management fees until stabilization, guided by effective gross revenue.

Model capital and partnership expenses, including construction costs, tenant improvement allowances, leasing commissions, capital reserves, asset management fees, and construction management fees as cash outflows over the hold period.

Develop capital reserves, asset management, and construction management fees using timing triggers and annual growth, within hold and construction periods to model monthly cash flows.

Master debt service calculations, post-debt cash flow, and acquisition and sale information in real estate development, including land acquisition price, closing costs, sale proceeds, and unlevered cash flow.

Learn to integrate construction and permanent loan information into project level cash flows, calculate total levered cash flow, and manage placeholders and formulas for debt service, proceeds, payoffs, and fees.

Create a separate investor cash flow table from the project level cash flow, defining beginning balance, capital contributions, capital distributions, and ending balance to compute total investor cash flow.

Explore investor-level cash flow distributions using a capital distributions formula in Excel, incorporating beginning balances, project cash flows, and distribution frequency across monthly, quarterly, and annual schedules.

Clean and format the monthly cash flow model, remove zero values, and create a sumif-based total that includes only hold-period cash flows to estimate the next buyer's NOI.

Develop the annual cash flow tab by duplicating the monthly cash flow tab, extending the ten-year hold, and building a dynamic sumifs model with year and month criteria.

Apply finish up steps in the real estate development model by adding right borders, copying sum formulas across annual and monthly cash flows, and preparing the financing tab for projection.

Create the financing tab to model debt cash flows, define equity, mezzanine debt, and senior debt funding, compute percentage of funding, and prepare for operating metrics.

Create an operating metrics table with a header and two timing rows, reference the escrow closing date for months, and outline line items for land/construction/financing costs and cash flow.

Calculate total land, construction, and financing costs during the acquisition and construction periods. Build Excel-driven calculations for land acquisition, loan fees, senior and mezzanine loans, and construction budgets.

Build dynamic operating shortfalls and positive operating cash flow in a monthly cash flow model, using if statements to combine net operating income, capital reserves, and asset management fees.

Build an equity financing table for a real estate project, compute starting and ending equity balances, track equity draws, and verify 25% developer funding before loan proceeds.

Model mezzanine financing by calculating construction loan draws, equity coverage, mezzanine loan proceeds, and capitalized interest to determine ending loan balances and loan payoff, linking financing with sources and uses.

Build out the sources and uses, with hard costs, soft costs, and contingencies. Include tenant improvements, leasing commissions, and construction management fees, then backsolve.

Create a monthly one-month sofr forward curve to project floating interest rates, compute interest expense, capitalized interest, and loan payoffs for construction financing.

Explain how to model mezzanine construction financing with month-by-month loan draws, equity checks, and capitalized interest; compute all-in interest, senior priorities, and payoff timing using Excel formulas.

Learn to model senior construction financing by replicating mezzanine structures. Calculate construction loan draws, interest expenses, and payoff timing with dynamic formulas.

Learn to model permanent financing in a real estate project, including construction loan terms, starting balances, principal and interest payments, loan payoff, and refinancing with an all in interest rate.

Create an interest rate model for permanent financing in Excel, using fixed vs floating logic with if statements and hlookup, and dynamic loan sizing with ltv, ltc, dsr, debt yield.

Develop dynamic permanent financing loan calculations using LTV, LTC, DSR, and debt yield thresholds, then project loan amounts at refinance with forward 12 months NOI and cap rate.

Compute permanent financing loan amounts under a DSR constraint using excel pv and averageifs, incorporating amortizing payments and forward 12 months of net operating income for LTV and debt yield.

Finalize funding checks in a real estate development model by calculating capitalized interest and loan fees with a circuit breaker, and verify equity and mezzanine debt against LTC values.

Finalize the monthly cash flow tab by integrating financing cash flows and debt service. Calculate interest and principal payments, loan payoffs, and loan fees across mezzanine, senior, and permanent financing.

Maximize accuracy in the expenses tab by calculating year-one property taxes and management fees with sumifs and linked cells to the summary tab, using stabilization timing for annualized costs.

Create a dynamic consolidated rent roll in the summary tab, capturing lease up start and end months, tenant names, suite numbers, square footage, base rent, reimbursements, and lease dates.

Build dynamic rent roll formulas with index and match to pull tenant names, square footage, reimbursements, start-end dates, and amount per square foot per year when square footage > 0.

Build a consolidated rent roll by calculating total square footage and a weighted average rent per year, then determine lease-up start and end months with dynamic, error-proof formulas.

Compute stabilized yield on cost and stabilized DSCR at refinance by forecasting forward NOI and applying Excel formulas like sumifs and PMT.

Calculate stabilized debt yield at refinance using 12 months net operating income and loan amount, then determine LTV at refinance with exit cap rate and assess levered and unlevered returns.

Calculate levered and unlevered IRR, equity multiple, and total profit using precise cash flows and dates, then build the average stabilized cash-on-cash return analysis in Excel.

Learn to build annual unlevered and levered cash-on-cash returns from the cash flow tab, using iferror, max, and sumif to manage equity investments and debt service.

Learn to compute levered cash on cash return by adjusting the equity basis for cash out refinance, incorporating loan proceeds, payoff, and fees, year by year.

Calculate levered and unlevered cash-on-cash returns using stabilized periods and lease-up timing, then analyze sensitivity and display dynamic charts of deal performance.

Build a dynamic sensitivity analysis to show how hold period and exit cap rate affect levered IRR and equity multiple, using a data table on the right and interval inputs.

Build a sensitivity analysis in Excel that displays levered IRR and equity multiple together using the text function and a data table fed by hold period and exit cap rate.

Create dynamic visuals to show lease-up timing and monthly occupancy percentage during the lease-up period. Build a stacked area chart of lease-up percentage over time.

Develop a dynamic, cumulative construction cost chart over time using an s-curve, including hard costs, soft costs, contingencies, tenant improvements, leasing commissions, and construction management fee.

Create a pie chart of development costs, including land costs, tea allowances, leasing commissions, hard costs, soft costs, contingency, construction management fees, capitalized interest, and loan fees to visualize allocation.

Build a checks tab to validate key real estate development model calculations using Excel formulas, conditional formatting, and data references for cash flows, equity, and loan balances.

Explore how to use a dynamic real estate development pro forma to test land price, financing, lease-up, expenses, and hold periods, and analyze IRR, cash-on-cash, and returns.

Practice strengthens your real estate development modeling skills by building dynamic models, customizing for different scenarios, and iterating with new data through repeated practice and rebuilding.