Distribution of Stock Returns and Expected Return of Trade

[DrZ]

I am trying to come up with the expected value of a trade I have on right now. The payoff is dependent on the price of 1 underlying stock.

I have the current stock price and the surrounding +/- 65% prices (in 1% incremens) the 1st column and the corresponding payoffs of the trade in the 2nd column.

I would like to have the probability that the stock will close at each of the prices in column 1 in the 3rd column so I could calculate the expected return of the trade using a SUMPRODUCT command on columns 2 and 3.

So, given the stock's average return and standard deviation of returns, how can I model the probabilities using Excel? I was thinking about trying to use NORMDIST but when I try it, the total probabililty exceeds 100%. I must be doing something wrong. (It has been a while since I took stats!)

TIA

 

[Tsotne]

So, given the stock's average return and standard deviation of returns, how can I model the probabilities using Excel? I was thinking about trying to use NORMDIST but when I try it, the total probabililty exceeds 100%.

No way. You are passing some illogical combination of values then. NORMDIST values are strictly within the range of 0-1. Think of it in integral manner. I'd like to have that example which you are practicing and I can do it with pleasure. Seems very easy. Just something is wrong.

 

[DrZ]

Thanks for your reply.

Yes thats what I thought so isn't there some way to normalize the variable and use the Z Score or something?

Here is a simplified spreadsheet with what I would like to do. Thanks again for your help!

 

[euroazn]

This is a perfectly irrelevant question since assuming stock prices follow normal distributions = death. Log-normal is slightly better.

 

[DrZ]

Good point. Ok so either log-normal or t-distribution or whatever is best then.

Thx

 

[Tsotne]

Lognormally distributed stock prices

This is a perfectly irrelevant question since assuming stock prices follow normal distributions = death. Log-normal is slightly better.

Right. Here I checked for the Lognormal distribution by the Chi-Square test and couldn't reject Lognormality. BTW, in such cases when the number of observations is very small as it was here, it brings some difficulties to construct a histogram. I only was able to follow the simple rule to calculate the number of bins. 3 in this case but since the maximum stock price is 50 you can safely drop the third one. I didn't do though since after result it didn't change anything at all. In case of financial data you can simply follow the intuition which distribution to check for initially and here as we dealt with stick prices I checked for lognormality. Although you couldn't reject the normality from the mathematical point of view if you used the Chi-square method to check the hypothesis for normality. But it is cleat from the above quote that assuming the normality of stock prices is not correct. I didn't concentrate mainly on the financial issue. Since I only did the mathematical calculation and the result was obtained. But in case of real situation I wouldn't be very reliable to the the data provided since the number of observations is very low creating some difficulties when modeling the data.

 

[M Souto]

If you do the Jarque-Bera test for normality, you will realize that most of the time returns do not follow a normal distribution or lognormal. Mostly because of some fat-tail problem, this can be proved by calculating kurtosis.
In a case of fat-tail distribution, t-student fits better.

 

[Tsotne]

If you do the Jarque-Bera test for normality, you will realize that most of the time returns do not follow a normal distribution or lognormal. Mostly because of some fat-tail problem, this can be proved by calculating kurtosis.
In a case of fat-tail distribution, t-student fits better.

Jarque-Bera is like a quick-test just for testing NORMALITY. I'm talking about the Chi-Square test, which itself is not the unique and standard measurer of the distribution. Kolmogorov has defined (as I remember) some tests for checking the distribution but seems more complex and are for very limited cases.

 

[DrZ]

I think that there may have been a misunderstanding. I did not upload any historical data. The Excel spreadsheet that I uploaded was to show the ouput that I want. I have modified the spreadsheet to include a wider range of stock values to estimate probabilities for and also to include historical data of the stock I am interested in from Yahoo!Finance.