Mastering Real Estate Financial Modeling

Explore how real estate was divided into mortgage-backed securities, driven by lax credit criteria and the roles of Fannie Mae, Freddie Mac, SPVs, CDOs, and AIG, leading to 2008 meltdown.

Explore how real estate weathered the 2008 mortgage meltdown and post-demonetisation growth in India, and learn to evaluate investments using criteria, dscr, lease rental, and irr with Excel models.

Explore opportunities in India's real estate as rising investments, population growth, and smart city plans boost demand, with government schemes and stricter laws shaping equity and debt capital markets.

Explore residential real estate types, from single- and multi-family homes to attached dwellings like apartments, duplexes, townhouses, villas, and bungalows, including cooperative housing and portable dwellings.

Explore commercial real estate basics, including lease types (single net, double net, triple net, gross) and office class designations (class a, class b, class c) and their effect on rent.

Explore six commercial real estate categories: office, industrial, retail, multi-family, land, and parking, and review cash flow components such as rent, operating expenses, and debt service.

Explore greenfield and brownfield land, infill and land recycling in urban planning, including redevelopment, economies of scale, environmental contamination, and regional regulatory frameworks.

Identify the cost of a property: land acquisition, land development, marketing, and overheads, then explore financing, including debt, private equity in real estate, and joint development or venture models.

Assess the lender's risk in real estate finance. Focus on credit risk and default probability with rating scales (AAA to D), and on market and regulatory risks, the RERA act.

Explains borrower risks in real estate finance, including market valuation decline, counterparty and tenant defaults, interest rate and liquidity risks, and hedging with forward rate agreements under RERA.

Assess liquidity risk across borrower and market contexts, identify related project risks (counterparty, inventory, execution), and apply mitigation strategies like research, phased development, fixed-price contracts, and pre-sale.

Explore real estate construction stages from mobilization to completion, including groundwork, substructure and superstructure works, finishing and service tasks, and stage-wise approvals for greenfield projects.

Analyze the stage-wise life cycle of greenfield projects, from layout plans to commencement and occupancy certificates, including brownfield redevelopment and SRA schemes. Explore how to determine project demand.

Learn to determine real estate demand by analyzing area-based factors and supply–demand dynamics, via exploratory research and subsequent quantitative analysis.

Evaluate developer questionnaires to assess approvals, project details, amenities, and charges, and analyze buyer profiles (investors vs end users) to gauge investment viability.

Explore the developer questionnaire for real estate modeling, covering bank involvement, approvals, floor area ratio, and government initiatives like Pradhan Mantri Awas Yojana, plus market dynamics.

Learn the law of demand in real estate, showing how quantity demanded moves inversely with price and is shaped by income, borrowing costs, credit availability, and buyer preferences.

Present the law of supply in real estate, showing how price affects quantity offered and how land, builders' efficiency, credit access, and skilled labor shape the supply function in India.

Explore how demographics, low interest rates, and growing demand shape property valuation, while location, amenities, infrastructure, and regulatory factors influence timing, price, and investment decisions.

Analyze how development level, land ownership type, and proximity to infrastructure and amenities determine demand and valuations in real estate.

Commercial real estate in Mumbai's suburbs rises as malls and IT parks develop, boosting valuations. Disposable income and land availability drive affordability and demand in housing markets.

Analyze how construction quality, customization, and physical attributes influence property prices. Examine supply-demand dynamics, land scarcity, and market conditions, including recession effects and growth barriers.

Explore how customer expectations shape real estate decisions—from budget and purchasing power to loan sanction and loan-to-value ratio—and how locality factors influence value, income returns, and capital gains.

Model real estate valuation with ten-year lease rentals cash flows, investment costs, and maintenance, then apply discounting using wacc and reflect equity and debt sources.

Learn how to determine sources of funds from equity, loans, and debentures, compute the weighted average cost of capital (WACC), and assess project viability with NPV and IRR.

Learn to calculate terminal value with Gordon's growth model using last-year free cash flow, growth, and WACC, and apply it to NPV and IRR.

Learn to determine total constructed and leasable areas, calculate vacancy and lease coverage, and build a basic real estate financial model including gross value, CAM, property tax, and WACC.

Learn to model lease rentals across 2016–2030, calculating monthly rents per square foot with annual escalations in a real estate financial model.

Calculate the numerator as M3 and the denominator as days from Jan 16 to the lease start date divided by 365, then apply references and a sumproduct to derive rentals.

