The Advanced Real Estate Financial Modeling Bootcamp

Master advanced Excel functions and formulas to build dynamic, cleaner real estate financial models. Learn naming cells, index-match, and yearfrac; explore ampersand, countif, and sorting for dynamic data.

Learn how to name individual cells in a real estate financial model to create readable references and simplify auditing, then build a formula for effective gross revenue using named cells.

Learn to combine index and match to dynamically return values at the intersection of two criteria, enabling interactive Excel models like a rent roll that updates by player and category.

Master dynamic conditional formatting in Excel to auto format lease start and expiration dates, display square footage with thousand separators, and apply index match for unique values across formats.

Combine the if function with isnumber and search to test if specific text exists in a cell, returning 'three bedrooms' for three-bedroom units and blank otherwise, with dynamic copy down.

Master the ampersand in Excel to dynamically combine unit type and renovation status in the rent roll, using an if statement and dash separators.

Learn to use partial count if to count cells containing loft, den, penthouse, and premium upgrade in a rent roll, using asterisks and dynamic criteria.

Learn to compute lease terms in full years using the year frac function and round down, capturing exact years elapsed between start and end dates with actual/actual basis.

Sort and filter a rent roll to clean data and view specific units, sorting by square footage (smallest to largest), unit type (a to z), and rent per month.

Use Excel's unique function to build a dynamic consolidated unit mix from a rent roll, then compute average square footage, rent, rent per square foot, and lease terms.

Use the mod and row functions with conditional formatting to highlight every other row in a rent roll, creating dynamic formatting that updates when rows are added or removed.

Master dynamic data reshaping in Excel by using the transpose function to convert a vertical list of tenants to a horizontal row, updating automatically as source values change.

Explore advanced real estate deal analysis by calculating break-even occupancy and applying dual and three-variable data tables, next buyer analysis, and IRR partitioning to optimize sale year and cash flows.

Explore dual return metric data tables to see purchase price and exit cap rate affect levered IRR and equity multiple. Learn to set up a two-metric sensitivity analysis in Excel.

Explore a three-variable data table to perform sensitivity analysis, measuring how rent growth, general vacancy, and exit cap rate affect levered IRR with dynamic revenue calculations in Excel.

Build a dynamic best, base, and weak case analysis in Excel, using index and match to drive vacancy, rent growth, and exit cap rate across scenarios via a dropdown.

Explore the next buyer analysis to justify the projected sale price using the next buyer’s unlevered IRR and an implied exit cap rate, incorporating acquisition costs and costs of sale.

Model next buyer sale proceeds using forward NOI and exit cap rate, deduct cost of sale, and compute unlevered cash flow to infer implied going-in cap rate and purchase price.

Compute the next buyer's unlevered irr using excel ex irr with a dynamic start date via offset tied to the sale year and the net unlevered cash flows.

IRR partitioning shows how returns split between operations and sale proceeds, for unlevered and levered IRR, by discounting cash flows with PV at 10.57% and 16.49%.

Partition IRR into operating and sale contributions by calculating unlevered and levered net sale proceeds with PV discounts, incorporating cost of sale and loan payoff.

Learn to use hold-sell analysis in Excel to identify the optimal sale year for maximizing IRR, comparing unlevered and levered cash flows across multiple hold periods.

Build levered cash flows for hold periods in Excel, compute cash flow after debt service and loan payoff, then identify the optimal sale year using IRR.

Explore advanced revenue and expense modeling for real estate, including gradual multifamily lease-up, renovation premiums, commercial turnover with renewal probability, and dynamic growth rates.

Dynamically compute the monthly lease-up progress with if statements and a min function to drive general vacancy and ultimately effective gross revenue, highlighting the crossover to stabilized general vacancy.

Build operating expenses and capital expense reserves for a multifamily lease-up using per-unit annual costs, monthly allocations, and year-over-year growth with dynamic start month.

