Calculating implied volatility using Lambda in MS Excel

Volatility is one of the important risk measures used in finance. In the context of financial instruments, historical volatility is simply the standard deviation of historical returns. However, historical volatility may not be the best approach to estimate future volatility as market conditions change.

One of the popular approaches for estimating volatility is ‘Implied volatility'. It refers to the value of standard deviation that results in the fair value of an option as per the black sholes model equal to its current market price.

Calculating implied volatility involves a trial and error-based approach. We have to start with an initial value – a good starting point would be the historical volatility. Then keep changing the volatility till the fair price equals target price (think ‘Goal seek’ in MS Excel)

Although it may look straightforward, one has to systematically approach the trial and error process. Else it may take a long to come to the correct answer.

One of the best ways to run trial and error is the Newton-Raphson method.

This approach helps us to decide the volatility number to be used for each iteration so that we can arrive at the final answer in the shortest possible steps. We shall not discuss the approach in detail but shall directly jump to see how it can be used here.

If the volatility we have used does not result in a target price equal to the market price, then we have to change the volatility number. Under the Newton-Raphson method, the volatility for the next trial would be as follows:

Volatility for next trial = Volatility for current trial 
                          + (Market price – Model price) 
                           / Vega of option

Note, vega of the option refers to the change in option premium for change in volatility. It is calculated as follows:

Vega = Stock price * Sqrt(time) * phi(d1)

You can read about the Black Sholes model, here.

Till recently, calculating implied volatility in MS Excel was possible only through user-defined function in VBA (“Goal seek” was not really considered efficient).

However, one of the problems in using VBA is that it is available only on the windows platform. One could not use that in MAC or in Office online.

However, with the introduction of the Lambda function (in Beta stage as of March 2021), it is now possible to calculate implied volatility in Microsoft Excel through a custom Lambda function.

You can read about the Lambda function, here

Here is the code for the same.

Calculating Implied volatility using Lambda

Function name: ImpVol

=LAMBDA(OpType,CMP,S,X,T,Rf,SD, 
        LET(gap, LN(S/X), 
         drift,(Rf-Sd^2/2)*T, 
         dTwo,(gap+drift)/(Sd*SQRT(T)), 
         dOne,dTwo + Sd*SQRT(T), 
         Vega,S*SQRT(T)*PHI(dOne),
         NdTwo,NORM.S.DIST(dTwo*Optype,TRUE), 
         NdOne,NORM.S.DIST(dOne*Optype,TRUE), 
         TP,Optype*(S*NdOne - X*NdTwo/EXP(Rf*T)), 
         IV,IF(ROUND(TP,2) = ROUND(CMP,2),
                            Volguess, ImpVol(OpType, 
                                       CMP,S,X,T,Rf, 
                                       SD+(CMP-TP) /Vega)                                
                                            ), 
         IV)
  )

The following are the values that need to be entered for the parameters:

OpType: It should be 1 for call option and -1 for put option

CMP: Market price of the option (i.e. premium)

S: Current market price of the share

X: Exercise price of the option

T: Time for option expiry

Rf: Risk free rate

SD: Initial guess for the standard deviation.

The above Lambda function does not factor in dividend yield. But it is not difficult to factor it. One only needs to discount the share price, wherever they appear in the equation, by the discount yield.

The Lambda function along with the Let function and dynamic array capabilities have literally made MS Excel into a functional programming platform that is starkly different from a mere spreadsheet.

If you want to stay ahead of the curve in financial modelling and data analytics, explore our courses.