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?


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/


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

function Covered() { 


d1=lnpq / vt;


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




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

  • $\begingroup$ Thanks Aleksey! That's a bit longer then one line. But should work if no simpler solution exist. $\endgroup$
    – Vtech
    Commented Jan 31, 2013 at 18:01
  • $\begingroup$ 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. $\endgroup$ Commented Jan 31, 2013 at 18:24
  • $\begingroup$ 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? $\endgroup$
    – Vtech
    Commented Jan 31, 2013 at 19:38
  • 1
    $\begingroup$ @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. $\endgroup$ Commented Jan 31, 2013 at 22:56
  • 2
    $\begingroup$ :) Just set the form.volatility.value to your IV value. No additional changes required. I mean this code doesn't care if you use IV or historical vol. IV and historical vol are just 2 methods to get estimate true volatility of the stock. $\endgroup$ Commented Feb 1, 2013 at 13:20

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

  • 1
    $\begingroup$ Why negative votes? This is a great shortcut calculation. $\endgroup$
    – baerrus
    Commented May 2, 2014 at 18:51

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.


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.


Isn't the option's delta a close approximation for the probability the option will be in the money?


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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