Implied volatility calculation.

G

Grant

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.
 
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&#8722;K*exp(&#8722;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&#8722;5; // make this smaller for higher accuracy
const int MAX ITERATIONS = 100;
const double HIGH VALUE = 1e10;
const double ERROR = &#8722;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&#8722;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&#8722;option price);
if (fabs(test)<ACCURACY) { return sigma; };
if (test < 0.0) { sigma low = sigma; }
else { sigma high = sigma; }
};
return ERROR;
};
 
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.
 
You must log in or register to reply here.
Back
Top