 |  |
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
next year.
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
| |