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:

1. 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
2. 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
3. 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
4. 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
5. 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
   • Comparing debt structures and impact on investor returns

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:

1. 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
2. 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)
3. 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
4. Case Study: Modelling a Real Estate Development in Excel
   • Constructing a development cash flow model
   • Using XIRR and XNPV analysis to assess viability
   • Running break-even analysis on cost overruns and delays

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