Develop and build a profit and loss account for a real estate model by recording revenues, lease rentals, and expenses, and applying sum if techniques across years 2016 to 2030.

Calculate and model real estate expenses, including cam expenses, property tax, interest, depreciation, and taxes, to derive profit before tax and net outcomes.

Explore the SLM depreciation method and its impact on real estate finance by calculating depreciation, expenses, taxes, and building a debt schedule with a 15-year repayment.

Analyze opening balances and repay principal while calculating interest expense from the assumption sheet, derive closing balances, and build a debt service cash flow with dscr ratio and average dscr.

Explore the debt service coverage ratio, a measure of cash flow to cover debt obligations. See how net operating income and total debt service drive lending decisions.

Calculate the dscr by extracting depreciation, interest, and principal repayment from the P&L and repayment sheets, then assess cash flow for debt service and report average and minimum dscr.

Explore the percentage of completion method for recognizing revenue and expenses as projects progress, compare it with the completed contract method, and apply it to long-term construction contracts.

Convert acres to square meters and feet, compute land cost with stamp duty, and apply the percentage of completion method for revenue recognition in construction in progress.

Analyze contract work recognition under the percentage of completion method. Identify assets and liabilities for gross amounts due and inspect built-up area to estimate sale price.

Explore the project cost split into hard costs (land, site development, construction) and soft costs (contingency, front-end fees, pre-operative expenses, interest during construction), with totals and percentages.

Understand the breakup of construction cost and two means of finance, equity (share capital/internal accruals) and term loans, via a cost table with site development, contingency, and land cost.

Use percentage of completion to allocate sellable area and construction costs, including site development, interest during construction, upfront fees, pre-operative expenses, contingency, and land cost.

Learn to build the promoter's contribution quarterly schedule in excel, determine financial year endings from quarter endings using date and if, and link land cost to project cost.

Learn to allocate soft costs across ten quarters in a real estate model, distributing site development, construction, contingencies, front end fees, and pre operative expenses to compute total outflow.

Determine the promoter's share capital and debt financing, with 43% equity and 57% term loans, then distribute equity across nine quarters, with the first year at 50%.

Compute year wise promoter contributions by grouping quarterly data into years, use the sumif function with a date criteria, and build cumulative share capital balances.

Learn to compute drawdown and quarterly interest during construction (idc), decide capitalization versus pnl impact, and build a loan schedule for promoters covering opening balance, debt drawn, repayments, closing balance.

Compute interest during construction and track drawdown percentages and actual numbers to prepare the idc and p&l projections in real estate financial modeling.

Develop a construction loan model by tracking opening and closing balances, debt drawdowns, and quarterly interest calculated as the average balance divided by four, with mid-year principal repayments.

Learn to calculate interest in a real estate financial model, distinguishing pre-construction and post-construction IDC, capitalizing to the asset and routing balances to the PNL.

Learn to use sum and sumif to compute principal repayment and interest during construction, link idc data across project cost and pnl, and apply percentage of completion for IRR.

Apply the percentage of completion method to calculate marketing expenditure by linking the sale price, total saleable area, and project cost across three years to determine the total sale consideration.

Compute revenue realization using percentage of completion, booking revenue as total sale consideration times completion while deducting prior-year revenue, and allocate proportionate costs and amortization by area sold.

Compute project income as revenue booked to date minus proportionate cost, carry forward work in progress, and apply percentage of completion to PNL, balance sheet, cash flows, DSR, and IRR.

Learn to calculate EBITDA, profit before tax, and profit after tax from total income, apply depreciation and interest, derive ratios, and project a balance sheet in INR crores.

Calculate secured loans and their closing balances from the drawdown and idc sheets, and assemble liabilities and assets including gross block, depreciation, current assets and liabilities, and total assets.

Build a cash flow statement from assets by calculating net current assets and cash in hand, using PNL and balance sheet data and practical Excel formulas.

Compute cash flow using the max function for decreases in advance against sales and term loans, adjust stock in trade, then assess dscr and irr.

Compute present value of cash inflows and outflows using discount rates such as WACC, determine NPV and IRR, and apply capital budgeting for machinery and plant decisions.

Explore how a loan against property works as a mortgage loan, including mortgage origination, lien, secured collateral, and securitization through a single SPV.

Explore securitization via SPVs of mortgage loans funded by investors. Review mortgage notes and security instruments, LTV and down payments, fixed or adjustable rates, and recourse versus non-recourse debt.

