89 NOTE this is where the TABLE command comes in handy 90 Part II Sensitivity Test a-d 91 92 Calculating Variance of Returns 93 94 Office Building (Return x Deviation (R - Squared P x (R-Expected 95 Scenario BTIRR Probability (P) Probability) Expected R) Deviation R)sq 96 Pessimistic(20%) 10.52% 25% 2.75% 8.26% 0.68% 0.1705% 97 Most Likely (15%) 10.52% 40% 4.40% 6.61% 0.44% 0.1746% 98 Optimistic(10%) 10.52% 35% 1.10% 9.91% 0.0982% 99 100 Expected Return 10.52% 101 Variance 3.99% -02 Standard Deviation 0.16% 103 Coefficient of Variation 0.02% 104 105 .06 Calculating Variance of Returns 107 108 Office Building (Return x Deviation (R - Squared P x (R-Expected -09 Scenario ATIRR Probability (P) Probability) Expected R) Deviation R)sq .10 Pessimistic (20%) -35.75% 25% -9.00% -27.00% 7.29% 1.8230% 111 Most Likely (15%) -35.75% 40% -14.40% -21.60% 4.67% 1.8668% 112 Optimistic (10%) -35.75% 35% -3.60% -32.40% 10.50% 1.0501% -13 .14 Expected Return 35.75% 115 Variance -21.01% 116 Standard Deviation 4.41% .17 Coefcient of Variation -2.55% 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 i_sfairly 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