# 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.

• What are you trying to produce with this? A distribution over future stock price values? Jul 28, 2017 at 19:53
• Correct, the ultimate tool is a retirement planning tool. However in this simple sheet that would be the goal. To have a sound distribution of future prices. Jul 28, 2017 at 19:56
• Something incredibly convenient/simple is to assume log returns $r_{t \rightarrow t+1} = \log ( 1 + R_{t \rightarrow t+1})$ are normally distributed $\mathcal{N}(\mu, \sigma^2)$. Then $r_{t \rightarrow t+k} \sim \mathcal{N}(k \mu, k \sigma^2)$. Exponentiate (i.e. $\frac{P_{t+k}}{P_t} = e^{r_{t \rightarrow t + k}}$) to get prices. But this will suffer from the same problem I mentioned earlier. Jul 28, 2017 at 20:07
• Honestly, to make this at all realistic, you really want to use the empirical distribution over actual market portfolio returns for a very long time frame. Jul 28, 2017 at 21:08
• The link says: "This item might not exist or is no longer available" Jul 31, 2017 at 14:44

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.

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.

• I do not entirely agree with the tone of your answer here. "It will be insanely challenging to do this via Excel... I would recommend Python or C" This is subjective - Python and C are two entirely different languages, and it is realyl easy to argue that it would be very challenging for someone who has never used either of them to use them here... Perhaps it's easy for you. Additionally, they are not looking to simulate some path dependant instrument, and you can't assume the distribution and match the market. Easily the most sane way to do this is mentioned in @MatthewGunn's comment above.
– will
Jul 31, 2017 at 16:34
• R would be another natural choice. Jul 31, 2017 at 17:41
• @will you apparently did not properly read my post. Clarifications are added. The only mathematically admissible solution is above. Per the Wald complete class theorem and from the results on sufficiency in Jaynes listed above, this is it. If you accept that cash flows are fungible, there is nothing wrong with this post. Aug 1, 2017 at 1:15
• @alexTaha I updated the answer to include the log-log case. It isn't fair that you are in a transition period. If you look at the link and download the papers, you can get my e-mail. If you need additional assistance, let me know. I am in a geographically isolated location at the moment and do not always have access to the Internet. I am more likely at the moment to come face to face with a bear than a computer. Aug 1, 2017 at 1:44
• @DaveHarris is this method able to accurately replicate all the option prices?
– will
Aug 1, 2017 at 9:47

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.

• Given that it will take me a while to read the papers and decide on an approach, would you say assuming normal distribution would make for a useless tool? I need something for the time being, current tool plans for clients based on fixed rate and i feel introducing and distribution still helps as it is more realistic Aug 7, 2017 at 6:50