Re: implied volatiliy and excel

[Jim Michael <genepool@xxxxxxxxxx>]

On Sat, 17 Apr 1999, Maurice Zandbelt wrote:

> I want to make a excel worksheet which calculates during trading hours,
> the delta of the option which I sold short. Can somebody help me with
> the formula to calculate the implied volatility or delta when I already
> have the price of the option ?

Here is my java code for calculating implied volatility using the 
Newton-Raphson technique. It converges rapidly and should be easily 
recoded in VBA.

Cheers,

Jim

      public double impliedVolatility(double stockPrice, double 
strikePrice, double timeToExpiration, double rate, double volatility, 
double actualOptionPrice, boolean putFlag) {
                double f1;
                double f2;
                double delta;
                double loops=0;
                double guess;
                double testValue;
                optionPrice q1;
                optionPrice q2;
                guess=volatility;
                do {
                        delta=guess+Math.exp(-5);
                        q1=new optionPrice(stockPrice, strikePrice, 
timeToExpiration, rate,guess+delta,putFlag);
                        f1=actualOptionPrice-q1.blackScholesOptionPrice();
                        q2=new optionPrice(stockPrice, strikePrice, 
timeToExpiration, rate, guess,putFlag);
                        f2=actualOptionPrice-q2.blackScholesOptionPrice();
                        guess=guess-delta*(1/((f1-1)/f2));
                        loops=loops+1;
                        testValue=Math.abs(f2);
                } while (testValue>.001 && loops<1000);
                return guess;
       }