Undergrad Function to find the probability distribution of a stock price

[beamthegreat]

Hi all. I'm trying to find a formula that will calculate the probability distribution of a stock price after X days, using the assumption that the price change follows a normal distribution. In the spreadsheet, you can see the simulation I've made of the probability distribution of the price of a stock that is initially at $100 after 252 days (1 trading year, using the assumption that the price moves with an SD of 3.5% per day)

Can someone please direct me to the simplified formula I can use to calculate this? I'm certain this has been done before.

Thanks!

Link: https://docs.google.com/spreadsheets/d/1beooSijC0OJ4uBfC_a-dxveeplFmfTBTiy4QoIhbC28/edit?usp=sharing

 

[jbriggs444]

Hi all. I'm trying to find a formula that will calculate the probability distribution of a stock price after X days, using the assumption that the price change follows a normal distribution. In the spreadsheet, you can see the simulation I've made of the probability distribution of the price of a stock that is initially at $100 after 252 days (1 trading year, using the assumption that the price moves with an SD of 3.5% per day)

So the idea is that the probability distribution at the end of one day is a normal distribution with mean equal to the morning's value and standard deviation equal to 3.5 percent of the morning's value? And that the distribution at the end of 252 days is the result of iterating this procedure 252 times?

The obvious difficulty is that the standard deviation for the second day's normal distribution will depend on the value of the sample drawn from the first day's distribution. The obvious remediation would be to use a log-normal distribution. Sum up 252 of those and take the anti-log.

https://en.wikipedia.org/wiki/Log-normal_distribution

[As an bonus, the log normal distribution is not obviously impossible. The normal distribution is obviously impossible since the left hand tail has a non-zero probability for negative prices]

 

[beamthegreat]

So the idea is that the probability distribution at the end of one day is a normal distribution with mean equal to the morning's value and standard deviation equal to 3.5 percent of the morning's value?

That is correct.

The obvious difficulty is that the standard deviation for the second day's normal distribution will depend on the value of the sample drawn from the first day's distribution. The obvious remediation would be to use a log-normal distribution. Sum up 252 of those and take the anti-log.

I got the gist of it, but I don't have sufficient knowledge of mathematics to derive the expression myself. I tried approximating it with a normal distribution using the following equation to find the final SD after n days, which is pretty accurate if n is small, but the distribution becomes increasingly skewed as the number of days increases: $SD_{final} = \sqrt {nSD_{initial}^2}$

I have no idea how to use the log-normal distribution to remediate this. Any help will be appreciated.

 

[jbriggs444]

That is correct.
I got the gist of it, but I don't have sufficient knowledge of mathematics to derive the expression myself. I tried approximating it with a normal distribution using the following equation to find the final SD after n days, which is pretty accurate if n is small, but the distribution becomes increasingly skewed as the number of days increases: $SD_{final} = \sqrt {nSD_{initial}^2}$

I have no idea how to use the log-normal distribution to remediate this. Any help will be appreciated.

I've never used one either, but the principle seems simple enough.

With a normal distribution, you'd be looking at a distribution of percentage growth which is centered on 0% growth with standard deviation 3.5%. So we are talking about a bell shaped curve roughly from 96.5% to 103.5% of the morning's starting value.

If we represent those values as natural logs, that's ln(96.5%) to ln(103.5%). Using the small log approximation, that's about -0.035 to +0.035. If precision is important, you could transform the normal distribution more carefully, taking the natural log of every value.

Pretend for the moment that the resulting distribution is approximately normal. So we have an estimated distribution with mean 0 and standard deviation approximately 0.035. But after summing with itself 252 times, the result will be a lot closer to normal. So we estimate the true distribution after 252 days as a normal distribution with mean = 252 times 0 and standard deviation = $\sqrt{252}$ times 0.035. The standard deviation of this will be around 0.55 -- corresponding to a rise or fall by a factor of $e^{0.55}$ ~= 1.73 in price.

Rescale this normal distribution by taking the exponential of each point and you have your expected final distribution. I do not expect that this will be a normal distribution.

Edit: It is gratifying to see that Wiki makes essentially the same points:

Occurrence and applications[edit]

The log-normal distribution is important in the description of natural phenomena. This follows, because many natural growth processes are driven by the accumulation of many small percentage changes. These become additive on a log scale. If the effect of anyone change is negligible, the central limit theorem says that the distribution of their sum is more nearly normal than that of the summands. When back-transformed onto the original scale, it makes the distribution of sizes approximately log-normal (though if the standard deviation is sufficiently small, the normal distribution can be an adequate approximation).

 

[Ray Vickson]

That is correct.
I got the gist of it, but I don't have sufficient knowledge of mathematics to derive the expression myself. I tried approximating it with a normal distribution using the following equation to find the final SD after n days, which is pretty accurate if n is small, but the distribution becomes increasingly skewed as the number of days increases: $SD_{final} = \sqrt {nSD_{initial}^2}$

I have no idea how to use the log-normal distribution to remediate this. Any help will be appreciated.

Google "lognormal distribution".

 

[alan2]

Stock prices are not lognormal, returns are not normal. What are you trying to do with this?