The Advanced Real Estate Pro Forma Modeling Master Class

Explore real estate pro forma fundamentals, including inputs, formulas, and outputs for cash flow projections, with emphasis on dynamic models, clean design, and color-coded inputs.

Build an Excel for Windows pro forma with clearly defined inputs and assumptions, applying basic real estate finance and practicing to improve skills.

Build the real estate pro forma model with the instructor to develop skills and muscle memory. Audit your work after each section by downloading the model to reinforce Excel concepts.

Create a clean summary dashboard for a real estate pro forma by removing grid lines, standardizing fonts, and configuring Excel with iterative calculations and data tables.

Rename and set up the summary tab to build a reusable property details table, including year built, net leasable area, land square footage, stories, and address, with consistent formatting.

Learn to build parking and acquisition sections in a real estate pro forma, calculate spaces per 1000ft², set up formulas, and format purchase price, fees, and closing details.

Explore how to model sale information, investor distribution frequency, and project-level fees in a real estate pro forma, including costs of sale, exit cap rate, and hold period.

Build a summary details table that highlights acquisition metrics, loan metrics (ltv, ltc, dsr, debt yield), occupancy, weighted average lease term (walt), and total upfront costs for deal evaluation.

Create a capital cost details table including the common area capital budget, construction management fees, tie allowances, leasing commissions, and construction timeline, plus a yes/no upfront working capital trigger.

Build a sale details table covering sale date, exit cap rate, cap rate expansion per year, sale price, costs of sale, and value CAGR.

Build levered and unlevered return metrics for real estate pro forma models, including internal rate of return, equity multiple, and cash on cash return, with formatting for sources and uses.

Build sources and uses tables for financing, modeling debt and equity including mezzanine loans, with currency formatting, covering total sources and uses at close, purchase price, and closing costs.

Add the IRR partitioning and circuit breaker to the summary tab, include operations versus sale or refinance contributions, and set an on/off data validation.

Design and populate the leases tab to capture in-place tenant details, pro rata shares, and reimbursement structures for long-term cash flows, including triple net, full-service gross, and base-year stop options.

Learn to model operating expense reimbursement structures in real estate pro forma, selecting reimbursed expenses for modified gross or base year stop, and setting base year stop amounts in Excel.

Learn to build a rent escalation schedule using in place lease amounts, start end dates, and per square foot per year growth, illustrated with a Trader Joe's sample deal.

Build releasing assumptions in a real estate pro forma, detailing renewal probability, lease terms, downtime, free rent, market rent, growth per year, and reimbursements for new and renewal leases.

Explore modeling releasing costs and reimbursement structures in a real estate pro forma, including tenant improvement allowances, growth, leasing commissions by new and renewal leases, and percentage rent considerations.

Model percentage rent by adding annual sales, health ratio, breakpoint, and a yes/no trigger; project sales growth and tenant cash flows through the hold period.

Set up the tenant cash flow table with a monthly timeline (year, month, month ending). Extend to 360 months to capture leasing commissions, using eomonth from the escrow closing date.

This lecture shows how to dynamically calculate releasing months for each suite in a pro forma model using Excel functions if, or, round, and eomonth, considering renewal probability and downtime.

Create and format a tenant cash flow table for pro forma modeling, featuring dynamic CF codes, in-place and releasing rent, reimbursements, capital costs, and total suite cash flow.

Build dynamic in-place base rent formulas tied to the rent escalation schedule, copying across months, using if and approximate match with vlookup on start/end dates and square footage.

Create placeholder operating expense variables and ending values in leases tab to drive in-place reimbursements, then apply 3% growth on monthly basis across expense line items in tenant cash flows.

Model in-place operating expense reimbursements with nested if statements, testing start-end dates and reimbursement structures (triple net, full service gross, modified gross) using pro rata shares and base year stop.

Build and test total percentage rent formulas using placeholder annual sales, breakpoint, and growth assumptions. Use a yes/no trigger and max safeguards to compute monthly rent within in-place lease term.

model upfront free rent as a negative base rent for the first six months using excel if statements, accounting for acquisition date and vacant suite.

Model releasing base rent using if and or logic, calculate weighted downtime with renewal probability, and project future market rent growth via fv, year frac, and rounding for releasing scenarios.

