I only need part 2 answered. Please put answer in excel. Thanks

Part I We are considering an acquisition of an existing medical office building (MOB) to our portfolio. There are 4 tenants. Tenants 1 and 2 occupy 15,000 and 8,000 square feet respective, at $18.00 per square foot. Tenant 3 has 10,000 at $19.00 and tenant 4 has 4,000 at $21.00. All the leases are presently expected to continue through the 4 year holding period and have CPI increases estimated at 2% per year. The asking price for the property is $3,000,000 of which 75% of the value would be allocated to the building; and vacancy and collection losses are projected at 10% of rents. First year operating expenses include $120,000 for (i.e. taxes, insurance, utilities, maintenance, etc.) and management estimated at 5% of EGI. Our tax rate is 40% and all loses are recognized in the year they are incurred. Capital gains and depreciation recapture are both taxed at 15%. A 70% loan can be obtained at 10% for 25 years. Both the property value and operating expenses are expected to grow 4% per year and our projected holding period is 4 years. Selling expenses are projected at 4% of the gross sales price. The reinvestment rate for cash flows is 7% and the discount rate is 12%. a.What is the before tax IRR and MIRR to the project under both unlevered and for the equity with the proposed mortgage arrangement? Comment on the differences for the yield estimates you just calculated considering both the unlevered and levered returns and the difference between IRR and MIRR.b. What is the after tax IRR, and the effective tax rate under this scenario?c.Calculate the terminal cap rate based on the information above.d. What is the NPV to the equity of the project with the current mortgage arrangement? e. Assume the 12% discount rate holds. The bank has provided a number of mortgage alternatives. Examine the equity BTIRR under the following sample of LTVs and interest charges. @ 65% 7%@ 70%10%@75%12%@80%15%Given the current funding options which option would you select given the information available?

Part II The market where the MOB is located is unstable with extensive tenant churning and lease buyouts occurring regularly. The truth is our 10% vacancy and collection loss estimate is fairly optimistic due to the perceived stability of current leases. A more realistic estimate (most likely) is 15% and a worst case (pessimistic) estimate for V&C would be 20%. Lets assign the probability of occurrence as follows: 40% for most likely, 35% for optimistic and 25% for worst case. Compute the following

a. BTIRR and ATIRR for each scenario (you already have the optimistic estimate)

b. The expected IRR given the 3 scenarios

c.The variance and standard deviation of IRRs

d. Is this project expected to throw off returns in excess of 12%?

e. What is the expected NPV considering the 3 scenarios?