Tutorial On Excel Rent Roll Modeling To Enhance DCF

Tutorial on Excel Rent Roll Modeling to Enhnace DCF JR DeLisle, Ph.D.Tutorial on Excel Rent Roll Modeling to Enhance DCFOverviewIn a basic Discounted Cash Flow Model, income and expenses are often treated as a single line item. This approach has some appeal and is adequate forpreliminary analysis or for analysis of a single-tenant building on a NNN lease. However, it lacks the precision needed for fine-tuning the value of amulti-tenant building, or a building in which more detailed treatment of expenses is warranted. The objective of this tutorial is to explore twoenhancements that can be incorporated in DCF models: First, we will discussion how the rent roll for a multi-tenant building can be modeled to arrive atmore precise forecasts of gross income. Second, we will explore the treatment of fixed and variable expenses in term of different types of leases andallocation of expenses. Finally, we will model the analysis in Excel, taking advantage of built-in functions to develop a robust model that canaccommodate changes in assumptions and treat leases with different terms without deferring to manual calculations that are rigid and fixed. Since we areinterested in developing more advanced modeling skills, we will incorporate some Excel hints and examples throughout this text. The basic tools andconcepts should provide you with enough of an understanding of the issues when you set up your own leasing models.BackgroundThis tutorial assumes you understand lease concepts and know how to construct lease payment schedules for individual leases (see: Exhibit 1). Thistutorial will build on that foundation, and show you how you can construct lease payment schedules for a multi-tenant building consisting of tenantsholding variations of these leases with different rates, adjustments, patterns, renewal probabilities and other assumptions. In addition, we will explorehow to calculate variable and fixed rate expenses and reimbursements will be built on top of these as well as leasing commissions, absorption and otherelements that affect the Net Operating Income. In addition, you should have a basic understanding of Excel and understand the equations built into basicDCF; if not, you will develop them by going through this exersize and the attached worksheet.i

Tutorial on Excel Rent Roll Modeling to Enhnace DCF JR DeLisle, Ph.D.Table of ContentsOVERVIEW . IBACKGROUND . IINTRODUCTION .1DCF/RENT ROLL ANALYSIS . 1Exhibit 1(a): Discounted Cash Flow . 1Exhibit 1(b): NOI Bundle of Leases, Varied Terms . 1ALTERNATIVE APPROACHES TO VALUATION . 2COMMON LEASE TERMS APPLIED IN CASE . 3CASE STUDY: GENERAL ASSUMPTIONS .4PROJECT PROFILE . 4Exhibit 1: Rent Roll Input Assumptions . 4OVERVIEW OF RENT ROLL . 4OTHER OPERATING ASSUMPTIONS . 5Exhibit 2(a): General Assumptions . 5Exhibit 2 (b): Expense Assumptions . 5Exhibit 3: Market Leasing Assumptions . 6PROPERTY PROFILE . 7Exhibit 4: Property Size and Floorplates . 7RENT ROLL ANALYSIS .8RENT ROLL SCHEDULE . 8Exhibit 5: Rent Roll Schedule . 8Hints on Rent Roll Schedule . 8Hints on Naming Tables. 9Exhibit 6: Rent Roll Triggers & Codes . 10Hints to Identify Rent Roll Triggers. 10MARKET MATRICES . 11Exhibit 7: Market LeasingAssumptions (MLA) . 11Hints on MLA Table . 11FUTURE VALUE RENTS AND TENANT IMPROVEMENTS (TI’S) . 12Exhibit 8: Rent & TI Forecast . 12Exhibit 9: Retail Sales and Percentage Rent Forecast . 13Hints on Percentage Rents . 13Exhibit 10 (a): Tenant Rent Forecast . 14ii

