Module: Real Estate Debt Structures, Risk & Financial Modelling

Key Learning Outcomes:

• Building a structured debt schedule in Excel using multiple senior debt structures
• Using Excel’s financial functions to model:
  • Interest-Only Loans (Bullet Loans)
  • Constant Amortisation
  • Constant Payment – Fully Amortising
  • Constant Payment with a Balloon
  • Rolled-Up Interest (R-Up)
• Debt serviceability modelling using Interest Coverage Ratio (ICR), Debt Service Coverage Ratio (DSCR), Loan-to-Value (LTV), and Debt Yield
• IRR and NPV analysis for leveraged investments in Excel
• Comparing debt structures and impact on investor returns
• Decision making ability for optimal loan structures

Topics Covered:

Comparing debt structures and impact on investor returns

Introduction to Debt Structuring in Excel

Understanding the capital stack and debt financing structure

Creating a debt input sheet with structured references

Using Named Ranges to improve model efficiency

Applying Data Validation to control user inputs

Excel-Based Modelling of Senior Debt Structures

Interest-Only (Bullet Loan): Structuring principal repayment at maturity

Constant Amortisation: Using IF formulas and amortisation schedules

Constant Payment (Fully Amortising): Using the PMT function

Constant Payment with a Balloon: Modelling residual balances with SUMIFS

Rolled-Up Interest (R-Up): Implementing compounded interest calculations

Leveraged Buy-Out (LBO) Modelling in Excel

Constructing a debt waterfall using SUMPRODUCT and INDEX-MATCH

Forecasting cash flows under different debt repayment structures

Automating debt drawdowns and repayments with conditional formatting

Debt Covenant Compliance & Scenario Analysis

ICR, DSCR, and Debt Yield formulas for covenant stress testing

Setting up dynamic scenario analysis using Data Tables

Forecasting Loan-to-Value (LTV) ratios with LOOKUP and CHOOSE functions

Creating Excel-based risk assessments using conditional formatting

Case Study: Excel-Based Modelling of a Geared Property Investment

Building an investment model with senior debt financing

Find optimal capital structure with Goal Seek and Data Tables

Key Learning Outcomes:

• Building an Excel-based development feasibility model
• Modelling construction costs and pro-forma cash flows
• Conducting discounted cash flow (DCF) valuation in Excel

Topics Covered:

Running break-even analysis on cost overruns and delays

Development Feasibility Modelling in Excel

Using residual valuation to determine land values

Forecasting Gross Development Value (GDV) vs. Net Development Value (NDV)

Setting up a DCF model using XNPV and XIRR functions

Modelling Development Costs and Revenues

Building an input table for site costs, hard costs, and soft costs

Implementing cost distribution curves using:

Straight Line (SUM function)

S-Curve (NORMSDIST function)

Known Curves (user-defined inputs)

Excel-Based Development Cash Flow Modelling

Setting up a monthly cash flow timeline

Structuring developer equity injections vs. debt drawdowns

Linking construction costs to financing needs using IF formulas

Case Study: Modelling a Real Estate Development in Excel

Constructing a development cash flow model

Using XIRR and XNPV analysis to assess viability

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