PART I"Basic Building Block" Excel Model Creation (Groups of up to 3individuals for Part I)

Reference any all CCIM/G-M modules/chapters in course textbook or the library for any required supporting information and the associated Reading Assignment(s)/Articles needed to complete this Project/Case which is Available on Course Website: (Grading: 100 / Total individual questions = 100/32 =

3.125 points each

1.) "Table I. Information on Sales of Comparable Shopping Centers" (Excel Model of Table I (shown down below) which is posted in unlocked format to expedite incorporating into comparable sales module.)

Other Supplementing Information/Sources you may find useful to complete this assignment:

The Appraisal of Real Estate 14th Edition, 2013 by the Appraisal Institute

Investment Analysis for Commercial Real Estate Reference Manual 2012 Edition by CCIM Institute

Commercial Investment 2nd Edition by Geltner and Miller 2013: Full Text on reserve in Main library.

Real Estate Principles: A Value Approach by Ling and Archer 5th Edition, 2013: Full Text on reserve in Main library.

The Basic Building Block Overview: "Retail Real Estate Appraisal/Investment analyzer"

You are required to build a fully functioning variable holding period investment model via Excel. It is suggested that you build your model using Tab 1 for all inputs to the model and then use subsequent Tabs for various modules of your "Real Estate Appraisal/Investment Analyzer". At a very minimum, your model must be able to take variations of inputs (i.e. no hard-coding cells) and render a valuation to the after-tax level by incorporating a discounted cash-flow model methodology. Set Excel to show 2 decimal places on spreadsheet but use full precision in all calculations for any and all TABs or subsequent expansion of models. Note: Use common sense in presentation of outputs (i.e. proforma 2 decimal places APRs 4 depending on cell formatting)

In General your "Basic Building Block" Model must be able to have inputs for and produce the following:

1)Be able to varying your particular holding for the investment (i.e. 1 to 30 years or more based on your investment strategy)

2)Be able to vary discount rates based on perceived risk of cash-flows. (Be sure you check actual market cap rates for your particular sector and class of property from previous sales. Remember: you have to competitively bid which in turn drives the market....look for where you have an advantage and core competency)

3)Solve for the going in/going out capitalization rates if this information is not provided.

4)Implement a "going out" capitalization rate technique for determining the gross sales proceeds in the terminal year (once again this must be able to vary the based the expected terminal year @Tx)

5)Varying commercial loan (debt) inputs (i.e. 30 year monthly amortization with balloons at various times (5 or 7 years for example), interest only loans with annual and or monthly payments)

6)Automatically build out the amortization table for the loan based on the loan package. (i.e. take a 360 month loan and calculate a payoff based on the input page of when it is going to be sold and find the balance etc.)

7)Be able to have different loan interest rates and costs inputted and show the APR for the loan package taking into account points etc. from the borrower's and lender's perspective over the holding period (i.e. "Lender's yield" (Regulation Z APR) and "effective Borrowing Cost (EBC)

8)Calculate depreciation schedule(s) for the holding period in terms of normal straight-line depreciation for a pretty good "first pass" to determine whether it is a worthwhile investment and warrants further detailed analysis and implementation of the mid-month convention for accurate forecasting of tax shields and reversion based on projected holding period(s).

9)Be able to handle different investor valuations based on current IRS tax criteria (i.e. have inputs that can be varied for the investor's marginal tax rate (MTR), long-term capital gains rate, depreciation recapture rate etc.

10)Track what the "Original Basis" is and the property plus any "Capital Improvements" for the basis at reversion.

11)For implementation of the model and ease of use, it is highly recommended you use Excel's "comment" feature to explain what is intended to be inputted into required cells and how it is used in the model. For each of the various models created, you are also required to include the "Overall" use instructions and notes on TAB A at the bottom of the screen or via "hyperlink" to an included WORD document. This is required so a subsequent analyst/appraiser building on your model does not have to start from scratch and can "get up to speed"

Introduction

Real Estate Appraisal/Investments Retail Property Analyzer

The goal of this particular case-based project is to build a model that will analyze a South Arlington, Texas strip shopping center via Excel. Over the next several weeks, the model will be able to answer all of the quantitative questions in the case and provide you a working Excel model to analyze future retail "Strip Centers" and form a basis for more complex models and property types.

