Financial Analysis and Modeling Using Excel and VBA

Chapter 29. Simulating Stock Prices

In the parallel chapter in Part Two we saw that it is possible to build Excel models to simulate stock prices. However, a major use of simulations in finance is in Monte Carlo simulations used to value European options and other derivatives, especially in situations where the payoffs depend on several underlying market variables. For such simulations, we have to sample tens of thousands or even hundreds of thousands of paths, which cannot be done in Excel. Such simulations have to be done in VBA or other programming languages. Alrhough we will not go into Monte Carlo simulations in this book, we will build a model in this chapter to simulate stock prices similar to the model we built in Excel. Unlike the Excel model, though, this model can be extended to do Monte Carlo simulations with appropriate modifications to speed up the computations and accommodate other requircmenrs.

This chapter requires no additional theory or concept. However, it is impor-rant that you have a good grasp of the theory and concepts I discussed in Chapter 18: Simulating Stock Prices. It will also be easier to follow the models in this chapter if you have already worked through the models in Excel in the earlier chapter.

Modeling Examples

MODEL 1: ESTIMATING A STOCK'S VOLATILITY

The Problem

Create a worksheet function to calculate the annualized volatility for a stock based on daily closing price data. The user would enter the number of days of historical data to use for the ...