Chapter 62: Simulating Stock Prices and Asset Allocation Modeling

Overview

• I recently bought 100 shares of GE stock. What is the probability that during the next year this investment will return more than 10 percent?

• I’m trying to determine how to allocate my investment portfolio between stocks, T-Bills, and bonds. What asset allocation over a five-year planning horizon will yield an expected return of at least 10 percent and minimize risk?

The last few years have shown us that future returns on our investments are highly uncertain. In this chapter, I’ll explain a relatively simple approach to assessing uncertainty in future investment returns. This approach is based on the idea of bootstrapping. Essentially, bootstrapping simulates future investment returns by assuming that the future will be similar to the past. For example, if we want to simulate the stock price of GE in one year, we can assume that each month’s percentage change in price is equally likely to be one of, for example, the percentage changes for the previous 62 months. This method allows us to easily generate thousands of scenarios for the future value of our investments. In addition to scenarios that assume that future variability and average returns will be similar to the recent past, we can easily adjust bootstrapping to reflect a view that future returns on investments will be less or more favorable than in the recent past.

After we’ve generated future scenarios for investment returns, it’s a simple matter to use Microsoft Office Excel 2007 Solver to work out the asset allocation problem-that is, how should we allocate our investments to attain the level of expected return we want but with minimum risk?

The following two examples will demonstrate the simplicity and power of the bootstrapping approach.

• I recently bought 100 shares of GE stock. What is the probability that during the next year this investment will return more than 10 percent?

• Let’s suppose that GE stock is currently selling for $28.50 per share. We have data for the monthly returns on GE (as well as for Microsoft and Intel) for the months between August 1997 and July 2002. You can find this data in the file Gesim.xlsx, shown in Figure 62-1. For example, in the month ending on August 2, 2002 (basically, this is July 2002), GE lost 12.1 percent. These returns include dividends (if any) paid by each company.

  
  Figure 62-1: GE, Microsoft, and Intel stock data

• The price of GE stock in one year is uncertain, so how can we get an idea about the range of variation in the price of GE stock one year from now? The bootstrapping approach simply estimates a return on GE during each of the next 12 months by assuming that the return during each month is equally likely to be any of the returns for the 60 months listed. In other words, the return on GE next month is equally likely to be any of the numbers in the cell range F5:F64. To implement this idea, we use the formula RANDBE-TWEEN(1,60) to choose a “scenario” for each of the next 12 months. For example, if this function returns 7 for next month, we use the GE return in cell F11 (4.1 percent), which is the seventh cell in the range, as next month’s return. The results are shown in Figure 62-2. (You’ll see different values because the RANDBETWEEN function automatically recalculates random values when you open the worksheet.)

  
  Figure 62-2: Simulating GE stock price in one year

• To begin, we enter GE’s current price per share ($28.50) in cell J6. Then we generate a scenario for each of the next 12 months by copying from K6 to K7:K17 the formula RANDBETWEEN(1,60). Next we use a lookup table to obtain the GE return based on our scenario. To do this, we simply copy from L6 to L7:L17 the formula VLOOKUP(K6,lookup,5). As the formula indicates, the range B5:F64 is named Lookup, with the returns for GE in the fifth column of the lookup range. In the scenarios shown in Figure 62-2, we see, for example, that the return for GE two months into the future is equal to our 8/1/1999 data point (a 3 percent return).

• Copying from M6 to M7:M17 the formula (1+L6)*J6 determines each month’s ending GE price. The formula takes the form (1+ month’s return)*(GE’s beginning price). Finally, copying from J7 to J8:J17 the formula =M6 computes the beginning price for each month as equal to the previous month’s ending price.

• We can now use a data table to generate 1000 scenarios for GE’s price in one year and the one-year percentage return on our investment. The data table is shown in Figure 62-3. In cell J19, we copy our ending price with the formula =M17. In cell K19, we enter the formula (M17–$J$6)/$J$6 to compute our one-year return as (Ending GE price–Beginning GE price)/Beginning GE price.

  
  Figure 62-3: Data table for GE simulation

