Construction Draw Schedule, Lender Yield & Real Estate Investment Pro Forma — Excel Model

Description
A fully integrated Excel pro forma model thatwalks through the complete lifecycle of a commercial real estate developmentproject — from construction draw scheduling and lender yield calculationthrough operational cash flows, depreciation, tax analysis, and investmentdecision-making using IRR and NPV. Designed for developers, analysts, lenders,and MBA/finance professionals seeking a ready-to-use, assumption-drivenframework for office, retail, or mixed-use projects.


This comprehensive Excel model provides a structured, end-to-end financial analysis framework for commercial real estate development projects. It covers the full project lifecycle across two distinct phases: the Construction Phase (Year 0) and the Operational & Disposition Phase (Years 1–5). Every calculation is dynamically linked to a centralized Assumptions sheet, allowing users to modify key inputs and instantly observe the impact on project economics, financing costs, returns, and investment viability.


The model is built around a realistic case study — a62,000 sq ft suburban office development with a 12-month construction timeline,phased draw schedule, permanent loan takeout, 5-year operating horizon, andterminal sale — but is fully adaptable to any commercial property typeincluding retail centers, mixed-use developments, and industrial facilities.


Key Features
• Centralized Assumptions Dashboard — All project inputs (site, construction costs, financing terms, operating parameters, sale & tax assumptions) are consolidated on a single sheet for easy scenario testing.
• Monthly Construction Draw Schedule — Models a two-phase draw structure (front-loaded and steady-state) with month-by-month beginning balance, draw amount, ending balance, and accrued interest.
• Lender Yield Calculation — Computes the construction lender’s annualized yield by combining interest carry with origination fee income against the average outstanding balance.
• Total Project Cost Buildup — Aggregates land, hard costs, soft costs, loan fees, and capitalized interest into a single total project cost figure, with depreciable base allocation.
• Equity Requirement Analysis — Statement of cash flows for the construction phase showing total outflows, loan proceeds, and the resulting equity gap.
• 5-Year Operating Pro Forma — Revenue projections with rent escalation, tenant reimbursements, vacancy adjustments, operating expenses, and Net Operating Income (NOI) for Years 1–5.
• Debt Service & Loan Amortization — Permanent loan modeling with monthly mortgage payment, annual debt service, interest/principal split, and year-end loan balances.
• Before-Tax & After-Tax Cash Flows — Complete BTCF and ATCF computation incorporating depreciation schedules (SL for capital improvements, DDB for tenant improvements), taxable income, and income tax.
• Property Disposition Analysis — Sale proceeds, selling costs, mortgage payoff, adjusted basis, gain on sale, capital gains tax, and after-tax cash flow from sale.
IRR, NPV & Go/No-Go Decision — Before-tax IRR,after-tax IRR, and NPV at the investor’s required return, with a clearACCEPT/REJECT recommendation.