Pro-Forma Real Estate Development Cash Flow

Development Cash Flow Modelling

• Inputs, assumptions, and timeline management
• Hard and soft cost analysis
• Sources and uses of capital

Sources of Investment Capital

• Financing phases:
• Preliminary phase
• Construction phase
• Lease-up and fit-out phase
• Stabilisation phase

Development Debt Finance

• Equity first capital deployment waterfall
• Debt repayment structures for development finance
• Rolled-up interest debt structuring (PIK – payment in kind)
• Senior and mezzanine finance structures

Joint-venture structures

• Equity waterfall structure
• Preferred returns
• Promote cash flow and hurdle rates

Joint Ventures and Promote Structures

• Structuring joint ventures
• Understanding promote cash flow, hurdle rates and distribution

Leveraged and Unleveraged Real Estate Development Returns Profile

Creating and interpreting returns profiles

• Internal Rate of Return (IRR) using XIRR
• Net Present Value (NPV) using XNPV

Residual Land Value (RLV)

Analysing and deriving the maximum bid price for land purchase based on leveraged and unleveraged target returns

RLV based on Profit on Cost, IRR and NPV

Capital Structure, Sources of Debt Finance and Lending Criteria

• Capital structures: debt & equity
• Explanation of different debt & equity structures
• Sources of debt fund & lending criteria

Senior debt repayment modelling

• Interest Only
• Constant Amortisation
• Fully-Amortising Constant Payment
• Partially-Amortising Constant Payment
• Rolled-Up (Capitalised) Interest (Payment in Kind – PIK)

Debt covenants modelling and analysis

• Interest Coverage Ratio (ICR), Debt Service Coverage Ratio (DSCR), Loan to Value (LTV), Debt Yield (DY)
• What happens when covenants are breached
• Cash sweep
• Revolving debt facilities
• Finding maximum borrowing amount and highest LTV
• Dynamic debt modelling using varying debt structures
• Leveraged return analysis based on IRR, NPV and Equity Multiples (MOIC)

Key Learning Outcomes:

• Debt structuring in Excel: Senior debt, mezzanine debt, preferred equity
• Building a debt waterfall model with structured references
• Using IF, MIN, and MAX functions for conditional repayments
• Optimising investor returns using debt layering techniques

Topics Covered:

1. Running equity IRR and NPV sensitivity analysis and debt facilities into the models. Analyse investment – grade commercial property transactions inAdvanced Real Estate Development Financing

• Modelling a capital stack with multiple debt layers
• Understanding rolled-up interest and cash sweep mechanisms
• Using Excel’s financial functions to structure debt repayments

2. Excel-Based Modelling of Mezzanine Debt & Preferred Equity

• Forecasting debt vs. equity cash flows using INDEX-MATCH
• Modelling mezzanine debt cash flow
• Creating a dynamic waterfall model with structured cash distributions

3. Joint Venture (JV) Modelling in Excel

• Setting up preferred equity waterfalls
• Using IF and MAX functions for promote structures
• Simulating profit-sharing mechanisms dynamically

4. Debt Structure Optimisation & Sensitivity Analysis

• Comparing debt financing scenarios using Goal Seek
• Running stress tests on equity IRR using Data Tables
• Evaluating refinancing options in Excel

5. Case Study: Structuring a Geared Development Project in Excel

• Running equity IRR and NPV sensitivity analysis
• Hands-on modelling of a leveraged real estate development
• Debt financing comparison: Senior vs. Mezzanine vs. JV