• Next we select our table range (J19:K1019), click What-If Analysis in the Data Tools group on the Data tab, and then select Data Table. We set up a one-way data table by selecting a blank cell as our Column Input Cell. After we click OK in the Data Table dia-log box, we have generated 1000 scenarios for GE’s stock price in one year. (The calculation option for this workbook has been set to Automatic Except For Tables on the Formulas tab in the Excel Options dialog box. You need to press F9 if you want to see the simulated prices change.)

• In cells M20:M24, I used the COUNTIF function (see Chapter 18, “The COUNTIF, COUNTIFS, COUNT, COUNTA, and COUNTBLANK Functions”) to summarize the range of returns that can occur in one year. For example, in cell M20, I computed the probability that we will lose money in one year with the formula COUNTIF(returns,"<0")/1000. (I named the range containing our 1000 simulated returns as Returns.) Our simulation indicates that, based on the data for 1997–2002, there is roughly a 42-percent chance that our GE investment will lose money during the next year. Similarly, we find the following results:

  • There is a 44 percent probability that we will make more than 10 percent.

  • There is a 14 percent probability that we will make between 0 and 10 percent.

  • There is a 14 percent chance that we will lose between 0 and 10 percent.

  • There is a 29 percent chance that we will lose more than 10 percent.

  • The average return for the next year will be approximately 10 percent.

• Many pundits believe that future stock returns will not be as good as in the recent past. Suppose we feel that in the next year, GE will perform 5 percent worse per year on average than it performed during the 1997–2002 period for which we have data. We can easily incorporate this assumption into our simulation by changing the final price formula for GE in cell M17 to (1+L17)*J17–0.05*J6. This simply reduces our ending GE price by 5 percent of its initial price, which will reduce our returns for the next year by 5 percent. You can see these results in the file Gesimless5.xlsx, shown in Figure 62-4.

  
  Figure 62-4: Pessimistic view of the future

• Note that we now estimate that there is a 42 percent chance that the price of GE stock will decrease during the next year. Our average is not exactly 5 percent lower than the previous simulation because each time we run 1000 iterations, the simulated values change.

• I’m trying to determine how to allocate my investment portfolio between stocks, T-Bills, and bonds. What asset allocation over a five-year planning horizon will yield an expected return of at least 10 percent and minimize risk?

• A key decision made by individuals, mutual fund managers, and other investors is how to allocate assets between different asset classes given the future uncertainty about returns for these asset classes. A reasonable approach to asset allocation is to use bootstrapping to generate 1000 simulated values for the future values of each asset class, and then use the Excel Solver to determine an asset allocation that yields an expected return yet minimizes risk. As an example, suppose we are given annual returns on stocks, T-Bills, and bonds during the 1972–2001 period. We are investing for a five-year planning horizon, and based on the historical data, we want to know which asset allocation yields a minimum risk (as measured by standard deviation) of annual returns and yields an annual expected return of at least 10 percent. You can see this data in the file Assetallsim.xlsx, shown in Figure 62-5. (Not all the data is shown.)

  
  Figure 62-5: Historical returns on stocks, T-Bills, and bonds

• To begin, we use bootstrapping to generate 1000 simulated values for stocks, T-Bills, and bonds in five years. We assume that each asset class has a current price of $1. (See Figure 62-6 on the next page.)

  
  Figure 62-6: Simulating five-year returns on stocks, T-Bills, and bonds

• For each asset class, we enter an initial unit price of $1 in the cell range H10:J10. Next, by copying from K10 to K11:K14 the formula RANDBETWEEN(1972,2001), we generate a “scenario” for each of the next five years. For example, for the data shown, next year will be similar to 1976, the following year will be similar to 1990, and so on. Copying from L10 to L10:N14 the formula H10*(1+VLOOKUP($K10,lookup,L$8)) generates each year’s ending value for each asset class. For stocks, for example, this formula computes the following:

   (Ending year t stock value)=(Beginning year t stock value)*(1+Year t stock return)

