 |
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 |
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 |
| Receive
an email whenever a new post is added to this thread by subscribing
to it. |
|
|
|
|
