Modeling variations of an asset, such as an index, bond or stock, allows an investor to simulate its price and that of the instruments that are based on it, like derivatives. Simulating the value of an asset on an Excel spreadsheet provides a more intuitive representation of its valuation for a portfolio.  

Pricing Model Simulation Using Excel

I – The Goal

Whether we wish to buy or sell a financial instrument, we gain by studying it both numerically and graphically. This data can help to view the next probable and less-likely price levels that the asset might take.

II – Model

The model first of all requires some prior hypotheses. We assume, for example, that the daily returns r (t) of these assets are normally distributed with the mean (μ) and standard deviation sigma (σ). These are the standard assumptions that we will use in this particular article, but there are many others that could be implemented to improve the accuracy of the model.

 

Which gives:


 

Which results in:

Finally:

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:

■ The computation of the volatility σ - volatility

    φ is a volatility with an average of random variable zero and standard deviation one. (For related reading, see also: What Volatility Really Means.)

Computing the 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 now apply our formula found above:

  
 
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.

Want to learn how to invest?

Get a free 10 week email series that will teach you how to start investing.

Delivered twice a week, straight to your inbox.