• Copying from H11 to H11:J14 the formula =L10 computes the value for each asset class at the beginning of each successive year.

• We can now use a one-way data table to generate 1000 scenarios of the value of stocks, T-Bills, and bonds in five years. Begin by copying the year 5 ending value for each asset class to cells I16:K16. Next select our table range (H16:K1015), click What If Analysis on the Data tab, and then click Data Table. Use any blank cell as the Column Input Cell to set up a one-way data table. After clicking OK in the Data Table dialog box, we obtain 1000 simulated values for the value of stocks, T-Bills, and bonds over five years. It is important to note that our approach models the fact that stock, T-Bills, and bonds do not move independently. In each of our five years, the stock, T-Bill, and bond returns are always chosen from the same row of data. This enables the bootstrapping approach to reflect the interdependence of returns on these asset classes that has been exhibited during the recent past. (See Problem 7 at the end of this chapter for concrete evidence that bootstrapping appropriately models the interdependence between the returns on our three asset classes.)

• We are now ready to find the optimal asset allocation, which I’ve calculated in the file Assetallocationopt.xlsx, shown in Figure 62-7. To start, I copy the 1000 simulated five-year asset values to a blank worksheet and paste them into the cell range C4:E1003. In cells C2:E2, I enter trial fractions of our assets allocated to stocks, T-Bills, and bonds, respectively. In cell F2, I add these asset allocation fractions with the formula SUM(C2:E2). Later, I’ll add the constraint F2=1 to our Solver model, which will ensure that we invest 100 percent of our money in one of the three asset classes.

  
  Figure 62-7: Optimal asset allocation model

• Next we want to determine our final portfolio value for each scenario. To make this calculation, we can use a formula such as (Final portfolio value)=(Final value of stocks)+(Final value of T-bills)+(Final value of bonds). Copying from cell F4 to F5:F1003 the formula SUMPRODUCT(C4:E4,$C$2:$E$2) determines our final asset position for each scenario.

• We now want to determine the annual return over the five-year simulated period for each scenario we generated. Note that (1+ Annual return)⁵=(Final portfolio value)/(Initial portfolio value). Because the initial portfolio value is just $1, this tells us that Annual return=(Final portfolio value)^1/5–1.

• Therefore, by copying from cell G4 to G5:G1003 the formula (F4/1)^(1/5)–1, we compute the annual return for each scenario during our five-year simulated period. After naming the range G4:G1003 (which contains the simulated annual returns) as Returns, I computed the average annual return in cell J3 with the formula AVERAGE(returns) and the standard deviation of our annual returns in cell J4 with the formula STDEV(returns).

• Now we’re ready to use Solver to determine the set of allocation weights that yields an expected annual return of at least 10 percent yet minimizes the standard deviation of our annual returns. The Solver Parameters dialog box set up to perform this calculation is shown in Figure 62-8 on the next page.

  
  Figure 62-8: Solver Parameters dialog box set up for our asset allocation model

  • We try to minimize the standard deviation of our annual portfolio return (cell J4).

  • Our changing cells are our asset allocation weights (cells C2:E2).

  • We must allocate 100 percent of our money to the three asset classes (F2=1).

  • Our expected annual return must be at least 10 percent (J3>=0.1).

  • We assume that no short sales are allowed, which is modeled by forcing the fraction of our money in each asset class to be nonnegative (C2:E2>=0).

• We find that the minimum risk asset allocation is 45.3 percent stocks, 36.3 percent T-Bills, and 18.4 percent bonds. This portfolio yields an expected annual return of 10 percent and an annual standard deviation of 4.1 percent. Deleting the constraint C2:E2>=0 yields the same solution, so it appears there is no benefit to short selling.

• Suppose we believe that the next 5 years will, on average, produce returns for stocks that are 5 percent worse than the last 30 years. It is easy to incorporate these expectations into our simulation (see Problem 4 at the end of the chapter).