The Commercial Real Estate Lease Modeling Master Class

Explore commercial real estate lease modeling fundamentals, including in-place tenants, base rent escalation, operating expense reimbursements, and retail percentage rent, with releasing assumptions and market rent forecasting.

Learn to model in-place rent in a commercial lease, input start and end dates, and implement annual and uneven escalations as well as increases every x years, using Excel.

Learn to model in-place rent in Excel with dynamic lease start and end dates, index/match to rent per square foot per year, and an array formula for monthly rent.

Model operating expense reimbursements in Excel across four structures—triple net, full service gross, modified gross, and base year stop—to compare tenant costs.

Calculate triple net reimbursements across five years by applying each tenant’s pro rata share to all operating expenses, using dynamic formulas and blue input cells.

Learn to calculate full service gross lease reimbursements by setting operating expense reimbursements to zero, showing the landlord pays all expenses, and preview the modified gross lease hybrid.

Build a dynamic modified gross reimbursements model with yes/no expense flags. Calculate 25 percent of 12,000 and 3,500, 0 percent of 5,000, and apply the pro rata share.

In the commercial real estate lease modeling master class, learn to calculate base year stop reimbursements using a dynamic Excel formula, applying the tenants pro-rata share and guarding against negatives.

Build a dynamic Excel model to compute expense reimbursement income using index and match, selecting from triple net, full service, gross modified gross, or base year stop by year.

Explore how percentage rent works for retail tenants, comparing fixed and natural breakpoints, how base rent sets the natural breakpoint, and calculating overage in Excel.

Learn to model percentage rent over a 10-year period in Excel, calculating base rent, tenant health ratio, natural breakpoint, and the percentage rent owed.

Explain how tenant improvement allowances and leasing commissions create upfront costs in commercial real estate leases, including upfront payment timing and tiered commission structures.

Learn to model tenant improvements and leasing commissions in a 10-year commercial lease, calculating TI allowances, scheduled and net base rent, free rent, and commissions.

Explore renewal probability and re-leasing scenario modeling to forecast cash flows from in-place lease expirations, using a weighted average for renewal versus new leases, including base rent and free rent.

Explore weighted average renewal modeling for commercial leases by integrating renewal probability, in-place expirations, market rent scenarios, downtime, free rent, allowances, and leasing commissions to project cash flows.

Build a 10-year releasing cash flow model in Excel, incorporating in-place lease expiration, downtime, rent growth, and line items such as base rent, free rent, tenant improvements, and leasing commissions.

Master releasing free rent in a re-leasing scenario. Calculate releasing base rent and free rent months, including potential second-year bleed using market rent per square foot and growth rate.

Build a commercial real estate lease model in Excel, creating a summary dashboard with property details, analysis start date, analysis length, occupancy, and weighted average lease term remaining.

Show how to build a lease model using blue manual inputs to drive formulas, set a Dec 31, 2021 start date, and format analysis length in years.

Build an in-place rent roll with six columns, including tenant, suite number, square footage, rent per year per sf, reimbursements, and lease end date, with dynamic totals and weighted averages.

Master dynamic rent modeling by calculating total square footage, applying sumproduct to weight rent per square foot per year, and deriving the weighted average lease end date (WALT) and term.

Build a tenant cash flow summary table to compare base rent, free rent, reimbursements, percentage rent, tenant improvements, and leasing commissions, releasing cash flow and prepare an npv analysis.

Learn to build a discounted cash flow analysis for tenant cash flows, apply a discount rate, and compute discounted tenant cash flow to evaluate real estate deals.

Create tenant assumptions year by year for eleven years, including a year zero value, apply custom formatting for dynamic cash-flow modeling, and add annual credit loss and metrics.

Build an annual cash flow dashboard for commercial real estate leases by mapping tenant assumptions, occupancy, operating expense reimbursements, net operating income, and annual tenant cash flow.

Build the commercial real estate leases tab to capture in-place tenant details, reimbursements, and per-tenant cash flows while using a single tenant model to scale to ten leases.

Build and format in-place reimbursement structures table in excel with four columns for expenses, modified gross, base-year stop, and base-year stop amount; include property taxes, insurance, cam, and management fee.

Build the rent escalation schedule for the in-place lease in Excel, outlining start and end dates, rent per square foot per year, and automatic annual increases.

Model releasing assumptions for re-leasing in commercial real estate by building a comprehensive schedule with renewal probability, lease terms, downtime, free rent, market rent, growth, and reimbursement structures.

Model leasing costs for commercial real estate, including upfront tie, leasing commissions, vacancy and renewal scenarios, using a seven-column layout with per-square-foot inputs and year-by-year details.

Build out percentage rent assumptions by setting tenant annual sales, health ratio, and breakpoint, then model overages with yearly sales growth and dropdown controls, culminating in monthly cash flow timelines.

Create a dynamic tenant cash flow timeline in Excel, modeling monthly cash flows for up to 31 years, with automated year assignment, month endings, and ready-to-copy placeholders for future leases.

Set up two header rows with dynamic cell combinations to show total cash flows and suite and tenant names, using a concatenation formula for labels in the cash flow model.

Model in-place base rent cash flows using an index–match array formula wrapped in if error, driven by a rent escalation schedule and tenant square footage.