Explore the loan against property model using a case study of 21 floors with 4 offices per floor, calculating total area, sold and leased units, and cash flow projections.

Compute the rate per square foot by dividing crores sales by total area, after distributing area month by month, and apply an 80/20 upfront versus deferred collection across the period.

this lecture explains calculating upfront and deferred collections, assembling cash inflows and outflows guided by the dsc ratio, and preparing term loan analysis in a real estate financial model.

Model the existing and new term loans in a real estate cash flow, building repayment and interest schedules in Excel to project quarterly outcomes.

Compute the total project cost by aggregating quarterly inflows and outflows from existing and new term loans, and analyze cash flow, repayment, net and cumulative cash flow, and DSR ratio.

Explore the three real estate private equity case types—core, value-added, and opportunistic—covering risk, return targets (IRR 9–12%, 10–15%, 15%+), leverage, and scenario-based modeling.

An investment analyst case study examines a 7-story Seattle apartment asset in Capital Hill, with 234 units, high 97% occupancy, transit access, robust local market growth, and strong employment drivers.

Lyric sits in a growing office market near Amazon, with rents around 37 per square foot and 6% vacancy, while the unit mix reveals rapid apartment construction and retail space.

Analyze the unit mix for studio apartments, featuring five units with varying sizes, and compare market rent to effective rent per unit and per square foot.

Assess peers through comparable properties and the comparable companies method, analyzing market rent per unit and per square foot, occupancy, concessions, and cap rates for Seattle submarkets and precedent transactions.

Analyze demographics, cap rates, and square-foot values in the Seattle metro's King area to model apartment performance, NOI, and both unleveraged and leveraged IRRs.

Analyze a real estate case study in Excel by using named ranges to model rents, occupancy, vacancy, and expenses across three scenarios, then compare DCF and comparable companies valuation methods.

Apply the comparable transaction method, or precedent transaction method, using past transactions and comparable property sales to derive price per unit, price per square foot, median values, and capitalization rate.

Calculate comp sales using cap rates on Kum sale data, derive implied value from price per unit and units, and apply median and dollar-per-square-foot metrics for acquisition and exit.

Analyze senior debt and investor equity through the loan-to-value ratio to determine acquisition financing, and compare scenarios of growth and decline with rental income, operating expense, and capex assumptions.

Determine base rental income in a steady growth scenario, linking historical year 14 to forecast year 15 using offset and fixed references, and calculate other income, vacancy losses, and egi.

Compute operating expenses per apartment unit and taxes with growth, derive replacement reserves, derive net operating income and NOI margin, and adjust for capital expenditures, tenant improvements, and leasing commissions.

Calculate the debt interest with ipmt and principal with pmt, then assess cash flow to equity, ending debt balance, and ratios such as interest coverage and DSCR, using NOI.

Explore adding interest and principal payments within real estate financial modeling, linking debt service coverage ratio, adjusted NOI, IRR, cash flow, and NPV calculations to assess returns.

Learn to model cash flow to equity investors in real estate, using cost of equity, estimate exit proceeds and debt repayment, and compute IRR, NPV, and cash-on-cash.

Explore discounted cash flow analysis using a 10% cost of equity and wacc, applying terminal value by multiple and perpetuity growth to estimate noi and unleveraged cash flow value.

Compute the present value of the terminal value using a 5.5% discount rate and terminal growth, then derive the implied property value and premium to the asking price.

Evaluate exit cap rate and exit price using offset on net operating income, derive price per unit and per square foot, and assess IRR, NPV, and cash-on-cash for Seattle multifamily.

Assess an opportunistic real estate private equity case in Sao Paulo, featuring a 303-unit multi-family development with 30% irr, 2x upside, and 1.2x downside.

Sao Paulo's market description highlights population growth and suburban expansion driving real estate demand. Use Park Global as a high-end comparable to analyze price per square meter and unit mix.

Compare sale properties using unit size, square meter price, and median values via DCF, comparable companies, and precedent transactions to benchmark valuation.

Evaluate a 72-month development timeline across three 24-month phases, with 60% pre-sold to begin the next phase. Model equity and debt draws, 12% interest, IRR hurdles, and a waterfall.

Explore how to build a V House investment analysis model using comparable developments, apply average and median calculations, and construct equity, debt, and waterfall scenarios with sensitivity analysis.

Learn how the interest coverage ratio measures a company's ability to pay interest on debt using EBIT or EBITDA, with practical examples and notes on limits.