Thread: calculate forward price range based on historic volatility

I am an absolute Math Dummy but I'm sure there is a solution for my issue, but I didn't find any in the net. I would like to calculate the potential forward price for an equity by knowing what todays equity-price is and what the historic volatility of the last 30 days is. Assuming that the stock-price is currently at 100 and the 30day volatility is 10%, what would be the price-assumption for day1, day2, day3.... as each following day the spread of the price gets bigger, I assume the chart has to look like




or will it look like
http://qvmgroup.com/invest/wp-conten...ilityCones.png
because here the cone looks much different then I assume.


Any idea of a formula that would work in Excel? I think it is correct that I only go forward with the price the many days as the historic volatility is = 30day vola = 30day price assumption.

Thx in advance for any input
rgds

PS: I tried in the attacheded Excel, but I calculated 30d Volatility for the lower band of the forward pricing that gives me a much higher Vola then the historic vola is, shouldn't this be same at the end?

Hey ColumA.

In finance we have what is called a Martingale approach.

Basically we say that all the information for potential future prices is contained in the current existing price and also (and this is the Martingale part) that the conditional expectation for later prices only depends on the current price.

You also have option pricing which takes a very similar approach but the volatility is captured in what is called a Brownian Motion or Wiener process.

If you have volatility information, then what you can do is simulate the process so many thousands of times and then get the average path for the amount of time simulated and this can be used to see the behavior of the process under the Wiener/Brownian motion process assumptions.

For this you will need to simulate from a Normal distribution and in Excel this is given by the function NORMDIST:

An Introduction to Excel's Normal Distribution Functions

So what you would do is calculate say 5000 simulations for each tick and then get the average and standard deviation of each tick and plot that if you wanted to.

Also this model is a simple one and if you have other assumptions or extra information, then it may not be adequate at all.

What kind of tick size were you thinking of?

Hi chiro

What kind of extra information are you thinking of? Is ex-dividend within the coming 30 days I would like to simulate one of them, and if yes, I should take this into account on/after ex-date i assume.

I read the Introduction and understood most (I'm Swiss) by the meaning and imagine that I add the ,Gausche Glockenkurve, at the end of the historic price which comes close to the clone in the gold-chart.

As I would like to simulate couple of hundreds equities each day, I do not want to take care on each specific ticksize and therefore would go for 0.01. To simulate 5000 on each tick shouldn't be a problem as I am programming on SQL, but Excel is a good way for me to see/get the way and it should be easy to implement later by VBA into SQL db

you mean like my modified xls? How do you go for 30 days or is this not needed?