Comparative lease analysis for the corporation.
To a manager responsible for locating a leased building and justifying its economic feasibility, the job of lease analysis can be demanding and time consuming. The difficult process of comparing leased facilities involves taking into account complicated variables such as methods of measuring floor size and efficiencies, formulas for escalating rent and operating costs, and methods of evaluating tenant improvements and amenities.
To make matters worse, projecting a lease's economic costs over a five to ten year period, considering all the possible "what if" scenarios, can be very tedious and frustrating.
Fortunately, the personal computer, with its powerful "number crunching" electronic spreadsheets, greatly increases the productivity of the real estate analyst. At Apple Computer, Inc., as in real estate departments throughout corporate America, the personal computer has become a standard tool, facilitating the creative manipulation of the many variables involved in lease negotiations and greatly increasing the property manager's ability to make decisions.
It is the intent of this article to demonstrate how an electronic spreadsheet, such as Microsoft's Excel, can assist in the evaluation and negotiation of lease contracts. In replicating the spreadsheet formulas in this article, managers will gain proficiency with spreadsheet formats and will acquire experience in the design and modification of custom templates for the specific purpose of lease analysis.
The reason for using Excel, or any other spreadsheet program, in projecting lease costs is to develop "what if" strategies - to modify assumptions and measure their effects on the bottom line. A manager who can quantitatively calculate the effects of different variables on a lease will be able to determine their effect on rent, and thereby will be able to negotiate the best possible lease.
The accompanying spreadsheet was designed for ease of use and interpretation by management. The results could be used as supporting documentation in comparing proposed rent schedules and in negotiating specific rates.
Data for each of the three buildings is input to the appropriate field on the Summary/Input Table (Figure 1). At any time, different financial criteria can be substituted to determine their effect on each property's rental rate.
The financial variables which may be manipulated in the course of the analysis include the following: the security deposit, building size, rentable area and required usable area, tenant improvement budget, as well as the building's improvement allowance, lease term, lease initiation date, date of rent commencement, free rent, rental rate, timing of rent escalations, and the discount rate.
As the analyst changes the input data in Figure 1, the subsequent tables (Figures 2-4) automatically recalculate.
The Summary/Input Table allows for easy modification of the input data. In addition to its input function, this table summarizes the input criteria and presents the conclusions of the 10-year analysis. The analyst may view the Conclusion section of Figure One to determine the net present value (NPV) of future rent flows, the NPV per square foot, and the building's load factor and employee density per usable area.
Variables in lease analysis
Three variables affecting the lease analysis are the building size, the rentable area, and the usable area of the premises. It is important for an analyst to distinguish clearly between usable square feet and rentable square feet. Typically building rent is quoted on a rentable basis and tenant improvement allowances on a usable basis. Yet one cannot assume that this is the standard practice of all managers across the country, so this point must be clarified.
The standard improvement allowance is the dollar allowance provided by the owner for the construction of the tenant's interior improvements, while the above standard allowance is the amount provided by the owner as a concession to induce the tenant to lease the facility. Both figures represent funds available to the tenant to improve the premises.
As seen in line 12, the owner of Building One is providing a standard tenant improvement allowance of $18 per square foot. Line 13 shows that an above-standard allowance of $5 per square foot is being offered as a lease incentive by Building One's landlord. Building Two offers a standard allowance of $20, and Building Three an allowance of $35 per square foot.
Neither Building Two nor Building Three is providing an above-standard construction allowance. As depicted in line 25, each building is providing a cash incentive of between $20,000 and $50,000, plus free rent (line 24) ranging from $226,500 to $346,267. As a result of the analysis, free rent and cash incentives may be reduced in an effort to increase funds available for the construction of interior improvements.
Tenant's costs and space planning are amounts estimated by the tenant to represent an actual dollar contribution, in excess of that provided by the owner. The cost of the tenant's interior improvements are amortized over the lease term (lines 104, 139 and 173) and added to the annual rent (lines 106, 141, 175). Therefore the cost of the improvements are discounted and reflected in the net present value of rent.
The analyst must define the lease term (number of years), the lease initiation date, and the date at which rent payments begin. These input fields are used to calculate the free rent period, as well as the dollar value equivalent of free rent.
As shown in line 22, the commencement date of rent for Buildings One and Three is June 1, 1990, with free rent for a period of five months, whereas Building Two has a free rent period of seven months. By changing the lease commencement date (line 20) or the date on which rental payments begin (line 22), the analyst may quickly determine the effect of these dates on the annual contract rent over the lease term (lines 96, 131, 165), and the time value of money reflected in net present value (lines 112, 147, 181).
If a tenant required an interior build-out which would significantly postpone the commencement date of the lease, the analyst could determine the impact of such a delay on the total rent. Delays in the commencement of rent may warrant an increase in the rental rate.
The rent for each property is input on a monthly basis. If the rental rate is fixed for the lease term, only one input is required. For example, upon entering a first month's rent of $2.25, this amount automatically cascades to the end of the 120-month analysis period. If the lease term is only 60 months, a zero dollar ($0.00) amount should be entered in month 61. This amount will automatically be reflected in the subsequent monthly periods. The automatic cascading design allows the analyst to enter simply a new or escalated rental amount in the appropriate month.
In an economic comparison between leases, it is important to calculate the net present value of the rental payments. Although it is possible to make calculations based upon annual lease costs or average rent expenditures, such measures ignore the time value of money. The correct method takes into consideration the actual timing of the cash expenditures and receipts. Typically, rent payments are made at the beginning of each month. To accurately assess the present worth of the payments, these flows should be discounted monthly, not on an annual basis. Tenant inducements such as cash or free rent should similarly be treated on a monthly basis.
The effect on income of many of the lease variables may be calculated by converting them to cash flows and discounting these cash flows over the lease term to determine their net present value.
For an example of cash flow calculations for a particular building, refer to Figures 2-4. This series of figures shows the results of the 10-year analysis. Lines 65-69 represent inputs for expenses associated with taxes, operating costs, electricity, janitorial, and miscellaneous expenses. Each expense category can be independently increased annually (D66-D70) by a percentage the analyst deems equivalent to cost of living increases.
Since the operating expenses are added to the annual contract rent (lines 96, 131, 165) the actual rent is comprised of a summation of annual contract rent, tenant electric, real estate taxes, operating costs, and janitorial. In the course of this summation, these cash expenditures are discounted and reflected in the net present value of rent. If excessive operating cost can be limited through professional property management, the cost savings will impact the total occupancy cost, and will in turn be reflected in the rent over term (lines 108, 143, 177).
Lines 74-76 represent the findings derived from the analysis sections of each lease in Figures 2-4. A nice feature in the design of this spreadsheet is that the analyst may ascertain the results of the projections for each of the three properties under consideration by referring only to the Conclusion section of Figure 1. [Tabular Data Omitted]
using a spreadsheet
As shown in the Summary/Input Table, each building under consideration is approximately 20,000 rentable square feet. Although the rentable area of each facility is similar, the loss factor associated with each building differs, and so therefore does its usable area.
As can be seen in line 76, Building One has a loss factor of 11.11 percent, Building Two has a loss of 7.53 percent, and Building Three 8.11 percent. Given the fact that the three buildings' sizes range from 135,000 square feet for Building Three to 150,000 square feet for Building One, the tenant's percentage share will differ.
The tenant's pro rata share is depicted in lines 87, 122, and 157. It should be noted in the analysis that the differences in the usable square footage directly relate to additional rental costs. The tenant pro rata share is a significant variable which directly affects the assumption of operating costs.
The most difficult task in the analysis of different leases is determining exactly what each building will provide in the way of tenant improvements and their associated financing. A lease may appear attractive due to its low monthly rent, yet the improvement allowance may be inadequate to cover the tenant's needs. In such cases, a tenant must take cash out-of-pocket or amortize the additional costs if agreeable with the owner, thereby increasing the monthly rent.
This spreadsheet combines the standard tenant improvement allowance (line 12) and the above standard allowance (line 13) to tally the total amount provided by the owner for improvements. The tenant's cost of construction is reflected in the analysis by combining space planning costs (line 15) with any tenant contributions (line 16) above the amount of the owner's allowance.
The initial rents for the three properties under consideration range from a low of $2.25 for Building One to a high of $2.45 for Building Two. But simple comparison and projection of building rents does not shed much light on their relative actual costs.
A major problem with the simple projection of building rent costs is that it overlooks operating expenses, taxes, and insurance, each of which represents a significant economic obligation to a corporate tenant.
Unaccounted-for lease costs are seldom pleasant surprises to a company manager responsible for the profitability of his or her division. Time after time, at fiscal year-end, tenants can be heard requesting an explanation for substantial charges of "additional rent." Why does such confusion occur when it should be reasonably simple to calculate lease cost?
Often such an event can be attributed to a certain type of lease, known as a "gross," or "full-service" lease, which includes a projected operating costs in the tenant's base rent. Under such a lease, operating expenses are reconciled at year-end and the overrun, if any, is billed to the tenant. For this reason, tenants must be provided with realistic projections of operating expenditures.
Operating expenses and the projected magnitude of their increases should also be accounted for in each lease analysis. Lines 65-69 represent the projected expenses for which the tenant is responsible, over and above the amount provided by the owner as an expense stop. Expense stops are expressed in dollars per square foot of rentable area.
It is important to ensure that projected expenses are realistic and represent typical operating costs for buildings with comparable amenities. Comparable information on typical building expenses is available from the Institute of Real Estate Management and other professional real estate organizations.
A review of Summary/Input Table lines 11-12 shows that Owner Three is willing to provide 100 percent of the tenant's improvement requirements at an initial rental rate of $2.35 per square foot. Owner One will provide an initial rental rate of $2.25 and an improvement allowance of $23 per square foot. If the required improvements will cost $35.00 per square foot, the tenant must contribute $2.00 per square foot toward space planning and $10.00 per square foot for improvements. Owner Two has proposed an initial rental rate of $2.45 per square foot, yet the tenant must contribute $8.00 per square foot for interior improvements and $2.00 per square foot for space planning.
Real estate taxes, electric, janitorial, operating expenses, and miscellaneous expenses all account for cost variations among properties. The load factor for each building ranges from a low of 7 percent, to a high of 11 percent. Therefore the usable area of the buildings differs; Building One consists of 18,000 usable square feet, whereas Building Two consists of 18,600, and Building Three of 18,500.
It is clear that a change in the magnitude of a variable can significantly impact the bottom line rent, and therefore the ultimate selection of a building. The only meaningful way to analyze the economic impact of the variables and the consequence of their variations is to convert them to cash flows and discount the cash flows to their present value.
Once the analyst inputs the data derived from the alternative lease proposals, the data may be manipulated to arrive at occupancy expenditures which best meet a tenant's needs. "What-if" scenarios can easily be developed for use during lease negotiations.
To illustrate, assume that for Lease One, the tenant would be required to spend approximately $234,000 for tenant improvements to obtain a below-market rent of $2.25. In this situation, it may be useful to ask: How much can the base rent be increased for Lease One during the first year, in exchange for a comparable shift of improvement costs from the tenant to the owner, while obtaining a net present value of total rent flows comparable to that of Lease Two? Several months of free rent could be traded by a tenant for an increase in the improvement allowance or an increase in the expense stop for operating expenses.
The "what-if" scenario may be manipulated and altered for each of the leases and used as a tool in negotiations in an effort to ascertain the best possible lease terms and conditions.
Author's note: All Microsoft Excel functions contained in this spreadsheet are identical to those of Lotus 1-2-3, with the exception of "sum" and the financial function of net present value: NPV. The Lotus 1-2-3 function for NPV must be proceeded with @. The Lotus 1-2-3 formula equivalent to Excel's formula found in line 111 of Figure 2 would be = (C8*@NPV (C72/12,B29:M38)) - (C5+((C15+C16)*C9)).
To designate the range in Lotus's "sum" function, three periods are used, not the colon as found in the Excel function. The Lotus 1-2-3 formula equivalent to Excel's formula found in line 95 of Figure 2 would be Sum(B28...M28)*C7.
The author wishes to thank Harrison Rose, president of Rose Systems and consultant to Apple Computer, Inc., for the review of Excel and Lotus formulas.
|Printer friendly Cite/link Email Feedback|
|Author:||Sarvis, Michael J.|
|Publication:||Journal of Property Management|
|Article Type:||Product/Service Evaluation|
|Date:||May 1, 1989|
|Previous Article:||Software review: MRI property management accounting systems.|
|Next Article:||A brief look at new rules for 1989.|