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.
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.
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 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.
=D10+B10*(G10-F10)where column D is the Cash Held column, column B is the Stock Price column and columns F and G are the Shares Purchased and Shrs. Sold columns respectively.
IF(test,value-if-true,value-if-false)where the value of the "if" function is determined by the truth of the test. A typical Excel formula for the Shares Purchased column in this worksheet is:
=IF(B10<B11, ROUND(0.1*D11/B11,0), 0)If the price has risen (B10
0.1*(cash on hand)/(stock price).The ROUND function rounds this quantity to the nearest integer. The zero in the second argument of the ROUND function tells Excel to round to the nearest integer rather than to a specific number of significant digits.
The last zero in the IF statement shows how many shares should be
purchased if the price of the stock has not risen.
=IF(B10>B11, ROUND(0.1*C11,0), 0)which amounts to selling 10% of the stock currently on hand.
Unfortunately, Excel does not provide a random number generator which directly produces samples from a general normal distribution. Instead it is necessary to first generate a uniform random number from the interval zero to one and then use this number to create a sample from the desired normal distribution. The good news is that Excel makes it very easy to perform each of these separate tasks.
The typical Excel formula in the delta Price column is:
=NORMINV(RAND(), (B11-B10)/10, B11/100)The RAND function produces the zero-one random number. The NORMINV function converts this into the sample from the normal distribution. The second argument to the NORMINV function is the mean of the distribution, 10% of the most recent price change in our example. The third argument is the standard deviation of the normal distribution, 1% of the most recent stock price in our example.
The second row of the spreadsheet contains a slightly different
formula in the delta Price column. The formula here is:
=NORMINV(RAND(), 0, B10/100)The mean is set to zero in this row because there is no previous price change to consider. This column is the only case in which the second row of the spread sheet does not contain the general formula for the column. The general formula appears in the third row of the spread sheet for the delta Price column.
To construction the spreadsheet enter the column headers, enter the initial values for each column in the first row, enter the general formulas in the second row (with the exception of the delta Price column discussed above), copy the formulas from the second row to the third row, making the necessary update to the delta Price column, and then copy the third row formulas to as many rows as you would like. Each row represents one day in the simulation.
The last row of the simulation will contain important information concerning the wisdom of the investing strategy. In particular, this row will tell you whether the initial investment has grown or dwindled under this investment regime. To keep the last row visible, you may want to hide the middle rows of your spreadsheet.
The spread sheet now contains one run of the model. As with any random model, it is a bad idea to base conclusions on a single replication of the model. Once the outcome of the first replication is noted, a second replication can be acheived by asking the spread sheet to recalculate itself. This will result in an entirely new set of random price changes.
The Data Table feature of Excel can be used to automate this replication process if you would care to perform a large number of them.
Rather than enumerating a list of suggested changes in the model, I will instead encourage you to create your own list of interesting changes. These changes may relate to a different, perhaps more sophisticated, buy/sell strategy or they may relate to constructing a better model of stock price evolution. It is interesting to evaluate a given buy/sell strategy over several different types of markets (bull, bear, steady).
It is also interesting to consider all the possibilities for visualization this model presents. Plots of stock prices over time are possible, as are plots of total net value. These could be plotted together to see if a rising stock market increases the net worth of the investor. Stock market trends could be analyzed with plots. If visualization is your thing, this model gives you plenty of opportunities for creative investigation.