Real Estate Pro Forma: Calculations

Every project is unique. Finding reasonable data for analyzing a real estate project is an art. Usually, a public planner or manager will depend on the developer to provide much of the data. The developer will usually assemble the data by comparing the proposed project to other similar projects. Developers often specialize in a few types of projects, and have considerable personal experience with projects similar to the one being proposed. A second source of information is a market survey. This is particularly useful for gauging anticipated rents and anticipated vacancies. Finally, the industry publishes several manuals of standard costs and revenues for various types of projects in different regions of the country. These are, perhaps, not as accurate as first-hand, local information, but for some projects there is no similar local project to serve as a comparison.

The Real Estate Pro Forma template includes twomodels for analyzing the economic feasibility of a real estate project. These models are based on the ones developed by the National Main Street Center of the National Trust for Historic Preservation. The first is a simplified front-door (acquisition-driven) model; the second is a simplified back-door (income-driven) model. These are useful for sketching the outlines of a project and developing a rough estimate of project feasibility. The most important index, return on equity, is not calculated in either model. Instead, the analyst enters the minimum acceptable return and the model calculates the minimum structure which will assure that return. If the proposed project comes in ahead of those figures, a detailed analysis can be run. If the project comes in short of those figures, a new structure should probably be developed before going through the trouble of a detailed analysis.

Both models work from an input format worksheet (Figure 5.1). The information from this worksheet is used to calculate the pro formas; there is no need for the user to enter any information into any of the pro forma worksheets. This protects the formulas in the spreadsheet from being accidentally overwritten. Some information is common to both pro formas. Some is specific to one or the other. The input formats worksheet reminds you of the type of measurement (dollars, square feet, percentages) that you should be entering for each item. Remember to enter percentages as decimals rather than whole numbers.

                                INPUT FORMATS

FOR ALL MODELS:

        ACQUISITION COST                      $....

        GROSS SQUARE FOOTAGE              .... SQ FT

        INDIRECT COST RATIO                   .... %

        MORTGAGE LOAN:  DURATION           ....YRS

                        RATE          .... %

            LOAN TO COST RATIO          .... %

        OPERATING EXPENSES                  $....

        REAL ESTATE TAXES                    $....

        EXPECTED VACANCY RATE              .... %

        REQUIRED RETURN ON EOUITY          .... %

        PERCENT OF SPACE LEASABLE          .... %

FOR ACQUISITION-DRIVEN MODEL

        IMPROVEMENT COSTS                    $....

        LOAN TO COST RATIO                   .... %

FOR INCOME-DRIVEN MODEL

        ANTICIPATED RENT                      $....

        DEBT COVERAGE RATIO                 .... %

                                Figure 5.1

The simplified acquisition analysis (Figure 5.2) begins with the cost of acquiring and improving a property, takes into account a user-specified minimum rate of return on equity and calculates the rent required to make the project work, the debt coverage ratio, and the default ratio. The worksheet calculates three subtotals: Total capital budget is the sum of acquisition, improvement, and indirect costs. Total annual cost is the sum of the annual debt service of the capital budget, operating expenses, and real estate taxes. Net operating income (NOI) is the sum of total annual costs and required return on equity. The gross effective income (NOI adjusted for expected vacancies) is then divided by the leasable space available to calculate the required rent per square foot.

        SIMPLIFIED ACQUISITION ANALYSIS (FRONT DOOR APPROACH)

        ACQUISITION COSTS       xxxxx

        IMPROVEMENT COSTS       xxxxx

        INDIRECT COSTS          xxxxx

                 TOTAL CAPITAL BDGT      xxxxx

           LOAN PRINCIPAL       xxxxx

           DEBT SERVICE RATE    xxxxx

        ANNUAL DEBT SERVICE     xxxxx

        OPERATING EXPENSES      xxxxx

        REAL ESTATE TAXES       xxxxx

                TOTAL ANNUAL COSTS      xxxxx

        RETURN ON EOUITY        xxxxx

          NET OPERATING INCM    xxxxx

        GROSS EFFECTIVE INM     xxxxx

           EXPECTED VACANCY     xxxxx

           NET LEASABLE SPACE   xxxxx

        RENT REQUIRED           xxxxx

        DEBT COVERAGE RATIO     xxxxx

        DEFAULT RATIO           xxxxx

                        Figure 5.2

The simplified income analysis (Figure 5.3) comes at the problem from the other direction. Given an existing market structure for rents, a stable vacancy rate, and a minimum rate of return on equity, it determines how much of an investment can be justified for acquiring and improving a property. The gross potential income is adjusted for vacancies, operating expenses, and taxes to arrive at the net operating income. NOI is divided between debt service (based on a specified minimum debt-coverage ratio) and return on equity (whatever is left over). The annual amount available for debt service determines the maximum loan justified by current market conditions; the required return on equity determines the maximum equity investment justified by current market conditions. The spreadsheet then calculates the total justified investment and the ratio due to the loan and due to owner equity. A project may fail to be funded if the ratio of loan and equity does not meet the lender's expectations. Finally, the total investment is allocated between acquisition costs (specified by the user) and funds available for improvements (calculated by the worksheet).

        SIMPLIFIED INCOME ANALYSIS (BACK DOOR APPROACH)

        GROSS POTENTIAL INCOME          xxxxx

        GROSS EFFECTIVE INCOME          xxxxx

        OPERATING EXPENSES              xxxxx

        REAL ESTATE TAXES               xxxxx

                NET OPERATING INCM              xxxxx

        AVAILABLE FOR LOAN              xxxxx

        AVAILABLE FOR RETRN ON EQTY     xxxxx

                MAX LOAN JUSTIFIED      xxxxx

                EQUITY JUSTIFIED                xxxxx

                TOTAL INVESTMENT                xxxxx

                LOAN PERCENT            xxxxx

                EQUITY PERCENT          xxxxx

        AVAILABLE FOR IMPROVEMENTS      xxxxx

                DIRECT COSTS-SQ FT      xxxxx

                INDIRECT COSTS          xxxxx

                TOTAL COSTS PER FT      xxxxx

                        Figure 5.3

609