IV Calculation

Discussion in 'Options' started by spindr0, Jun 7, 2010.

  1. Anyone know of a source for an Excel spreadsheet (free) that calcualtes IV given all other inputs? TIA
     
  2. Nanook

    Nanook

    Not sure if this meets your request but there is a spreadsheet named "implied_volatility.xls" in a long list of free financial spreadsheets:

    http://www.gummy-stuff.org/Excel/
     
  3. That would be sorta what I'm looking for. However, they get an IV of 14.5 for their example whereas my BS formula in a spreadsheet and a stand alone program indicate that it's more like 22 (scratching head).
     
  4. rosy2

    rosy2

    quantlib excell addin
     
  5. Here is a C++ implementation of the Method of Bisections, by Bernt Odegaard.

    #include <cmath>
    #include "fin_recipes.h"
    double option price implied volatility call black scholes bisections(constdouble& S,
    constdouble& K,
    constdouble& r,
    constdouble& time,
    constdouble& option price){
    if (option price<0.99*(S K*exp( time*r))){ //checkforarbitrageviolations.
    return 0.0; //Optionpriceistoolowifthishappens
    };
    //simplebinomialsearchfortheimpliedvolatility.
    //reliesonthevalueoftheoptionincreasinginvolatility
    constdouble ACCURACY =1.0e 5; //makethissmallerforhigheraccuracy
    constint MAX ITERATIONS =100;
    constdouble HIGH VALUE =1e10;
    constdouble ERROR = 1e40;
    //wanttobracketsigma.rstndamaximumsigmabyndingasigma
    //withaestimatedpricehigherthantheactualprice.
    double sigma low=1e 5;
    double sigma high=0.3;
    double price = option price call black scholes(S,K,r,sigma high,time);
    while (price < option price){
    sigma high =2.0* sigma high; //keepdoubling.
    price = option price call black scholes(S,K,r,sigma high,time);
    if (sigma high>HIGH VALUE) return ERROR; //panic,somethingwrong.
    };
    for (int i=0;i<MAX ITERATIONS;i++){
    double sigma =(sigma low+sigma high)*0.5;
    price = option price call black scholes(S,K,r,sigma,time);
    double test =(price option price);
    if (fabs(test)<ACCURACY){ return sigma;};
    if (test < 0.0){ sigma low = sigma;}
    else { sigma high = sigma;}
    };
    return ERROR;
    };
     
  6. Bob111

    Bob111

  7. stoic

    stoic

    Excel VBA IV Function.

    Function ImpliedCallVolatility(UnderlyingPrice, ExercisePrice, Time, Interest, Target, Dividend)
    High = 5
    Low = 0
    Do While (High - Low) > 0.0001
    If CallOption(UnderlyingPrice, ExercisePrice, Time, Interest, (High + Low) / 2, Dividend) > Target Then
    High = (High + Low) / 2
    Else: Low = (High + Low) / 2
    End If
    Loop
    ImpliedCallVolatility = (High + Low) / 2
    End Function
    '===========================================
    Function ImpliedPutVolatility(UnderlyingPrice, ExercisePrice, Time, Interest, Target, Dividend)
    High = 5
    Low = 0
    Do While (High - Low) > 0.0001
    If PutOption(UnderlyingPrice, ExercisePrice, Time, Interest, (High + Low) / 2, Dividend) > Target Then
    High = (High + Low) / 2
    Else: Low = (High + Low) / 2
    End If
    Loop
    ImpliedPutVolatility = (High + Low) / 2
    End Function
     
  8. Thanks for the suggestions. I've looked at a lot of links but haven't found exactly what I'm after yet. It's out there somewhere.
     
  9. Have you seen this here, spindr0? There's a bit of VBA code (it's on the second page and written by none other than Collector) that implements the Newton-Raphson method.
    http://www.wilmott.com/messageview.cfm?catid=4&threadid=3656
     
  10. Carl K

    Carl K

    Many functions to look through here
    http://www.smartxl.com/

    "The Collector" has lots of functions on his site,
    but takes time to find them on the site.
    http://www.espenhaug.com/

    Carl
     
    #10     Jun 8, 2010