Tutorial on Excel Rent Roll Modeling to Enhance DCF

1 Tutorial on Excel Rent roll Modeling to Enhnace DCF JR DeLisle, Tutorial on Excel Rent roll Modeling to Enhance DCF. Overview In 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 for preliminary 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 a multi-tenant building, or a building in which more detailed treatment of expenses is warranted. The objective of this Tutorial is to explore two enhancements 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 at more precise forecasts of gross income.

2 Second, we will explore the treatment of fixed and variable expenses in term of different types of leases and allocation of expenses. Finally, we will model the analysis in Excel , taking advantage of built-in functions to develop a robust model that can accommodate changes in assumptions and treat leases with different terms without deferring to manual calculations that are rigid and fixed. Since we are interested in developing more advanced Modeling skills, we will incorporate some Excel hints and examples throughout this text. The basic tools and concepts should provide you with enough of an understanding of the issues when you set up your own leasing models.

3 Background This Tutorial assumes you understand lease concepts and know how to construct lease payment schedules for individual leases (see: Exhibit 1). This Tutorial will build on that foundation, and show you how you can construct lease payment schedules for a multi-tenant building consisting of tenants holding variations of these leases with different rates, adjustments, patterns, renewal probabilities and other assumptions. In addition, we will explore how to calculate variable and fixed rate expenses and reimbursements will be built on top of these as well as leasing commissions, absorption and other elements that affect the Net Operating Income.

4 In addition, you should have a basic understanding of Excel and understand the equations built into basic DCF; 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, Table of Contents OVERVIEW .. I. BACKGROUND .. I. INTRODUCTION ..1. DCF/RENT roll ANALYSIS .. 1. Exhibit 1(a): Discounted Cash Flow .. 1. Exhibit 1(b): NOI Bundle of Leases, Varied Terms .. 1. ALTERNATIVE APPROACHES TO VALUATION .. 2. COMMON LEASE TERMS APPLIED IN CASE .. 3. CASE STUDY: GENERAL ASSUMPTIONS.

5 4. PROJECT PROFILE .. 4. Exhibit 1: Rent roll Input Assumptions .. 4. OVERVIEW OF RENT roll .. 4. OTHER OPERATING ASSUMPTIONS .. 5. Exhibit 2(a): General Assumptions .. 5. Exhibit 2 (b): Expense Assumptions .. 5. Exhibit 3: Market Leasing Assumptions .. 6. PROPERTY PROFILE .. 7. Exhibit 4: Property Size and Floorplates .. 7. RENT roll ANALYSIS ..8. RENT roll SCHEDULE .. 8. Exhibit 5: Rent roll Schedule .. 8. Hints on Rent roll Schedule .. 8. Hints on Naming 9. Exhibit 6: Rent roll Triggers & Codes .. 10. Hints to Identify Rent roll 10. MARKET MATRICES .. 11. Exhibit 7: Market LeasingAssumptions (MLA).

6 11. Hints on MLA Table .. 11. FUTURE VALUE RENTS AND TENANT IMPROVEMENTS (TI'S) .. 12. Exhibit 8: Rent & TI Forecast .. 12. Exhibit 9: Retail Sales and Percentage Rent Forecast .. 13. Hints on Percentage Rents .. 13. Exhibit 10 (a): Tenant Rent Forecast .. 14. ii Tutorial on Excel Rent roll Modeling to Enhnace DCF JR DeLisle, Hints on Tenant Rent Forecast .. 14. Exhibit 10 (b): Market Rent Forecast .. 15. Exhibit 11: Rent roll Triggers and Rollover Codes .. 16. Hint: Rollover Codes .. 16. Exhibit 12: Expected Rollover and Leasing Patterns .. 17. Exhibit 13: Rents Schedule Adjusted for 18.

7 Annual Rent Adjusted for Months Hints .. 18. Exhibit 14: Gross Rent/SF Schedule .. 19. Exhibit 15: Vacancy on Renewals .. 19. Exhibit 16 (a): Free Rent and Vacant on Re-leasing in Months .. 20. Exhibit 16 (b): EGI net of Vacant/Free w/o % Rent .. 21. Exhibit 17: EGI With Percentage Rent .. 22. Exhibit 18: Tenant Improvements .. 23. Hint on TIs .. 23. Exhibit 19: TI's and Leasing Commissions .. 24. Hint on Leasing Commissions .. 24. Exhibit 20: Cash Flow from Tenants after TI, Leasing & Percent Rent .. 25. Exhibit 21 (a): Expense Schedule Recap .. 25. Exhibit 21(b): Expenses and Reimbursement.

8 26. Exhibit 22: Expense Stops and Reimbursements .. 27. Exhibit 23: Net Income by Tenant and Overall Cash Flow .. 27. Exhibit 24: Cash Flow Schedule .. 28. Exhibit 25(a): NPV based on BTCF .. 28. Exhibit 25(b): Average NPV of BTCF .. 29. Exhibit 26 (a): NPV of BTCF and NOI-capped Terminal Value .. 29. Exhibit 26 (b): Average NPV of NOI Capped .. 30. Exhibit 26 (c): IRR at Average $/SF .. 30. iii Tutorial on Excel Rent roll Modeling to Enhnace DCF. Introduction DCF/Rent roll Analysis Exhibit 1(a): Discounted Cash Flow Cash Flow Model NR. As noted in Exhibit 1 (a), the real estate values can be PV.

9 Calculated as the Net Present Value of Future Benefits. In this context, Future Benefits consist of Net Operating PVNR /Exit Cap Income (NOI), Tax Benefits (or costs), and Net Terminal GI. Value ( , After Tax Proceeds on Sale). Once these PVNI. /Wcc Vacancy benefits are modeled, they can be discounted back by some Property Tax Expenses Rate to establish the Present Value ( , V = I/R). Principal Interest Stabilized NI. The Income (I) in this application is the NOI + TSOI (Tax Land Costs Savings on Other Income). The NOI is in turn based on the = NI. aggregation of individual leases with different rates, terms, Hard Costs adjustments and options.

10 The leases are often staggered to Soft Costs avoid excessive market risk, with some probability of renewal. At the same time, market conditions are changing, = TRC. resulting in a set of assumptions regarding future lease terms that would kick in if the tenant does not renew, along with leasing commissions, tenant improvements and other costs. In addition to Rental Income, NOI might be affected by Expense Reimbursement (ER) in which 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 is related to the certainty of Rental Income which depends on the bundling of leases (see: Exhibit 1 (b) and credit of tenants.)