Using Excel and via iteration, to calculate implied I compare the market price with a theoretical price. When there is convergence, I have the implied. The method has room for improvement but I don't know how to set up the formula. Perhaps someone can help? Currently, I start with an initial implied, eg 10%. If the theoretical is less than the market price, the implied is increased by eg, 0.01%, and vice versa (that's Latin) . The procedure works but adjusting increments (by 0.01%) seems somewhat tortuous. An improvement could be something like this: Current: Start with initial implied. If theoretical is less than market, increase implied by X% If theoretical is greater than market, decrease implied by X% Modify to: Start with initial implied. A) If theoretical is less than market, increase implied by X% (50%?) B) If theoretical is greater than market, decrease implied by X% (50%?) Following A) If theoretical is greater than market, decrease implied by 50% of the initial increase Otherwise increase by 50% of initial Following B), If theoretical is less than market, increase implied by 50% of the initial increase Otherwise decrease by 50% of initial This loop is repeated until convergence. The main point however is that the constant 50% adjustment leads to a more rapid convergence. Unfortunately, I can't work out how to reproduce this directly on Excel, ie in the cells, rather than VBA. However, if a VBA solution can be readily implemented then it will certainly be considered. Please note: Personally, VBA is as legible as Aramaic so please be precise. Thank you for any advice. Grant.
For those interested all that functionality and much much more is available in http://www.hoadley.net/options/calculators.htm It's less fun maybe to buy it, but at least it'll work tomorrow Ursa..
FWIW you can check your results against the implied volatility posted online at ivolatility.com. For example, this is for CVS: http://www.ivolatility.com/options.j?ticker=CVS&R=0&top_lookup__is__sent=1
Here is the method of bisection for finding IV in C++ by Bernt Odegaard. #include <cmath> #include "fin_recipes.h" double option price implied volatility call black scholes bisections(const double& S, const double& K, const double& r, const double& time, const double& option price){ if (option price<0.99*(S−K*exp(−time*r))) { // check for arbitrage violations. return 0.0; // Option price is too low if this happens }; // simple binomial search for the implied volatility. // relies on the value of the option increasing in volatility const double ACCURACY = 1.0e−5; // make this smaller for higher accuracy const int MAX ITERATIONS = 100; const double HIGH VALUE = 1e10; const double ERROR = −1e40; // want to bracket sigma. rst nd a maximum sigma by nding a sigma // with a estimated price higher than the actual price. 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; // keep doubling. price = option price call black scholes(S,K,r,sigma high,time); if (sigma high>HIGH VALUE) return ERROR; // panic, something wrong. }; 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; };
Just wondering. Do any of you all pay for ivolatility services or the free service is enough for you. Thanks
MajorUrsa and Stoxtrader, Thank you for the references. Iâve done a basic check, and will examine thoroughly in due course. From all the brief, but invaluable, suggestions so far I am somewhat overwhelmed by material. However, if knowledge is strength⦠Stoxtrader, I appreciate the trouble taken to provide your solution. Thank you. Grant.