Group Work

One goal of this case is to become more proficient in excel and a great way to do this is by working together. This project can be done in groups of up to 3 people for undergraduates and 2 for graduates (should not be more but can be less. See Group Formation Stipulations for additional details about grading procedures). If you choose to work in a group, each group member will receive the same grade. You may discuss the project with other groups, but each group should build its own Excel model.

Implementation

It is suggested that to further your understanding of the model that you start the project assignment by going through the math of each of the questions that will need to be answered. This will allow you to start implementing the use of an Excel model and provide you with a check of your answer outputs via the model. It is suggested that you provide this mathematical information on an Exhibit so any carry-through errors can be accounted for and not result in "no points awarded" for individual questions based on errors.

The model you create should be flexible enough to vary many of the parameters. In other words, I should be able to alter the variables to analyze/apprise an investment using different assumptions. For example, exterior and general maintenance is expected to increase by $120 per year. What happens to all of the case answers if this actually turns out to be $170 per year? This can be easily answered if the model is created to handle this type of flexibility.

You may be asking, "What is the easiest way to implement this flexibility?" I recommend creating an input section on Tab A of the Excel Workbook of the important variables listed in the case. Refer to these inputs whenever that variable is used in a calculation.

Student Implementation Schedule

See the course website for Due Dates. In general, the grading will come in one part with two check points to ensure student progression.

Questions to be answered for Part I:

A.) Questions 1-11 (See the course website) I will offer critiques at every class meeting from now until Final. B.) Questions 12-25 (See the course website) but Questions 1-25 will be due in whole written form by final Exam

Your model should be able to answer the case questions listed by a particular date which is shown on the course website. I will check the flexibility of your model by changing several underlying assumptions from the case.

South Arlington STRIP SHOPPING CENTER I Appraisal/NVESTMENT (Unanchored)

Physical Characteristics:Strip shopping center with brick/steel construction.

5 stores, 2,000 square feet of gross area each. (10,000 sq.ft. Total)

