1. A simple stock model
In this lesson, you will get practice simulating stock prices. Let's get started.
2. Types of stock return
We are going to simulate Apple stock prices using the current stock price and volatility values we found in the last video. There are two new parameters to introduce: the expected return as mu versus k. Mu is the expected return if we had all the data across time or the entire population of data. K is the compounded rate of return for finite intervals of time, like specific days or years that we have sampled.
3. Realistic returns (k)
To calculate k type equals mu minus volatility squared divided by 2.
4. Simulate random normal data
The next part of our model is to simulate random normal data, as we know, stock change over time is normally distributed. We will combine the normsinv() and rand() functions. normsinv() takes a probability value and converts it to a z score, while rand creates a random number from 0 to 1. Together, normsinv() supplied a rand number will give you an expected random change in stock prices. The first estimate will be the original stock price, so we put an X in row 9 and start randomly estimating in row 10.
5. Day 1 estimates
Next, we will add Stock Price now to our other two columns, which are the estimated stock price and the certainty. Simply type equals and then B3 for the stock price now.
6. All other days are random
We will estimate the rest of the stock price days based on a random change. You will multiply the previous day stock price by the exp function of k times 1 divided by 252 plus volatility times our random number times the square root of 1 divided by 252. The fraction of 1 divided by 252 represents the annualization or the 252 stock days in a year.
7. Certainty is not random
Certainty is the expected value of stock given expected return with no randomized or volatility component. We calculate this value to indicate how much a stock should be over time. The formula is the previous days stock times the exp function of k times 1 divided by 252. To complete the model, fill in the formula for stock prices and certainty by copying to a large set of random estimates. The more simulated numbers, the better, but at least 100 should give you a good feel for the probable stock values.
8. Graph your simulation
To visualize the simulation, you can create a line graph of stock prices and certainty. Highlight the cells for the two columns, then select insert, graph, and click line graph as the type.
9. Explore the model
The resulting graph gives us a picture of a potential outcome for Apple's stock. On the left, we see a simulation that predicts that Apple's stock will underperform and be less than our expectations of the certain amount. On the right, another simulation suggests that the stock might be approximately equal to the certain amount until the end of the year and over perform. Since this is a simulation based on random numbers, each version will be different.
10. Let's practice!
Let's try simulating the stock prices for a different company now!