I'm trying to setup a little option pricing model in excel. I have all the information for the inputs (interest rate, IVs for different deltas, time to expiry, strike price, underlying price) but what I do not have is the actual delta for which I should be drawing the IVs from. To clarify, I have a list of IVs for each possible delta you want for the underlying security. But it's as if you're in an endless loop because you don't know what IV list to choose since it's linked to a specific delta: which you do not know.

Attaching a Delta formula in case it helps anyone visualize things.

I do not have the actual call prices since I'm dealing with illiquid cases and the end goal is to calculate those prices.


Black Sholes Delta formula

  • $\begingroup$ This image looks familiar 😁😁 $\endgroup$ – Sanjay Aug 23 '19 at 15:29
  • 1
    $\begingroup$ Anyway. Welcome to Quant stackexchange. To me, it is unclear what exactly you’re asking about. Consider editing your question with more precise formulation. $\endgroup$ – Sanjay Aug 23 '19 at 15:34
  • $\begingroup$ I find it hard to understand what information you have and what information you are trying to compute. Generally untraded options don't have an IV. You would have to use the historical volatility (HV) for the underlying stock. Do you have a price history for the underlying stock? $\endgroup$ – Alex C Aug 23 '19 at 15:38
  • 1
    $\begingroup$ Does it relate to FX options where the IV is quoted by delta? $\endgroup$ – Magic is in the chain Aug 23 '19 at 16:39
  • $\begingroup$ @Magicisinthechain, it's not FX, the underlying are commodity futures, so to be treaded simply as any non-dividend paying futures $\endgroup$ – Andrej Aug 23 '19 at 17:06

If I understand the question correctly, you have the implied vol by delta, and you would like to calculate the price using the Black Scholes formula. And I assume you know the other inputs-e.g., underlying price, interest rate and maturity. Very typical problem in the FX world, so what you can do is first convert delta (using the other inputs and vol) to strike, and then you have vol by strike, which you can then plug into the BS to get the price. The delta comes in different shades, but if it is the simple unadjusted delta, then you can easily isolate strike on one side:


$d_1=N^{-1} \left(\Delta\right)$

Then substitute the expression for $d_1$ and solve for K.

$K=S e^{\left(r +0.5\sigma^2\right)T-\sigma \sqrt{T}N^{-1}\left(\Delta\right)}$

Now you have vol by strike, which you can use with the other inputs to calculate the price.

  • $\begingroup$ More details on how this work in FX on this page quantpie.co.uk/fx/strikes_given_vol_delta.php that I contributed to. $\endgroup$ – Magic is in the chain Aug 23 '19 at 17:27
  • $\begingroup$ Ah I see so that I'd have a mapping by strike price rather than a mapping by delta, and then come back to it afterwards. Thanks a lot $\endgroup$ – Andrej Aug 23 '19 at 18:39
  • $\begingroup$ Indeed, and the reason this style is used is because underlying is moving all the time so delta provides a better reference point. Once all the terms have been agreed then you can base the option on the most recent market reference point. $\endgroup$ – Magic is in the chain Aug 23 '19 at 19:03
  • $\begingroup$ This conversion assumes a sticky strike. Given that the vols are being quoted in delta, this is probably not correct. $\endgroup$ – will Aug 24 '19 at 8:57
  • $\begingroup$ Thanks @will. Do you mind highlighting which part need changing please? I thought the question was about simple conversion rather than smile modelling, as the smile is already given. $\endgroup$ – Magic is in the chain Aug 24 '19 at 12:46

Not the answer you're looking for? Browse other questions tagged or ask your own question.