1 / 46

Introduction

Introduction. Real Estate Characteristics and Corporate versus Project Finance Analysis. Real Estate and Project Finance Project Stages Non-recourse Debt Cash Flow and Financing Equity IRR Real Estate and Corporate Finance Portfolio of Projects Terminal Values

charde-alexander
Download Presentation

Introduction

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Introduction

  2. Real Estate Characteristics and Corporate versus Project Finance Analysis • Real Estate and Project Finance • Project Stages • Non-recourse Debt • Cash Flow and Financing • Equity IRR • Real Estate and Corporate Finance • Portfolio of Projects • Terminal Values • Financing of consolidated projects

  3. Valuation Ratios in Real Estate versus Project Finance and Corporate Finance • Corporate Finance • Enterprise Value • P/E Ratio • Discounted Cash Flow • Project Finance • Project IRR • Equity IRR • Real Estate Finance • Cap Rates

  4. Example of Model Output

  5. Challenging Financing Issues • Cascade of financing during construction • Inclusion of land loan and revolving credit facility • Cash flow waterfall with cash sweeps on debt facilities • Cash flow sweep and allowance for dividends • Break-even analysis from debt perspective

  6. Overview of Real Estate Exercises

  7. Exercise 1 – A to Z Basic Model • FAST Modeling Standards Illustration • Flexible • Accurate • Structured • Transparent • Model Illustrates timing issues for flexibility, accuracy checks, structuring with different parts and transparency with simple formulas

  8. Exercise 1 - Continued • Demonstrates the measurement of return • Project IRR • Equity IRR • MIRR • XIRR

  9. Exercise 1 – Hotel Project Finance • Demonstrates basic structure of project finance model for real estate: • Structuring the construction period and holding period separately • Structuring the financing of the model • Setting-up inputs and operations sections in a structured manner • Computing IRR and DSCR outputs

  10. Example of Sources and Uses Summary

  11. Exercise 2 – Key Points • Setting up Flexible Investment Model • Consolidating Investments • Financing Investments at SPV and Consolidated Levels • Creating Cash Flow Waterfall for Consolidated Model • Cash Flow Sweep in Consolidated Model

  12. Example of Summary Page

  13. Exercise 2 – Investment Portfolio

  14. Key Mechanical Points in Exercise 3 • Create common start date for all projects in portfolio • Use INDEX function with project code number to arrange inputs for individual projects • Create different time period codes for each project • Use lookup table with time period codes to set-up S-curve for each project

  15. Exercise 3 – Building Model with Multiple Investments Compute the IRR from the above information

  16. Exercise 4 – Consolidation of Portfolio with Data Table • Same inputs as Exercise 3 • Mechanical Issues • Create Index Code • Use INDEX Function • Use DATA TABLE with Index Code to Consolidate Accounts

  17. Data Table in Consolidation • Set up table with Project Code Number • Put code numbers in different rows • Put the output desired on the column above the rows • Sum the individual rows for the consolidated analysis

  18. Exercise 5 – Flexible Dates • The exercise demonstrates how to: • Create a model with flexible periods • Establish an analysis that does not begin at the first day of a period • Set-up begin and end dates in a model • Compute IRR’s with flexible dates

  19. Exercise 6 – Mixed Use Development Without Automatic Consolidation • Demonstrates Principles of Mixed Use Analysis • Varying S-Curves for individual projects • Varying progress payments for individual projects • Requirement to consolidate individual projects

  20. Input Database Example

  21. Input for Development Plan

  22. Time Line for Different Projects in Mixed Development

  23. Exercise 7 – Portfolio with Data Table • Simple Annual Model that Illustrates how to Efficiently Consolidate Projects in Mixed Use Development

  24. Exercise 8 – A to Z Model with Scenario Analysis • FAST Modeling Standards Illustration • Flexible • Accurate • Structured • Transparent • Model Illustrates timing issues for flexibility, accuracy checks, structuring with different parts and transparency with simple formulas

  25. Exercise 8 – Single Project Annual • This exercise works through structuring and financial issues associated with a single project • Issues include: • Flexible Timing in Annual Model • Flexible Structuring of Inputs • Operating and Financing Analysis • Financing • Scenario Analysis • Model Verification

  26. Exercise 9 - Comprehensive Mixed Use Development

  27. Exercise 10 – Lease Roll Analysis • Objective • Model different risk profiles from alternative lease contracts • Use Scenario Analysis and Break-even analysis for market lease rates • Apply monte carlo simulation to analysis

  28. Inputs for Lease Roll

  29. Inputs for Analysis • Market Lease Rates and Growth • Sensitivity Analysis • Idle Time After Lease • Multiple Lease Expirations

  30. Create Code Number • Code for First Lease • Date is less than or equal to the expiration date • Code for Idle Time • Date is greater than the expiration date and less than or equal to the expiration date plus the idle time period • Need the AND function • Code for Spot Rates • Date is greater than the expiration date plus the idle time

  31. Range Names and Code Numbers • Since the formula is long for the code number can create range names for the code number • Put formula together with range name • Make different range names for each component of the code number

  32. Choose Function with Row Number • Use the code number as the basis for the CHOOSE function • First option • Lease Rate • Zero for Idle Period • Net Spot Lease Rate After Idle Period

  33. Exercise 10a – Lease Roll with Monte Carlo Simulation • Apply on Monthly Basis and Compute Months • Use the EOMONTH to Compute • Expiration Month • End of Idle Period • End of Second Lease • Compute the Fraction of Month Off the Lease • Use the DAYS360 Function divided by 360

  34. Exercise 11: Cap Rate Analysis • Enter Assumptions for: • Net Rental Growth Rate • Cost of Capital • Tax Rate • Depreciation Rate • Remaining Life • Derive Cap Rate • Evaluate Cap Rate for Different Cost of Capital

  35. Valuation Analysis in Corporate Finance, Project Finance and Real Estate • Corporate Finance • P/E Ratio, EV/EBITDA Ratio and Discounted Cash Flow • Terminal Value is Major Part • Project Finance • Equity IRR and Project IRR • Evaluate over Project Life without Terminal Value • Real Estate Finance • Equity IRR and Project IRR • Terminal Value with Cap Rates

  36. Credit Analysis in Corporate Finance, Project Finance and Real Estate Finance • Corporate Finance • Debt Repayment from Ability to Re-finance and Credit Quality of Company • Use Debt/EBITDA, Debt/Equity and Interest Coverage • Project Finance • Debt Repayment Directly from Cash Flow without Valuation • Use DSCR, LLCR and PLCR • Real Estate Finance • Debt Repayment from Cash Flow and Ability to Sell Project • Use Debt to Value with Cap Rate in Valuation

  37. Complex Lease Roll

  38. Exercise 11 – Cap Rate Analysis

  39. www.edbodmer.com Definition of Cap Rate • Formula • Cap Rate = Net Rental/Value of Property • With Yield • Assume the yield on a property = cap rate • Yield = Net Rental/Investment • Net Rental = Yield x Investment • Yield and Cap Rate • Cap Rate = Yield x Investment/Value of Property • Cap Rate/Yield = Investment/Value of Property • Where Yield = Cap Rate • Here the Investment = Value of Property

  40. Apply Monte Carlo Simulation • Enter the volatility for lease rates • Develop a time series function for lease rates • Create Macro for Simulation • Evaluate alternative distributions of value

  41. Exercise 12: Residential Exercise • Set-up Inputs with Different Milestones for Construction • Walling • Foundation • Completion • Set-up Inputs for Different Payments (which can be the same as the milestone dates) • Percent of Payment and Percent of Construction

  42. Residential Exercise - Mechanics • Set-up dates with common start date and evaluate dates on a monthly basis • Use the EOMONTH function for milestone payments and for dates • Compute the construction period in months for the different project phases • Compute switches for dates and for the construction periods

  43. General Inputs for Financing

  44. Example of Inputs for Residential Project

  45. Financing and Cash Flow Waterfall • Set-up Cash Account, Equity Account and Debt Account • Use MAX and MIN functions to evaluate what happens with positive and negative cash flow

  46. Exercise 13 - Financing

More Related