Top Story
The financial analysis spreadsheets presented here are intended to be used together with the third edition of Professional Real Estate Development: The ULI Guide to the Business. These spreadsheets are provided in Excel format, which allows users to download the information and substitute their own numbers for those used in the case studies.
The financial analysis tools presented here are for educational purposes only. The authors and the Urban Land Institute assume no responsibility for the content or accuracy of the information. Furthermore, it is advised that individuals rely on their own financial assumptions and criteria for feasibility as background to any investment decision.
Land Development Financial Feasibility Analysis
Chapter 3 Spreadsheets
Land Development is analyzed using a For-Sale method appropriate for a subdivision, condominiums, for-sale homes, and other real estate where the product is sold at completion rather than being operated as income-producing property (see chapter 4). Financial feasibility analysis for land development is performed in two stages:
Stage 1: a “quick and dirty” analysis that summarizes the project’s sales revenues, expenses, interest, and profit; and
Stage 2: a multiperiod discounted cash flow (DCF) analysis that provides a detailed projection of cash flows, equity and loan needs, profits, and internal rates of return (IRR). (Note that we use a two-stage model for land development analysis, as compared to a five-stage model for income property analysis below.)
The figures used in the financial analysis model are taken from Veranda, a project developed in Lancaster, Pennsylvania, by Charter Homes. The project consists of 180 finished lots permitted for three housing types. Charter Homes is both the land developer and homebuilder and has analyzed the two components of development as separate businesses.
Stage 1 – Quick and Dirty Analysis Explanation
Quick and dirty analysis provides the initial estimate of a project’s financial performance. It summarizes projected revenues from the sale of lots, land and development costs and interest, and the expected profit. All figures are aggregated for the entire project life. No time dimension for development or sales is considered. Quick and dirty analysis represents the starting point for evaluating the deal. It does not, however, give the developer all the information needed to make proper decisions.
In the quick and dirty analysis for Veranda, sales are grouped into four phases of 30 to 83 units each. Sales prices for the lots range from $35,000 for a townhome lot to $68,000 for a single-family lot, generating total revenue of $10,686,000. (See figure 3-7, page 101, in Professional Real Estate Development.)
Land for the project cost $2,262,000, or $12,567 per lot, reflecting the fact that the land is already entitled and the developer has little entitlement risk. Development costs include on-site land improvement, off-site improvements, and estimates for administration, contingency, and marketing (5 percent of revenues). Total development costs are $7,277,600. Financing costs are estimated by assuming that Charter Homes invests $4 million in equity and borrows the rest. In actuality, the development loan will be done in four phases and will not reach the $12,554,100 shown in the analysis (total development costs of $16,554,100 minus equity of $4,000,000). A principal shortcoming of quick and dirty analysis is the back-of-the envelope methodology. Interest is estimated by assuming that it takes one year to develop the project with an average loan balance of $6,277,050, or $470,779 total interest. Profit is $1,079,121.
Quick and Dirty Analysis Excel Spreadsheet 3-7 (Excel file)
Stage 2 – Multiperiod Discounted Cash Flow Explanation
Multiperiod discounted cash flow (DCF) analysis is an application of the capital asset pricing model to real estate. The cash flow analysis assigns revenues and expenditures from the quick and dirty analysis to specific periods of time. Its principal purpose is to compute 1) returns to the overall project, 2) loan requirements, and 3) returns to joint venture participants. It is a before-tax computation.
Land development DCF analysis is rerun many times during the feasibility period and over the life of the project. The land use budget, cost, and sales assumptions are changed as more accurate information becomes available. The primary difference between land development analysis and income property analysis is the financing. Land development financing is a form of construction loan that is paid off as the land is sold. Income property is primarily analyzed from the perspective of owning and operating it once it is completed. It is financed with a permanent mortgage that is not paid off until the property is sold. As with land development, a construction loan is used to construct income property, but the construction loan is replaced or “taken out” by the permanent mortgage once the building is completed and achieving stabilized operations.
Spreadsheets for the multiperiod DCF analysis for the Veranda example are available here. (See figure 3-8a, page 103; figure 3-8b, pages 104–105; figure 3-8c, pages 106–107; figure 3-8d, pages 106–109; and figure 3-8e, pages 108–109 in Professional Real Estate Development).
Multiperiod Discounted Cash Flow Spreadsheets Figures 3-8a, 3-8b, 3-8c, 3-8d, 3-8e (Excel file)
Income Property Financial Feasibility Analysis
Chapetr 4 Market Analysis: Jefferson Pointe
Chapter 4 Spreadsheets
Evaluating financial feasibility for income property development involves five stages of analysis, each more detailed than the previous one. These stages are:
Stage 1: Simple Capitalization;
Stage 2: Discounted Cash Flow Analysis;
Stage 3: Combined Analysis of the Development and Operating Periods;
Stage 4: Monthly Cash Flows during the Development Period; and
Stage 5: Discounted Cash Flow for Investors.
Explanation of the Stages of Analysis
Spreadsheets illustrating each stage of the analysis for the case study in Chapter 4, West River Commons in Minneapolis, Minnesota, are presented here.
Stage 1 Excel spreadsheets: Figs 4-3a, 4-3b, 4-3c, 4-3d, 4-3e
Stage 2 Excel spreadsheets: Figs 4-4a, 4-4b
Stage 3 Excel spreadsheets: Figs 4-5, 4-6, 4-7
Stage 5 Excel spreadsheets: Figs 4-9
West River Commons includes 57 rental apartments, three for-sale condominium homes, and 7,925 square feet of rentable retail space.
Estimated development costs total $10,751,191. The analysis is complicated by the inclusion of three for-sale condominiums as well as the retail space on the ground floor. The apartment project cost after subsidies is $7,419,410 (figure 4-3d). Note that for purposes of analyzing the apartments, this cost represents the sum of the depreciable basis of $6,274,534 and the land cost of $1,144,876 (figure 4-4a: Stage 2a DCF, lines 7 and 15). These spreadsheets may be adapted to other projects by the user with or without the condo spreadsheet (figure 4-4b: Stage 2b).
Stage 1 – Simple Capitalization
Stage 1 analysis is used to develop the two simple return measures common to all income properties—overall capitalization rates and cash-on-cash returns. The rental summary includes estimates that the developer believes can be achieved based on a careful analysis of comparable properties in the market area (figure 4-3a, page 169).
The pro forma statement provides estimates for rents and expenses for the stabilized project. Both income and expense estimates should reflect local conditions and any specific features of the project. For West River Commons, the pro forma indicates a total rental revenue of $892,317 and sales revenue from selling the three condos of $975,000 (figure 4-3a, page 169). Net operating income (NOI) is $523,942 (figure 4-3b, page 170).
For the debt calculation, two common criteria are used to determine maximum loan amount: debt coverage ratio (DCR) and loan-to-value (LTV) ratio. Lenders typically look at both criteria when underwriting a loan and use the more restrictive one. In this example, LTV is more restrictive, so the maximum loan on the property would be $6,234,849 (figure 4-3c, page 170).
Development costs are the other part of the equation needed to evaluate a project’s feasibility. An overall static cost estimate for the project must be calculated. Eventually, the developer will have firm construction bids, but the initial financial feasibility analysis relies on the developer’s experience from other similar projects and information provided by contractors and consultants. The total development costs for West River Commons before interest and lease-up are $9,926,951. With estimated interest during construction of $538,553 and an operating reserve during lease-up of $284,687, project costs total $10,751,191. The total project cost after subsidies is $8,274,208 (figure 4-3d, page 171).
Stage 1 analysis is sometimes called a back-of-the-envelope analysis, because the simple returns can literally be computed on the back of an envelope. Still, the overall return (NOI divided by total project cost) and cash-on-cash return (cash flow after debt service divided by equity) are the two most commonly cited measures of return in the industry. For Shady Hollow, the overall return is 7.1 percent and the cash-on-cash return is 7.4 percent. The development profit for the apartments of $1,312,963 represents the difference in market value of the project at stabilized occupancy minus total project costs to reach that point (figure 4-3e, page 173).
Stage 2 – Discounted Cash Flow Analysis
Discounted cash flow analysis of the operating period is the most important of the five stages for income property. It is used by lenders, appraisers, and investors to determine projected returns of the proposed development. Even if the developer plans to sell the project as soon as it reaches stabilized occupancy, Stage 2 analysis is the most widely used methodology to evaluate an income property investment or development. Figure 4-4a, pages 174–176, shows the Stage 2 analysis for the rental apartments and retail space. The cash flows from the condominiums (figure 4-4b, page 178) are added into the IRR calculations.
Both unleveraged (all-cash) before-tax returns and leveraged (financed) returns can be calculated on the same spreadsheet simply by changing the assumptions about the mortgage and income taxes. The unleveraged IRR in figure 4-4a is 9.15 percent. Although this figure is important, developers are primarily interested in the return on equity (ROE). The return on equity also is expressed as an IRR and takes into account the financing and personal income taxes of the owner/developer. West River Commons’ before-tax IRR is 21.42 percent, and the after-tax IRR is 18.07 percent.
Stage 3 – Combined Analysis of the Development and Operating Periods
Stage 3 has three parts: quarterly cash flows during the development period (figure 4-5, pages 180–183), a sources and uses statement summarizing the development costs annually (figure 4-6, page 184), and combined annual before- and after-tax cash flows during the development and operating periods (figure 4-7, pages 184–186). Stage 3 analysis is the most complex of the five stages. The IRRs are more precise than those computed in Stage 2. The unleveraged IRR for West River Commons is 10.68 percent, the before-tax IRR is 22.21 percent, and the after-tax IRR is 18.61 percent.
Stage 4 – Monthly Cash Flows During the Development Period
Stage 4 analysis (not shown) focuses on just the development period and refines the cash flow projections to support the request for the construction loan. It resembles the quarterly analysis in figure 4-6 except that the projections are made monthly rather than quarterly. It is not uncommon for developers to stop at Stage 3, presuming that quarterly cash flow analysis will be sufficiently fine-grained to give them a reasonably accurate picture of their funding needs. But monthly projections are recommended because they give both the developer and the lender the most accurate picture of funding needs and serve as a useful tool for monitoring cash flows once construction begins.
Stage 5 – Discounted Cash Flow Analysis for Investors: Joint Venture-Syndication Analysis
Stage 5 is used to divide the cash flows for the whole project into the investor’s and developer’s shares. Although the final version of Stage 5 for the offering package to investors is usually prepared by an accountant on an after-tax basis, the developer’s analysis typically focuses on before-tax cash flows and IRRs to the investor. The project’s viability hinges on attracting sufficient equity capital, so the investor’s IRR is one of the key measures of return. Based on the deal structure presented in this case, the investor’s before-tax IRR is 19.41 percent on an equity investment of $1,356,858 (figure 4-9, page 189).
Chapter 6 spreadsheets
Chapter 6 is about industrial development. A feature box outlines a sample deal structure with a three-tier hurdle rate of return. The spreadsheet for Figure B, shown on page 313 is available here.