Create a dedicated operating expense sheet with assumptions for annual expense growth, property management fees, and capital reserves per square foot to support in-place reimbursements and monthly cash-flow modeling.

Build and format monthly operating expenses for commercial real estate leases, including property taxes, insurance, common area maintenance, and management fees, using a consistent month-end timeline and blue manual inputs.

Set up placeholder monthly operating expenses and create dynamic formulas applying annual growth to taxes and insurance and monthly growth to cam and other costs, and calculate total operating expenses.

Learn to model in-place reimbursements in commercial leases using Excel formulas, handling triple net, full service gross, modified gross, and base year stop structures with pro rata shares.

Learn to model percentage rent cash flows in Excel by calculating total percentage rent from annual sales, base rent, and natural breakpoint, using or logic and year-based growth.

Model upfront free rent by timing when a vacant suite starts a new lease. Negate in-place base rent during the six month upfront period; apply releasing based rent after expiration.

Learn to build releasing base rent cash flows by applying renewal probability and market rent growth, deriving a weighted average lease term in months, downtime, and monthly future value calculations.

Model re-leasing free rent using timing triggers, weighted downtime, and renewal probability to determine months of free rent for new and renewal leases.

Reuse the releasing base rent and in-place reimbursement formulas to model releasing reimbursements within the lease term using the timing trigger. Finalize total suite operating cash flow.

Model up front tenant improvements as a negative cash outflow funded in the month prior to the lease start, using weighted average TII growth and present value.

Model upfront leasing commissions by splitting first five years of base rent from years six and beyond, paid prior to start date, using Excel calculations for new leases.

Explore modeling releasing tenant improvements and leasing commissions in commercial real estate leases, incorporating timing, renewal probability, weighted average ties per square foot, and present-value calculations.

Models releasing leasing commissions within a releasing scenario, calculating releasing base rent, downtime, free rent, renewal probability, and the total net sweet cash flow.

Calculate total tenant cash flows over a 10-year analysis using the summary tab and create cash flow codes for base rent, reimbursements, and leasing commissions.

Group cells to simplify the rent roll and view ten tenants at a glance, then dynamically copy, paste, and consolidate leases for tenant cash flows analysis.

Build dynamic cash flows for multiple suites by copying existing formulas, expanding suites, and testing how square footage and pro rata share affect base rent and reimbursements, monthly occupancy calculations.

Learn to build dynamic occupancy calculations at the bottom of the sheet, linking tenant square footage and suites to monthly occupancy across the analysis timeline.

Learn to build dynamic occupancy calculations in Excel using index, match, and countif to compute each tenant's occupied square footage per month, including in-place and releasing base rent.

Finalize the occupancy table and compute the net leasable area, then determine each tenant's pro rata share and health ratio using dynamic Excel formulas for monthly and annual cash flows.

Build and format monthly cash flow tab, copying formatting from screen releases tab, consolidating base rental revenue, free rent, and credit loss into net rental income and an annual view.

Calculate month one base rental revenue with sumif on cash flow codes, copy across months, add free rent during releasing periods, and apply credit loss via lookup to rental income.

Model other income by including Arcam reimbursements and percentage rent, define the line items, and compute total other income and the effective gross revenue for operating expense planning.

Link line items to the expenses tab to automate operating expenses. Calculate management fees from effective gross revenue, and derive monthly net operating income with capital expenses.

Modeling tenant improvements and leasing commissions, the lecture builds capital expenses and total tenant cash flow using excel-like formulas, linking TI and LC with monthly cash flows and noi.

Master the sum if function on the monthly cash flow tab to sum values for the analysis period, then consolidate into the annual cash flow tab.

convert the monthly cash flow tab to an annual view for a 10-year analysis by setting year endings and trimming columns. create a dynamic formula to sum yearly line items.

Learn to build a dynamic annual cash flow model in Excel using SUMIFS with multiple criteria, linking monthly to annual data and automating rent, CAM, and cash flow.

Finalize the summary tab by building dynamic formulas for suite count, net leasable area, and occupancy, and link to the monthly cash flow tab and the discounted cash flow analysis.

Develop a dynamic discounted cash flow model for commercial real estate leases, computing total tenant cash flow, applying a discount rate, and using Excel XNPV to evaluate monthly cash flows.

Build an in-place consolidated rent roll using dynamic index and match to populate suite numbers and tenant data, then compute rent per square foot per year with a dynamic formula.

Create a helper column for suite and tenant, then use index and match to compute monthly base rent per year per square foot for the tenant cash flow summary.

Build dynamic tenant cash flow and releasing cash flow summaries using a dynamic drop-down, data validation, and index and match formulas across the summary and releases tabs.

Learn to compute dynamic annual occupancy using averageif with year criteria, wrap in iferror, and project net operating income, leasing commissions, tenant improvements, and tenant cash flow chart.

Learn to build annual operating metrics by dynamically calculating net operating income and total tenant cash flow across the analysis period using if statements and index-match.

Build a dynamic tenant cash flow chart in Excel to display annual net operating income and total tenant cash flow through year 10, using a currency area chart.

Learn how to use a three-tenant retail lease model to test scenarios, adjust square footage, lease terms, rents, and renewals, and see dynamic cash-flow impacts.

Practice building and refining commercial lease models to apply to existing multifamily and retail or office scenarios, then advance to a full pro forma using our advanced guidance.