Using Spreadsheets to Estimate the Volatility of Stock Prices
Kyle Riley · Mathematics and computer education · 2002
INTRODUCTION The concepts of mean and variance arise naturally in modeling the erratic behavior of stock prices. We present a simple method for estimating the volatility of stock prices and use a spreadsheet (Microsoft Excel is used here) to apply our method to actual stock prices. This method is a nice application of parameter estimation and can be used in an introductory statistics course. STOCK PRICES, FINANCIAL OPTIONS, AND VOLATILITY Even the most naive investor knows that stock prices vary greatly over any period of time and this fluctuation introduces an element of risk into stock market investing. The plot of one year's closing prices in Figure 1 illustrates the erratic motion of stock prices for a given stock over a year-long period. Note that there are 249 active trading days for the US stock market in a 365 day year. The volatility of a stock is a measure of how great the stock price can fluctuate. A stock with a high volatility will be more susceptible to greater fluctuations in prices, both up and down. Moreover, a stock with high volatility can be thought of as a riskier investment than a stock with a lower volatility. Estimating the volatility of a stock plays a vital role in assessing the risk of an investment and in computing the value of financial options associated with the asset. A financial option, sometimes referred to as a financial derivative, is a tool that is often used to reduce exposure to risk. For example, a European call option is a contract where the buyer of the option has the right to buy a certain number of shares of an asset at a fixed price at a future date. To illustrate, suppose you buy an option that allows you to pay $30 a share for ten shares at a date that occurs sixty days later. If at the end of the sixty days the market value for the shares has risen to $35 per share then you can buy your ten shares at the contracted price of $30 and then turn around and sell these shares on the open market, thus yielding $50 of gross profit. On the other hand, if the market value for the shares remains at $30 per share, or is below $30 a share, then the buyer has the option not to purchase any shares and is out only the original price of the option. Financial options in isolation are basically bets on whether share prices will go up or down. However, in concert with other investments a financial option can help reduce exposure to risk. To illustrate this concept of reducing risk, consider the financial dilemma of a cattle feedlot owner. The feedlot owner is exposed to a risk of increasing feed prices since higher feed prices translate into a higher price for operating the feedlot. If the owner purchases call options on feed commodities, then a rise in feed prices translate into a profit from the option that can then be used to defray the higher feed costs. If the feed prices fall, then the option will not render a profit, but the owner will still be able to buy feed with the prices being at a lower level. …