0

I want to create a function to create stock prices over a period in Excel. What is the distribution of a stock price?

Clarinetist
  • 19,519
user184539
  • 3

1 Answers1

1

One very common and simple model is to write that the returns $$\frac{ X(t) - X(t-\delta t)}{X(t-\delta t)} $$ are gaussian with some standard variantion $\sigma (\delta t)$. If you live in a no-free lunch world (meaning that there is way to make money without taking risks: this is convenient except for high frequency time series), then the returns of non-overlapping periods are uncorrelated.

Finally, note that empirical studies reveal that $\sigma (\delta t) \propto \sqrt{\delta t}$. This leads to a geometric Brownian motion model.

mookid
  • 28,236
  • Thanks for your answer. Can you simplify your answer. Is that function random. What do you mean by you have to take risk. Stocks move in trends, right? – user184539 Oct 18 '14 at 13:03
  • Assume that you know that $X$ will rise tomorrow. Then everybody does, and an investment consisting of buying today and selling tomorrow makes cash without risk. As everybody can do that, the price is underestimated and must rise now, mechanically. Otherwise money can be made without risk. – mookid Oct 18 '14 at 13:33
  • Another way to say that: there is no money on the side walk in the streets, because everybody can just take it. – mookid Oct 18 '14 at 13:34
  • I'm not talking of predicting price movement of the future. Is it not possible to make profit by limiting loss in case of movement in opposite direction and letting profit run with trailing stop loss. – user184539 Oct 18 '14 at 13:51
  • I know. But this has to be a feature of your model. Otherwise your model does not reflect a fundamental property of financial markets. – mookid Oct 18 '14 at 13:53
  • What if my assumption about financial market is that it is collection of random events? Is my assumption correct? If yes can any model be created to take advantage of the market, say from people who try to predict price of future? – user184539 Oct 18 '14 at 14:55
  • it is the whole point of a random model, actually. Even if there are elements of economy that cannot be neglected. You can try to create suhc model, people working in banks do that. – mookid Oct 18 '14 at 15:06
  • Is it possible theoretically? Ignoring all predictions about economy and all other factors. – user184539 Oct 18 '14 at 15:10
  • models have a validity limit. Contrary to other fields of science, a stationary hypothesis is not justified (because actors of financial market adapt to new models). Hence even if it is "possible" it is damn difficult in practice. You can at best classify some phenomenon that you can see on financial markets dynamics. – mookid Oct 18 '14 at 15:15
  • I will accept. Final clarifications. 1.How market will adapt to a particular model which is used to manage very small amount of money? 2.what is the formula to create variable which varies like a stock price? – user184539 Oct 18 '14 at 15:24
  • I do not get what is the particularity of the small amounts of money. The market do not adapt to small investments. The formula in the model I talked about is given in the wikipedia article linked: $X_t = \exp (-\sigma^2 / 2 t + \sigma W(t))$ with $W$ a Brownian motion. – mookid Oct 18 '14 at 15:30
  • My point is amount of money should be large enough to remove the advantage of a particular model. Am I correct? – user184539 Oct 18 '14 at 15:36
  • I can't see why, actually. – mookid Oct 18 '14 at 17:31
  • I just want to see how the function creates charts. – user184539 Oct 18 '14 at 17:33
  • 1
    you can start here: http://investexcel.net/geometric-brownian-motion-excel/ – mookid Oct 18 '14 at 17:50