Tutorial on Excel Rent Roll Modeling to Enhnace DCF JR DeLisle, Ph.D.Hints on Tenant Rent Forecast . 14Exhibit 10 (b): Market Rent Forecast . 15Exhibit 11: Rent Roll Triggers and Rollover Codes . 16Hint: Rollover Codes . 16Exhibit 12: Expected Rollover and Leasing Patterns . 17Exhibit 13: Rents Schedule Adjusted for Months. 18Annual Rent Adjusted for Months Hints . 18Exhibit 14: Gross Rent/SF Schedule . 19Exhibit 15: Vacancy on Renewals . 19Exhibit 16 (a): Free Rent and Vacant on Re-leasing in Months . 20Exhibit 16 (b): EGI net of Vacant/Free w/o % Rent . 21Exhibit 17: EGI With Percentage Rent . 22Exhibit 18: Tenant Improvements . 23Hint on TIs . 23Exhibit 19: TI’s and Leasing Commissions . 24Hint on Leasing Commissions . 24Exhibit 20: Cash Flow from Tenants after TI, Leasing & Percent Rent . 25Exhibit 21 (a): Expense Schedule Recap . 25Exhibit 21(b): Expenses and Reimbursement . 26Exhibit 22: Expense Stops and Reimbursements . 27Exhibit 23: Net Income by Tenant and Overall Cash Flow . 27Exhibit 24: Cash Flow Schedule . 28Exhibit 25(a): NPV based on BTCF . 28Exhibit 25(b): Average NPV of BTCF . 29Exhibit 26 (a): NPV of BTCF and NOI-capped Terminal Value . 29Exhibit 26 (b): Average NPV of NOI Capped . 30Exhibit 26 (c): IRR at Average /SF . 30iii

Tutorial on Excel Rent Roll Modeling to Enhnace DCFIntroductionDCF/Rent Roll AnalysisExhibit 1(a): Discounted Cash FlowNRCash Flow ModelAs noted in Exhibit 1 (a), the real estate values can becalculated as the Net Present Value of Future Benefits. Inthis context, Future Benefits consist of Net OperatingIncome (NOI), Tax Benefits (or costs), and Net TerminalValue (i.e., After Tax Proceeds on Sale). Once thesebenefits are modeled, they can be discounted back by someRate to establish the Present Value (i.e., V I/R).PV/Exit CapPVNRPVNIGI/WccInterestVacancyProperty TaxExpensesPrincipalStabilized NIThe Income (I) in this application is the NOI TSOI (TaxLand Costs NISavings on Other Income). The NOI is in turn based on theHard Costsaggregation of individual leases with different rates, terms,Soft Costsadjustments and options. The leases are often staggered toavoid excessive market risk, with some probability of TRCrenewal. At the same time, market conditions are changing,resulting in a set of assumptions regarding future lease termsthat would kick in if the tenant does not renew, along withleasing commissions, tenant improvements and other costs. In addition to Rental Income, NOI might be affected by Expense Reimbursement (ER) inwhich tenants pay a portion of expenses, often over some floor. The Rate is a function of capital flows and demand, along with the Risk profile which isrelated to the certainty of Rental Income which depends on the bundling of leases (see: Exhibit 1 (b) and credit of tenants.Exhibit 1(b): NOI Bundle of Leases, Varied Terms3 yr7 yr1 JR DeLisle, Ph.D.

Tutorial on Excel Rent Roll Modeling to Enhnace DCFAlternative Approaches to ValuationBefore launching into an example of constructing a rent roll, along with related expenses and other financial elements, it is useful to see where this typeof modeling fits into the investment analysis continuum. As noted in Exhibit 2, there are two basic types of financial models in real estate; static anddynamic. In general, rent roll and lease analyses are built into dynamic models, with the exception of rental structure analysis that can be used inFrontdoor/Backdoor models. In this tutorial, we will be seeking to develop a subset or module for rent Roll analysis and expense analysis that can beinserted in a basic DCF model. For more advanced modeling, we will defer to Argus.Exhibit 2: Alternative Real Estate Models Static– Attributes Fixed Cash Flows; Annuitized In Perpetuity Before Tax– Cap Rate Overall Cap Rate Gross Income Multiplier Net Income Multiplier– Frontdoor/Backdoor Application: Filtering deals, go/no go Model: annuitized cash flows Risk: in Rate or scenarios, etc.Dynamic– Attributes Variable Cash Flows Fixed Time After Tax– DCF Excel: Base Application: Preliminary Go/No Model: single source income, expenses, investment Risk: IRRs, MIRRs other Ratios; scenarios, simulation– DCF Enhanced: Rent Roll and Investor-specific Application: Final Commitment, Due Diligence Model: precise c

accommodate changes in assumptions and treat leases with different terms without deferring to manual calculations that are ri gid and fixed. Since we are . Retail 1,725 4.2% 7/1/2006 3 18.40 CPI, Lease Year Net, Pro-rata Share Renew All MLA 2