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/

Bard1970 wrote:

What I want to do now is add a right hand side calculator [...]. So basically work out Columns k & J in the Excel sheet above. [....] I'm not sure what the formula would be?

I suggest the following. See the "std dev prob (2)" worksheet in "optn prob calc.xlsx" (click here).

Formulas:

I7: =I3*I4*I5/I6

J10: =$I$3 - $I$7*NORMSINV(I10 + (1 - I10)/2)

K10: =$I$3 + $I$7*NORMSINV(I10 + (1 - I10)/2)

I16: =NORMSDIST(2) - NORMSDIST(-2)

I22: =NORMSDIST(1) - NORMSDIST(-1)

N10: =NORMSINV(I10 + (1 - I10)/2)

Note: The formulas in column N (and O and P, not shown) are not necessary.

For a given probability, the price limits are calculated by u +/- z*sd, where "u" is the stock price in I3, and "sd" is the std dev in I7.

The probabiity (column I) should be a middle region of the normal distribution curve. Thus, the +/-z corresponding to the limits are calculated by formulas similar to N10. For example, for 90%, the limits correspond to +z at 95% and -z at 5%.

To that end, I corrected the calculations of the probabilities for 1sd and 2sd in I22 and I16.

I also corrected the probability in I10. We cannot have a probability that is truly 100%. But +/-8sd is very close, namely: 99.9999999999999% (which actually corresponds to +/-7.99sd). And I added a few more probabilities in I11:I13. All of these additions are calculated in a manner similar to I16.

(Also note that you calculated 1sd and 2sd incorrectly in your L13 and L19. You reversed them. No matter: I eliminated the calculations.)

The DTE std dev is approximated in I7 the same way that you calculate it C8, namely: price (I3) times implied volatility (I4) times SQRT(DTE/252), which is I5/I6, based on the square-root-of-time rule.

Aside.... See my comments for L5. Since 252 is the number of trade days in a typical year, it seems that DTE should be 10, the number of trade days between 11/10 and 11/26 excluding Veteran's Day and Thanksgiving. OTOH, some people calculate the DTE sd based on DTE/365 (for example, click here). In that case, it seems that DTE should be 16, the number of calendar days. In your left-hand table, you seem to be inconsistent, using SQRT(DTE/252) in C8, but SQRT(DTE/365) in D9.

PS.... On second thought, 99.44% in I14 (your I11) seems like an unusual number. It corresponds to about 2.77sd (N14). Perhaps it is a misguided attempt to represent 3sd, which I calculate correctly in I13; in that case, delete row 14. Alternatively, use a formula like I16 to calculate the correct percentage for the intended z-score(?); for example (but unlikely):

=NORMSDIST(2.77) - NORMSDIST(-2.77) .

Aside....

Please image from CBOE website below.

I wish you had provided the URL for the origin of the image. The table seems incorrect.

As I explained above, The probabiity (column I) should be a *middle region* of the normal distribution curve.

There is insufficient information in the CBOE table to confirm that.

But the limits for 50% obviously do not correspond to the middle 50%, which is between 25% and 75%.

Instead, since the limits are the same, I would presume they are the price that the table is based on.

However, that conclusion is inconsistent with other entries in the table. See the calculations in columns K:R of the "cboe table" worksheet.

In lieu of the price and std dev that the CBOE table is based on, I used Solver to find a price and std dev that minimizes the differences in the upper limits. See B15 and B16.

And based on that, my "corrected" table is in columns A:E, FWIW.

Bard1970 wrote:

What I want to do now is add a right hand side calculator [...]. So basically work out Columns k & J in the Excel sheet above. [....] I'm not sure what the formula would be?

I suggest the following. See the "std dev prob (2)" worksheet in "optn prob calc.xlsx" (click here).

Formulas:

I7: =I3*I4*I5/I6

J10: =$I$3 - $I$7*NORMSINV(I10 + (1 - I10)/2)

K10: =$I$3 + $I$7*NORMSINV(I10 + (1 - I10)/2)

I16: =NORMSDIST(2) - NORMSDIST(-2)

I22: =NORMSDIST(1) - NORMSDIST(-1)

N10: =NORMSINV(I10 + (1 - I10)/2)

Note: The formulas in column N (and O and P, not shown) are not necessary.

For a given probability, the price limits are calculated by u +/- z*sd, where "u" is the stock price in I3, and "sd" is the std dev in I7.

The probabiity (column I) should be a middle region of the normal distribution curve. Thus, the +/-z corresponding to the limits are calculated by formulas similar to N10. For example, for 90%, the limits correspond to +z at 95% and -z at 5%.

To that end, I corrected the calculations of the probabilities for 1sd and 2sd in I22 and I16.

I also corrected the probability in I10. We cannot have a probability that is truly 100%. But +/-8sd is very close, namely: 99.9999999999999% (which actually corresponds to +/-7.99sd). And I added a few more probabilities in I11:I13. All of these additions are calculated in a manner similar to I16.

(Also note that you calculated 1sd and 2sd incorrectly in your L13 and L19. You reversed them. No matter: I eliminated the calculations.)

The DTE std dev is approximated in I7 the same way that you calculate it C8, namely: price (I3) times implied volatility (I4) times SQRT(DTE/252), which is I5/I6, based on the square-root-of-time rule.

Aside.... See my comments for L5. Since 252 is the number of trade days in a typical year, it seems that DTE should be 10, the number of trade days between 11/10 and 11/26 excluding Veteran's Day and Thanksgiving. OTOH, some people calculate the DTE sd based on DTE/365 (for example, click here). In that case, it seems that DTE should be 16, the number of calendar days. In your left-hand table, you seem to be inconsistent, using SQRT(DTE/252) in C8, but SQRT(DTE/365) in D9.

PS.... On second thought, 99.44% in I14 (your I11) seems like an unusual number. It corresponds to about 2.77sd (N14). Perhaps it is a misguided attempt to represent 3sd, which I calculate correctly in I13; in that case, delete row 14. Alternatively, use a formula like I16 to calculate the correct percentage for the intended z-score(?); for example (but unlikely):

=NORMSDIST(2.77) - NORMSDIST(-2.77) .

Aside....

Please image from CBOE website below.

I wish you had provided the URL for the origin of the image. The table seems incorrect.

As I explained above, The probabiity (column I) should be a *middle region* of the normal distribution curve.

There is insufficient information in the CBOE table to confirm that.

But the limits for 50% obviously do not correspond to the middle 50%, which is between 25% and 75%.

Instead, since the limits are the same, I would presume they are the price that the table is based on.

However, that conclusion is inconsistent with other entries in the table. See the calculations in columns K:R of the "cboe table" worksheet.

In lieu of the price and std dev that the CBOE table is based on, I used Solver to find a price and std dev that minimizes the differences in the upper limits. See B15 and B16.

And based on that, my "corrected" table is in columns A:E, FWIW.