IV Calculation

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

 

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/

 

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).

 

quantlib excell addin

 

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;
};

 

http://www.google.com/search?q=blac...s=org.mozilla:en-US:official&client=firefox-a

http://www.google.com/search?q=impl...s=org.mozilla:en-US:official&client=firefox-a

 

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

 

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.

 

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

 

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