You have decided to develop a new 225,000 square foot shopping center in Edwardsville. You plan to hold the property for 10 years and then sell it at the end of year 10 before moving to a tropical island. The property will house 3 anchor tenants and 10 in-line tenants as shown below.

	SF	Base Rent per SF (Annual)	Overage Rent	Breakpoint	Sales
Fresh Foods	75,000	$12.00	3%	$1,500,000	$4,300,000
Great Electronics	55,000	$14.00	3%	$1,000,000	$4,100,000
Fancy Shoes	45,000	$15.50	3%	$1,000,000	$3,700,000
10 In-Line Tenants	50,000	$18.00	N/A	N/A	$3,300,000
Total	225,000

In addition to the base rent, the anchor tenants will be paying overage rent that will be based on 3% of their sales above the breakpoint. In-line tenants will not be paying any overage rent. Base rent will increase 3% each year while sales of the anchor tenants are expected to increase 2% per year. Breakpoints will remain fixed each year at the levels shown above. Market analysis suggests that vacancy and collection losses for this type of property in this market are 10%. You anticipate operating expenses to be 40% of the propertys effective gross income (EGI).

You have identified three comparables (shown below) that have recently sold. You believe that each of the three comparable warrants the same consideration for determining the current cap rate.

Comparables	Price	NOI
Property A	$28,750,000	$2,300,000
Property B	$26,000,000	$1,950,000
Property C	$30,000,000	$2,100,000

You anticipate that your property will sell at the end of year 10 at a price that will be based on the terminal cap rate in year 10 and NOI in year 11. You also anticipate that the terminal cap rate will be equal to the cap rate of todays 10-year old properties that are similar to the subject property. You have been able to gather data on three recently sold 10-year old properties (shown below) that are similar to your property in almost every aspect other than the age. You also believe that each one of these three older properties deserves the same consideration for determining the terminal cap rate.

10 Year-old Comps.	Price	NOI
Property D	$18,750,000	$1,500,000
Property E	$17,500,000	$1,750,000
Property F	$20,000,000	$1,800,000

First Venture Bank has committed to proving you with a loan to finance your project. The loan amount will be based on an interest rate of 5%, 20-year amortization with monthly payments, and payments that will provide a DCR of 1.3x in year 1.

Additional information:

• You purchased the land for $1,000,000.
• Construction cost is estimated at $22,000,000.
• An appropriate rate of return for the property is 10%.
• This property will be depreciated over 39 years (ignore mid-month convention in Year 1).
• Applicable tax rates:
  • Income tax rate: 35%
  • Tax rate on the price appreciation: 15% (hint: treat the construction cost + land price as the original price of the property since this is your cost basis in the property)
  • Tax rate on the depreciation recapture: 25%

Based on the information contained herein, prepare an Excel spreadsheet to address the below items following a similar format used in class. Feel free to copy and paste the numerical charts from here into your spreadsheet to save some time.

1. Prepare a pro forma statement for the 10-year period.
2. Estimate the value of the property using a direct capitalization method.
3. Estimate the value of the property using a discounted cash flow method.
4. Calculate the IRR on the property (unleveraged IRR).
5. Calculate the IRR on the equity portion (BTIRR).
6. Calculate the after-tax IRR (ATIRR).