How To Calculate Volatility on Excel

  1. Trading for professionals: Options trading
    1. Call Option Basics learn the basic Definition with Examples
    2. Call option and put option understanding types of options
    3. What Is Call Option and How to Use It With Example
    4. Options Terminology The Master List of Options Trading Terminology
    5. Options Terms Key Options Trading Definitions
    6. Buy call option A Beginner’s Guide to Call Buying
    7. How to Calculate Profit on Call Option
    8. Selling Call Option What is Writing/Sell Call Options in Share Market?
    9. Call Option Payoff Exploring the Seller’s Perspective
    10. American vs European Options What is the Difference?
    11. Put Option A Guide for Traders
    12. put option example: Analysis of Bank Nifty and the Bearish Outlook
    13. Put option profit formula: P&L Analysis and Break-Even Point
    14. Put Option Selling strategies and Techniques for Profitable Trading
    15. Call and put option Summary Guide
    16. Option premium Understanding Fluctuations and Profit Potential in Options Trading
    17. Option Contract moneyness What It Is and How It Works
    18. option moneyness Understanding itm and otm
    19. option delta in option trading strategies
    20. delta in call and put Option Trading Strategies
    21. Option Greeks Delta vs spot price
    22. Delta Acceleration in option trading strategies
    23. Secrets of Option Greeks Delta in option trading strategies
    24. Delta as a Probability Tool: Assessing Option Profitability
    25. Gamma in option trading What Is Gamma in Investing and How Is It Used
    26. Derivatives: Exploring Delta and Gamma in Options Trading
    27. Option Gamma in options Greek
    28. Managing Risk in Options Trading: Exploring Delta, Gamma, and Position Sizing
    29. Understanding Gamma in Options Trading: Reactivity to Underlying Shifts and Strike Prices
    30. Mastering Option Greeks
    31. Time decay in options: Observing the Effect of Theta
    32. Put Option Selling: Strategies and Techniques for Profitable Trading
    33. How To Calculate Volatility on Excel
    34. Normal distribution in share market
    35. Volatility for practical trading applications
    36. Types of Volatility
    37. Vega in Option Greeks: The 4th Factors to Measure Risk
    38. Options Trading Greek Interactions
    39. Mastering Options Trading with the Greek Calculator
    40. Call and Put Option Guide
    41. Option Trading Strategies with example
    42. Physical Settlement in Option Trading
    43. Mark to Market (MTM) and Profit/Loss Calculation
Marketopedia / Trading for professionals: Options trading / How To Calculate Volatility on Excel

Calculating Volatility on Excel

In the earlier chapter, we introduced standard deviation and its role in assessing ‘Risk or Volatility’ for a stock. Before progressing further into this topic, we must understand how to find volatility data. It is not accessible readily so it is most beneficial to know how to calculate it personally.

In the previous chapter, we analysed Billy and Mike’s examples and outlined the steps for calculation.

  1. Compute the average
  2. Compute the deviation by subtracting the mean from the actual observation
  3. Square each deviation and sum them up – this is referred to as variance
  4. Calculate the square root of the variance – known as the standard deviation

 

In the previous chapter, we demonstrated the mechanics of how to calculate the standard deviation. I believe it is essential to understand what lies beneath a formula, as it can only help to expand your understanding. This chapter will teach us a much easier way to compute standard deviation or the volatility of a given stock using MS Excel. As always, Excel does all the hard work for us- all we need to do is press a button!

Let me outline the overall steps involved and then provide further details for each step:

 

Step 1: Obtain the historical closing prices dataset.

Step 2: Calculate the daily returns.

Step 3: Utilize the STDEV function.

 

Now, let’s delve into the first step:

 

Step 1: Acquire the historical closing prices by downloading the relevant dataset.

You can retrieve data from various sources – some of the reliable, free ones include the NSE India website and Yahoo Finance.

I will collect my data from the National Stock Exchange of India. NSE’s website is incredibly useful and provides a wealth of information, making it one of the finest stock exchange websites globally.

In this chapter, we will determine the volatility of Wipro. To retrieve the historical closing prices, you can visit the website http://www.nseindia.com/products/content/equities/equities/equities.htm and click on the ‘historical data’ section. From there, select the desired search option to access the necessary data.

Once you search, a series of fields will open up for you to fill in; the process is easy, simply enter the required information and click ‘Get Data’. Ensure that you are selecting data from the past year, starting with 22nd July 2014 and ending with 21st July 2015.

Once ‘get data’ has been clicked, NSE’s website will search for your desired information and present it to you.

 

Note that I have eliminated any superfluous data, keeping only the date and end of day pricing.

Step 2 – Calculate Daily Returns

Daily returns can be ascertained by subtracting the previous day’s closing price from the current one.

The return can be calculated using the formula (Ending Price / Beginning Price) – 1. However, for practicality and ease of calculation, this equation can be approximated to the natural logarithm of the ratio of the ending price to the beginning price, denoted as LN (Ending Price / Beginning Price). 

This approximation is commonly referred to as “Log Returns.”

This is a snapshot of the calculation I have done to determine the daily log returns of WIPRO.

I have used the Excel function ‘LN’ to calculate the long returns.

Step 3 – Use the STDEV Function

After computing the daily returns, you can use an excel function ‘STDEV’ to calculate WIPRO’s daily Volatility.

Note: To use the STDEV function, follow these simple steps:

  1. Select an empty cell.
  2. Type ‘=’ to begin a formula.
  3. Enter the function syntax, which is STDEV, followed by an opening bracket.
  4. the empty cell would look like =STEDEV(
  5. After opening the bracket, choose all the data points for daily returns and then close the bracket.
  6. Press enter

Once this is completed, Excel will quickly calculate the daily standard deviation for WIPRO. My response was 0.0147 giving a percentage of 1.47%.

The daily fluctuation rate of WIPRO is 1.47%.

We have determined WIPRO’s daily volatility, so what is its annual volatility?

It’s vital to remember that to establish the annual volatility, we must multiply daily volatility by the square root of time.

To convert annual volatility into its daily equivalent, simply divide the value by the square root of the time period.

We have determined the daily volatility of WIPRO and now we must calculate the annual volatility. Here, we will do the same.

  • Daily Volatility = 1.47%
  • Time = 252
  • Annual Volatility = 1.47% * SQRT (252)
  • = 23.33%

From this data, we can see that WIPRO has a daily variance of 1.47% and an annual volatility of approximately 23%.

Let’s cross-reference these figures with what the National Stock Exchange (NSE) has posted on their website. The NSE only gives information about Futures and Options stocks, here is a preview of that data.

Our figures are similar to the ones calculated by NSE – Daily Volatility of Wipro is estimated at 1.34%, with an Annualized Volatility around 25.5%.

So why is there a slight discrepancy between our calculation and NSE’s? A possible explanation may be that we are using spot value while NSE is utilizing Futures pricing. However, I don’t find it necessary to delve into the cause of this divergence. What needs to be done here is finding out how to evaluate the volatility of a security based on its day-to-day returns.

Finally, to conclude this chapter, let’s work out one more calculation. Suppose WIPRO’s annual volatility is determined as 25.5%, how can we calculate its daily volatility?

To convert annual volatility to daily volatility, one just needs to divide the annual figure by the square root of time; consequently, in this case –

= 25.5% / SQRT (252)

= 1.60%

We understand volatility and the way to calculate it. Moving on, in the following chapter we’ll explore its practical application.

We are still working on understanding volatility, but our final goal is to comprehend Vega and what it implies. Let’s not forget this aim.

    captcha


    Get the App Now
    • Open FREE Demat account
      Welcome to StoxBox !