What is the most likely stock price?

1. What is the most likely stock price?

Now we will work on the second half of our stock probability model.

2. Cumulative versus density probabilities

For this exercise, it's helpful to distinguish between the cumulative density function and the probability density function. Cumulative probability is the likelihood of a particular stock and all the stock prices below it. Density probability is the likelihood of that particular stock price. You will use the normdist() function to estimate the cumulative probabilities. This function takes four arguments, x, or the value of the stock, the mean of the stock, the standard deviation, and TRUE to get the cumulative probability.

3. Cumulative probability

For the x argument in the normdist() function, we use the natural log of the stock price step. For the mean argument, we use the natural log of the stock price now plus expected return k times time. For the standard deviation, we use the volatility times the square root of time, and then TRUE in all caps to create the cumulative distribution. Note the absolute references for expected return, time, and volatility, so you can copy this formula to all stock price steps.

4. Cumulative probability check

If you have entered the formula correctly, the cumulative probabilities should always be increasing for each stock price step, as you are including more and more stock values.

5. Price column for graphing

Next, we have added an extra price column that is simply equal to the stock price step. This column makes it just a little bit easier to create the final graph of stock price probabilities.

6. Density probabilities

To add the probability of each individual stock, you would subtract the probability of step 2 minus the probability of step 1 or B16 minus B15 here. This number represents the probability of that specific stock price.

7. Copy the formula to all rows but the last

You will then copy the formula down to all the rows of stock data. Be careful with the last row, as you cannot create a probability for the final data point. Be sure not to include this negative number in the last point, as probabilities cannot be negative.

8. Graph the density function

To create a graph, you can highlight the cells for price and probability and click insert chart. You will want to select a line chart as the graph type, then use column c or the price column as the X-axis labels.

9. Lognormal stocks

By looking at the graph of the stocks, we can see that the stocks are lognormally distributed with a long tail, indicating that the high prices for the stock are not very probable. The peak of the distribution is around 185 dollars, and you can click on the graph and hover your mouse over the blue line to see the values of each data point. It appears the most likely price for Apple's stock is in the 180 dollar to 190 dollar range given 1-point-5 years of data.

10. Let's practice!

Your turn! Let's see what the most likely prices are for the ABC health care company stock.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.