Formula Transformation Help Requested.

[steveal]

I have a spreadsheet which calculates stock option probabilities.

Amongst the formulae is the following entry into a cell to calculate the new stock price for an X percent probability move:

New_Price = EXP(NORMSINV(X)*((B3/100)*SQRT(B5/365)))*B2

Where:
B2 is the current stock price
B3 is the stock volatility and
B5 is the number of days to option expiration

I want to transpose the formula to calculate X, the probability of touching the new stock price.
Can anyone help?

Stephen

[sandy666]

Maybe:

Please Login or Register  to view this content.

[steveal]

Sandy,

Unfortunately, The brackets seem wrong. The formula works if I delete your last closing bracket.
But then the result is always 100%.

[sandy666]

Right, last bracket was wrong, but

EXP() <=> LN()
NORMSINV(p) = z
NORMSDIST(z) = p
rest is a math

try:

Please Login or Register  to view this content.

where random values:
B2=2
B3=3
B5=4
New_Price=5
result: 0.999809091

[steveal]

Sandy,

Got it! Thanks for the lead. Here's what worked:

=NORMSDIST((LN(New_Price/B2))/((B3/100)*SQRT(B5/365)))

Stephen

[sandy666]

If it's working well (with correct values) that's ok
You are welcome.