Mixed Development and Investment Portfolios of Projects

This page describes the modelling of mixed development projects in real estate. Mixed developments consist of a portfolio of different projects with different construction start dates, occupancy dates and holding periods.  The mixed development projects generally have different types of project with commercial, and residential.

Introduction to Mixed Development Modelling and PDF File

To big difference between real estate finance and other project finance are: (1) in real estate there is a generally a portfolio of projects with different occupancy dates (TOP) and different construction schedules; and (2) in real estate, the projects are often sold after a few years meaning that you must evaluate cap rates.  Real estate models are also very similar to solar roof-top analysis.

Portfolio Issues in Real Estate Models

This is a solar rooftop analysis that involves identical issues to real estate analysis that include creating a portfolio with different characteristics including start dates, end dates changes in utilisation and operating expenses. It includes detail methods for computing working and financing. There is a set of 15 videos that constitutes a course associated with both basic and advanced issues associated with this analysis.

 

.

.

Financial Model where Different Projects in the Portfolio are Modelled with Separate SPV’s

The file available for download below includes separate SPV’s for different facilities.  You set up names of the different facilities that could be residential homes, commercial buildings, solar roof-tops etc. with the start dates, size, construction periods, rental rates and other factors.  Then you can copy the template for a single SPV to other SPV’s.  After creating the SPV’s and the sheet names, you can consolidate the SPV’s and put in financing.  Finally, you can evaluate how the consolidated financing works with different scenarios and see how fast the consolidated loan can be paid off. The file is available for downloading by clicking on the button below.

 

Financial Model from Separate Solar Rooftop Projects Structured as SPV's that are Consolidate to Financial Model

 

A few screenshots may give you an idea of how the file to consolidate different SPV’s works.  The first picture is intended to give you an idea of how the final consolidated process works.  This consolidation works by using the INDIRECT function to add together cash flows and balances from individual sheets.

 

 

The next screenshots illustrate how the individual SPV’s are set-up.  The name of the SPV is the same sheet name. You create an SPV that demonstrates how the different inputs and structure for a single SPV are Created. The date of starting construction, the construction costs, the yield and other inputs are included in the summary sheet.

 

 

This is an analysis where multiple SPV’s are consolidated into a portfolio. A major innovation in the analysis is use of a flexible template for the SPV that includes currency risks and different types of financing. A macro copies that SPV to multiple different sheets. The INDIRECT function along with MATCH and INDEX are used to consolidate the SPV’s and analyse the results.

 

 

The third set of screenshots demonstrate the outputs of individual SPV’s that are eventually rolled-up into the consolidated analysis.  This depends on the financing structure of at the SPV and whether debt is issued on a project basis. The graphs show how debt service is paid at the individual SPV and how the individual SPV receives cash flow from the solar project in different scenario cases.

 

 

 

 

 

 

Problem of Interest During Construction and Circularity in Real Estate Models

Interest during construction must be allocated to different projcects.  To do this you need to keep track of the amount of plant in service and the construction work in progress.

 

.

.

 

The following files are associated with the videos that explain the probelms with interest during construction and circularity.

 

PDF Model of Mixed Development that You Can Covert and Evaluated

 

This model is in a pdf file that demonstrates that type of issues that you may face in constructing a mixed use model of commercial, residential and maybe even industrial.  I will eventually replicate this model with an excel file.  If this is very interesting to you please send me an e-mail at edwardbodmer@gmail.com.

Files on Resource Library Not Yet Uploaded

Other files that I have not yet put on this website are show below. You can find these files in the resource library by e-mailing me at edwardbodmer@gmail.com. They are in the Real Estate Folder of Chapter 1.  I will try to get these uploaded soon.

  • Portfolio of Small Developments.xlsm
  • Mixed Use Analysis.xlsm
  • Mixed Use and Residential Model with Financing.xlsm
  • IDC and Circularity with Real Estate.xlsm
  • IDC and Circularity with Real Estate – Function.xlsm
  • IDC and Circularity with Real Estate – Copy and Paste.xlsm
  • Mixed Use Analysis.xlsm
  • Comprehensive Real Estate Exercise.xlsm
  • Mixed Use and Residential Model with Financing.xlsm
  • Exercise 6 – Mixed Development.xls
  • Exercise 7 – Portfolio Data Table.xls
  • Exercise 11 – Comprehensive Mixed Use Exercise.xls
  • Exercise 1 – Simple Portfolio.xls
  • Exercise 3 – Investment Portfolio.xls