Simulating Stock Prices from Normally Distributed Log Returns

# Modeling stock performance in excel


# Modeling stock performance in excel












I am trying to model the ending value of a stock after a certain number of years, I need it for a bigger project but I made this sample sheet to get help.

This sheet is assuming that annual returns are normally distributed and I am generating a return for each year from a normal distribution. However I feel this is somewhat flawed as even if we assume daily returns are normally distributed, annual returns will not be due to the effect of compounding.

How about I do exponential(normally generated annual return)? Help please!

https://1drv.ms/x/s!AgTfphgT-fk83AdP5jq0AA5d97j6

## Answer by Dave Harris (score 2)

https://quant.stackexchange.com/a/35394

The problem is you are trying to model it with a normal distribution. Prices are data. Returns are not data. Returns are mathematical transformations of raw data. It is mathematically impossible for returns to be normally distributed unless losses are anticipated in every period.

Returns can be constructed in four ways. You can either use: $$\frac{p_{t+1}}{p_t}-1,$$ $$\log(p_{t+1})-\log(p_t),$$ $$p_{t+1}=Rp_t+\varepsilon_{t+1},$$ or $$p_{t+1}=p_t\exp{(r\Delta{t}+\epsilon_{t+1})}.$$

Each possible choice has a different statistical distribution, however, it was proven by Mann and Wald in 1943 that R is normal in the third equation if $|R|<1$. White in 1958 proved it was a Cauchy distribution if it is greater than one. Of course, since people want to make a profit, it must be greater than one. This also means that there is no non-Bayesian solution possible.

The actual argument is very long and detailed, but the short-form argument would be that no sufficient statistic exists for any of the four equations. Because of this, no projective method will be admissible unless it is a Bayesian method because it will lose information from the data. That isn't quite rigorous, but the answer works out that way for all four cases in the end.

Since $p_t$ can be viewed as $p_t^*+\varepsilon_t,\forall{t}$, then a ratio of prices is the ratio of two random variates. Under Markowitz's assumptions, this should be a Cauchy distribution. That isn't quite realistic, but if you remove firms going into bankruptcy or merger, it will be close if you truncate it to prohibit returns less than 100%.

It will be insanely challenging to do this via Excel. What you will need to do is a Markov Chain Monte Carlo algorithm to perform the calculations. There is a proof that no analytic solution can exist. You cannot create "point estimators" that will be useable to solve your problem. Proof of this is by Koopman in 1936. It would form the basis of the Pitman-Koopman-Darmois theorem. If it is possible to do in Excel, then you will need to use Visual Basic.

I would recommend Python or C. There are issues in computational algebra that I am not sure Excel could do.

If you remove mergers and bankruptcies, then your Bayesian likelihood function is close to $$\left[\frac{\pi}{2}+\tan^{-1}\left(\frac{R}{\Gamma}\right)\right]^{-1}\frac{\Gamma}{\Gamma^2+(r_t-R)^2}.$$ This likelihood does not account for the intertemporal budget constraint, but is close enough for most purposes. To adjust for the intertemporal budget constraint you will likely need to use survival functions, either the logistic curve or the arctangent will work, though each have a different interpretation.

You will need to construct the Bayesian posterior predictive distribution.

For an extended discussion on this you can read my papers on the topic at https://papers.ssrn.com/sol3/cf_dev/AbsByAuth.cfm?per_id=1541471. Some of the papers have been submitted for publication.

Bibliography:

B. O. Koopman, On distributions admitting a sufficient statistic," vol. 39, pp. 399-409, May 1936.

J. H. Curtiss, On the distribution of the quotient of two chance variables," Annals of Mathematical Statistics, vol. 12, p. 409, 1941.

H. Mann and A. Wald, On the statistical treatment of linear stochastic difference equations," Econometrica, vol. 11, pp. 173-200, 1943.

J. S. White, The limiting distribution of the serial correlation coefficient in the explosive case," The Annals of Mathematical Statistics, vol. 29, pp. 1188-1197, Dec 1958.

I would also recommend reading

E. T. Jaynes, Probability Theory: The Language of Science. Cambridge: Cambridge University Press, 2003.

EDIT

The reason I said that it would be very challenging to do in Excel has to do with limitations of the product. While Excel is wonderful for what it was designed to do, it has design limitations that should impact its use here. There is a field called "compuational algebra." All computational software has built in limits.

Excel's will likely be binding. Computational algebra answers questions such as how to solve $1/3\times{3}$. In computational terms this is not equivalent to $3\times{1}/3$. $1/3$ is .3 repeating. Times 3 it is .9 repeating. $3\times{1}$ is 3, divided by 3 is 1 and not .9 repeating.

Visual Basic in Excel isn't designed for this. It is probable that using Visual Basic would cause the programmer to spend a lot of time firefighting.

EDIT The only necessary mathematical assumptions to arrive at the likelihood is that there are many potential buyers and many potential sellers and that stocks are sold in a double auction. There is an additional constraint imposed for the limitation of liability. Nothing in that is controversial. The solution meets de Finetti's requirement of exchangeability. As a result, path dependence or path independence is an irrelevant question.

EDIT For the log-log model, you cannot assume normality because it is not the correct likelihood function. It is what is done, but it is also not correct. Again, read above papers. If you have removed mergers and bankruptcies, then the likelihood function is $$\frac{1}{2\gamma}\text{sech}\left[\frac{\pi}{2}\left(\frac{\log(p_{t+1})-\log(p_t)-R}{\gamma}\right)\right]$$

You do not want to multilply the variance by 250 and take the square root. It is not clear in this likelihood function that there is sufficient independence for that to be valid. Instead, make $p_{t+1}$ one year, or two years or whatever into the future. If there is no trade on the exact date, such as would be the case if it ended on a weeekend or holiday, adjust the return for the slight difference in dates. For example, if it were 363 days later, then in logs multiply it by 365 and divide by 363. That would be close.

If you use a Frequentist method with logs, then you will overestimate annual returns by two percent and underestimate the standard deviation by four percent. There is a theoretical flaw in using the standard Frequentist solution. I know this because I compared these for all end of day trades from 1925-2013 in the CRSP universe.

If you have no experience with Bayesian methods, then you should read William Bolstad's book "Introduction to Bayesian Statistics, 3rd Edition."

I am sorry that you are being caught up in the transition from a math that was problematic to the correct math, but that is what is about to happen to lots of people.

## Answer by xgg (score 1)

https://quant.stackexchange.com/a/35415

> Hi All, I have been reading for days trying to get to the bottom of this question, however I don’t have a solid enough math background to reach the answer. I want to simulate the performance of an asset and I am assuming it is normally distributed and it will behave in a similar manner in the future. 1-) Take the daily changes in an assets price 2-) Apply natural log on those price (LN) 3-) Calculate the Average and Sd of (2) 4) Multiple the average by 250 to arrive at the annual expected return and Sd by Sqrt(250) to arrive at annual volatility If I am running a simulation with annual periods, how do I correctly use the data from step 4. If I draw value from a distribution with the return and volatility from step 4. Should I apply exponential to that randomly generated before using it in the simulation? Thanks!

As you have done already in step 4, that's the annualized volatility and return. You are assuming ln(s1/s0) ~ N(mu, sigma), so s1 = s0*exp(N(mu, sigma)). exp() ensures s1 will never be less than $0.