Leaseable area to gross building area, 95.5%. (Gross leasable Area GLA for a particular tenant captures the amount of space occupied and controlled by the tenant and is therefore similar to an office tenant's usable area for Office properties. (See page 591 L.A. v5)

Effective age is 10 years.

5,000 square feet of on-site parking.

Legal Aspects:Leased fee. Overall the tenants appear to have "B" credit ratings.

Market Analysis:Data on four comparable properties are in Table 1. The comparables appear to be rented at market rents. Demand for this type of property is currently weak and rents are projected to increase 0% annually (compounded) with vacancy and bad debt losses expected to be 25%. Prices of shopping centers in South Arlington have been flat over the past year.

Operating Expenses:Real estate property taxes are projected to be $7,000 annually (based on projected option sales price of $510,000) and taxes are projected to increase $800 every 3 years. Last increase was 1 year ago. Exterior and general maintenance is currently

$100 a month. Maintenance is projected to increase annually by $10 per month. Roof should be replaced during Year 2 at a projected cost of $12,500.

Management fees are 5% of rents collected. All other expenses are paid by tenants.

Current Leases:Store A: Lease expires in two years.

Rent is $825 per month. (Note: Calculate the tenant renewal based on leasable SF. See page 591 L.A. v5)

Tenant will re-lease at market rent at expiration.0

Store B: Lease expires in 5 years and will be renewed at market rents at the end of term. (Note: Calculate the tenant renewal based on leasable SF. See page 591

L.A. v5)

Rent is $1,223 per month.

Rent will increase at a rate of 3% annually or the change in the CPI, whichever is greater.

CPI is expected to increase 3% annually over the next 5 years. Stores C, D, E Space recently leased for 10 years.

Annual rent is $8 per leaseable sq ft. with escalator (increases) clauses of 4% per year.

Reversion Assumptions:Projected holding period is five years.

Sale price is estimated by capitalizing the Year 6 NOI at a "going-out" Cap rate of 12.84% (50 basis points higher than market extracted going-in Cap rate).

Sale expenses estimated at 4% of sale price.

Construction Costs:Data from a cost estimation service indicates that the base construction costs for this type of property is $44.00 per gross square foot. A location adjustment factor of 1.05 is required to account for the fact that the property is in a higher cost of construction area. The property has an effective age of 10 years. Physical depreciation is at a rate of 2% for each year of age. There appears to be no economic or functional depreciation. Other site improvements (parking lot, etc) are estimated to have a depreciated cost of $25,000. Current land transactions indicate a land value of $75,000. Entrepreneurial (Developer) profit is $50,000.

Depreciation:Straight line method; 39 year useful life; According to Tarrant County Appraisal District (TAD), improvements to land value represent a 85%/15% split for this type of property in this area. (Note: See L-A v5 page 524 for depreciable basis of property and page 526-7 for application of mid-month convention or CCIM module 1)

Mortgage Financing:65% loan-to-value ratio (LTV).

8.5% interest rate; 20 year maturity (monthly payments)

$10,000 in financing costs paid at the "table" on closing date (simply means this is part of the costs being paid at closing on the scheduled date and implies a separate depreciation schedule for amortization of financing costs).

Prepayment penalty of 3% of outstanding balance

Equity Required Rates

of Return14% on 100% equity financing.

18% dividend rate of return

22% before-tax required rate of return 12% after-tax required rate of return

Investor's Marginal Tax:

RatesOrdinary income tax rate is 28% (MTR) Capital gain tax rate is 15% Depreciation recapture tax rate is 25%

Asking Price:$510,000 (TREC type Option Contract in "Due Diligence Period" for 7 days. Closing to occur on Dec 31st 2010 (Note: Implementation of the mid-month convention is inferred. For depreciation tax shields during the 5 year holding period (IRS reporting anyways) you should apply the mid-month convention based on Federal Reporting Tax years (See L-A v5 page 526-7 or CCIM Module 1). This means Dec. 31th will be different in terms of depreciation as well as the month in which it sells 5 years from now.

The investor expects $7,500 in acquisition costs paid at the "table" on closing date (simply means this is part of the costs being paid at closing on the scheduled date. This in turn will increase your depreciable basis in the property. (See L-A p. 524, 531 footnotes or CCIM Module 1). In summary, any costs associated with the acquisition like points paid to originators, legal fees, etc. are depreciable over the life of the loan. If sold early then the total remaining deduction associated with the cost of acquisition are taken in that final year so they are not lost).

Assume investor is seeking to make "positive NPV" investments and not just seeking tax losses to write off against other active or passive income.

Table 1. Information on Sales of Comparable Shopping Centers

Comparable Property

1

2

3

4

Sale Price

$504,000

$488,000

$608,000

$401,600

Months since sale

2.0

1.0

1.0

3.0

Gross income at sale

$75,000

$72,000

$88,000

$61,050

Vacancy

0

0

0

0

Operating Expense Ratio at sale (OER)

0.17

0.17

0.15

0.18

Property rights

leases fee

leases fee

leases fee

leases fee

Construction

Brick/steel

Brick/steel

Brick/steel

Brick/steel

Effective age (years)

10

9

8

9

Number of stores

4

5

5

3

Tenant credit rating

A

B

B

C

Onsite parking

4,000 sq.ft

5,000 sq.ft

4,500 sq.ft

6,000 sq.ft

Leasable Area

9,500 sq.ft

9,000 sq.ft

11,000 sq.ft

7,400 sq.ft

Gross Area

10,000

sq.ft

9,375 sq.ft

11,500 sq.ft

7,800 sq.ft

Question 1: For each of the comparable sales, compute the following:

a.Rent per leaseable square foot

b.Rent per gross square foot

c.Sale price per leaseable square foot

d.Sale price per gross square foot

Question 2: Forecast the NOI and NSP at Contract Rental Rates.

Question 3: Forecast the NOI and NSP at Market Rental Rates.

Question 4: Estimate the market value assuming 100% equity financing. The investor's required rate of return is 14.0%.

Question 5: Estimate the leased-fee value assuming 100% equity financing. The investor's required rate of return is 14%.

Question 6: Calculate and show the mortgage amortization schedule for years 1 through 5, at the $510,000 asking price. (Note: You should be able to cut and paste this information from the Excel model even though the "full blown" amortization table is over a longer term. I am just asking for your "holding" period answers based on similar holding periods for this type of investment.

Question 7: Calculate the yearly depreciation deduction, at the $510,000 asking price assuming this transaction is completed and held for the stipulated amount of time. Yes, this is below NOI information but understanding this will give you the appraiser insight to how the market is determining "Market Value" (Note: Use the mid- month convention)

Question 8: Set up the depreciation schedule for years 1 through 5. (Note: Use the mid-month convention)

Question 9:

a.) Forecast the BTCF's for years 1 through 5 using the NOI under the existing leases. b.) Forecast the ATCF's for years 1 through 5 using the NOI under the existing leases.

Question 10:

a.) Forecast the Market Value in year 5, using the forecast of NSP under the existing leases. b.) Forecast the ATER in year 5, using the forecast of NSP under the existing leases.

Question 11: At the asking price of $510,000, compute the following expected rates of return:

a.Overall Rate

b.Equity dividend rate

Question 12: Compute the NPV and IRR on equity using the forecasted before-tax cash flows.

The investor's required rate of return is 22%. (Note: for NPV and IRR, you can "role up" 12 months of information to annualize it. Or you can do it monthly x 12 like a BAIIPlus calculator would. It is still a 5 year hold either way even though the Depreciation Deduction follows calendar years and Holding period return is for a stated amount of time (years)

Question 13: Compute the NPV and IRR on equity using the forecasted after-tax cash flows.

Question 14: Estimate the leased-fee value using the gross income multiplier approach.

Question 15: Estimate the market value using the gross income multiplier approach. Question 16: Estimate the leased-fee value using a market extracted "going-in cap rate. Question 17: Estimate the market value using a market extracted "going-in" cap rate.

Question 18: The Investors can finance the project with a loan-to-value ratio of 75% with a mortgage interest rate of 8.5% with (monthly) payments over an amortization period of 20 years. The required equity dividend rate is 18%. Estimate the:

a.)Market value

