In this tutorial, we’ll teach you how to** simulate random stock price paths** in Excel. In particular, we’ll assume that the stock price movements are determined by **Geometric Brownian Motion** as follows:

where *P _{t}* is the stock price at time

*t*(say, today),

*P*is the stock price at time

_{t-1}*t-1*(say, yesterday),

*μ*represents the mean return (also known as the “drift rate”), σ stands for the standard deviation of returns (i.e., return volatility), and

*z*is a random variable that follows a standard normal distribution with zero mean and unit volatility:

_{t}*z*.

_{t} ∼ N(0,1)The key variable here is *z _{t}* as it is the variable that causes

*random*changes in stock prices over time, creating a

**random walk**. Intuitively, it represents random shocks or noise. In fact, as we show below, because

*z*is normally distributed, (log) returns are normally distributed as well.

_{t}If you’re visiting this page from our YouTube channel, go to the “Video tutorial and Excel template” section below to download the spreadsheet we use in the video tutorial.

#### Contents

## Step 1: Choose the mean return, return volatility, and initial price

In order to simulate stock price paths, you need to first decide on the stock’s mean return and return volatility. In this example, we’ll focus on the daily returns of a stock, but you could follow the same steps for any return interval (weekly, monthly, etc.). As you can see in Figure 1 below, we’ve assumed that the stock is expected to generate a daily return of 0.05% with 1% daily volatility (see cells F2 and F3). We need to choose an initial price (*P _{0}*) as well. We picked $10 (cell C2), but any price would do.

## Step 2: Generate a series of random shocks using the RAND and NORMINV functions

The second step is to use Excel’s random number generator to generate a series of random shocks. Specifically, for each day *t*, we generate *z _{t}* using Excel’s

**RAND()**and

**NORM.INV()**(or the legacy NORMINV()) functions as follows (see also Figure 2 below):

`=NORM.INV(RAND(),0,1)`

Note that in the NORM.INV() function above, 0 is the mean and 1 is the standard deviation of *z _{t}*. And, we use the exact same formula for each cell in column B. We generate

*z*values for 2,500 days (cells B3 to B2502), which is approximately 10 years based on 252 trading days per year, but you can work with any length of period you like.

_{t}## Step 3: Simulate the stock prices using the random shocks

Next, we can simulate stock prices using the random shocks we just generated. For example, the stock price on the 1st day *P _{1} *is determined as follows (see Figure 3 as well):

`=C2*EXP($F$2+$F$3*B3)`

where C2 is the previous day’s closing price (*P _{0}*), $F$2 is the mean return (constant for all

*P*, $F$3 is the return volatility (constant for all

_{t})*P*as well), and B3 is

_{t}*z*. Then, copy-pasting the formula in cell C3 all the way down yields the random stock price path in column C.

_{1}## Step 4: Plot the random walks generated in a single chart

The final step is to plot the random walk of stock prices we just generated. This can be easily done by going to the **Insert** tab and choosing a **line chart**. Figure 4 shows the plot of a single stock price path simulated by following the steps above. You can also save each path you generate and plot them together in a single chart. For example, in Figure 5, we have plotted 100 random walks that are independent from one another. This gives us an idea about some of the possible paths our stock may follow over a 10-year period.

## Normal distribution of log returns

When stock prices follow a Geometric Brownian Motion, we can show that log returns are normally distributed as follows. We begin with our initial equation:

Then, divide both sides by *P _{t-1}* to obtain:

Finally, taking the natural logarithm of both sides yields:

where we have the log return on the left-hand side of the equation. Since *z _{t}* is normally distributed and the log return is a linear combination of

*z*, the log return has a normal distribution as well.

_{t}## Video tutorial and Excel template

Click on the link below to download the Excel template used in this tutorial.

##### What is next?

This tutorial is part of our series on analyzing stock returns.

**Previous tutorial**: We demonstrated how to estimate Jensen’s alpha in Excel.

We recommend you check the following tutorials as well