Model renovation premium for an existing multifamily property, comparing in-place rents to post-renovation rents within a 12–31 month window and a 50% unit renovation plan, including vacancy dynamics.

Model straight-line renovation costs from a $1,050,000 budget over 20 months. Use a weighted rent mix of in-place and renovated rents, with 10% vacancy during renovation and 7% otherwise.

Model commercial lease turnover by integrating in-place lease terms, renewal probability, and releasing assumptions with market rent growth, downtime, tenant improvements, leasing commissions, and new and renewal lease terms.

Model commercial lease turnover with renewal probability, downtime, and weighted average terms for new and renewal rents. Use a present-value rent calculation, based on market rent growth and square footage.

Model cash outflows for tenant improvements and leasing commissions before lease release, using t per square foot, downtime, renewal probability, and t growth to project staged costs.

The real estate financial modeling bootcamp lecture models varied year over year growth rates for rents and expenses, with monthly or annual growth toggles and vlookup for revenue and vacancy.

Roll up monthly cash flows into annual and quarterly views, while modeling dynamic working capital, fixed and variable expenses, and property tax resets for occupancy-driven scenarios.

Roll up quarterly cash flows by updating quarter numbers, copying sheets, and using the sum if function to compute levered net cash flow per quarter from acquisition through 40 quarters.

Model working capital accounts in real estate finance, including upfront capital funding, distributions, and monthly ending balances. Manage unlevered and levered working capital distributions and track construction capital in Excel.

Explain how to model unlevered working capital distributions, incorporating ending balances, minimum working capital, excess distributions, and sale-month triggers, using max and if statements to ensure positive cash flows.

Learn to build a dynamic fixed and variable expense model in Excel that links occupancy and vacancy to taxes, insurance, cam, and utilities for operating costs across a 10-year period.

Model property tax resets in excel by dynamically calculating reassessed values at acquisition and sale using current assessed value, millage rate, growth rate, and reassessment triggers.

Learn floating rate debt modeling with forward index curve assumptions, amortizing loans, refinances, future funding for tenant improvements and construction costs, mezzanine loans, and dynamic equity distributions.

Explore modeling floating-rate debt with a dynamic 120-month amortization, mixing interest-only periods with principal payments using Excel PMT, index rate, and interest-rate spread for a variable rate.

Model a refinance in excel with tables: acquisition loan terms at 4.7% all-in and a 24-month interest-only period, followed by a 5% refinance at month 36 with a 60-month payoff.

Model refinances from an acquisition baseline by zeroing values until the refinance month, then compute loan funding, interest, principal, and payoff through a pmt-based amortization schedule.

Model future loan funding or good news money to cover construction costs, allowances, and leasing commissions, using a $5 million acquisition loan with up to $500,000 additional proceeds.

Model the initial loan funding during the acquisition period, cap additional loan proceeds at the maximum, and compute monthly payments, interest, principal, and payoff with PMT for good news money.

Model a ground-up development construction loan, timing loan proceeds, require equity, capitalize interest during construction, and compute payoff using the loan-to-cost ratio and total project cost in Excel.

Master construction loan cash flow modeling by tracking operating income shortfalls and post-construction NOI growth, using if, and, max, and min formulas to reach breakeven timing.

Learn equity financing for construction loans, calculating the starting equity balance and 30/70 funding split while building sources and uses to model land costs, construction costs, and equity draws.

Model construction financing cash flows by calculating construction loan draws from equity and costs, applying a loan-to-cost trigger, and tracking capitalized interest, interest expense, and loan payoff.

Model mezzanine construction loans as a second layer of debt for ground-up development, integrating equity, senior construction financing, and mezzanine sources and uses in a two-layer funding structure.

Explore mezzanine construction loan modeling as a junior financing layer, learn funding order with senior lender priority, calculate draws and capitalized interest, and align loan proceeds with mezzanine limits.

