The free financial analysis spreadsheets presented here are intended to be used together with Professional Real Estate Development: The ULI Guide to the Business. These spreadsheets are provided in Excel format that allows the user to download the information and substitute their own numbers for those used in the case studies.

The financial analysis tools presented here are for information purposes only. The authors and ULI-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.

The complete list of spreadsheets in Chapters 3 and 4 as well as the Lookback IRR spreadsheet in Chapter 6 may be downloaded for a small fee from the following website: http://www.swissoutpost.com/realestate/real_estate_software.aspx

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) that provides a detailed projection of cash flows, equity and loan needs, profits, and internal rates of return (IRR).

The figures used in the financial analysis model are taken from San Luis Rey, a project developed in Oceanside, California by Granite Homes. The project consists of 111 single-family lots. The average lot size is 6,800 square feet, the minimum 6,000 square feet, which is typical for the area. Lot development by Granite Homes includes grading, underground placement of wet and dry utilities, and construction of storm drains, streets, curbs, gutters and sidewalks. Information on this project was provided by Dan Kassel, co-president of Granite Homes in Irvine, California.

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 San Luis Rey sales are grouped into four phases of 20 to 30 homes each for simplicity. Sales prices for the lots range from $160,000 to $166,000, generating total revenue of $18,104,000. (See Figure 3-8, page 85 in Professional Real Estate Development.)

Land for the project cost $9,276,500, or $83,572 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 Granite 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-8 (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 it so 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.

Three spreadsheets for the Multiperiod DCF analysis for the San Luis Rey example are available here. Additional spreadsheets for the Cash Flow Summary (including leveraged financing), Cash and Loan Calculations and IRRs, and Investor Return Analysis are available for a one-time fee at http://www.swissoutpost.com/realestate/real_estate_software.aspx.  (See figure 3-9a, page 87; figure 3-9b, page 88-89; figure 3-9c, page 90; figure 3-9d, page 91; figure 3-9e, page 92 in Professional Real Estate Development).

Evaluating financial feasibility for income property development involves five stages of analysis, each more detailed than the previous one. These stages include:

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 the first two stages of analysis for a hypothetical new 200-unit apartment complex, Shady Hollow, in Dallas, Texas are presented here.

Stages 1 and 2 Spreadsheets (excel file)
The financial analysis for stages 3 and 5 are available for a fee at http://www.swissoutpost.com/realestate/real_estate_software.aspx. (Stage 4 is not shown.)

These spreadsheets illustrate the Shady Hollow case study. This project is assumed to have one-, two- and three-bedroom units averaging 958 square feet (90 square meters) that rent for an average of $1,021 per month. Estimated development costs total $14,849,637. Project cost and operating information for this example are adapted from data for a new property under construction by Trammell Crow Company in a suburb east of Dallas. Although costs, unit sizes, distributions, and operating costs reflect Trammell Crow Residential’s experience in Dallas in 2002, interest rates and terms of the deal structures have been adjusted to represent those that a beginning developer would be likely to obtain. Robert Buzzbee, division partner for the south central region, Trammell Crow Residential, provided project cost and operating data for Alexan Town East, a 224-unit, three-story wood-frame building complex in Mesquite, Texas.

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 149).

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 Shady Hollow, the proforma indicates a total income of $2,365,367 and NOI of $1,397,579 (Figure 4-3b, page 149).

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 $10,870,060 (Figure 4-3c, page 150).

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 cost bids for building the project. The initial financial feasibility analysis, however, relies on the developer’s experience from other similar projects and information provided by contractors and consultants. The total development costs for Shady Hollow before interest and lease-up are $14,285,901. With estimated interest during construction of $529,915 and an operating reserve during lease-up of $600,359, project costs total $15,416,175 (Figure 4-3d, page 151).

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 9.1 percent and the cash-on-cash return is 12.6 percent. The development profit of $1,025,932 represents the difference in market value of the project at stabilized occupancy minus total project costs to reach that point. (Figure 4-3e, page 152).

Stage 2 – Discounted Cash Flow Analysis

Discounted cash flow analysis of the operating period is the most important of the five stages. 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-4, page 154).

Both unleveraged (all-cash) before-tax returns and leveraged (financed) returns can be done on the same spreadsheet simply by changing the assumptions about the mortgage and income taxes. The unleveraged IRR in Figure 4-4 is 10.8 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 person income taxes of the owner/developer. Shady Hollow’s before-tax IRR is 19.15 percent, the after-tax IRR is 15.43 percent.

Stage 3 – Combined Analysis of the Development and Operating Periods

Stage 3 evaluates cash flows quarterly during the development period, taking into account the anticipated construction schedule and projected monthly lease-up rate. The most complex of the five stages of analysis, Stage 3 analysis consists of 3 parts as shown in Professional Real Estate Development (figure 4-6, pages 160-163; figure 4-7, page 164; figure 4-8, pages 166-169).

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 (figure 4-9, page 170).