Please see the question attach and use the excel template to solve it..

Rutgers University Rutgers School of Business Case Assignment: Industrial Property Real Estate Finance You work for a small private pension fund looking for more value in it's real estate acquisitions. With current capitalization rates for class A o ce and multi-family properties near historic lows, and corresponding prices near all-time highs, the fund manager has asked you to look into dierent property types and price points, particularly those involving investments requiring less than $5M in equity in markets and property types where there might be less competition. After talking with several brokers, you have identi...ed what looks to be an attractive opportunity to acquire a small industrial property in Houston, Texas. The property at 13150 Space Center Boulevard oers 52,400 square feet of space that is currently leased by The Boeing Company. The broker representing the seller has suggested than an oer of $6.85M would be accepted. The investment committee requires a 10-year pro-forma cash ow statement of cash ows before debt service and before tax cash ows. In addition to the cash ow projections, they require a graph illustrating the relationship between the before tax IRR and the terminal cap rate for cap rates ranging from 7% to 11% in 50 basis point intervals. Basic information: You will take possession of the property on August 1st , 2017 and any fees and equity contribution that are required to acquire the building into escrow on July 1st , 2017. For simplicity, assume that any rent received and expenses incurred within the lease year are paid/due in full on August 1st of each year. The lease is a net lease, meaning that the tenant is responsible for operating expenses so long as they occupy the property. If the property is vacant for any period of time, however, the property owner is responsible. Furthermore, the owner is responsible for administrative and capital expenditures regardless of occupancy. The expense information for the property is provided on the attached data sheet. The acquisition will be ...nanced with a 10-year non-recoure loan at 65% of the purchase price with a 6.125% mortgage rate. Payments will be made annually and based on a 25-year amortization period. Boeing has options to renew their lease in August, 2021 and August, 2026. The renewal options give Boeing the right to lease the space at $12.50/sf beginning 8/2021 and $14.00/sf beginning 8/2026. If Boeing chooses to exercise their option and renews their lease, you will be obligated to pay a leasing commission equal to 10% of their ...rst year's rent and provide tenant improvements equal to $5/sf. You estimate that Boeing will exercise each option with probability .75. If Boeing vacates the property, then you expect that it will take six months to ...nd a new tenant. A new tenant is assumed to sign a 5-year lease agreement at the current market rent and terms similar to the existing lease and will receive $15/sf for tenant improvements in the ...rst year. The leasing commission for a new tenant is 20% of their ...rst year's rent. [Note that the lease expiration takes place in the 5th and 10th year of your projections.] Assignment: 1 Construct projections of NOI, cash ows before debt service and before-tax cash ows for the given holding period. Compute the internal rate of return that the equity holder expects to receive on both an unlevered and levered basis Construct a graph illustrating the relationship between the before tax return and the terminal cap for cap rates ranging from 7% to 11% in 50 basis point intervals. The assignment is due by the beginning of class on Tuesday, November 15th. Please format your output so that it ...ts in landscape mode onto three pages,1) Data, 2) NOI, 3) BTCF, and submit a printed copy of your spreadsheets - I will not accept assigments sent via email. If you cannot turn in a hard copy in class, it can be turned into the front desk at the Center for Real Estate. Finally, this is an individual assignment. If it is determined that your output was copied from another source, you will receive a zero. 2 Boeing Company: Houston Market rent Market rent growth Inflation Property information: Purchase price Rentable square footage Operating expenses: Utilities (1.55/sf, 35% fixed) RETax Ins General & administrative (% of EGI) Lease information: Initial lease term Lease expiration Rent/sf, 8/17 Base escalation/sf Net Market leasing: Lease term Rent/sf - renew, 8/21 Rent/sf - renew, 8/26 Rent/sf - new Net Renewal probability Months vacant 12.00 2.00% 2.50% Capital expenditures: Reserves/sf, 100% fixed Tenant improvements/sf - new Tenant improvements/sf - renew Leasing commision, % of first year's rent - new Leasing commision, % of first year's rent - renew 6,850,000 52,400 81,220 35% 51,250 35,000 1% 10 years 7/31/2020 11.10 0.25 5 years 12.50 14.00 Market 0.75 6 Inflation Inflation Inflation 0.15 15.00 5.00 20% 10% Mortgage LTV at origination Rate Term Amortization Origination fee 65% 6.125% 10 25 2.50% Disposition: Terminal cap rate Cost of sale 9.00% 3.00% Inflation Inflation Inflation Jul-17 Operations: Rent/sf Potential gross income Vacancy adjustment Effective gross income Owner Expenses: G &A Utilities RETax Ins Net operating income (NOI) Capital reserves Tenant improvements Leasing commissions Net income after reserves Sale: Reversion value Cost of sale Net gain from sale Cash flow before debt service IRR Initial cap rate ====================== Market rent/sf Expenses (100% occupancy): Utilities/sf Utilities RETax Ins TI/sf (new tenant) TI/sf (existing tenant) 1 Aug-17 2 Aug-18 3 Aug-19 4 Aug-20 5 Aug-21 6 Aug-22 7 Aug-23 8 Aug-24 9 Aug-25 10 Aug-26 11 Aug-27 Jul-17 1 Aug-17 2 Aug-18 3 Aug-19 4 Aug-20 5 Aug-21 6 Aug-22 7 Aug-23 8 Aug-24 9 Aug-25 10 Aug-26 Net income after reserves Debt service BTCF from operations Reversion value Cost of sale Debt repayment BTCF from sale Origination fee Equity contribution BTCF IRR Mortgage: Payment Interest Principal Balance Cap rate 7.00% 7.50% 8.00% 8.50% 9.00% 9.50% 10.00% 10.50% 11.00% IRR IRR 1200.00% 1000.00% 800.00% IRR 600.00% 400.00% 200.00% 0.00% 7.00% 7.50% 8.00% 8.50% 9.00% 9.50% 10.00% 10.50% 11.00%