Utilize weighted average downtime and weighted average free rent, guided by renewal probability, to calculate releasing free rent, adjust releasing base rent, and prepare releasing reimbursements with dynamic renewal scenarios.

Explore modeling releasing reimbursements with a timing-driven Excel framework, using multiple nested if statements to determine downtime, renewals, and pro rata shares for base year stop and modified gross structures.

model upfront tenant improvement allowances in a real estate pro forma by triggering payments in the month before lease start, applying t growth rate, and calculating a negative cash outflow.

Calculate upfront leasing commissions by applying the first five years of net base rent to commission percentages, triggered in the month before a new lease starts, using sumifs and eomonth.

Model releasing tenant improvements after the in-place lease term by calculating TI allowances with weighted average downtime, renewal probability, and annual growth, to derive a present-value amount per square foot.

Build and dynamically calculate releasing leasing commissions using offset and sum if function, incorporating weighted average downtime and renewal probability across the first five years of the pro forma.

Explore building cash flow codes and totals for lease analysis, using codes like BR, CAM, PR, FR, BRR, T, LC, and dynamic sumif formulas to capture hold-period rent and reimbursements.

Group and insert new tenant rows, copy inputs to add ten suites, and collapse views for a cleaner rent roll while preserving formulas.

Add suite cash flows to the model by copying tenant inputs and formulas, ensuring dynamic, correctly spaced tables with rent escalations, dates, and reimbursements.

Add tenants to the pro forma by modeling Nike store, Apple store, and Disney store with triple net structures, rent escalations, renewal probabilities, downtime, and TI allowances.

Build monthly occupancy calculations by linking square footage per suite to tenant cash flows using indirect references and a dynamic 16-row pattern in Excel.

Develop a dynamic Excel occupancy model by linking suite labels with indirect references, using index and match to count monthly square footage from in-place or releasing rents, and project totals.

Wrap up the leases tab by calculating total occupied and leasable square footage and computing monthly occupancy with an iferror safeguard.

Finalize the cre leases tab by adjusting pro rata share with the net leasable area, then compute tenant health ratio from occupancy costs as a share of annual sales.

Set up the expenses tab to build expense cash flows, using t-12 operating expenses and hold period assumptions, with categories for property taxes, insurance, cam, management fees, and a catch-all.

Learn to build hold period operating expenses and property tax information for real estate pro forma models, including millage rates, fixed charge assessments, reassessment triggers, and percentage of value assessed.

Develop operating expense assumptions, including a property management fee, annual expense growth, and capital reserves per square foot, and model year-by-year credit loss and property tax growth.

Build and model property tax growth rates and hold period property taxes with annual calculations, incorporating assessed value, fixed charge assessments, tax credits, and a total property taxes line.

Model hold period property taxes by applying a static 1.2% tax rate, calculating assessed value with a reassessed upon purchase toggle, and incorporating growth, fixed charges, and credits.

Build operating expense calculations in the expenses tab using eomonth dates and formulas for property taxes, insurance, and CAM, applying 3% annual growth linked to the monthly cash flow tab.

Set up a construction budget tab to capture capital costs not tied to leases, including line items, per-square-foot costs, start and end months, total months, and monthly cash flow calculations.

Create a subtotal row in Excel for construction budgets, summing totals and per square foot costs, while using min, max, and if to derive start date, end date, and months.

Import the timeline from the expenses tab to build construction cash flows, then apply a dynamic if-then formula to spread costs monthly.

Create an error-checking row in the pro forma using an if statement to compare item totals with the total construction cost, using abs difference under 0.01.

Establish a contingency buffer in the capital budget by setting a 10% placeholder, calculating monthly contingency amounts, and updating the total capital budget for accurate cash flow.

Create a monthly cash flow tab that integrates revenue, operating expenses, and capital costs into 11-year projections, including acquisition date, timeline development, eomonth dates, and quarterly calculations.

Model monthly cash flow by calculating net rental income from base rent, free rent, and credit loss. Learn Excel formatting and dynamic formulas to build a real estate pro forma.

Apply credit loss percentages to base rental revenue net of free rent in the monthly cash flow using an HLOOKUP tied to the current year from the expenses tab.

Add and calculate other income items, including operating expense reimbursements and percentage rent, then sum net rental income with total other income to yield effective gross revenue for noi.

