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

 

1996 A.J.Filipovitch
Revised 11 March 2005