P2. Naples Investments Group (NIG) buys and sells rental income propertys in southwest Florida. Bill Hunter, president of NIG, has asked for your assistance I analyzing a small apartment building the group is interested in purchasing.

The property in questions is a small two-story structure with three rental units on each floor. The purchase price of the property is $170,000 representing $30,000 in land value ans$140m,000 in buildings and improvements. NIG will depreciate the buildings and improvements value on a straight line basis over 27.5 years. NIG will make a down payment of $40,000 to acquire the property and finance the remainder of the purchase price over 20 years with an 11% fixed-rate loan with payments due annually. The Data spreadsheet in the Excel file named NIG summarizes this and other pertinent information.

If all units are fully occupied, Mr. Hunter expects the property to generate rental income of $35,000 in the first year and expects to increase the rent at the rate of inflation (currently at 4%). Because vacancies occur and some residents may not always be able to pay their rents, Mr. hunter factors in a 3% vacancy and collection (V&C) allowance against rental income. Operations expenses are expected to be approximately 45% if rental income. The groups marginal tax rate is 28%.

If the group decides to purchase this property, their plan is to hold it for 5 years and then sell it to another investor. Presently, property values in this area are increasing at a rate of approximately 2.5% per year. The group will have t pay a sales commission of 5% of the gross selling price when they sell the property.

The spreadsheet Model in Excel file NIG shows a model Mr. Hunter developed to analyze this problem. This model first uses the data and assumptions given in spreadsheet Data to generate the expected net cash flow in each of the next 5 years. It then provides a final summary of the proceeds expected from selling the property at the end of 5 years. The total NPV of the project is then calculated in cell I18 using the discount rate of 12% in cell C24 from the spreadsheet Data. Thus, after discounting all the future cash flows associated with this investment by 12% per year, the investment still generates a NPV of $2,007.

Although the group has been using this type of analysis for many years to make investments decisions, one of Mr. Hunters investment partners recently read an article in the Wall Street Journal about risk analysis and simulation using spreadsheets. As a result, the partner realizes there is quite a bit of uncertainty associated with many of the economic assumptions shown in the Data spreadsheet. After explaining the potential problem to Mr. Hunter, the two have decided to apply simulation to this model before making a decision. Because neither of them know how to do simulation, they have asked for your assistance.

To model the uncertainty in this decision problem, Mr. Hunter and his partner have decided that the growth in rental income from one year to the next could vary uniformly from 2% to 6% in years 2 through 5. Similarly, they believe the V&C allowance in any year could be as low as 1% and as high as 5%, with 3% being the most likely outcome. They thin the operating expenses in each year should be normally distributed with a mean 45% and standard dev of 2% but should never be less than 40% and never greater than 50% of gross income. Finally, they believe the property value growth rate could be as small as 1% or as large as 5%, with 2.5% being the most likely outcome.

Prepare a report to Mr. Hunter and his partner to make a final decision by incorporating the following:

1. Revise the spreadsheets Data and Model to reflect the uncertainties outlined.
2. What are the best and worst case scenarios for the total NPV?
3. Construct a 95% confidence interval for the average total NPV that NIG can expect if they undertake this project. Interpret this confidence interval.
4. Perform a sensitivity analysis and report two input factors that have the strong impact on the NPV.
5. If NIG would like to have a NPV of at least $1000, what is the risk (probability) it will not achieve this goal?
6. Based on your analysis, what is the probability of this project generating a positive total NPV if the group uses a 12% discount rate?
7. Suppose the investors are willing to buy the property if the expected total NPV is greater than zero. Based on your analysis, should they buy this property?
8. If the investors decide to increase the discount rate to 14%, how would the probability of this project generating a positive total NPV change?
9. What discount rate results in a 90% chance of the project generating a positive total NPV?

(For the simulation, under Options menu, set: trials per simulation = 1000; random seed = 999; select sampling method = Monte Carlo)

Naples Investment Group Acquisition Data Land Value Buildings/Improvements Purchase Price $30,000 $140,000 $170,000 Financing Data Down Payment Amount Financed APR Term Annual Payment $40,000 $130,000 11.0% 20 $16,325 Economic Assumptions Annual Gross Rental Income $35,000 Rental Income Growth Rate 4.0% V&C Allowance 3.0% Operating Expenses 45.0% Tax Rate 28.0% Property Value Growth Rate 2.5% Sales Commission 5.0% Discount Rate 12.0% Cash Flows in Year 3 4 $36,400 $37,856 $39,370 5 $40,945 $35,000 Financial Summary Sales price @ year 5 $ 192,339 Less: Selling expense $9,617 Tax basis $144,545 Taxable gain $38,177 Gross Income Less: V&C Allowance Operating Exp. Net Operating Income Less: Depreciation Interest Taxable Income $1,050 $1,092 $1,136 $1,181 $1,228 $15,750 $16,380 $17,035 $17,717 $18,425 $18,200 $18,928 $19,685 $20,473 $21,291 $182,722 $5,091 $14,300 ($1,191) $5,091 $14,077 ($240) $5,091 $13,830 $764 $5,091 $13,556 $1,826 $5,091 $13,251 $2,950 Proceeds from sale Less: Taxes Loan pay off Net cash from sale $10,689.55 $117,390 $54,643 Taxes Paid (Saved) ($333) ($67) $214 $511 $826 Principal Paid $2,025 $2,248 $2,495 $2,769 $3,074 PV of sale proceeds PV of cash flows Less: Original Equity Total NPV $31,006 $11,001 $40,000 $2,0071 Net Cash Flow $2,209 $2,670 $3,146 $3,636 $4,141 Naples Investment Group Acquisition Data Land Value Buildings/Improvements Purchase Price $30,000 $140,000 $170,000 Financing Data Down Payment Amount Financed APR Term Annual Payment $40,000 $130,000 11.0% 20 $16,325 Economic Assumptions Annual Gross Rental Income $35,000 Rental Income Growth Rate 4.0% V&C Allowance 3.0% Operating Expenses 45.0% Tax Rate 28.0% Property Value Growth Rate 2.5% Sales Commission 5.0% Discount Rate 12.0% Cash Flows in Year 3 4 $36,400 $37,856 $39,370 5 $40,945 $35,000 Financial Summary Sales price @ year 5 $ 192,339 Less: Selling expense $9,617 Tax basis $144,545 Taxable gain $38,177 Gross Income Less: V&C Allowance Operating Exp. Net Operating Income Less: Depreciation Interest Taxable Income $1,050 $1,092 $1,136 $1,181 $1,228 $15,750 $16,380 $17,035 $17,717 $18,425 $18,200 $18,928 $19,685 $20,473 $21,291 $182,722 $5,091 $14,300 ($1,191) $5,091 $14,077 ($240) $5,091 $13,830 $764 $5,091 $13,556 $1,826 $5,091 $13,251 $2,950 Proceeds from sale Less: Taxes Loan pay off Net cash from sale $10,689.55 $117,390 $54,643 Taxes Paid (Saved) ($333) ($67) $214 $511 $826 Principal Paid $2,025 $2,248 $2,495 $2,769 $3,074 PV of sale proceeds PV of cash flows Less: Original Equity Total NPV $31,006 $11,001 $40,000 $2,0071 Net Cash Flow $2,209 $2,670 $3,146 $3,636 $4,141