How to Calculate Daily Volatility in Excel (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Volatility is the speed and magnitude of the fluctuations of the price of a specific time period. The price of an asset or a commodity might change every day and so does the Daily Volatility. Excel can help a lot to calculate Daily Volatility to keep up with the changes. In this article, we will walk you through 2 easy ways to calculate Daily Volatility in Excel.


How to Calculate Daily Volatility in Excel: 2 Easy Ways

In this article, we will demonstrate two suitable methods to calculate daily volatility in Excel. We will use the following dataset for this purpose. The dataset contains the Closing Price of a commodity for each day from December 1, 2022, to December 14, 2022.

how to calculate daily volatility in excel


1. Apply Mathematical Formula to Calculate Daily Volatility

In this method, we will use a simple mathematical formula to calculate volatility in Excel. Read the following steps to learn how to do it.

Steps:

  • First of all, insert a column named Daily Return.
  • Then type the following formula in cell D6 to get the daily return for the closing price.
=C6/C5-1

Apply Mathematical Formula to Calculate Daily Volatility in Excel

  • Next, AutoFill the formula to the rest of the cells in column D to find the daily return for each date.

Apply Mathematical Formula to Calculate Daily Volatility in Excel

  • After that, select cells D6 to D14. Then from the Home tab, go to,

Home → Number → Percent Style(%)

  • As a result, you will get the daily return values in percentage.

  • Now select cell D16 and write down the following formula. After that, hit Enter.
=STDEV(D6:D14)
  • Hence, you will get the Daily Volatility in decimal.

Apply Mathematical Formula to Calculate Daily Volatility in Excel

  • To convert the Daily Volatility to percentage, click on Percent Style(%).
  • Finally, you will get your desired Daily Volatility.

Apply Mathematical Formula to Calculate Daily Volatility in Excel

Read More: How to Calculate Annualized Volatility in Excel


2. Use LN Function to Calculate Daily Volatility in Excel

We will now use the LN function to calculate daily volatility in Excel. Follow the steps described in the following section.

Steps:

  • First, select cell D6 and write down the following formula to determine the daily return for December 2, 2022.
=LN(C6/C5)

Use LN Function to Calculate Daily Volatility in Excel

  • After that, AutoFill the function to the rest of the cells in column D.
  • As a result, you will get all the daily return values in decimal format.

Use LN Function to Calculate Daily Volatility in Excel

  • To convert the values in percentages, select all daily return values and go to,

Home → Number → Percent Style(%)

  • All the daily return data will turn into percentages.

  • Next, select cell D16 and type the formula given below.
=STDEV(D6:D14)
  • This formula will give you the Daily Volatility in decimal format.

Use LN Function to Calculate Daily Volatility in Excel

  • Finally, change the Daily Volatility value in percentage by clicking on Percent Style (%).

how to calculate daily volatility in excel

Notes:
  • Don’t forget to give proper cell references or you won’t get the desired result.
  • The first method gives more accurate results than the second one.

Read More: How to Calculate Historical Volatility in Excel


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

Thanks for making it this far. I hope you find this article useful. Now you know two easy ways to calculate Daily Volatility in Excel. Please let us know if you have any further queries, and feel free to give us any recommendations in the comment section below.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alif Bin Hussain
Alif Bin Hussain

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Civil Engineering from Bangladesh University of Engineering & Technology (BUET). I am a fresh graduate with a great interest in research and development. I do my best to acquire new information and try to find out the most efficient solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo