The property in question 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.

If all units are fully occupied, Mr. Hunter expects the property to generate a 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% of 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 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 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 an NPV of $2,007.

Although the group has been using this type of analysis for many years to make investment decisions, one of Mr. Hunter's 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 knows how to do simulation, they have asked for your assistance.

**Determine the desirable Rental Income Growth Rate to achieve an NPV of $3,000

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 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