Explore senior construction financing by calculating loan draws, capitalized interest, and payoff to determine ending balances, while analyzing equity, mezzanine, and senior debt sources, yield maintenance calculations, and prepayment penalty.

Learn how to model yield maintenance in commercial real estate finance, calculating remaining terms, outstanding balance, and the prepayment penalty with a yield-based formula in Excel.

Explore part 1 of investor distribution modeling, forecasting quarterly and annual distributions and tracking beginning balances, levered net cash flow, capital contributions, and investor irr and equity multiple.

Build a dynamic Excel model using nested if statements to calculate quarterly, monthly, or annual capital distributions and key investor metrics like IRR and equity multiple.

Dynamically model a preferred equity financing in real estate, compute current and accrued returns, equity contributions, and cash flows to common and preferred investors with a senior loan.

Build a dynamic model for total common and preferred equity net cash flow from levered cash flows. Compute IRR and equity multiples for both holders with preferred payment priority.

Build a dynamic monthly pro forma for a multifamily acquisition using rent roll and T12 operating expenses inputs. Analyze debt, equity, and sale metrics to answer exam questions.

Build a timeline by adding year, month, and month ending rows, generate months to 132, and dynamically calculate year values with round up for levered and unlevered IRR.

Learn to build revenue modeling in Excel, including gross potential rent, vacancy, other income, and effective gross revenue, plus in place and renovated market rent and renovation progress.

Calculate month two projections for in place and renovated market rents using year-based growth via Vlookup and 1/12 monthly adjustments; determine renovation progress and gross potential rent.

Weight gross potential rent by renovation completion to blend in place and renovated market rents, then apply general vacancy and monthly other income growth to derive effective gross revenue.

Learn to build dynamic operating expense models for real estate, including management fee as a percent of effective gross revenue and monthly growth of T12 expenses with precise Excel formulas.

Model real estate taxes with month one and month two formulas, incorporating reassessment triggers, annual growth, and alignment with NOI and operating expenses.

In this module, create and format capital expenses, define capital improvements and reserves, and allocate total capital expenses monthly based on improvements per unit over the capital improvement period.

Implement unlevered working capital distributions to fund cash flow before debt service during construction, track monthly working capital ending balances, and apply a hold period to cap distributions.

Build a refinance debt service model in Excel, calculating principal and interest payments with PMT and IPMT, using and tests to honor hold periods and interest-only phases.

Model levered working capital distributions and cash flow after debt service, using dynamic tests to cover deficits and allocate distributions from prior balances to fund the upfront capital raise.

Link acquisition and sale information to dynamically calculate sale proceeds and unlevered net cash flow, incorporating purchase price, closing costs, upfront working capital, sale costs, and exit cap rate.

Learn to model acquisition and refinance financing information, including loan proceeds, fees, and payoffs, and calculate levered net cash flow using DSR, debt yield, and LTV constraints with NOI.

Develop and model refinance loan proceeds in a real estate financial model by testing acquisition terms, calculating forward 12 months NOI, applying DSCR and debt yield constraints, and scheduling payments.

Develop a dynamic excel model to compute acquisition and refinance loan payoffs, using if, or, and logic to reveal outstanding loan balances across the hold period to the sale month. Incorporate acquisition period loan fees, principal payments, and financing information to derive the bottom-line levered net cash flow and initial equity investment.

Calculate the going-in cap rate from month one NOI, then compute the going-in loan constant with an amortizing PMT, and finally assess unlevered and levered IRR via XIRR.

Compute unlevered and levered equity multiples from distributions and equity contributions using sumifs, then determine stabilized NOI yield on cost with forward 12 months NOI and total project cost.

Calculate the compound annual growth rate from purchase to sale using Excel rate, yearfrac, and eomonth, and determine dynamic cash flows to hit a 14% levered IRR.

Practice builds muscle memory; repeat the exercises to solidify skills. Remember that advanced doesn't mean overly complex; use simple Excel functions and treat this course as a reusable resource.