Hello, I am posting this to try and figure out the correct way to solve and write the VBA function in the excel sheet provided, with the explanation provided in order to solve for the strike rather than the delta.
delta - volatility to strike conversion, rather than the volatility - strike to delta conversion that you see in the sheet attached.
The following is an explanation I found online, in which the first 3 formulas match the VBA function I already have for finding Delta.
"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) )
Here is a link to the full problem and answer:
[login to view URL]
I have made an attempt myself but I am going to leave that out this time as it is probably just confusing.
If you need further info please do ask