Question

PLEASE ANSWER IN EXCEL WITH WORK. An investor has projected three possible scenarios for a project as follows: Pessimistic- NOI will be $200,000 the first year, and then decrease 2 percent per year over a five year holding period. The property will sell for 1.8 million after five year. Most Likely- NOI will be level at $200,000 per year for the next five years(level NOI) and the property will sell for 2 million. Optimistic- NOI will be 200,000 the first year and increase 3 percent per year over a 5 year holding period. The property will then sell for 2.2 million The asking price for the property is 2 million. The investor thinks there is about a 30 percent probability for the pessimistic scenario, a 40 percent probability for the most likely scenario, and a 30 percent probability for the optimistic scenario. a. Compute the IRR for each scenario. b. Compute the expected IRR c. Compute the variance and standard deviation of the IRRs. d. Would this project be better than one with a 12 percent expected return and a standard deviation of 4 percent?

Answer 1

Answer:

• IRR (pessimistic) = 7.93%
• IRR (most likely) = 10.00%
• IRR (optimistic) = 12.12%
• Expected IRR = 10%
• Variance of IRR = 0.0263%
• Standard deviation of IRR = 1.6216%
• Current project is better than alternative project

Step-by-step explanation

The IRR for each scenario is shown in the table below.

The IRR was calculated using the Excel function IRR().

The expected IRR is 0.30(7.93%) + 0.4(10.00%) + 0.3(12.12%) = 10%

The variance is 0.30(7.93%-10%)² + 0.4(10.00%-10%)² + 0.3(12.12%-10%)² = 0.0263%

The standard deviation is (0.0263%)^0.5 = 1.6216%

Comparing the risk-return characteristics:

• Alternative project generates a return of 12% / 4% = 3 times per unit of risk
• Current project generates a return of 10% / 1.62% = 6.18 times per unit of risk.

.

Therefore, the current project is better than the alternative project.