Results 1 to 4 of 4

Math Help - calculate forward price range based on historic volatility

  1. #1
    Newbie
    Joined
    Dec 2012
    From
    ZH
    Posts
    3

    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?
    Attached Files Attached Files
    Follow Math Help Forum on Facebook and Google+

  2. #2
    MHF Contributor
    Joined
    Sep 2012
    From
    Australia
    Posts
    3,837
    Thanks
    676

    Re: calculate forward price range based on historic volatility

    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?
    Follow Math Help Forum on Facebook and Google+

  3. #3
    Newbie
    Joined
    Dec 2012
    From
    ZH
    Posts
    3

    Re: calculate forward price range based on historic volatility

    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
    Follow Math Help Forum on Facebook and Google+

  4. #4
    Newbie
    Joined
    Dec 2012
    From
    ZH
    Posts
    3

    Re: calculate forward price range based on historic volatility

    you mean like my modified xls? How do you go for 30 days or is this not needed?
    Attached Files Attached Files
    Follow Math Help Forum on Facebook and Google+

Similar Math Help Forum Discussions

  1. Calculate the Price of a Bond
    Posted in the Business Math Forum
    Replies: 2
    Last Post: September 14th 2011, 01:05 AM
  2. Delivery Price of a Forward Contract
    Posted in the Business Math Forum
    Replies: 4
    Last Post: March 23rd 2011, 11:52 PM
  3. Calculate output power based on speed
    Posted in the Advanced Applied Math Forum
    Replies: 3
    Last Post: December 31st 2009, 08:55 AM
  4. Replies: 1
    Last Post: April 30th 2009, 01:28 AM

Search Tags


/mathhelpforum @mathhelpforum