1. Living with uncertainty
In this final chapter, we are going to explore how to estimate and understand stock prices.
2. Defining uncertainty
Stocks are, essentially, a share or a small piece of a company. People buy and sell stocks to make money, but they are hard to predict or model because they are volatile. Volatility is a measure of the variability in a stock across time, which is a form of standard deviation. Volatile stocks are more risky investments because they change a lot more than other stocks.
3. Model Apple's volatility
This is an example of a model in which we will calculate the mean daily return, daily standard deviation, and volatility. The data on the right side is the last year of Apple's stock prices listed by day, with some values we need to calculate for our model.
4. Relative price and daily return
First, let's calculate relative price. Relative price is a ratio of the current day's stock price to the previous day's stock price. We calculate that by typing equals then click on the first day in E4 divided by the previous day in E5. This ratio is then converted to the daily return, which is the natural log of the relative price ratios. This value represents a proportion change across days. In the daily return column type equals ln() then click on the relative price ratio for that day.
5. No #DIV/0!
The very first day of data that you have, which is the last row in our dataset, is simply for comparison. Therefore, as you copy over the formulas from the first row for relative price and daily returns, you will want to make sure you delete the last row of these two columns only. Otherwise, they will show a divide by zero error, and that will impact your calculation of volatility.
6. Days of data
As part of the volatility formula, we need to know the number of days of data we are including. You can use the count() function to count the number of rows in the data. Type equals count() and then highlight the entire column for date, excluding the word date.
7. Descriptive statistics for Apple
To calculate mean daily return, we will take the average of the daily return column. Type equals average() and highlight all the values in the daily return column. Standard deviation is a similar process, using equals stdev() and then highlighting the daily returns data.
8. Annualize volatility
The mean daily returns for Apple stock are basically zero, indicating that the stock is about equal price across the year. The daily standard deviation is a proportion of change, so approximately 1-point-9 percent change across days. Volatility is then annualized or converted to a yearly rate. The formula for volatility is the daily standard deviation divided by the square root of the time, which is 252 days in this example.
9. Apple's volatility uncovered
The model reveals that Apple's volatility is about 31 percent change across the year. Compared to another large company, such as Coca-Cola, with a volatility of 15 to 20 percent, this may imply that Apple's stock changes more than others.
10. Let's practice!
Now it's your turn to calculate volatility for a large health care company.