Every once in a while, my job affords me the opportunity to learn something really cool – granted in a very nerdy kind of way. Recently, I learned one method for modeling stock prices and interest rates called Geometric Brownian Motion (first used in particle theory, but later used in financial instruments). This conversation might get a little technical, but I’ll start first with a conceptual description before moving into the math. For those who are uninterested in the nerdier parts of finance, feel free to skip this post
In theory, it is nearly impossible to predict what stock prices are going to be in the future – if someone knew, they would be very very very wealthy (or in jail for using illegal methods). If you know of a legal way, let me know and we can quit our jobs and trade stocks for the rest of our lives! But just because stocks are unpredictable doesn’t mean there aren’t ways to try to figure out what these prices over time might look like, especially if you are trying hundreds or thousands of iterations to test for averages, highs, and lows. For instance, what are the odds that Microsoft’s stock price will hit 40, or the odds that it will hit 20? We do this by way of probability and statistics.
For instance, we can probably guess that a stock like General Electric will likely stay around the $15-$16 range that it’s been at for the last month. Maybe there is a 50% chance that it rises a bit tomorrow, and maybe there is a 50% chance that it falls, but we can overwhelmingly say that it’s probably not going to drop to $1.
To demonstrate this, lets take that GE stock and try to figure out what one path of stock prices over the next week might look like. Take out a coin and flip it: if it comes out heads, add $0.25 to the price of GE’s shares, and if it comes out tails, subtract $0.25. Now repeat this process 10 times. You should have a series of prices that looks somewhat plausible – perhaps GE’s price will trend upwards, or perhaps it will trend downwards, but more likely, it will look like what financial engineers call a “random walk“, or a seemingly random series of prices – sometimes moving up, sometimes moving down.
So, to start to define this better, we know that tomorrow’s stock price will likely equal today’s stock price plus or minus some small but random amount. Note that random is the key word here. Now, that’s just using a coin to figure out our probability of stock increases and decreases with a 50% chance of rising by a quarter, and a 50% chance of falling by a quarter. These are certainly not the only rises and falls that could happen, and things are hardly ever assigned a 50% probability. For instance, what happens if the US government black lists GE and refuses to ever buy any GE products again? The stock would tank! A low probability, for sure, but it could happen.
Thus, we need a more sophisticated way of doing this. Luckily, statistics has something called a “Normal Distribution” which will provide us with more granularity than just a binary flip of the coin to figure out how stock prices might change. This will also allow us to factor in those seemingly low probability events (like GE tanking). A normal distribution looks something like this:
What this graph says is that most of the frequency data for a particular sample will cluster around the mean in the center (the symbol that looks like µ). As you get further and further away from the center and head to the extremes, the frequency of certain values becomes less and less. For instance, take the average height of a man, which is about 5′ 10″ in the US. Most guys you meet will probably be about 5’10″. There are going to be several that are 5′ 11″ or 5′ 9″, but you will very rarely find someone who is 5’1″ or 6’6″ – those people are on the extremes.
So to end the conceptual part of this discussion before getting into the modeling, to figure out one possibility tomorrows price, we simply take today’s price and then add some normally distributed (around a mean of zero) change (be it 0 or -.1 or +2), and then repeat for the day after and so on.
An Example using Excel:
You’ll have 3 input’s to this model:
- the current stock price
- the standard deviation of the stock price ( =stdev(stockprices))
- the time units for which you want to make this prediction (days or 1/360, weeks or 1/52 , months or 1/12, etc) – make sure to stay consistent between the units of time for 2 and 3.
Now your formula should look like this:
P1 = P0 + Stdev * Sqrt(time) * normsinv(rand() )
What does this mean? To directly translate: take the standard deviation of the stock, multiply by the square root of time, then multiply by a random normally distributed number (the function rand() generates a random number of uniform distribution between 0 and 1 and normsinv takes this as a probability and maps that to a normal distribution – thus, if rand() returns .60, normsinv says, given a probability of 60% where does that fall on a curve with mean zero and standard distribution of one), and finally add this amount to today’s stock price.
The one thing that threw me off at first was the multiplication by the square root of time, however this is basically due to the standard deviation’s units being in price/(square root of time), so to cancel units you have to multiply by the same factor.
I’ve attached a sample Excel spreadsheet which demonstrates this process. Just hit F9 to see how the price of GE’s stock changes over time. Brownian Motion.xls