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
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) )
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.
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?
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.
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/messageview.cfm?catid=3&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.
Correction I needed to use the absolute value of delta on the put side: 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(abs(delta)* exp(r*T) ) * sigma * sqrt(T) + ((sigma^2)/2) * T)) return( strike); } By the way it works with vectors (or scalars) as input parameters.
At the start of the thread, it was mentioned that the put delta is equal to: delta(put) = exp(-rf*Time) * pnorm(d1v) - 1 However when you rearrange and take the inverse CDF of (delta+1/EXP-fT) this number delta+1/EXP-fT is greater than one in some cases which is impossible as its meant to be a probability. Is the above formula correct? i.e. is the delta put formula above the correct formula it seems to be wrong?
Calculating option strike by inputting delta https://www.dogpile.com/serp?q=Calculating+option+strike+by+inputting+delta&sc=SFa2VNuo19LZ10