General Topics
Technical Topics
Brokerage Firms
Community Lounge
Site Support

# IV Calculation

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

1. ### spindr0

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

2. ### 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. ### spindr0

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

5. ### panzerman

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.rstndamaximumsigmabyndingasigma
//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;
};

7. ### 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. ### spindr0

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. ### Martinghoul

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.

10. ### 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
ET IS FREE BECAUSE OF THE FINANCIAL SUPPORT FROM THESE COMPANIES: