Take the 2-minute tour ×
Quantitative Finance Stack Exchange is a question and answer site for finance professionals and academics. It's 100% free, no registration required.

I am looking for one line formula ideally in Excel to calculate stock move probability based on option implied volatility and time to expiration?

I have already found a few complex samples which took a full page of data to calculate. Is it possible to simplify this calculation in one line formula with the following variables:

  1. Current stock price
  2. Target Target Price
  3. Calendar Days Remaining
  4. Percent Annual Volatility
  5. Dividend=0, Interest Rate=2%
  6. Random value to get something similar to Monte Carlo model?

I need these results:

  1. Probability of stock being above Target Price in %
  2. Probability of stock being below Target Price in %

similar to optionstrategist.com/calculators/probability

Any recommendations?

share|improve this question

6 Answers 6

If you use a risk-neutral pricing model and consider the probability there, then you get the probability with respect to a risk neutral measure, in addition that probability depends on the chosen numeraire. For example, in Black-Scholes model taking the risk-neutral measure with respect to the bank account $B$ gives

$$P(S(T)<K) = Q^{B}(S(T)<K) = \Phi(d_{-})$$

and taking the risk-neutral measure with respect to the asset $S$ you get

$$P(S(T)<K) = Q^{S}(S(T)<K) = \Phi(d_{+})$$

If you like to have a real world probability you have to consider the market price of risk and a real estimate for the volatility (not the implied one). Both are not listed in your parameters. If you like to get this probability use the first formula, but replace the interest rate $r$ with the drift of the stock (which contains the market price of risk) and the implied volatility with an appropriate estimate (you might consider historic volatility or assume that implied vol is an appropriate estimate or have a different view).

Since you mentioned Monte-Carlo simulation: I have a spreadsheet implementing a Monte-Carlo simulation of a Black-Scholes model (using multiple time-steps). The calculation of $d_{-}$ can be found in this sheet too. The sheet is here: http://www.christian-fries.de/finmath/spreadsheets/

share|improve this answer

If you are happy with the OptionStrategist probability values, why don't just do it exactly how they do it:

function Covered() { 

form=document.callreturn;
p=form.price.value;
q=form.strike.value;
t=form.days.value/365;
v=form.volatility.value/100;

vt=v*Math.sqrt(t);
lnpq=Math.log(q/p);
d1=lnpq / vt;

y=Math.floor(1/(1+.2316419*Math.abs(d1))*100000)/100000;
z=Math.floor(.3989423*Math.exp(-((d1*d1)/2))*100000)/100000;
y5=1.330274*Math.pow(y,5);
y4=1.821256*Math.pow(y,4);
y3=1.781478*Math.pow(y,3);
y2=.356538*Math.pow(y,2);
y1=.3193815*y;
x=1-z*(y5-y4+y3-y2+y1);
x=Math.floor(x*100000)/100000;

if (d1<0) {x=1-x};

pabove=Math.floor(x*1000)/10; 
pbelow=Math.floor((1-x)*1000)/10;

form.pbelow.value=pbelow;
form.pabove.value=pabove;

}

This is JavaScript, but it should be rather straightforward to do it in Excel.

share|improve this answer
    
Thanks Aleksey! That's a bit longer then one line. But should work if no simpler solution exist. –  Vtech Jan 31 '13 at 18:01
    
Actually the calculation itself is pretty short. I think you can squeeze it in one excel formula. It's just reading values/writing them back that makes it long. –  Alexey Kalmykov Jan 31 '13 at 18:24
    
The only problem this script uses historical annual stock volatility and i would like to use option implied volatility. Do you know how to change this script to use option implied volatility rather then historical annual volatility? –  Vtech Jan 31 '13 at 19:38
    
@Vtech I think you don't need to change anything. You just need to use annualized IV rather then historical volatility. It all depends on your approach: some people prefer use historical volatility, while others believe that implied volatility is more reliable. –  Alexey Kalmykov Jan 31 '13 at 22:56
    
Да, Алексей ;) I want to use IV. Do you know how to change this script to use option implied volatility rather then historical volatility? –  Vtech Feb 1 '13 at 9:51

You can certainly calculate the probability of changes in variation but I have not come across a model that only looks at an isolated iVol and its associated term and then deriving a directional probability.

However, what you can do, and what options traders do all the time is to look at changes in skew which involves a range of implied data points. In Fx traders look at risk reversals. Also, in the short term, where trades in the option relative to the book take place has a bearing on directional probabilities. I am not gonna provide a formula, because I use some of that as part of my own business, just trying to push you into the right direction.

share|improve this answer

I think that you may be looking for $$ \mathbb{P}(S_T<K) = \frac{\partial P}{\partial K}(K) = 1 + \frac{\partial C}{\partial K}(K) $$ where $P(K)$ and $C(K)$ are the european put and call undiscounted price functions for the maturity $T$. The proof goes (roughly) as this: $$ \begin{eqnarray} \frac{\partial P}{\partial K} &=& \frac{\partial}{\partial K}\int_{0}^{\infty} (K-S_T)^+p(S_T,T,S_0,t_0) \\ &=& \int_{0}^{\infty} \frac{\partial}{\partial K}(K-S_T)^+p(S_T,T,S_0,t_0) \\ &=& \int_{0}^{\infty} \textbf{1}_{\{S_T<K\}}p(S_T,T,S_0,t_0) \\ &=& \mathbb{P}(S_T<K) . \end{eqnarray} $$ The one-line formula goes as $$ \begin{eqnarray} \frac{\partial P(K,\sigma(K))}{\partial K} &=& \frac{\partial P}{\partial K} + \frac{\partial P}{\partial \sigma}\times\frac{\partial \sigma}{\partial K} \\ &=& \Phi(d_-) + K\phi(d_-)\sqrt{T}\times\frac{\partial \sigma}{\partial K} . \end{eqnarray} $$ Where $$ d_- = \frac{\log(F_T/K) - \frac{1}{2}\sigma(K)^2T}{\sigma(K)\sqrt{T}} , $$ being $F_T$ the forward price of the stock at time $T$.

Note that if you have a flat volatility (Black-Scholes model), then the probability is simply $$ \mathbb{P}(S_T<K) = \Phi(d_-) . $$ For a market with non flat implied volatilities you still have to find the term $\frac{\partial \sigma}{\partial K}(K)$ doing some sort of interpolation/extrapolation of the volatility surface. Also, you should have to make sure that when interpolating or extrapolating you get admissible cdf's, otherwise a static arbitrage is guaranteed.

share|improve this answer

These equations gives the probability of a successful trade for a

European put finishing in the money (that is, the probability that the strike price is above the market price at maturity). European call finishing in the money (that is, the probability that the strike price is below the market price at maturity) Probability of a Successful Option Trade

enter image description here

  • N() is the normal distribution function, and is equivalent to Excel’s NORMSDIST() function
  • S is the stock price
  • X is the strike price
  • v is the implied volatility
  • T is the time to expiry

See http://investexcel.net/probability-of-a-successful-option-trade/

share|improve this answer

Not sure about all of the complicated math and programming above, but I can tell you that, if you want to calculate for 1 Standard Deviation from the current stock price X days away, the following calculation will give you a +/- value from the current stock price.

1 StdDev Move = (Stock Price X Implied Volatility X the Square Root of 'how many days') all divided by the Square Root of 365.

Add this value to the stock price for the Upper Range and subtract it for the Lower Range. This will be 68% of the expected range (which is what is considered the normal move for a stock most of the time - 1 Standard Deviation).

share|improve this answer
    
Why negative votes? This is a great shortcut calculation. –  baerrus May 2 at 18:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.