The Advanced Real Estate Pro Forma Modeling Master Class

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.

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.

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 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.

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.

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.

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.

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.

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.

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.

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.

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.

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 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.

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.

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.

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.

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 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.

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.

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.

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 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 NOI charts by creating data tables, formatting currency, and using formulas to project net operating income within a hold period and sale date.