You have been presented with an investment opportunity and asked to conduct an investment analysis using Excel. In creating your Excel model, do not “hard code” the Excel calculations. Instead, the s

You have been presented with an investment opportunity and asked to conduct an investment analysis using Excel. In creating your Excel model, do not “hard code” the Excel calculations.  Instead, the solution must change if one of the input assumptions changes. 

Review of the Investment Opportunity

Basic Assumptions.  Mr. Stan Smith has asked you to see if he is making a reasonable deal on an industrial property that has been offered him for $1,700,000.  The subject property is a 25,000 square foot industrial building, which is currently 70% occupied at $8.00 per square foot per year.  All leases are absolute net.

Market rent is expected to increase 3% per year after year one.  The vacancy rate is expected to be 20% in year 2, and 10% thereafter.  Mr. Smith is expected to pay operating expenses equal to 12% of effective gross income each year.  Annual capital expenditures are expected to be 3% of EGI.  

The selling price at the end of the expected 10-year holding period is anticipated to be year 11 NOI capitalized at a terminal (going-out) cap rate of 9.00%.  Selling costs at that time are expected to be 4% of the selling price.  Mr. Smith feels that the appropriate rate for him to discount unlevered (before debt) cash flows is 12%.

Financing Assumptions.  Mortgage financing has been offered to Mr. Smith at $1,200,000 for 10 years at 6.5% (annual) with monthly payments. Mortgage payments will be based on 30-year amortization.  Up-front financing costs will total 2% of the loan amount.  Assume (for simplicity) the appropriate rate to discount levered (after debt) cash flows remains at 8%.

Income Tax Assumptions: Assume real property depreciation is based on 80% of the $1,700,000 purchase price and that land accounts for the remaining 20% (i.e., assume there is no personal property associated with the acquisition).  Assume the depreciable basis is “straight-lined” over the appropriate cost recovery period for industrial property.  Assume Mr. Smith’s ordinary income tax rate is 30%, his capital gain tax rate is 20%, and his depreciation recapture tax rate is 25%.  Finally, assume that none of the taxable income produced by the property is subject to passive activity loss restrictions.  Thus, Federal Income Tax is equal to Taxable Income times the ordinary tax rate. Assume initially a 10-year holding period.

Requirements:

1.              Assume a 10-year holding period. Project before tax cash flows from operations and sale. 30 points

2.              Assume a 10-year holding period. Project after tax cashflows from operations and sale. 25 points

3.              Assume a 13% required return on levered  after-tax cashflows. What is the (10-year) levered, after-tax, internal rate of return and net present value?  5 points

4.              Create a spinner box for holding period and have holding period change from 1 to 16 years. Your model should be calculating IRR and NPV for each holding period. Sale price should be calculated only in the appropriate year, for other years it should be empty. For longer than 10 holding periods assume that the debt has been refinanced in year 10 and payments are exactly the same as the payments on the original loan (no change in annual debt service compared to shorter holding periods). 20 points

5.              Create a spinner box for the terminal going out cap-rate have have it take values from 8 to 10 percent with increments of 0.5% (8, 8.5, 9, 9.5 and 10%). 10 points

6.              Create a list box for loan type and have two options: interest-only and amortizing 30 year. 10 points

7.              Create a two-way table where you change the holding period from 2 to 16 years, with increments of 2 years (2, 4, 6, 8, 10, 12, 14, 16 years) and the terminal going out cap-rate from 8 to 10 percent with increments of 0.5% (8, 8.5, 9, 9.5 and 10%) . 10 points

8.              Find the expected optimal holding period using Solver subject to constraint that holding period is between 2 and 16 years. (Terminal cap rate is fixed at 9 percent). 10 points