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 three models 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 two are a simplified front-door (acquisition-driven) model and 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. The third model is a detailed acquisition-driven model which includes tax considerations.
All three 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 all three 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 .... % FOR TAX-DRIVEN MODEL CONSTRUCTION LOAN: DURATION ....YRS RATE .... % ORIGINATION FEE .... % LOAN TO COST RATIO .... % TAX BRACKET .... % INVESTMENT TAX CREDIT RATE .... % DEPRECIATION LIFE .... YRS LEASING FEE .... % Figure 5.1
The spreadsheet also includes a more detailed set of input worksheets for the tax-driven model (Figure 5.2). You must use these worksheets to calculate the values for items to be used in the tax-driven analysis. The construction Loan Financing table calculates the costs associated with the short-term (usually 1 year) construction financing which is traditionally used to carry a project until it is ready to rent. It makes two passes at a solution; the first time through it calculates the construction loan and the associated costs; the second time through it adds the cost of acquiring the loan into the principal and calculates a new loan based on the increased costs. These costs are then folded into the annual debt service costs, calculated on the basis of the mortgage loan information provided in the input format.
DIRECT COSTS GROUND FL ....SQ. FT X ....PER SQ FT = xxxxx ADDL FLRS ....SQ. FT X ....PER SQ FT = xxxxx FACADE ....SQ. FT X ....PER 50 FT = xxxxx ROOF ....SQ. FT X ....PER SQ FT - xxxxx OTHER ....SQ. FT X ....PER SQ FT = xxxxx TOTAL DIRECT COST = xxxxx CONSTRUCTION LOAN FINANCING FIRST RUN SECOND RUN TOTAL COSTS = xxxxx TOTAL COSTS = xxxxx CONSTRUCTION LOAN= xxxxx CONSTRUCTION LOAN= xxxxx INTEREST ON LOAN= xxxxx INTEREST ON LOAN= xxxxx ORIGINATION FEE= xxxxx ORIGINATION FEE= xxxxx INDIRECT COSTS ARCHITECT/ENGINEER FEES @ ....% OF DIRECT COST = xxxxx LEGAL/ACCOUNTING FEES @ ....% OF DIRECT COST = xxxxx LEASING FEES @ ....% OF GROSS INCOME= xxxxx LOAN ORIGINATION FEES @ ....% OF LOAN = xxxxx INTEREST ON CONSTRUCTION LOAN = xxxxx TOTAL INDIRECT COST= xxxxx INDIRECT COST RATIO= xxxxx RENTAL INCOME SPACE 1 RETAIL ....SQ. FT X ....PER SQ FT = xxxxx SPACE 2 OFFICE FL ....SQ. FT X ....PER SQ FT = xxxxx SPACE 3 RESIDENCE ....SQ. FT X ....PER SQ FT = xxxxx SPACE 4 ....SQ. FT X ....PER SQ FT = xxxxx SPACE 5 ....SQ. FT X ....PER SQ FT = xxxxx SPACE 6 ....SQ. FT X ....PER SQ FT = xxxxx SPACE 7 ....SQ. FT X ....PER SQ FT = xxxxx TOTAL GROSS POTENTIAL INCM= xxxxx OPERATING EXPENSES EXPENSE 1 HEAT/LITE ....SQ. FT X ....PER SQ FT = xxxxx EXPENSE 2 CLEANING ....SQ. FT X ....PER SQ FT = xxxxx EXPENSE 3 LET SPACE ....SQ. FT X ....PER SQ FT = xxxxx EXPENSE 4 PROP TAX ....SQ. FT X ....PER SQ FT = xxxxx EXPENSE 5 ....SQ. FT X ....PER SQ FT = xxxxx EXPENSE 6 ....SQ. FT X ....PER SQ FT = xxxxx EXPENSE 7 ....SQ. FT X ....PER SQ FT = xxxxx LEASE FEE ....% OF GROSS EFFECTIVE INCOME = xxxxx TOTAL OPERATING EXPENSES = xxxxx Figure 5.2
The simplified acquisition analysis (Figure 5.3) 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.3
The simplified income analysis (Figure 5.4) 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.4
The third worksheet is an acquisition-driven model which includes tax considerations (Figure 5.5). The first part of the worksheet is similar to the simplified model; it calculates total capital budget, gross effective income, annual debt service (here combined into single line), and net operating income. Net operating income is calculated both before and after debt service is paid, since the tax analysis deals with debt service separately from other operating expenses. The worksheet then calculates three pre-tax feasibility ratios: Return on Equity, Debt Coverage, and Default Ratio. The worksheet then calculates six years of after-tax cash flow, although you can expand the analysis beyond six years if you choose. Net operating income (after debt service) is adjusted for tax savings and, in the first year, for an investment tax credit (if appropriate). The sum of these savings is "cumulative return on investment." The worksheet then calculates the three feasibility ratios for after-tax benefits (return on investment, debt coverage. and default ratio), plus a return on investment ratio.
ACQUISITION-DRIVEN ANALYSIS WITH TAX CONSIDERATIONS ACQUISITION COSTS xxxxx DIRECT IMPROVEMENT COSTS xxxxx INDIRECT COSTS xxxxx TOTAL CAPITAL BUDGET xxxxx ANNUAL DEBT SERVICE xxxxx GROSS POTENTIAL INCOME xxxxx GROSS EFFECTIVE INCOME xxxxx OPERATING EXPENSES xxxxx NET OPERATING INCOME (BEFORE DEBT SRVC) xxxxx (AFTER DEBT SRVC) xxxxx PRE-TAX R 0 E xxxxx PRE-TAX DEBT COVER xxxxx PRE-TAX DEFAULT % xxxxx SIX-YEAR CASH FLOWS (AFTER TAXES) YR 1 YR 2 YR 3 YR 4 YR 5 YR 6 CASH FLOW (PRE-TAX) xxxx xxxxx xxxxx xxxxx xxxxx xxxx SAVINGS/COST ON TAXES xxxx xxxxx xxxxx xxxxx xxxxx xxxx INVESTMNT TAX CREDI xxxx 0 0 0 0 0 TOTAL CUMULATIV R 0 I xxxx xxxxx xxxxx xxxxx xxxxx xxxx % R 0 I xxxx xxxxx xxxxx xxxxx xxxxx xxxx % R 0 E xxxx xxxxx xxxxx xxxxx xxxxx xxxx POST-TAX DEBT CVRG xxxx xxxxx xxxxx xxxxx xxxxx xxxx POST-TAX DEFAULT % xxxx xxxxx xxxxx xxxxx xxxxx xxxx Figure 5.5
Tax savings (or, perish the thought, tax costs) are calculated
on a separate worksheet (Figure 5.6). It works from NOI before
debt service, since the interest on the debt is deductible, but
the principal is not. Interest and depreciation are subtracted
from NOI to calculate tax loss or savings. The cost of the investment
credit (if there is one) is subtracted from the depreciable basis
of a property, and you should adjust the depreciation formula
to reflect this difference. The worksheet also calculates the
outstanding principal for each year. This is used to calculate
the portion of debt service devoted to interest payments in the
SIX-YEAR CASH FLOWS N 0 I xxxx xxxx xxxx xxxx xxxx xxxx INTEREST xxxx xxxx xxxx xxxx xxxx xxxx DEPREC'TN xxxx xxxx xxxx xxxx xxxx xxxx TAX LOSS xxxx xxxx xxxx xxxx xxxx xxxx TAX SVNGS xxxx xxxx xxxx xxxx xxxx xxxx PRINCIPAL xxxx xxxx xxxx xxxx xxxx xxxx Figure 5.6