Please explain in excel as it will easy for me to understand

Thank you.

Part 1 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? 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. e. @ 65% 7% @ 70% 10% @75% 12% @80% 15% Given the current funding options which option would you select given the information available? of rents of EGI years 4 ASSUMPTIONS: 5 6 Asking Price 7 Rent year 1 8 Growth-Rent 9 Vacancy & Coll. Loss 10 Management 11 Operating Expenses 12 Loan-to-Value 13 Loan Interest 14 Loan term 15 Growth in Value/Op Expenses 16 Holding Period 17 Selling costs 18 Equity discount rate 19 Reinvestment rate 20 Tax rate 21 Cap Gains/Dep Recapture Tax 22 23 Equity 24 Loan 25 Montly Mortgage Payment 26 Annual Debt Service years of sale price year end 0 1 2 3 3 4 5 27 Mortgage Balance 28 29 Year 30 PGI 31 Vacancy & Collection Loss 32 EGI 33 Operating Expenses 34 Mangement 35 NOI 36 Debt Service 37 BTCF 38 39 Cash flow from sale in year 40 Sales Price 41 Selling costs 42 Mortgage Balance 43 Before-tax cash flow from sale 44 45 BTCF Total 46 47 (a) BTIRR/MIRR ON EQUITY 4 1 2 3 4 49 BTIRR on Equity 50 BTMIRR on Equity 51 52 53 54 (b) AFTER TAX IRR AND EFFECTIVE TAX RATE 55 56 Taxable Income 57 58 0 59 NOI 60 Interest 61 Depreciation 62 Taxable income 63 Tax Cost (Savings) 64 ATCF from Operations 65 66 Sales Price 67 Sales costs 68 Mortgage Balance 69 Before-tax cash flow from sale 70 71 Sales Price Less Selling Costs 72 73 Original Basis 74 Accumulated Depreciation 74 Accumulated Depreciation 75 Adjusted Basis 76 77 Capital Gain/Price Appreciation 78 Depreciation Recapture 79 80 Cap Gain Tax 81 Depreciation Recapture Tax 82 Tax on Sales Proceeds 83 84 After-tax Cash Flow From Sale 85 86 ATCF Total 87 ATIRR 88 Effective Tax Rate 89 90 91 92 (c) Terminal Cap Rate 93 94 95 96 97 (d) NPV BTIRR 99 Equity NPV 100 101 102 (e) Leverage Impact on BTIRR 103 104 @ 65% 105 @ 70% 106 @75% 107 @80% 108 109 110 111 i 7% 10% 12% 15% 112 Part 1 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? 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. e. @ 65% 7% @ 70% 10% @75% 12% @80% 15% Given the current funding options which option would you select given the information available? of rents of EGI years 4 ASSUMPTIONS: 5 6 Asking Price 7 Rent year 1 8 Growth-Rent 9 Vacancy & Coll. Loss 10 Management 11 Operating Expenses 12 Loan-to-Value 13 Loan Interest 14 Loan term 15 Growth in Value/Op Expenses 16 Holding Period 17 Selling costs 18 Equity discount rate 19 Reinvestment rate 20 Tax rate 21 Cap Gains/Dep Recapture Tax 22 23 Equity 24 Loan 25 Montly Mortgage Payment 26 Annual Debt Service years of sale price year end 0 1 2 3 3 4 5 27 Mortgage Balance 28 29 Year 30 PGI 31 Vacancy & Collection Loss 32 EGI 33 Operating Expenses 34 Mangement 35 NOI 36 Debt Service 37 BTCF 38 39 Cash flow from sale in year 40 Sales Price 41 Selling costs 42 Mortgage Balance 43 Before-tax cash flow from sale 44 45 BTCF Total 46 47 (a) BTIRR/MIRR ON EQUITY 4 1 2 3 4 49 BTIRR on Equity 50 BTMIRR on Equity 51 52 53 54 (b) AFTER TAX IRR AND EFFECTIVE TAX RATE 55 56 Taxable Income 57 58 0 59 NOI 60 Interest 61 Depreciation 62 Taxable income 63 Tax Cost (Savings) 64 ATCF from Operations 65 66 Sales Price 67 Sales costs 68 Mortgage Balance 69 Before-tax cash flow from sale 70 71 Sales Price Less Selling Costs 72 73 Original Basis 74 Accumulated Depreciation 74 Accumulated Depreciation 75 Adjusted Basis 76 77 Capital Gain/Price Appreciation 78 Depreciation Recapture 79 80 Cap Gain Tax 81 Depreciation Recapture Tax 82 Tax on Sales Proceeds 83 84 After-tax Cash Flow From Sale 85 86 ATCF Total 87 ATIRR 88 Effective Tax Rate 89 90 91 92 (c) Terminal Cap Rate 93 94 95 96 97 (d) NPV BTIRR 99 Equity NPV 100 101 102 (e) Leverage Impact on BTIRR 103 104 @ 65% 105 @ 70% 106 @75% 107 @80% 108 109 110 111 i 7% 10% 12% 15% 112