b.)leased-fee value using the weighted average cost of capital approach (WACC).

Question 19: Data from a cost estimation service indicates that the base construction costs for this type of property is $44.00 per gross square foot. A location adjustment factor of 1.05 is required to account for the fact that the property is in a higher cost of construction area. The property has an effective age of 10 years. Physical depreciation is at a rate of 2% for each year of age. There appears to be no economic or functional depreciation. Other site improvements (parking lot, etc) are estimated to have a depreciated cost of $25,000. Current land transactions indicate a land value of $75,000. Entrepreneurial (Developer) profit is $50,000. What is the indicated value based on the cost approach?

Question 20: Estimate the market value of the subject property using the sales comparison approach, using price per square leaseable square foot as the unit of comparison. Property values have increased 6% over the last year (.5% per month). Each year of property age reduces value by 2%.

Question 21: Estimate the market value of the shopping center using mortgage-equity analysis. The equity required rate of return is 22%. The project can be financed as follows: L/V ratio = 75%; i = 8.5%; monthly payments over 20 years

Question 22: Estimate the leased fee value using mortgage-equity analysis. The equity required rate of return is 22%. The project can be financed as follows: L/V ratio = 75%; i = 8.5%; monthly payments over 20 years.

Question 23: What is the leased-fee value using mortgage-equity analysis adjusted for the acquisition costs, financing costs, and mortgage prepayment penalty?

Question 24: Summarize and critique the various estimates of value used in this project/case study.

Question 25: Now that you have a working model, change the information to approximate the current real estate investment climate as of November 2018. Because of decreased underwriting standards in commercial real estate and the "thawing" of the CMBs conduits, the "skin in the deal" is required to be 25% instead of 30%. Another words, lenders will only make loans for a 70% LTV. In addition, cap rates have decreased by 150 basis points for the type of shopping centers you are valuing based in extremely recent arm's length sales transactions.

1)Holding all else constant, how does this change your appraisal/valuation of the shopping center and what is the value to you now that you would write on the "offer price" line in the sales contract (if any)?

2)What if, in addition to the above information, your market study now forecasts that rents will increase at half of that in your original model which was 0% and is now .2%. Furthermore, you now forecast a35% vacancy loss instead of 25% as in the original model. What is the market value now?