Excel macro

To calculate implied volatility I have a macro on an Excel sheet whose origins lie in Lotus from many years ago. It was imported from Lotus to Excel.

The macro is triggered via control-a and still works perfectly.

It was written in Lotus Command language whereby the instructions are enclosed in curly brakets.

My questions are, can this easily be converted to a VBA macro; if so, would it be faster?

Please refer to the attached file. This is the procedure for call options:

The letters at left designate the named cells at right.

1. dh: Let the expiry month [exp] be the first (current) expiry month, eg Sep

2. di: Let the implied volatility [v] be the default implied volatility [iv], eg 10%

3. dj: If all the implieds (for this expiry month) are calculated, go to next expiry month (“eh” is the start of the next calculation for the following month)

4. If the call theoretical value [c_th_v] is less than the minimum value [mv] (the difference between the theoretical price and market price), go to dk

otherwise

5. If the call premium (last trade) [cp] is greater than the theoretical call [th_c] , then increase the implied volatility [v] by the increment [inc] (eg 0.1%)

or

6. If the call premium (last trade) [cp] is less than the theoretical call [th_c] , then decrease the implied volatility [v] by the increment [inc] (eg 0.1%)

7. dk: if the strike [x] (of the call premium) = the first strike [cx_1] (eg 5000), let the corresponding (first) implied volatility [cv_1] be the (newly determined) implied volatility [v].

7. dk is repeated for all strikes.

The whole process is repeated for all calls/puts for all expiries.

The result would be displayed as, eg:

Calls SEP OCT NOV
5000 17% 20% 23%
5050 18% 19% 21%

And so on.

Thank you for any advice/comments.

Grant.

 

Hi I am brand new to this sight, my email address is brianpanicco@telkomsa.net

Vba code for implied vol for European options using B&S and Balck76 for futures options is simple enough. Email me a request and i will send you the excel VBA functions free of charge

 

http://www.vbnumericalmethods.com/vbnumericalmethods/..\financial.asp

 

Bob111,

Thank you for the info. The site you referenced looks, from a quick glance, a mine of excellent resources.

I think I'll be spending some time here.

Grant.