By Kardi Teknomo, PhD.

stochastic process

Share this: Google+
< Previous | Contents | Next >

Simulation of Normally Distributed Random Walk in Microsoft Excel

In this section, you will learn how to generate time series data in Microsoft Excel like the following figures. We start with initial location X(0) = 100 and generate the random walk based on normal probability distribution.

First you need to assume the probability distribution. Next, you need to know the initial value. Those are the two inputs of random walk. For our practice, we will create a white noise, that is a random walk based on Gaussian distribution with mean zero and unit variance.

Formula: X(t)= X(t-1) + Normal(0,1)

More general formula of random walk with Gaussian distribution can involve average distribution and standard deviation, thus, the formula becomes

Random walk Formula:

General Brownian Motion Formula

First, we create a column to represent the time. Start with time 1, then in the next cell, add 1 to the cell of previous row.

Next, we create a column to represent the time series data based on Brownian motion. We start with 100. Then, we add NORMSINV(RAND()) to the cell of the previous row. NORMSINV is an Microsoft Excel function to generate inverse of Standard Normal distribution (i.e. the value) given the probability. We randomize the probability values. In older version of Microsoft Excel 2003 and below, use NORMINV(RAND(),0,1) to replace NORMSINV(RAND()) because Standard Normal Distribution means the mean is zero and the standard deviation is one.

After that you copy the formula of the last row into many rows below.

When you plot the time versus data, you will get the example figure on the left below. Copy the data to the next column and plot the two data will generate the example figure on the right below.

Continue to copy the data to many more rows (up to 500) and plot it will give us example of the figure below. You can press F9 (Function key 9) to recalculate and make new generation of random walk.

< Previous | Contents | Next >

Do you have question regarding this Stochastic Process tutorial? Ask your question here

Share and save this tutorial
Add to: Add to: Digg Add to: StumbleUpon Add to: Reddit Add to: Slashdot Add to: Technorati Add to: Netscape Add to: Newsvine Add to: Mr. Wong Add to: Webnews Add to: Folkd Add to: Yigg Add to: Linkarena Add to: Simpy Add to: Furl Add to: Yahoo Add to: Google Add to: Blinklist Add to: Blogmarks Add to: Diigo Add to: Blinkbits Add to: Ma.Gnolia Information

These tutorial is copyrighted .

Preferable reference for this tutorial is

Teknomo, Kardi. (2017) Stochastic Process Tutorial .