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 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?
SSUMPTIONS of rents of EGI Asking Price Rent year 1 Growth-Rent Jacancy & Coll. Loss Management Operating Expenses Loan-to-Value Loan Interest Loan term Growth in Value/Op Expenses Holding Period Selling costs Equity discount rate Reinvestment rate Tax rate Cap Gains/Dep Recapture Tax years years of sale price Equity Loan Montly Mortgage Payment Annual Debt Service Mortgage Balance year end 0 1 2 3 4 5 Year PGI Vacancy & Collection Loss EGI Operating Expenses Mangement NOI Debt Service BTCF Marginal Return Cash flow from sale in year par ned Dart II 4 Marginal Return Cash flow from sale in year Sales Price Selling costs Mortgage Balance Before-tax cash flow from sale BTCF Total (a) BTIRR/MIRR ON EQUITY BTIRR on Equity BTMIRR on Equity (b) AFTER TAX IRR AND EFFECTIVE TAX RATE Taxable income 0 1 2 4 NOI Interest Depreciation Taxable income Tax Cost (Savings) ATCF from Operations Sales Price Sales costs Mortgage Balance Before-tax cash flow from sale Sales Price Less Selling Costs Original Basis Accumulated Depreciation Original Basis Accumulated Depreciation Adjusted Basis Capital Gain/Price Appreciation Depreciation Recapture Cap Gain Tax Depreciation Recapture Tax Tax on Sales Proceeds After-tax Cash Flow From Sale ATCF Total ATIRR NOTE this is where the TABLE command comes in handy Part II Sensitivity Test ad Calculating Variance of Returns Office Building (Return Probability) Deviation (R- Expected R) Probability (P) Squared Deviation PX (R-Expected R]sa BTIRR Scenario Pessimistic (20%) Most Likely (15%) Optimistic (10%) 1 2 3 Expected Return Variance Standard Deviation Coefficient of Variation 5 6 7 Calculating Variance of Returns STUTTUUU DEVIUTTUM Coefficient of Variation Calculating Variance of Returns Office Building ATIRR Probability (P) (Return x Probability) Deviation (R- Expected R) Squared Deviation Px (R-Expected Risa Scenario Pessimistic (20%) Most Likely (15%) Optimistic (10%) Expected Return Variance Standard Deviation Coefficient of Variation Part II Marginal Return Comparison e If sold year 4 If sold year 5 Sale price Selling Costs Mortgage balance Capital gain tax Cash flow Marginal return = (Cash flow if sold next year + NOI over next year - Cash flowfrom sale if sold today) / Cash flow if sold today Marginal return =( ) SSUMPTIONS of rents of EGI Asking Price Rent year 1 Growth-Rent Jacancy & Coll. Loss Management Operating Expenses Loan-to-Value Loan Interest Loan term Growth in Value/Op Expenses Holding Period Selling costs Equity discount rate Reinvestment rate Tax rate Cap Gains/Dep Recapture Tax years years of sale price Equity Loan Montly Mortgage Payment Annual Debt Service Mortgage Balance year end 0 1 2 3 4 5 Year PGI Vacancy & Collection Loss EGI Operating Expenses Mangement NOI Debt Service BTCF Marginal Return Cash flow from sale in year par ned Dart II 4 Marginal Return Cash flow from sale in year Sales Price Selling costs Mortgage Balance Before-tax cash flow from sale BTCF Total (a) BTIRR/MIRR ON EQUITY BTIRR on Equity BTMIRR on Equity (b) AFTER TAX IRR AND EFFECTIVE TAX RATE Taxable income 0 1 2 4 NOI Interest Depreciation Taxable income Tax Cost (Savings) ATCF from Operations Sales Price Sales costs Mortgage Balance Before-tax cash flow from sale Sales Price Less Selling Costs Original Basis Accumulated Depreciation Original Basis Accumulated Depreciation Adjusted Basis Capital Gain/Price Appreciation Depreciation Recapture Cap Gain Tax Depreciation Recapture Tax Tax on Sales Proceeds After-tax Cash Flow From Sale ATCF Total ATIRR NOTE this is where the TABLE command comes in handy Part II Sensitivity Test ad Calculating Variance of Returns Office Building (Return Probability) Deviation (R- Expected R) Probability (P) Squared Deviation PX (R-Expected R]sa BTIRR Scenario Pessimistic (20%) Most Likely (15%) Optimistic (10%) 1 2 3 Expected Return Variance Standard Deviation Coefficient of Variation 5 6 7 Calculating Variance of Returns STUTTUUU DEVIUTTUM Coefficient of Variation Calculating Variance of Returns Office Building ATIRR Probability (P) (Return x Probability) Deviation (R- Expected R) Squared Deviation Px (R-Expected Risa Scenario Pessimistic (20%) Most Likely (15%) Optimistic (10%) Expected Return Variance Standard Deviation Coefficient of Variation Part II Marginal Return Comparison e If sold year 4 If sold year 5 Sale price Selling Costs Mortgage balance Capital gain tax Cash flow Marginal return = (Cash flow if sold next year + NOI over next year - Cash flowfrom sale if sold today) / Cash flow if sold today Marginal return =( )
