The Foxridge Investment Group buys and sells rental income properties in southwest Virginia. Bill Hunter, president of Foxridge, has asked for your assistance in analyzing a small apartment building the group is interested in purchasing.

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. Foxridge will depreciate the buildings and improvements value on a straight-line basis over 27.5 years. The Foxridge Group 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.

Figure 12.48 (and the file Fig12-48.xlsm that accompanies this book) 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 4%). Because vacancies occur and some residents may not always be able

Figure 12.48

Assumptions for the Foxridge Investment Group case

To pay their rent, Mr. Hunter factors in a 3% vacancy and collection (V&C) allowance against rental income. Operating expenses are expected to be approximately 45% of 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 five 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.
Figure 12.49 shows a spreadsheet model Mr. Hunter developed to analyze this problem. This model first uses the data and assumptions given in Figure 12.48 to generate
FIGURE 12.49
Cash flow and financial summary for the Foxridge Investment Group case

The expected net cash flows in each of the next five years. It then provides a final summary of the proceeds expected from selling the property at the end of five years. The total NPV of the project is then calculated in cell I18 using the discount rate of 12% in cell C24 of Figure 12.47. 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 Figure 12.48. 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 think the operating expenses in each year should be normally distributed with a mean of 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%, with 2.5% being the most likely outcome.
a. Revise the spreadsheets shown in Figures 12.48 and 12.49 to reflect the uncertainties outlined.
b. Construct a 95% confidence interval for the average total NPV the Foxridge Investment Group can expect if they undertake this project. Interpret this confidence interval.
c. Based on your analysis, what is the probability of this project generating a positive total NPV if the group uses a 12% discount rate?
d. 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?
e. Assume the investors decide to increase the discount rate to 14% and repeat questions 2, 3, and 4.
f. What discount rate results in a 90% chance of the project generating a positive total NPV?

Transcribed Image Text:

Fg2-48-Excel HON趕 INSERT PAGELAYOUT FORMULAS DATA REVEw view DEVELOPER ADONS Anaytic Solver Platfor Foxridge Investment Group Acquisition Data Land Value Financing Data Amount Financed Annual Payment Economic Assumptions Annual Gross Rental Income Rental Income Growth Rate S35,000 Operating Expenses Property Value Growth Rate Sales Commission Discount Rate 2.5% Data Model 110% F2-48-Ee FILE HOME INSERT PAGE LAYOUT FORMULASDATAREVIEWDEVELOPER ADD-INS Analytic Solver Plaor Cash Flows in Year Financial Summary Sales price year 5 $192,339 Selling expense$9,617 38,177 Proceeds from sale$182,722 10,690 Gross Income Less: 35,000 36,400 $37 856 39 370 $40.945 Less: V&C Allowance$1.050 Operating Exp $15,750 $16,380 $17,035 $1092$1136 $1,181 $1,228 Tax basis $17,717 $18,425Taxable gain 8 Net Operating Income $18,200 $18,928 $19,685 $20 473 21,291 9 Less: 0Depreciation 5,091 $5.091 $5091 $5 091 $5 09 14,300 $14,077 $13,830 $13,556 $13 25 $1,191) (240) 764 $1,826 $2,950 Less Taxes Loan payolfl Interest 12 Taxable Income 13 $117.39 Net cash from saleSC 14 Taxes Paid (Saved)(333) (S6T) $214511826 PV of sale proceeds 15 6 Principal Paid 17 18 Net Cash Flow 2.025 $2 248 $2495 $2,769 $3,074 PV of cash flows 2.209 $2,670 $3,146 $3,636 $4,14 $11,001 Less: Original Equity$40.000 Total NP Data Model