Hi,

This post is especially geared for the investment banking domain, but all are welcome on-board always :).

Embarking on this topic is more mathematically challenging as the crux of this topic is more geared to wards advance maths. But putting that aside for a while in this post I am trying to achieve which is an eye candy for any investment banker/trader requirement for any system they adopt.

In order to simulate the stock prices close to reality on any system (or lets say excel only) a normal random function can not achieve the same.

Why, just plot the “RAND” function for 50~100 points on any chart and see if it by any means represent’s any stock price movement over the past few years.

The thing above lacks the “Drift” portion in the randomness, any stock price over the history is surly chaotic, but it has got some directional movement over the time, which the above chart lacks.

Thus to effectively simulate any stock price the mathematical model has to have a random/distortion and drift component to it. And this is where the complex mathematics start which I don’t want to delve in this post. But here are the links for the interested readers to dig in more.

http://en.wikipedia.org/wiki/Brownian_motion

http://en.wikipedia.org/wiki/Wiener_process

http://en.wikipedia.org/wiki/It%C5%8D’s_lemma

(Ito’s Lemma has got some thing to with rocket science, enough to get you excited until to land up to its wiki page)

So in order to achieve a nice simulated stock price for our future financial models, just by using the native formulas, below is the spreadsheet to achieve the same.

The above looks more like a stock price movement (see the directional movement, in addition to the randomness), so go ahead and change some parameters and run your own stock simulation models.

So the big question ???

Why, do we need to simulate the stock prices ?? Ain’t the real data of the world is worth enough to run off our models…

Answer is NO, because this stock price generation system will lay the foundation of the future financial models I will introduce, to price complex derivatives like options, futures and its variant’s. To give you an insight using the above model foundation we will run internally thousands and millions of simulations of stocks prices to price the optimal price of the derivative. Remember my simulation strategy post (link). The concept is similar.

Hope it helps !!!

Enjoy !!!

**Downloads:
**http://amolpandey.com/home/downloads/blog33_download.zip