# 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`
` `
` `

Revised 11 March 2005

` `
` `
` `
` `