Naples Investments Group (NIG) buys and sells rental income property's 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 and $140,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 spreadsheet Data in the Excel file named NIG-201 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 group's 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 to pay a sales commission of 5% of the gross selling price when they sell the property. The spreadsheet Model 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 118 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. Part A) Your first assignment is to determine the desirable Rental Income Growth Rate to achieve a NPV of $3,000. (in Part B below you will be asked to report your findings) Additionally, to model the uncertainty in this decision problem, Mr. Hunter and his partner have decided that the Rental Income Growth Rate from one year to the next could vary uniformly from 2% to 6% in years 2 through 5. Similarly, they think the operating expenses in each year should be normally distributed with a mean 45% and standard deviation 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%. 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 2 3 $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 $15,750 $18,200 $1,092 $16,380 $18,928 $1,136 $17,035 $19,685 $1,181 $17,717 $20,473 $1,228 $18,425 $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 payoff 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 NPV $31,006 $11,001 $40,000 $2,007 Net Cash Flow $2,209 $2,670 $3,146 $3,636 $4,141 Part B) Prepare a report to Mr. Hunter and his partner to make a final decision by incorporating the following: 1. The analysis of your first assignment above (Part A) Revise the spreadsheets Data and Model to reflect the uncertainties outlined above and address the following: 2. What are the best and worst case scenarios for the total NPV? 3. What is the expected NPV? For this question, provide a probability distribution of the NPV (as a visual data analysis) 4. If NIG would like to have a NPV of at least $1200, what is the risk (probability) it will not achieve this goal? 5. Based on your analysis, what is the probability of this project generating a positive NPV if the group uses a 12% discount rate? 6. Suppose the investors are willing to buy the property if the expected NPV is greater than zero at a maximum discount rate of 14%. Based on your analysis, should they buy this property? (For the simulation, use at least 1,000 trials) Naples Investments Group (NIG) buys and sells rental income property's 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 and $140,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 spreadsheet Data in the Excel file named NIG-201 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 group's 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 to pay a sales commission of 5% of the gross selling price when they sell the property. The spreadsheet Model 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 118 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. Part A) Your first assignment is to determine the desirable Rental Income Growth Rate to achieve a NPV of $3,000. (in Part B below you will be asked to report your findings) Additionally, to model the uncertainty in this decision problem, Mr. Hunter and his partner have decided that the Rental Income Growth Rate from one year to the next could vary uniformly from 2% to 6% in years 2 through 5. Similarly, they think the operating expenses in each year should be normally distributed with a mean 45% and standard deviation 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%. 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 2 3 $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 $15,750 $18,200 $1,092 $16,380 $18,928 $1,136 $17,035 $19,685 $1,181 $17,717 $20,473 $1,228 $18,425 $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 payoff 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 NPV $31,006 $11,001 $40,000 $2,007 Net Cash Flow $2,209 $2,670 $3,146 $3,636 $4,141 Part B) Prepare a report to Mr. Hunter and his partner to make a final decision by incorporating the following: 1. The analysis of your first assignment above (Part A) Revise the spreadsheets Data and Model to reflect the uncertainties outlined above and address the following: 2. What are the best and worst case scenarios for the total NPV? 3. What is the expected NPV? For this question, provide a probability distribution of the NPV (as a visual data analysis) 4. If NIG would like to have a NPV of at least $1200, what is the risk (probability) it will not achieve this goal? 5. Based on your analysis, what is the probability of this project generating a positive NPV if the group uses a 12% discount rate? 6. Suppose the investors are willing to buy the property if the expected NPV is greater than zero at a maximum discount rate of 14%. Based on your analysis, should they buy this property? (For the simulation, use at least 1,000 trials)