Please do it on excel

Working Example Part I We are considering an acquisition of an existing medical office building (MOB) to our portfolio. There are 4 tenants. Tenants I and 2 occupy 15,000 and 8,000 square feet respective, at $19.00 per square foot. Tenant 3 has 10.000 at $20.00 and tenant 4 has 4,000 at $22.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 (i.e. taxes, insurance, utilities, maintenance, etc.) and management estimated at 5% of EGI. Our tax rate is 36% and all loses are recognized in the year they are incurred. Capital gains and depreciation recapture are both taxed at 15%. A 75% loan can be obtained at 9.0% 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 reinvestm a. What is the before tax IRR and MIRR to the investor under the proposed mortgage arrangement? Comment on the differences for the investor yield estimates you just calculated. 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? e. Assume the 12\% discount rate holds. Examine the equity BTIRR under the following sample of LTVs and interest charees. Does this project have positive or negative leverage at the given LTV levels? Part II The market where the MOB is located is unstable with extensive tenant chuming 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%. Let's 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) 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 (i.e. taxes, insurance, utilities, maintenance, etc.) and management estimated at 5% of EGI. Our tax rate is 36% and all loses are recognized in the year they are incurred. Capital gains and depreciation recapture are both taxed at 15%. A 75% loan can be obtained at 9.0% 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 investor under the proposed mortgage arrangement? Comment on the differences for the investor yield estimates you just calculated. 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? e. Assume the 12\% discount rate holds. Examine the equity BTIRR under the following sample of LTVs and interest charges. Does this project have positive or negative leverage at the given LTV levels? 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%. Let's 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 and the coefficient of variation for both before and after tax d. Are the expected returns in excess of 12% ? e. Extra Credit: Now, assume that the increase in value for year five is expected to be 6%. What is the marginal return for keeping the property one additional year? What is your advice? Working Example Part I We are considering an acquisition of an existing medical office building (MOB) to our portfolio. There are 4 tenants. Tenants I and 2 occupy 15,000 and 8,000 square feet respective, at $19.00 per square foot. Tenant 3 has 10.000 at $20.00 and tenant 4 has 4,000 at $22.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 (i.e. taxes, insurance, utilities, maintenance, etc.) and management estimated at 5% of EGI. Our tax rate is 36% and all loses are recognized in the year they are incurred. Capital gains and depreciation recapture are both taxed at 15%. A 75% loan can be obtained at 9.0% 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 reinvestm a. What is the before tax IRR and MIRR to the investor under the proposed mortgage arrangement? Comment on the differences for the investor yield estimates you just calculated. 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? e. Assume the 12\% discount rate holds. Examine the equity BTIRR under the following sample of LTVs and interest charees. Does this project have positive or negative leverage at the given LTV levels? Part II The market where the MOB is located is unstable with extensive tenant chuming 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%. Let's 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) 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 (i.e. taxes, insurance, utilities, maintenance, etc.) and management estimated at 5% of EGI. Our tax rate is 36% and all loses are recognized in the year they are incurred. Capital gains and depreciation recapture are both taxed at 15%. A 75% loan can be obtained at 9.0% 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 investor under the proposed mortgage arrangement? Comment on the differences for the investor yield estimates you just calculated. 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? e. Assume the 12\% discount rate holds. Examine the equity BTIRR under the following sample of LTVs and interest charges. Does this project have positive or negative leverage at the given LTV levels? 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%. Let's 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 and the coefficient of variation for both before and after tax d. Are the expected returns in excess of 12% ? e. Extra Credit: Now, assume that the increase in value for year five is expected to be 6%. What is the marginal return for keeping the property one additional year? What is your advice