Learn to model operating expenses in the monthly cash flow, reflect cash outflows as negatives, and calculate net operating income from effective gross revenue minus expenses.

Develop a monthly cash flow for capital and partnership expenses, including construction costs, TI, LC, and capital reserves, with hold-period rules for TI and LC.

Model capital expense reserves, asset management fees, and construction management fees within a monthly real estate pro forma, applying hold periods, per-square-foot calculations, and annual expense growth.

Develop and track unlevered working capital with an ending balance and distributions from construction costs, tenant improvement allowances, leasing commissions, and operating shortfalls to compute cash flow before debt service.

Model debt service by separating interest and principal to compute total debt service. Use levered and unlevered working capital distributions to determine cash flow after debt service.

Model acquisition and sale cash flows, including purchase price, acquisition fee, closing costs, upfront working capital, sale proceeds, and disposition costs, to compute total unlevered cash flow before debt.

Build a complete levered cash flow model by detailing loan information—initial and additional loan proceeds, fees, prepayments, and payoff—then compute total levered cash flow and plan investor distributions.

Develop investor level cash flows by differentiating them from project level cash flows, adding beginning cash balance, capital contributions, capital distributions, ending cash balance, and a dynamic monthly distribution formula.

Develop the capital distributions model to allocate investor-level cash flows by distribution frequency and timing of monthly, quarterly, or annual cycles, incorporating beginning cash and project-level cash flows.

Build a dynamic total cash flow column using sumif to sum hold-period cash flows, including revenue, other income, expenses, and net operating income across 11 years.

Copy the monthly cash flow model to an annual tab, set a ten-year horizon, adjust year and month timelines, and build dynamic sumifs-based annual cash flows.

Build annual cash flows in a real estate pro forma by summing cash flows and updating beginning and ending cash balances. Prepare for financing calculations in the next section.

Create the financing tab in the advanced real estate pro forma to model acquisition, mezzanine, and refinancing loans with LTV, LTC, DSCR, debt yield sizing and terms.

Configure debt metric inputs for acquisition, mezzanine, and refinancing loans using a size-by dropdown (LTV, LTC, DSR, debt yield), with manual blue inputs, rates, caps, and fees.

Learn to build a max proceeds table and size loans using LTV, LTC, DSR, and debt yield from NOI and purchase price in dynamic acquisition loan sizing.

Showcases dynamic mezzanine loan sizing at a 65% ltv, adjusting assumptions, and computing proceeds with max, index, and match to align acquisition and refinance outcomes.

Size mezzanine loan proceeds under debt yield and dsr constraints using max, iferror, pv, and pmt, then verify against NOI and acquisition loan payments.

demonstrate dynamic refinance loan sizing by using a minimum dsr, calculating noi-based value, applying cap rate and max ltv, and analyzing hold periods and prepayment penalties.

Compute dynamic refinance loan sizing by tying upfront costs to the LTC limit, using a PV-based loan amount, applying NOI within a DSR and debt yield framework, and hold-period constraints.

Build the acquisition financing table and checks row, add a cash flow code column, and copy formatting from the monthly cash flow tab for months one through 120.

Set the starting loan balance in month one from the acquisition loan amount and compute additional loan proceeds only during the acquisition and hold periods, funded by a financing percentage.

Learn to pull forward curves from Chatham Financial, apply sofr forward curves to a real estate pro forma, and build monthly floating rate payments in Excel.

Build a dynamic monthly interest rate in a real estate pro forma by switching between fixed and floating paths, using a forward SOFR curve, spread, and index lookups.

Calculate total loan payments by modeling interest-only period and a floating-rate PMT-based amortization, using dynamic tests, starting loan balance, and monthly rates.

Model monthly interest and principal payments in a real estate pro forma using an if statement, calculating interest from the starting balance plus loan proceeds during the acquisition hold period.

Model the loan payoff at sale or term end by summing starting balance and new proceeds, subtracting principal paid, and applying prepayment penalties to compute the ending balance.

Build a total column for acquisition financing cash flows, include a payoff check with tolerance, and apply cash flow codes (lp, pp, IPI, prp) for mezzanine planning.

Builds out the mezzanine financing table by copying the acquisition table, linking month ending values, and adjusting balances, loan proceeds, and interest terms to ensure accurate cash flows.

