Your Account  •  Become a Member  •  Help  •  Search
 Forums ›› Main ›› Options ›› Calculating option strike by inputting delta.

 Page 1 of 2:   1  2
 as586   Registered: Mar 2012 Posts: 3 03-21-12 01:30 PM I am trying to create a formula in Excel which allows me to calculate an options strike by inputting a delta % (as well as tenor, future price, p/c, vol and i/r). Im using the black 76 model as I am trying to price options on base metals. The purpose is to speed the process of finding strikes for, as an example, 25 delta risk reversals, which at the moment I am simply calculating through trial and error. Any help would be appreciated. Thanks in advance Edit/Delete • Quote • Complain
 sle   Registered: Apr 2003 Posts: 1610 03-21-12 02:41 PM Dude, it's just a matter of backing it out of the formula for delta: delta(call) = exp(-rf*Time) * pnorm(d1v) delta(put) = exp(-rf*Time) * pnorm(d1v) - 1 d1 = (log(spot/strike)+(rf+(sigma^2)/2)*Time)/(sigma*sqrt(Time)) so, solve for d1 first: d1(call) = qnorm(delta/exp(-rf*Time)) d1(put) = qnorm(delta/exp(-rf*Time) + 1) and then solve for the strike: strike = exp( d1 - (rf+(sigma^2)/2)*Time)/(sigma*sqrt(Time) ) Edit/Delete • Quote • Complain
 EquityGuy4321   Registered: Sep 2004 Posts: 182 03-21-12 02:46 PM Get the formula for delta and just rearrange to solve for strike given delta and all other inputs. A google search might get you the formula if you don't know enough math to do this yourself. However, I think you need something that calculates an inverse cdf, which I'm not sure you can do in Excel. Edit - lucky dude...sle was a good guy and gave you the info. Edit/Delete • Quote • Complain
 as586   Registered: Mar 2012 Posts: 3 03-21-12 05:57 PM OK thanks very much. My maths isnt great so could you pls clarify.. For the d1(call), what do you mean by qnorm? Is that the inverse of N, which i could use in excel with the function NORMINV? And where would I input the future price? Edit/Delete • Quote • Complain
 MasterAtWork   Registered: Jul 2008 Posts: 298 03-24-12 04:17 AM You would need to know implied volatility for a given delta. If you can't get it, you won't be able to use the previous formula. Edit/Delete • Quote • Complain
 comintel   Registered: Jun 2008 Posts: 1113 02-15-13 07:00 AM I was looking for the same thing and found this thread helpful, as I am sure others will in the future. I also found: http://www.quantessential.com/messa...&threadid=24601 Here is my working R code in case anyone needs/likes it in this particular form: BSStrikeFromDelta <- function(S0, T, r, sigma, delta, right) { strike <- ifelse(right=="C", S0 * exp(-qnorm(delta * exp((r)*T) ) * sigma * sqrt(T) + ((sigma^2)/2) * T), S0 * exp(qnorm(delta* exp((r)*T) ) * sigma * sqrt(T) + ((sigma^2)/2) * T)) return( strike); } Obviously it is exactly the same approach kindly suggested earlier by sle. Edit/Delete • Quote • Complain
 Page 1 of 2:   1  2