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 Pt is the stock price at time t (say, today), Pt-1 is the stock price at time 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 zt is a random variable that follows a standard normal distribution with zero mean and unit volatility: zt ∼ N(0,1).
The key variable here is zt 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 zt is normally distributed, (log) returns are normally distributed as well.
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 (P0) 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 zt 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 zt. And, we use the exact same formula for each cell in column B. We generate zt 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.

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 P1 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 (P0), $F$2 is the mean return (constant for all Pt), $F$3 is the return volatility (constant for all Pt as well), and B3 is z1. Then, copy-pasting the formula in cell C3 all the way down yields the random stock price path in column C.

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 Pt-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 zt is normally distributed and the log return is a linear combination of zt, the log return has a normal distribution as well.
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