Build refinancing cash flow models in Excel by creating dynamic refinance proceeds and starting loan balance calculations that return zero outside the hold period using nested if and or functions.

Adjust the timing trigger to fund additional refinance loan proceeds only during the refinance period, update funding percentages, and align cash flow for construction expenses, tenant improvements, and leasing commissions.

Learn to model the total refinance loan payment by building a dynamic, interest-only to amortizing schedule using assumptions, hold periods, and the PMT function.

Apply Excel-based refinancing modeling to calculate interest and principal payments, loan payoff, and prepayment penalties using timing triggers, payoff rules, and all-in interest rate calculations.

Finalize the monthly cash flow tab by calculating debt service payments, interest and principal payments, and initial loan proceeds, including refinance provisions.

Model acquisition and mezzanine loan fees as a percentage of loan proceeds, funded at acquisition or refinance, and reflect them as negative cash flow in the monthly cash flow tab.

Build a dynamic property tax model using if statements and hlookup for reassessed sale values, and include management fees with a circuit breaker to prevent circular references.

Finalize the summary tab by calculating going in cap rate from year one NOI and purchase price, and evaluate LTV, LTC, DSR, and debt yield from acquisition and mezzanine loans.

Set dynamic occupancy from month one and build the upfront cost model by totaling purchase price, fees, and capital budgets; compute all-in costs per square foot.

Finalize sale details by deriving sale price from monthly cash flows, applying exit cap rate, and calculating levered IRR and levered equity multiple.

Compute unlevered and levered cash-on-cash returns for each year using cash flow before debt service, adjusted for refinancing, with dynamic formulas to keep values nonnegative.

Learn to model levered cash-on-cash returns by calculating cash out at refinance, incorporating refinance proceeds, loan payoffs, and penalties, and updating adjusted levered equity basis year by year.

Compute levered and unlevered cash-on-cash returns, IRR, and equity multiple from multi-year cash flows using Excel functions such as averageif and sumif, with period filtering.

Finalize the sources and uses at close by calculating total sources, including acquisition and mezzanine loan proceeds and equity, from the monthly cash flow.

Partition the levered IRR by separating cash flow from operations and sale/refinancing, compute their NPVs with levered IRR as the discount rate, and prepare a sensitivity analysis.

Develop a dual-metric, two-variable sensitivity table to show how levered IRR and equity multiple respond to hold period and exit cap rate, with dynamic text outputs in Excel.

Learn to build a consolidated rent roll for real estate pro forma modeling, including suite, tenant, sf, rent per year, reimbursements, and lease end dates with weighted average lease term.

Build a dynamic rent roll using index and match to pull tenant names, square footage, reimbursements, and end dates across all suites. Apply the same formula to other columns.

Use index and match to compute in-place rent per square foot per year, identify the suite, multiply by 12, divide by square footage, and handle errors with zero.

Build a consolidated rent roll by calculating total square footage and a weighted average base rent per square foot, then determine weighted end date and lease term with yearfrac.

Build and populate a checks tab to verify monthly cash flows align with annual cash flows, ensure equity equals negative cash flows, and confirm funded construction and loan balances.

Validate monthly versus annual cash flows within a real estate pro forma using an exacting 0.01 threshold, and confirm equity contributions align with projected capital inflows.

Build dynamic checks to verify construction funding during the hold period, test loan balances and upfront working capital, and consolidate results for chart-ready property projections.

Build dynamic NOI charts by creating data tables, formatting currency, and using formulas to project net operating income within a hold period and sale date.

Track average annual occupancy and dscr over time in the summary tab using Excel, referencing the leases and annual cash flow tabs and applying average if calculations.

Create and customize dynamic pro forma charts for net operating income, occupancy, DSCR, and IRR partitioning, using line, column, and pie charts that update with hold period changes.

Create a dynamic donut chart showing each tenant's share of base rent by calculating tenant percentages from the area and rent per square foot, then display the IRR partitioning alongside.

Use the summary tab to adjust purchase price and upfront working capital to hit target levered IRR, guiding offers and equity planning; perform hold-period and sensitivity analyses.

Develop your real estate financial modeling skills through practice and reps, customize dynamic pro forma templates, and iterate cash-flow models for varied deal scenarios.