Envision It! Workshop, October 5, 1996

Steve McKelvey

Department of Mathematics

Saint Olaf College

Excel and Simulation

In addition to formulaic analysis of datasets, spreadsheets have the ability to model dynamically changing situations which may or may not include a random component. The investment model presented here is an example of using Excel to model such a situation. The advantage of using Excel in a situation like this, as opposed to something like Stella, is that the formulas involved in the Excel model are more natural and more easily understood than the formulas in the equivalent Stella model. This model also serves as an introduction to the random number generating functions of spreadsheets.

Overview of the Stock Market/Investment Strategy Model.

The purpose of this spreadsheet is to exam various investment strategies to determine which are more likely to result in a good positive return over a long period of time. The investor being modeled defines a decision algorithm through which her decisions to buy or sell stock are determined. This likely to be a rather mechanical algorithm, so this approach might be said to best model a situation where an investor wants to spend a minimal amount each day managing her portfolio.

The general situation we consider is an investor who has both cash assets and stocks. The goal of the investor is, each day, to update the allocation of her assets between cash and stocks. The goal is to increase the total value of her assets over time. To simplify matters, we consider a single stock. A straight forward modification would allow us to build a model with more equity alternatives.

For our purposes we will assume a very simple (some would say excessively naive) investment strategy. If the price of the stock today is higher than it was yesterday, the investor spends 10% of her cash assets to purchase shares of the stock. The investor hopes that the increased price signals an upward trend in the stock so she increases her holdings of the stock. If the price of the stock declines, the investor sells 10% of her holdings, trying to get out before the downward trend intensifies.

This is a very conservative strategy. In fact, some would advocate that her buy/sell decisions ought to be reversed, that she should buy when the price is going down and sell when the price is going up. Other possible strategies include waiting until the market has gone up or down for two or three consecutive days, or moved in the same direction three out of four days, etc. It is also possible to base investment strategies on absolute price, buy when the stock price is below some price and sell if the price rises above some other price. The point of this model is to shed some light on the relative wisdom of these, and other, strategies.

I should also point out that no brokerage commissions are included in this model. Brokerage commissions could be added, in which case the investment strategy should probably be tailored to reduce the number of transactions.

The Stock Market Model

Since this model is based on movements in the price of a stock, it is clear that the model must include a mechanism for changing the price of the stock. It is also clear that this mechanism should involve some randomness, but the exact form this market model should take is not at all clear. In fact, stock pricing is the hardest part of any market-based simulation to model. Much academic research is dedicated to looking into this question. Perhaps the reason so public progress has been publicly reported is that anyone who truly understood market moves would do well to keep the information to herself.

I want to be clear form the outset that I have not done any serious research into modeling the stock market, and that I make no claim that the model presented here has any relationship whatsoever to the true workings of the stock market. If I knew how the market really worked I would be on the phone with my broker right now.

Despite all these caveats, we need a model to run our spreadsheet. There are many models to choose from; here’s the one I chose. Every day the change in the price of the stock is affected by change in the price of the stock the previous day. The change is a random number sampled from a normal (bell-shaped) distribution with a standard deviation equation to 1% of the previous day’s stock price and a mean equal to one-tenth the previous day’s change. For example, if the stock went from $100 to $102 the previous day, today’s change would be sampled from a normal distribution with a mean of $2/10=$0.20 and a standard deviation of $100/100=$1.00.

This model produces a string of price changes with modest support for trends, but trends which are likely to quickly dampen in the short term.

It would be quite reasonable to adjust the various parameters in this stock price model to see what effect different market characteristics have on the wisdom of various buy/sell strategies. Many other models exist which use an entirely different approach to modeling price changes in stocks. Most of these can be easily substituted for the model demonstrated here.

The Excel Spreadsheet

The Excel spreadsheet used to create this investment strategy evaluation model is shown here:

Each line of the spreadsheet represents a snapshot of the investor’s portfolio at the beginning of each day. The delta Price column represents information available to the investor in the morning, namely the price change the previous day. This is the only random quantity in this model. It is important to note that every time the spreadsheet is recalculated these price changes will change, reflecting a different replication of the random model.

The first line of numbers in the spreadsheet represents initial values for the various quantities. These are all constants, not formulas, and must be entered manually every time their values change. The exception here is the Total Worth column, which is computed by combining the stock price, shares held and cash held.

The actual modeling begins on the second row.

• The Stock Price value is determined by adding the previous day’s stock price to the randomly generated delta Price figure for the day.