How to Use Excel to Simulate Stock Prices

Some active investors model variations of a stock or other asset to simulate its price and that of the instruments that are based on it, such as derivatives. Simulating the value of an asset on an Excel spreadsheet can provide a more intuitive representation of its valuation for a portfolio. 

Building a Pricing Model Simulation

Whether we are considering buying or selling a financial instrument, the decision can be aided by studying it both numerically and graphically. This data can help us judge the next likely move that the asset might make and the moves that are less likely.

First of all, the model requires some prior hypotheses. We assume, for example, that the daily returns, or "r(t)," of these assets are normally distributed with the mean, "(μ)," and standard deviation sigma, "(σ)." These are the standard assumptions that we will use here, though there are many others that could be used to improve the accuracy of the model.

 $\begin{aligned} &r ( t ) = \frac { S ( t ) - S ( t - 1 ) }{ S ( t - 1 ) } \sim N ( \mu, \sigma ) \\ &\textbf{where:} \\ &S ( t ) = \text{close}_t \\ &S ( t - 1 ) = \text{close}_{t - 1} \\ \end{aligned}$​r(t)=S(t−1)S(t)−S(t−1)​∼N(μ,σ)where:S(t)=closet​S(t−1)=closet−1​​

Which gives:

 $\begin{aligned} &r ( t ) = \frac { S ( t ) - S ( t - 1 ) }{ S ( t - 1 ) } = \mu \delta t + \sigma \phi \sqrt { \delta t } \\ &\textbf{where:} \\ &\delta t = 1 \ \text{day} = \frac { 1 }{ 365 } \ \text{of a year} \\ &\mu = \text{mean} \\ &\phi \cong N ( 0, 1 ) \\ &\sigma = \text{annualized volatility} \\ \end{aligned}$​r(t)=S(t−1)S(t)−S(t−1)​=μδt+σϕδt​where:δt=1 day=3651​ of a yearμ=meanϕ≅N(0,1)σ=annualized volatility​

Which results in:

 $\begin{aligned} &\frac { S ( t ) - S ( t - 1 ) }{ S ( t - 1 ) } = \mu \delta t + \sigma \phi \sqrt { \delta t } \\ \end{aligned}$​S(t−1)S(t)−S(t−1)​=μδt+σϕδt​​

Finally:

 $\begin{aligned} S ( t ) - S ( t - 1 ) = & \ S ( t - 1 ) \mu \delta t + S ( t - 1 ) \sigma \phi \sqrt { \delta t } \\ S ( t ) = & \ S ( t - 1 ) + S ( t - 1 ) \mu \delta t \ + \\ & \ S ( t - 1 ) \sigma \phi \sqrt { \delta t } \\ S ( t ) = & \ S ( t - 1 ) ( 1 + \mu \delta t + \sigma \phi \sqrt { \delta t } ) \\ \end{aligned}$S(t)−S(t−1)=S(t)=S(t)=​ S(t−1)μδt+S(t−1)σϕδt​ S(t−1)+S(t−1)μδt + S(t−1)σϕδt​ S(t−1)(1+μδt+σϕδt​)​

And now we can express the value of today’s closing price using the prior day close.

• Computation of μ:

To compute μ, which is the mean of the daily returns, we take the n successive past close prices and apply, which is the average of the sum of the n past prices:

 $\begin{aligned} &\mu = \frac { 1 }{ n } \sum_{ t = 1 } ^ { n } r ( t ) \\ \end{aligned}$​μ=n1​t=1∑n​r(t)​

• The computation of the volatility σ - volatility

φ is a volatility with an average of random variable zero and standard deviation one.

Computing Historical Volatility in Excel

For this example, we will use the Excel function "= NORMSINV (RAND ())." With a basis from the normal distribution, this function computes a random number with a mean of zero and a standard deviation of one. To compute μ, simply average the yields using the function Ln (.): the log-normal distribution.

In cell F4, enter "Ln (P (t) / P (t-1)"

In the F19 cell search "= AVERAGE (F3:F17)"

In cell H20, enter “=AVERAGE(G4:G17)

 In cell H22, enter "= 365*H20" to compute the annualized variance

 In cell H22, enter "= SQRT(H21) " to compute the annualized standard deviation

So we now have the "trend" of past daily returns and the standard deviation (the volatility). We can apply our formula found above:

 $\begin{aligned} S ( t ) - S ( t - 1 ) = & \ S ( t - 1 ) \mu \delta t + S ( t - 1 ) \sigma \phi \sqrt { \delta t } \\ S ( t ) = & \ S ( t - 1 ) + S ( t - 1 ) \mu \delta t \ + \\ & \ S ( t - 1 ) \sigma \phi \sqrt { \delta t } \\ S ( t ) = & \ S ( t - 1 ) ( 1 + \mu \delta t + \sigma \phi \sqrt { \delta t } ) \\ \end{aligned}$S(t)−S(t−1)=S(t)=S(t)=​ S(t−1)μδt+S(t−1)σϕδt​ S(t−1)+S(t−1)μδt + S(t−1)σϕδt​ S(t−1)(1+μδt+σϕδt​)​

We will do a simulation over 29 days, therefore dt = 1/29. Our starting point is the last close price: 95.

• In the cell K2, enter "0."
• In the cell L2, enter "95."
• In the cell K3, enter "1."
• In the cell L3, enter "= L2 * (1 + $F$19 * (1/29) + $H$22 *SQRT(1/29)*NORMSINV (RAND ()))."

Next, we drag the formula down the column to complete the entire series of simulated prices.

This model allows us to find a simulation of the assets down to 29 dates given, with the same volatility as the former 15 prices we selected and with a similar trend.

Lastly, we can click on "F9" to start another simulation since we have the rand function as part of the model.