🕷️ Crawler Inspector

URL Lookup

Direct Parameter Lookup

Raw Queries and Responses

1. Shard Calculation

Query:

Response:

Calculated Shard: 148 (from laksa189)

2. Crawled Status Check

Query:

curl -X POST \
  'http://laksa148.int.ahrefs:8124/' \
  -H 'Content-Type: text/plain' \
  -H 'X-ClickHouse-Database: crawler3' \
  -H 'Authorization: Basic YXBpOg==' \
  -d 'SELECT getAhrefsURLFromUnparsed(src_unparsed) AS found_url, ifNull(toUnixTimestamp(download_stamp), 0) AS crawl_time, ifNull(toUnixTimestamp(props_url_first_seen), 0) AS first_indexed_time, download_http_code AS http_code, src_unparsed AS src_unparsed, src_root_hash AS src_root_hash, history_drop_reason AS history_drop_reason, meta_title AS meta_title, meta_descriptions AS meta_descriptions, attrs_boilerpipe_text AS attrs_boilerpipe_text, attrs_markdown AS attrs_markdown, attrs_readable_markdown AS attrs_readable_markdown, meta_canonical AS meta_canonical FROM crawler3.page_info_local FINAL PREWHERE (src_root_hash, src_unparsed) IN ((getAhrefsRootHashFromUnparsed(getAhrefsUnparsedNoserviceFromURL(\'https://www.investopedia.com/ask/answers/021015/how-can-you-calculate-volatility-excel.asp\')), getAhrefsUnparsedNoserviceFromURL(\'https://www.investopedia.com/ask/answers/021015/how-can-you-calculate-volatility-excel.asp\'))) FORMAT JSONEachRow'

Response:

{"found_url":"https:\/\/www.investopedia.com\/ask\/answers\/021015\/how-can-you-calculate-volatility-excel.asp","crawl_time":1771892499,"first_indexed_time":1510517620,"http_code":200,"src_unparsed":"com,investopedia!www,\/ask\/answers\/021015\/how-can-you-calculate-volatility-excel.asp s443","src_root_hash":"7273739457760125548","history_drop_reason":null,"meta_title":"How to Calculate Stock Volatility with Excel","meta_descriptions":["Learn to calculate stock volatility in Excel using historical prices and manage risk with confidence."],"attrs_boilerpipe_text":"Key Takeaways\nHistorical volatility assesses risk by measuring return dispersion over time.\nHigh volatility offers potential profit opportunities for traders.\nMany investors use volatility in crafting tailored trading strategies.\nGet personalized, AI-powered answers built on 27+ years of trusted expertise.\nHistorical volatility measures how much a stock’s price has fluctuated over a given period, helping investors assess its risk level. Traders and investors use this data to shape their strategies, such as identifying suitable entry and exit points or choosing between aggressive and conservative positions. In this article, you’ll learn how to calculate and annualize volatility in Excel step by step. As a trader, you can benefit from high volatility because it can offer opportunities in the short term, while low volatility is better if you're using a long-term, risk-averse approach.\nGetty Images, juststock\nCalculating Historical Volatility in Excel: A Comprehensive Guide\nTo calculate the\nvolatility\nof a given security in a Microsoft Excel spreadsheet, first determine the time frame for which the metric will be computed.\nDetermining the Appropriate Timeframe for Volatility Calculation\nVolatility is a time-bound measurement, meaning that it measures the price swings of an asset or security over a particular period. Depending on the type of trader you are, different time periods would be more appropriate. A day trader, for instance, may only care about weekly volatility while a swing trader may care about monthly. For the purposes of this article, a 10-day time period will be used in the example.\nInputting Price Data into Excel \nAfter determining your timeframe, the next step is to enter all the closing stock prices for that timeframe into cells B2 through B12 in sequential order, with the newest price at the bottom. (Keep in mind that if you are doing a 10-day timeframe, you will need the data for 11 days to compute the returns for a 10-day period.)\nCalculating Inter-Day Returns \nIn column C, calculate the inter-day returns by dividing each price by the closing price of the day before and subtracting one. For example, if McDonald's (\nMCD\n) closed at $147.82 on the first day and at $149.50 on the second day, then the return of the second day would be (149.50\/147.82) - 1, or 0.011, indicating that the price on day two was 1.1% higher than the price on day one.\nFinding Standard Deviation to Assess Volatility \nVolatility is inherently related to variance, and by extension, to\nstandard deviation\n, or the degree to which prices differ from their mean. In cell C13, enter the formula \"=STDEV.S(C3:C12)\" to compute the standard deviation for the period.\nImportant\nThe link between standard deviation and volatility is evident in the types of technical indicators that investors use to chart a stock's volatility, such as\nBollinger Bands\n, which are based on a stock's standard deviation and the\nsimple moving average (SMA)\n.\nConverting Period Volatility into an Annualized Figure\nHistorical volatility is usually converted into an annualized figure, so to convert the daily standard deviation calculated above into a usable metric, it must be multiplied by an annualization factor based on the period used. The annualization factor is the square root of however many periods exist in a year.\nThe table below shows the volatility for McDonald's within a 10-day time frame:\nThe example above used daily closing prices, and there are 250-252 trading days per year. Therefore, in cell C14, enter the formula \"=SQRT(252)*C13\" to convert the standard deviation for this 10-day period to annualized historical volatility.\nImportance of Volatility for Investors\nWhile volatility in a stock can sometimes have a bad connotation, many traders and investors actually seek out higher volatility investments. They do this in the hopes of eventually making higher profits. If a stock or other security does not move, it has low volatility. However, it also has a low potential to make \ncapital gains\n.\nOn the other hand, a stock or other security with a very high volatility level can have tremendous profit potential. But by the same token, the risk of loss is quite high.\nTo be a trader or investor who capitalizes on volatility, the timing of any trades must be perfect. Even a correct market call could end up losing money if the security's wide price swings trigger either a\nstop-loss order\nor a\nmargin call\n.\nWhy Is Historical Volatility Important?\nThe volatility of a particular asset or security is thought to exhibit\nmean reversion\nover time. This means that if a security is uncharacteristically volatile, it should return eventually to its long-run average. Likewise, if it is subdued, its volatility should increase. Calculating historical volatility is how to arrive at this average or mean level.\nWhat Does the Volatility of a Stock Mean?\nVolatility describes the speed and magnitude of price swings over a given period of time (often on an annualized basis). Highly volatile stocks experience large and swift price swings, and they are often considered to be riskier than less volatile stocks.\nIs Higher or Lower Historical Volatility More Advantageous?\nFor day traders and options traders, high volatility can provide more opportunities to move and out of positions or profit from volatility spikes. For most long-term buy-and-hold investors, however, lower volatility is often preferred.\nThe Bottom Line\nHistorical volatility can help you assess the risk of a security by measuring the dispersion of its returns over time. To calculate historical volatility with Excel, input a time frame, log closing prices, compute inter-day returns, calculate the standard deviation, and then annualize the volatility. Technical indicators like Bollinger Bands show a link between standard deviation and volatility.\nThe historical volatility lets you identify risk and profit opportunities. But your investment strategy can dictate whether you should use high volatility—it generally helps day traders find opportunities, but long-term investors who are concerned with short-term market movements may want to avoid it. Securities that are uncharacteristically volatile should return to long-run averages. This is known as mean reversion.","attrs_markdown":"\n\n[Skip to content](https:\/\/www.investopedia.com\/ask\/answers\/021015\/how-can-you-calculate-volatility-excel.asp#main)\n\n[Investopedia](https:\/\/www.investopedia.com\/)\n\n[Trade](https:\/\/www.investopedia.com\/simulator\/?inv_to_sim=global_nav)\n\n- Please fill out this field.\n- [Newsletters](https:\/\/www.investopedia.com\/ask\/answers\/021015\/how-can-you-calculate-volatility-excel.asp)\n\nPlease fill out this field.\n\n- [News](https:\/\/www.investopedia.com\/news-4427706)\n  News\n  \n  - [Markets](https:\/\/www.investopedia.com\/markets-news-4427704)\n  - [Companies](https:\/\/www.investopedia.com\/company-news-4427705)\n  - [Earnings](https:\/\/www.investopedia.com\/earnings-reports-5218419)\n  - [CD Rates](https:\/\/www.investopedia.com\/certificates-deposit-news-7111770)\n  - [Mortgage Rates](https:\/\/www.investopedia.com\/mortgage-rates-5184598)\n  - [Economy](https:\/\/www.investopedia.com\/economic-news-5218422)\n  - [Government](https:\/\/www.investopedia.com\/political-news-4689737)\n  - [Crypto](https:\/\/www.investopedia.com\/cryptocurrency-news-5114163)\n  - [Live Markets News](https:\/\/www.investopedia.com\/live-markets-news-11713202)\n  - [Personal Finance](https:\/\/www.investopedia.com\/personal-finance-news-5114159)\n  - [View All](https:\/\/www.investopedia.com\/news-4427706)\n- [Investing](https:\/\/www.investopedia.com\/investing-4427685)\n  Investing\n  \n  - [Stocks](https:\/\/www.investopedia.com\/stocks-4427785)\n  - [Cryptocurrency](https:\/\/www.investopedia.com\/cryptocurrency-4427699)\n  - [Bonds](https:\/\/www.investopedia.com\/bonds-4689778)\n  - [ETFs](https:\/\/www.investopedia.com\/etfs-4427784)\n  - [Options and Derivatives](https:\/\/www.investopedia.com\/options-and-derivatives-trading-4689663)\n  - [Commodities](https:\/\/www.investopedia.com\/commodities-4427780)\n  - [Trading](https:\/\/www.investopedia.com\/trading-4427765)\n  - [Automated Investing](https:\/\/www.investopedia.com\/financial-technology-and-automated-investing-4689759)\n  - [Brokers](https:\/\/www.investopedia.com\/brokers-4689772)\n  - [Fundamental Analysis](https:\/\/www.investopedia.com\/fundamental-analysis-4689757)\n  - [View All](https:\/\/www.investopedia.com\/investing-4427685)\n- [Simulator](https:\/\/www.investopedia.com\/simulator\/?inv_to_sim=global_nav)\n  Simulator\n  \n  - [Login \/ Portfolio](https:\/\/www.investopedia.com\/simulator\/portfolio)\n  - [Trade](https:\/\/www.investopedia.com\/simulator\/trade\/stocks)\n  - [Research](https:\/\/www.investopedia.com\/simulator\/research)\n  - [My Games](https:\/\/www.investopedia.com\/simulator\/games\/my-games)\n  - [Leaderboard](https:\/\/www.investopedia.com\/simulator\/games)\n- [Banking](https:\/\/www.investopedia.com\/banking-4427754)\n  Banking\n  \n  - [Savings Accounts](https:\/\/www.investopedia.com\/savings-accounts-4689728)\n  - [Certificates of Deposit (CDs)](https:\/\/www.investopedia.com\/certificate-of-deposits-4689733)\n  - [Money Market Accounts](https:\/\/www.investopedia.com\/money-market-account-4689730)\n  - [Checking Accounts](https:\/\/www.investopedia.com\/checking-accounts-4689732)\n  - [View All](https:\/\/www.investopedia.com\/banking-4427754)\n- [Personal Finance](https:\/\/www.investopedia.com\/personal-finance-4427760)\n  Personal Finance\n  \n  - [Budgeting and Saving](https:\/\/www.investopedia.com\/budgeting-and-savings-4427755)\n  - [Personal Loans](https:\/\/www.investopedia.com\/personal-loans-4689729)\n  - [Insurance](https:\/\/www.investopedia.com\/insurance-4427716)\n  - [Mortgages](https:\/\/www.investopedia.com\/mortgage-4689703)\n  - [Credit and Debt](https:\/\/www.investopedia.com\/credit-and-debt-4689724)\n  - [Student Loans](https:\/\/www.investopedia.com\/student-loans-4689727)\n  - [Taxes](https:\/\/www.investopedia.com\/taxes-4427724)\n  - [Credit Cards](https:\/\/www.investopedia.com\/credit-card-4689721)\n  - [Financial Literacy](https:\/\/www.investopedia.com\/financial-literacy-resource-center-7151950)\n  - [Retirement](https:\/\/www.investopedia.com\/retirement-planning-4689695)\n  - [View All](https:\/\/www.investopedia.com\/personal-finance-4427760)\n- [Economy](https:\/\/www.investopedia.com\/economy-4689801)\n  Economy\n  \n  - [Government and Policy](https:\/\/www.investopedia.com\/government-and-policy-4689793)\n  - [Monetary Policy](https:\/\/www.investopedia.com\/monetary-policy-4689792)\n  - [Fiscal Policy](https:\/\/www.investopedia.com\/fiscal-policy-4689796)\n  - [Economics](https:\/\/www.investopedia.com\/economics-4689800)\n  - [View All](https:\/\/www.investopedia.com\/economy-4689801)\n- [Reviews](https:\/\/www.investopedia.com\/financial-product-reviews-7110783)\n  Reviews\n  \n  - [Best Online Brokers](https:\/\/www.investopedia.com\/best-online-brokers-4587872)\n  - [Best Crypto Exchanges](https:\/\/www.investopedia.com\/best-crypto-exchanges-5071855)\n  - [Best Savings Rates](https:\/\/www.investopedia.com\/high-yield-savings-accounts-4770633)\n  - [Best CD Rates](https:\/\/www.investopedia.com\/best-cd-rates-4770214)\n  - [Best Life Insurance](https:\/\/www.investopedia.com\/the-best-life-insurance-companies-8763666)\n  - [Best Mortgage Rates](https:\/\/www.investopedia.com\/mortgage-rates-5094943)\n  - [Best Robo-Advisors](https:\/\/www.investopedia.com\/the-best-robo-advisors-8764849)\n  - [Best Personal Loans](https:\/\/www.investopedia.com\/the-best-personal-loans-8761582)\n  - [Best Debt Relief Companies](https:\/\/www.investopedia.com\/the-best-debt-relief-companies-8763179)\n  - [View All](https:\/\/www.investopedia.com\/financial-product-reviews-7110783)\n\n- [Newsletters](https:\/\/www.investopedia.com\/ask\/answers\/021015\/how-can-you-calculate-volatility-excel.asp)\n\nFollow Us\n\n- [News](https:\/\/www.investopedia.com\/news-4427706)\n  - [Markets](https:\/\/www.investopedia.com\/markets-news-4427704)\n  - [Companies](https:\/\/www.investopedia.com\/company-news-4427705)\n  - [Earnings](https:\/\/www.investopedia.com\/earnings-reports-5218419)\n  - [CD Rates](https:\/\/www.investopedia.com\/certificates-deposit-news-7111770)\n  - [Mortgage Rates](https:\/\/www.investopedia.com\/mortgage-rates-5184598)\n  - [Economy](https:\/\/www.investopedia.com\/economic-news-5218422)\n  - [Government](https:\/\/www.investopedia.com\/political-news-4689737)\n  - [Crypto](https:\/\/www.investopedia.com\/cryptocurrency-news-5114163)\n  - [Live Markets News](https:\/\/www.investopedia.com\/live-markets-news-11713202)\n  - [Personal Finance](https:\/\/www.investopedia.com\/personal-finance-news-5114159)\n  - [View All](https:\/\/www.investopedia.com\/news-4427706)\n- [Investing](https:\/\/www.investopedia.com\/investing-4427685)\n  - [Stocks](https:\/\/www.investopedia.com\/stocks-4427785)\n  - [Cryptocurrency](https:\/\/www.investopedia.com\/cryptocurrency-4427699)\n  - [Bonds](https:\/\/www.investopedia.com\/bonds-4689778)\n  - [ETFs](https:\/\/www.investopedia.com\/etfs-4427784)\n  - [Options and Derivatives](https:\/\/www.investopedia.com\/options-and-derivatives-trading-4689663)\n  - [Commodities](https:\/\/www.investopedia.com\/commodities-4427780)\n  - [Trading](https:\/\/www.investopedia.com\/trading-4427765)\n  - [Automated Investing](https:\/\/www.investopedia.com\/financial-technology-and-automated-investing-4689759)\n  - [Brokers](https:\/\/www.investopedia.com\/brokers-4689772)\n  - [Fundamental Analysis](https:\/\/www.investopedia.com\/fundamental-analysis-4689757)\n  - [View All](https:\/\/www.investopedia.com\/investing-4427685)\n- [Simulator](https:\/\/www.investopedia.com\/simulator\/?inv_to_sim=global_nav)\n  - [Login \/ Portfolio](https:\/\/www.investopedia.com\/simulator\/portfolio)\n  - [Trade](https:\/\/www.investopedia.com\/simulator\/trade\/stocks)\n  - [Research](https:\/\/www.investopedia.com\/simulator\/research)\n  - [My Games](https:\/\/www.investopedia.com\/simulator\/games\/my-games)\n  - [Leaderboard](https:\/\/www.investopedia.com\/simulator\/games)\n- [Banking](https:\/\/www.investopedia.com\/banking-4427754)\n  - [Savings Accounts](https:\/\/www.investopedia.com\/savings-accounts-4689728)\n  - [Certificates of Deposit (CDs)](https:\/\/www.investopedia.com\/certificate-of-deposits-4689733)\n  - [Money Market Accounts](https:\/\/www.investopedia.com\/money-market-account-4689730)\n  - [Checking Accounts](https:\/\/www.investopedia.com\/checking-accounts-4689732)\n  - [View All](https:\/\/www.investopedia.com\/banking-4427754)\n- [Personal Finance](https:\/\/www.investopedia.com\/personal-finance-4427760)\n  - [Budgeting and Saving](https:\/\/www.investopedia.com\/budgeting-and-savings-4427755)\n  - [Personal Loans](https:\/\/www.investopedia.com\/personal-loans-4689729)\n  - [Insurance](https:\/\/www.investopedia.com\/insurance-4427716)\n  - [Mortgages](https:\/\/www.investopedia.com\/mortgage-4689703)\n  - [Credit and Debt](https:\/\/www.investopedia.com\/credit-and-debt-4689724)\n  - [Student Loans](https:\/\/www.investopedia.com\/student-loans-4689727)\n  - [Taxes](https:\/\/www.investopedia.com\/taxes-4427724)\n  - [Credit Cards](https:\/\/www.investopedia.com\/credit-card-4689721)\n  - [Financial Literacy](https:\/\/www.investopedia.com\/financial-literacy-resource-center-7151950)\n  - [Retirement](https:\/\/www.investopedia.com\/retirement-planning-4689695)\n  - [View All](https:\/\/www.investopedia.com\/personal-finance-4427760)\n- [Economy](https:\/\/www.investopedia.com\/economy-4689801)\n  - [Government and Policy](https:\/\/www.investopedia.com\/government-and-policy-4689793)\n  - [Monetary Policy](https:\/\/www.investopedia.com\/monetary-policy-4689792)\n  - [Fiscal Policy](https:\/\/www.investopedia.com\/fiscal-policy-4689796)\n  - [Economics](https:\/\/www.investopedia.com\/economics-4689800)\n  - [View All](https:\/\/www.investopedia.com\/economy-4689801)\n- [Reviews](https:\/\/www.investopedia.com\/financial-product-reviews-7110783)\n  - [Best Online Brokers](https:\/\/www.investopedia.com\/best-online-brokers-4587872)\n  - [Best Crypto Exchanges](https:\/\/www.investopedia.com\/best-crypto-exchanges-5071855)\n  - [Best Savings Rates](https:\/\/www.investopedia.com\/high-yield-savings-accounts-4770633)\n  - [Best CD Rates](https:\/\/www.investopedia.com\/best-cd-rates-4770214)\n  - [Best Life Insurance](https:\/\/www.investopedia.com\/the-best-life-insurance-companies-8763666)\n  - [Best Mortgage Rates](https:\/\/www.investopedia.com\/mortgage-rates-5094943)\n  - [Best Robo-Advisors](https:\/\/www.investopedia.com\/the-best-robo-advisors-8764849)\n  - [Best Personal Loans](https:\/\/www.investopedia.com\/the-best-personal-loans-8761582)\n  - [Best Debt Relief Companies](https:\/\/www.investopedia.com\/the-best-debt-relief-companies-8763179)\n  - [View All](https:\/\/www.investopedia.com\/financial-product-reviews-7110783)\n\n- Top Stories\n- [Average Retirement Savings at 59](https:\/\/www.investopedia.com\/ideal-retirement-savings-goal-by-age-59-11909464)\n- [Average Credit Score in Your 40s and 50s](https:\/\/www.investopedia.com\/the-average-credit-score-in-your-40s-and-50s-how-do-you-stack-up-11908475)\n- [Compare Your Net Worth to Others Making Similar Incomes](https:\/\/www.investopedia.com\/how-your-net-worth-compares-to-others-with-the-same-income-you-might-be-surprised-11909297)\n- [Simple Ways to Earn Money With ChatGPT](https:\/\/www.investopedia.com\/10-simple-ways-to-earn-money-using-chatgpt-without-any-tech-skills-11888989)\n\nTable of Contents\n\nExpand\n\nTable of Contents\n\n- [Calculating Volatility in Excel](https:\/\/www.investopedia.com\/ask\/answers\/021015\/how-can-you-calculate-volatility-excel.asp#toc-calculating-historical-volatility-in-excel-a-comprehensive-guide)\n- [Why Volatility Matters to Investors](https:\/\/www.investopedia.com\/ask\/answers\/021015\/how-can-you-calculate-volatility-excel.asp#toc-importance-of-volatility-for-investors)\n- [FAQs](https:\/\/www.investopedia.com\/ask\/answers\/021015\/how-can-you-calculate-volatility-excel.asp#toc-why-is-historical-volatility-important)\n- [The Bottom Line](https:\/\/www.investopedia.com\/ask\/answers\/021015\/how-can-you-calculate-volatility-excel.asp#toc-the-bottom-line)\n\n# How to Calculate Stock Volatility with Excel\nBy\n\n[Claire Boyte-White](https:\/\/www.investopedia.com\/contributors\/53885\/)\n\n![]()\n\n![](https:\/\/www.investopedia.com\/thmb\/yuyY5XexZUGW3O-2wc-qMFbgOFc=\/200x200\/filters:no_upscale\$\$:max_bytes\$150000\$:strip_icc\$\$\/biopic_2__claire_boytewhitebw-5bfc261bc9e77c00262f61d9.png)\n\n[Full Bio](https:\/\/www.investopedia.com\/contributors\/53885\/)\n\nClaire Boyte-White is the lead writer for NapkinFinance.com, co-author of I Am Net Worthy, and an Investopedia contributor. Claire's expertise lies in corporate finance & accounting, mutual funds, retirement planning, and technical analysis.\n\nLearn about our  [editorial policies](https:\/\/www.investopedia.com\/legal-4768893#editorial-policy)\n\nUpdated February 20, 2026\n\nReviewed by\n\n[Khadija Khartit](https:\/\/www.investopedia.com\/khadija-khartit-4799776)\n\n![Khadija Khartit]()\n\n![Khadija Khartit](https:\/\/www.investopedia.com\/thmb\/W2re2_rNx_MIW9RPQv9BMFNHVH4=\/200x200\/filters:no_upscale\$\$:max_bytes\$150000\$:strip_icc\$\$\/Pic1-KhadijaKhartit-cc5b57fba2bd46ed87bdb3ddbcd2ef69.jpg)\n\nReviewed by Khadija Khartit\n\n[Full Bio](https:\/\/www.investopedia.com\/khadija-khartit-4799776)\n\nKhadija Khartit is a strategy, investment, and funding expert, and an educator of fintech and strategic finance in top universities. She has been an investor, entrepreneur, and advisor for more than 25 years. She is a FINRA Series 7, 63, and 66 license holder.\n\nLearn about our  [Financial Review Board](https:\/\/www.investopedia.com\/investopedia-financial-review-board-5076269)\n\nFact checked by\n\n[Jared Ecker](https:\/\/www.investopedia.com\/jared-ecker-5219601)\n\n![BW Photo]()\n\n![BW Photo](https:\/\/www.investopedia.com\/thmb\/uVEuJUr0M3SUKG_9PA0z42QdNAs=\/200x200\/filters:no_upscale\$\$:max_bytes\$150000\$:strip_icc\$\$\/2G8T0126-BW-5a525a4d8c434885b365bee7b3807f60.jpg)\n\nFact checked by Jared Ecker\n\n[Full Bio](https:\/\/www.investopedia.com\/jared-ecker-5219601)\n\nJared Ecker is a researcher and fact-checker. He possesses over a decade of experience in the Nuclear and National Defense sectors resolving issues on platforms as varied as stealth bombers to UAVs. He holds an A.A.S. in Aviation Maintenance Technology, a B.A. in History, and a M.S. in Environmental Policy & Management.\n\nLearn about our  [editorial policies](https:\/\/www.investopedia.com\/legal-4768893#editorial-policy)\n\n### Key Takeaways\n- Historical volatility assesses risk by measuring return dispersion over time.\n- High volatility offers potential profit opportunities for traders.\n- Many investors use volatility in crafting tailored trading strategies.\n\nGet personalized, AI-powered answers built on 27+ years of trusted expertise.\n\nASK\n\nHistorical volatility measures how much a stock’s price has fluctuated over a given period, helping investors assess its risk level. Traders and investors use this data to shape their strategies, such as identifying suitable entry and exit points or choosing between aggressive and conservative positions. In this article, you’ll learn how to calculate and annualize volatility in Excel step by step. As a trader, you can benefit from high volatility because it can offer opportunities in the short term, while low volatility is better if you're using a long-term, risk-averse approach.\n\n![Businessman working with data and graphs in spreadsheet documents for online analysis Microsoft Excel project dashboard accounting digital]()\n\n![Businessman working with data and graphs in spreadsheet documents for online analysis Microsoft Excel project dashboard accounting digital](https:\/\/www.investopedia.com\/thmb\/JyGpB9cH95mPbUP69TtON3khiRc=\/1500x0\/filters:no_upscale\$\$:max_bytes\$150000\$:strip_icc\$\$\/GettyImages-1597729432-457d2a5ed5ec4e1a8df44aa9e934f820.jpg)\n\nGetty Images, juststock\n## Calculating Historical Volatility in Excel: A Comprehensive Guide\nTo calculate the [volatility](https:\/\/www.investopedia.com\/terms\/v\/volatility.asp) of a given security in a Microsoft Excel spreadsheet, first determine the time frame for which the metric will be computed.\n\n### Determining the Appropriate Timeframe for Volatility Calculation\nVolatility is a time-bound measurement, meaning that it measures the price swings of an asset or security over a particular period. Depending on the type of trader you are, different time periods would be more appropriate. A day trader, for instance, may only care about weekly volatility while a swing trader may care about monthly. For the purposes of this article, a 10-day time period will be used in the example.\n\n### Inputting Price Data into Excel\nAfter determining your timeframe, the next step is to enter all the closing stock prices for that timeframe into cells B2 through B12 in sequential order, with the newest price at the bottom. (Keep in mind that if you are doing a 10-day timeframe, you will need the data for 11 days to compute the returns for a 10-day period.)\n\n### Calculating Inter-Day Returns\nIn column C, calculate the inter-day returns by dividing each price by the closing price of the day before and subtracting one. For example, if McDonald's ([MCD](https:\/\/www.investopedia.com\/markets\/quote?tvwidgetsymbol=MCD)) closed at \\$147.82 on the first day and at \\$149.50 on the second day, then the return of the second day would be (149.50\/147.82) - 1, or 0.011, indicating that the price on day two was 1.1% higher than the price on day one.\n\n### Finding Standard Deviation to Assess Volatility\nVolatility is inherently related to variance, and by extension, to [standard deviation](https:\/\/www.investopedia.com\/terms\/s\/standarddeviation.asp), or the degree to which prices differ from their mean. In cell C13, enter the formula \"=STDEV.S(C3:C12)\" to compute the standard deviation for the period.\n\n### Important\nThe link between standard deviation and volatility is evident in the types of technical indicators that investors use to chart a stock's volatility, such as [Bollinger Bands](https:\/\/www.investopedia.com\/terms\/b\/bollingerbands.asp), which are based on a stock's standard deviation and the [simple moving average (SMA)](https:\/\/www.investopedia.com\/terms\/s\/sma.asp).\n### Converting Period Volatility into an Annualized Figure\nHistorical volatility is usually converted into an annualized figure, so to convert the daily standard deviation calculated above into a usable metric, it must be multiplied by an annualization factor based on the period used. The annualization factor is the square root of however many periods exist in a year.\n\nThe table below shows the volatility for McDonald's within a 10-day time frame:\n\n![]()\n\n![](https:\/\/www.investopedia.com\/thmb\/K0IAl35t0iFaM-7CDaKAX8H889Y=\/1500x0\/filters:no_upscale\$\$:max_bytes\$150000\$:strip_icc\$\$\/volatility-5bfd885b46e0fb002640af5a)\n\nThe example above used daily closing prices, and there are 250-252 trading days per year. Therefore, in cell C14, enter the formula \"=SQRT(252)\\*C13\" to convert the standard deviation for this 10-day period to annualized historical volatility.\n\n## Importance of Volatility for Investors\nWhile volatility in a stock can sometimes have a bad connotation, many traders and investors actually seek out higher volatility investments. They do this in the hopes of eventually making higher profits. If a stock or other security does not move, it has low volatility. However, it also has a low potential to make [capital gains](https:\/\/www.investopedia.com\/terms\/c\/capitalgain.asp).\n\nOn the other hand, a stock or other security with a very high volatility level can have tremendous profit potential. But by the same token, the risk of loss is quite high.\n\nTo be a trader or investor who capitalizes on volatility, the timing of any trades must be perfect. Even a correct market call could end up losing money if the security's wide price swings trigger either a [stop-loss order](https:\/\/www.investopedia.com\/articles\/stocks\/09\/use-stop-loss.asp) or a [margin call](https:\/\/www.investopedia.com\/terms\/m\/margincall.asp).\n\n## Why Is Historical Volatility Important?\nThe volatility of a particular asset or security is thought to exhibit [mean reversion](https:\/\/www.investopedia.com\/terms\/m\/meanreversion.asp) over time. This means that if a security is uncharacteristically volatile, it should return eventually to its long-run average. Likewise, if it is subdued, its volatility should increase. Calculating historical volatility is how to arrive at this average or mean level.\n\n## What Does the Volatility of a Stock Mean?\nVolatility describes the speed and magnitude of price swings over a given period of time (often on an annualized basis). Highly volatile stocks experience large and swift price swings, and they are often considered to be riskier than less volatile stocks.\n\n## Is Higher or Lower Historical Volatility More Advantageous?\nFor day traders and options traders, high volatility can provide more opportunities to move and out of positions or profit from volatility spikes. For most long-term buy-and-hold investors, however, lower volatility is often preferred.\n## The Bottom Line\nHistorical volatility can help you assess the risk of a security by measuring the dispersion of its returns over time. To calculate historical volatility with Excel, input a time frame, log closing prices, compute inter-day returns, calculate the standard deviation, and then annualize the volatility. Technical indicators like Bollinger Bands show a link between standard deviation and volatility.\n\nThe historical volatility lets you identify risk and profit opportunities. But your investment strategy can dictate whether you should use high volatility—it generally helps day traders find opportunities, but long-term investors who are concerned with short-term market movements may want to avoid it. Securities that are uncharacteristically volatile should return to long-run averages. This is known as mean reversion.\n\nRead more\n\n- [Business](https:\/\/www.investopedia.com\/business-4689640)\n- [Corporate Finance](https:\/\/www.investopedia.com\/corporate-finance-and-accounting-4689821)\n- [Financial Ratios](https:\/\/www.investopedia.com\/financial-ratios-4689817)\n\nPartner Links\n\n## Related Articles\n[![Businessman working with data and graphs in spreadsheet documents for online analysis Microsoft Excel project dashboard accounting digital]() ![Businessman working with data and graphs in spreadsheet documents for online analysis Microsoft Excel project dashboard accounting digital](https:\/\/www.investopedia.com\/thmb\/DSO6rMDXoP3V8qJhWIWsGwKEL3Q=\/282x188\/filters:no_upscale\$\$:max_bytes\$150000\$:strip_icc\$\$\/GettyImages-1597729432-457d2a5ed5ec4e1a8df44aa9e934f820.jpg) How to Calculate CAGR in Excel: A Simple Formula Guide](https:\/\/www.investopedia.com\/ask\/answers\/071014\/what-formula-calculating-compound-annual-growth-rate-cagr-excel.asp)\n\n[![A desk with paper that says Internal Rate of Return]() ![A desk with paper that says Internal Rate of Return](https:\/\/www.investopedia.com\/thmb\/OSd1TP8D3MtlZpIZbU5p5hqs6Qk=\/282x188\/filters:no_upscale\$\$:max_bytes\$150000\$:strip_icc\$\$\/GettyImages-1025425776-419c3d61504148ca9daf14cdd7d5caa7.jpg) Calculate IRR in Excel: Step-by-Step Guide for Investors](https:\/\/www.investopedia.com\/ask\/answers\/022615\/what-formula-calculating-internal-rate-return-irr-excel.asp)\n\n[![]() ![](https:\/\/www.investopedia.com\/thmb\/c1B9xgAFKagsdoN3UNYK5xVzlqc=\/282x188\/filters:no_upscale\$\$:max_bytes\$150000\$:strip_icc\$\$\/active-trading-lrg-5bfc2b24c9e77c005876f40d.jpg) Measure Stock Volatility with Historical Data: A Practical Approach](https:\/\/www.investopedia.com\/articles\/basics\/09\/simplified-measuring-interpreting-volatility.asp)\n\n[![Man working from home]() ![Man working from home](https:\/\/www.investopedia.com\/thmb\/rlp08TGSsir5TT-gIBqLeDnOGNQ=\/282x188\/filters:no_upscale\$\$:max_bytes\$150000\$:strip_icc\$\$\/GettyImages-926084920-c6562fc8f4144ae796ef089bf7c6af44.jpg) Invest Wisely Using Price-to-Book (P\/B) Ratio](https:\/\/www.investopedia.com\/investing\/using-price-to-book-ratio-evaluate-companies\/)\n\n[![Businessperson working with business document]() ![Businessperson working with business document](https:\/\/www.investopedia.com\/thmb\/ZnPN3BjL-DdWVg_2ePe12ZfZc2A=\/282x188\/filters:no_upscale\$\$:max_bytes\$150000\$:strip_icc\$\$\/GettyImages-953844116-3875ca5809a54851b75286564c4ba358.jpg) How to Calculate Return on Assets (ROA) with Examples](https:\/\/www.investopedia.com\/ask\/answers\/031215\/what-formula-calculating-return-assets-roa.asp)\n\n[![JPMorgan Chase bank headquarters building in London]() ![JPMorgan Chase bank headquarters building in London](https:\/\/www.investopedia.com\/thmb\/uAIDNIajcouE-8V77faNmSnoQyE=\/282x188\/filters:no_upscale\$\$:max_bytes\$150000\$:strip_icc\$\$\/INV_JPMorganChaseHQinLondon_GettyImages-2196274932-1a9ec636044548949face55c4dab57a0.jpg) Understanding the Capital Adequacy Ratio for Banks](https:\/\/www.investopedia.com\/ask\/answers\/051415\/what-formula-calculating-capital-risk-weight-assets-ratio-bank.asp)\n\n[![]() ![](https:\/\/www.investopedia.com\/thmb\/vWK1g410oV4zbUM9jjhCI2MLdIo=\/282x188\/filters:no_upscale\$\$:max_bytes\$150000\$:strip_icc\$\$\/ap578045340213-5bfc2fb246e0fb00260233a5.jpg) Retail Sector P\/E Ratios: Understand the Market Valuation](https:\/\/www.investopedia.com\/ask\/answers\/071615\/what-average-pricetoearnings-ratio-retail-sector.asp)\n\n[![Presenting company annual report statement document, papers chart, growing up graph.]() ![Presenting company annual report statement document, papers chart, growing up graph.](https:\/\/www.investopedia.com\/thmb\/Kwx64GZGQgzyekKt0d9HqlgNgiA=\/282x188\/filters:no_upscale\$\$:max_bytes\$150000\$:strip_icc\$\$\/GettyImages-958271424-0d5a8e8f1e8246218841e30fb7f98d4b.jpg) How to Calculate the PEG Ratio for Stock Valuation](https:\/\/www.investopedia.com\/ask\/answers\/06\/pegratioearningsgrowthrate.asp)\n\n[![stack of coins and a bar graph]() ![stack of coins and a bar graph](https:\/\/www.investopedia.com\/thmb\/bnbOb5APCr4r71mcqJ7tPtAjzx4=\/282x188\/filters:no_upscale\$\$:max_bytes\$150000\$:strip_icc\$\$\/GettyImages-1158986487-249fb6e8fdf1496dab20d2801571a8cb.jpg) Calculating Variance in Gross Margin Percentages by Price and Cost](https:\/\/www.investopedia.com\/ask\/answers\/031715\/how-do-you-calculate-variance-gross-margin-percentage-due-price-and-cost.asp)\n\n[![ROE]() ![ROE](https:\/\/www.investopedia.com\/thmb\/r1ETfzcFQ9YDG93YMp5e4WASK4Y=\/282x188\/filters:no_upscale\$\$:max_bytes\$150000\$:strip_icc\$\$\/returnonequity-3e37bbcaf73a47039344551bc172a699.png) Return on Equity (ROE) Calculation and What It Means](https:\/\/www.investopedia.com\/terms\/r\/returnonequity.asp)\n\n[![ROI]() ![ROI](https:\/\/www.investopedia.com\/thmb\/czZoWKmsG9zkWZlf0xpiD6Y34ds=\/282x188\/filters:no_upscale\$\$:max_bytes\$150000\$:strip_icc\$\$\/Roi-be50b2f1f1e447b4af1b917bda2708f9.png) What Is Return on Investment (ROI) and How to Calculate It](https:\/\/www.investopedia.com\/terms\/r\/returnoninvestment.asp)\n\n[![Debt-Service Coverage Ratio (DSCR)]() ![Debt-Service Coverage Ratio (DSCR)](https:\/\/www.investopedia.com\/thmb\/kx-nlPb-vHeDwkVupz6I6N4wDjE=\/282x188\/filters:no_upscale\$\$:max_bytes\$150000\$:strip_icc\$\$\/Debt-service-coverage-ratio-294523bd49304cefabe7af0ad69a28f4.jpg) Debt-Service Coverage Ratio (DSCR): How to Use and Calculate It](https:\/\/www.investopedia.com\/terms\/d\/dscr.asp)\n\n[![New York Stock Exchange, Wall st, New York, USA]() ![New York Stock Exchange, Wall st, New York, USA](https:\/\/www.investopedia.com\/thmb\/IMIfvnzkzGMCvcS1UlDYHGeqK5U=\/282x188\/filters:no_upscale\$\$:max_bytes\$150000\$:strip_icc\$\$\/GettyImages-668600179-4215b1859f4d4181b8a468c116939c86.jpg) Top Income Statement Ratios: Gross, Profit, and Operating Margins Explained](https:\/\/www.investopedia.com\/ask\/answers\/102714\/what-are-main-income-statement-ratios.asp)\n\n[![]() ![](https:\/\/www.investopedia.com\/thmb\/x4KyOC-ODVw96m4ASOAQkWhqXBo=\/282x188\/filters:no_upscale\$\$:max_bytes\$150000\$:strip_icc\$\$\/investing1-5bfc2b9046e0fb005119b292.jpg) Evaluating Low P\/E Ratio Stocks: Are They Good Investments?](https:\/\/www.investopedia.com\/ask\/answers\/05\/lowperatiostocksbetterinvestments.asp)\n\n[![Cost of Goods Sold (COGS)]() ![Cost of Goods Sold (COGS)](https:\/\/www.investopedia.com\/thmb\/OkRSASAYJttN7Y7WRXDiujU-M1k=\/282x188\/filters:no_upscale\$\$:max_bytes\$150000\$:strip_icc\$\$\/Cost-of-Goods-Sold-COGS-d670f0a578d745c0979681c75bd43099.png) Cost of Goods Sold (COGS) Explained With Methods to Calculate It](https:\/\/www.investopedia.com\/terms\/c\/cogs.asp)\n\n[![Accountant hand holding pen working on calculator and laptop computer.]() ![Accountant hand holding pen working on calculator and laptop computer.](https:\/\/www.investopedia.com\/thmb\/PiesMviXY0E_1HMmrAYhH9tPhbA=\/282x188\/filters:no_upscale\$\$:max_bytes\$150000\$:strip_icc\$\$\/GettyImages-865226698-2fef1d5dae1944839ece7a3cd81902ac.jpg) Understanding the Correlation Between Price-to-Book Ratio and ROE](https:\/\/www.investopedia.com\/ask\/answers\/010915\/does-high-price-book-ratio-correspond-high-roe.asp)\n\n[Investopedia](https:\/\/www.investopedia.com\/)\n\n[Newsletter Sign Up](https:\/\/www.investopedia.com\/ask\/answers\/021015\/how-can-you-calculate-volatility-excel.asp)\n\nFollow Us\n\n- [News](https:\/\/www.investopedia.com\/news-4427706)\n- [Investing](https:\/\/www.investopedia.com\/investing-4427685)\n- [Simulator](https:\/\/www.investopedia.com\/simulator\/?inv_to_sim=global_nav)\n- [Banking](https:\/\/www.investopedia.com\/banking-4427754)\n- [Personal Finance](https:\/\/www.investopedia.com\/personal-finance-4427760)\n- [Economy](https:\/\/www.investopedia.com\/economy-4689801)\n- [Reviews](https:\/\/www.investopedia.com\/financial-product-reviews-7110783)\n- [Dictionary](https:\/\/www.investopedia.com\/financial-term-dictionary-4769738)\n\n- [About Us](https:\/\/www.investopedia.com\/about-us-5093223)\n- [Editorial Process](https:\/\/www.investopedia.com\/legal-4768893#toc-editorial-policy)\n- [Careers](https:\/\/www.people.inc\/careers)\n- [Contact Us](https:\/\/support.investopedia.com\/contactus)\n- [Privacy Policy](https:\/\/www.people.inc\/brands-privacy)\n- [Terms of Service](https:\/\/www.people.inc\/brands-termsofservice)\n- [Advertise](https:\/\/www.people.inc\/advertising)\n\n- [\\#](https:\/\/www.investopedia.com\/terms-beginning-with-num-4769350)\n- [A](https:\/\/www.investopedia.com\/terms-beginning-with-a-4769351)\n- [B](https:\/\/www.investopedia.com\/terms-beginning-with-b-4769352)\n- [C](https:\/\/www.investopedia.com\/terms-beginning-with-c-4769353)\n- [D](https:\/\/www.investopedia.com\/terms-beginning-with-d-4769354)\n- [E](https:\/\/www.investopedia.com\/terms-beginning-with-e-4769355)\n- [F](https:\/\/www.investopedia.com\/terms-beginning-with-f-4769356)\n- [G](https:\/\/www.investopedia.com\/terms-beginning-with-g-4769357)\n- [H](https:\/\/www.investopedia.com\/terms-beginning-with-h-4769358)\n- [I](https:\/\/www.investopedia.com\/terms-beginning-with-i-4769359)\n- [J](https:\/\/www.investopedia.com\/terms-beginning-with-j-4769360)\n- [K](https:\/\/www.investopedia.com\/terms-beginning-with-k-4769361)\n- [L](https:\/\/www.investopedia.com\/terms-beginning-with-l-4769362)\n- [M](https:\/\/www.investopedia.com\/terms-beginning-with-m-4769363)\n- [N](https:\/\/www.investopedia.com\/terms-beginning-with-n-4769364)\n- [O](https:\/\/www.investopedia.com\/terms-beginning-with-o-4769365)\n- [P](https:\/\/www.investopedia.com\/terms-beginning-with-p-4769366)\n- [Q](https:\/\/www.investopedia.com\/terms-beginning-with-q-4769367)\n- [R](https:\/\/www.investopedia.com\/terms-beginning-with-r-4769368)\n- [S](https:\/\/www.investopedia.com\/terms-beginning-with-s-4769369)\n- [T](https:\/\/www.investopedia.com\/terms-beginning-with-t-4769370)\n- [U](https:\/\/www.investopedia.com\/terms-beginning-with-u-4769371)\n- [V](https:\/\/www.investopedia.com\/terms-beginning-with-v-4769372)\n- [W](https:\/\/www.investopedia.com\/terms-beginning-with-w-4769373)\n- [X](https:\/\/www.investopedia.com\/terms-beginning-with-x-4769374)\n- [Y](https:\/\/www.investopedia.com\/terms-beginning-with-y-4769375)\n- [Z](https:\/\/www.investopedia.com\/terms-beginning-with-z-4769376)\n\nInvestopedia is part of the [People Inc.](https:\/\/www.people.inc\/) publishing family.\n\nNewsletter Sign Up\n\nNewsletter Sign Up","attrs_readable_markdown":"### Key Takeaways\n- Historical volatility assesses risk by measuring return dispersion over time.\n- High volatility offers potential profit opportunities for traders.\n- Many investors use volatility in crafting tailored trading strategies.\n\nGet personalized, AI-powered answers built on 27+ years of trusted expertise.\n\nHistorical volatility measures how much a stock’s price has fluctuated over a given period, helping investors assess its risk level. Traders and investors use this data to shape their strategies, such as identifying suitable entry and exit points or choosing between aggressive and conservative positions. In this article, you’ll learn how to calculate and annualize volatility in Excel step by step. As a trader, you can benefit from high volatility because it can offer opportunities in the short term, while low volatility is better if you're using a long-term, risk-averse approach.\n\nGetty Images, juststock\n\n## Calculating Historical Volatility in Excel: A Comprehensive Guide\nTo calculate the [volatility](https:\/\/www.investopedia.com\/terms\/v\/volatility.asp) of a given security in a Microsoft Excel spreadsheet, first determine the time frame for which the metric will be computed.\n\n### Determining the Appropriate Timeframe for Volatility Calculation\nVolatility is a time-bound measurement, meaning that it measures the price swings of an asset or security over a particular period. Depending on the type of trader you are, different time periods would be more appropriate. A day trader, for instance, may only care about weekly volatility while a swing trader may care about monthly. For the purposes of this article, a 10-day time period will be used in the example.\n\n### Inputting Price Data into Excel\nAfter determining your timeframe, the next step is to enter all the closing stock prices for that timeframe into cells B2 through B12 in sequential order, with the newest price at the bottom. (Keep in mind that if you are doing a 10-day timeframe, you will need the data for 11 days to compute the returns for a 10-day period.)\n\n### Calculating Inter-Day Returns\nIn column C, calculate the inter-day returns by dividing each price by the closing price of the day before and subtracting one. For example, if McDonald's ([MCD](https:\/\/www.investopedia.com\/markets\/quote?tvwidgetsymbol=MCD)) closed at \\$147.82 on the first day and at \\$149.50 on the second day, then the return of the second day would be (149.50\/147.82) - 1, or 0.011, indicating that the price on day two was 1.1% higher than the price on day one.\n\n### Finding Standard Deviation to Assess Volatility\nVolatility is inherently related to variance, and by extension, to [standard deviation](https:\/\/www.investopedia.com\/terms\/s\/standarddeviation.asp), or the degree to which prices differ from their mean. In cell C13, enter the formula \"=STDEV.S(C3:C12)\" to compute the standard deviation for the period.\n\n### Important\nThe link between standard deviation and volatility is evident in the types of technical indicators that investors use to chart a stock's volatility, such as [Bollinger Bands](https:\/\/www.investopedia.com\/terms\/b\/bollingerbands.asp), which are based on a stock's standard deviation and the [simple moving average (SMA)](https:\/\/www.investopedia.com\/terms\/s\/sma.asp).\n### Converting Period Volatility into an Annualized Figure\nHistorical volatility is usually converted into an annualized figure, so to convert the daily standard deviation calculated above into a usable metric, it must be multiplied by an annualization factor based on the period used. The annualization factor is the square root of however many periods exist in a year.\n\nThe table below shows the volatility for McDonald's within a 10-day time frame:\n\nThe example above used daily closing prices, and there are 250-252 trading days per year. Therefore, in cell C14, enter the formula \"=SQRT(252)\\*C13\" to convert the standard deviation for this 10-day period to annualized historical volatility.\n\n## Importance of Volatility for Investors\nWhile volatility in a stock can sometimes have a bad connotation, many traders and investors actually seek out higher volatility investments. They do this in the hopes of eventually making higher profits. If a stock or other security does not move, it has low volatility. However, it also has a low potential to make [capital gains](https:\/\/www.investopedia.com\/terms\/c\/capitalgain.asp).\n\nOn the other hand, a stock or other security with a very high volatility level can have tremendous profit potential. But by the same token, the risk of loss is quite high.\n\nTo be a trader or investor who capitalizes on volatility, the timing of any trades must be perfect. Even a correct market call could end up losing money if the security's wide price swings trigger either a [stop-loss order](https:\/\/www.investopedia.com\/articles\/stocks\/09\/use-stop-loss.asp) or a [margin call](https:\/\/www.investopedia.com\/terms\/m\/margincall.asp).\n\n## Why Is Historical Volatility Important?\nThe volatility of a particular asset or security is thought to exhibit [mean reversion](https:\/\/www.investopedia.com\/terms\/m\/meanreversion.asp) over time. This means that if a security is uncharacteristically volatile, it should return eventually to its long-run average. Likewise, if it is subdued, its volatility should increase. Calculating historical volatility is how to arrive at this average or mean level.\n\n## What Does the Volatility of a Stock Mean?\nVolatility describes the speed and magnitude of price swings over a given period of time (often on an annualized basis). Highly volatile stocks experience large and swift price swings, and they are often considered to be riskier than less volatile stocks.\n\n## Is Higher or Lower Historical Volatility More Advantageous?\nFor day traders and options traders, high volatility can provide more opportunities to move and out of positions or profit from volatility spikes. For most long-term buy-and-hold investors, however, lower volatility is often preferred.\n## The Bottom Line\nHistorical volatility can help you assess the risk of a security by measuring the dispersion of its returns over time. To calculate historical volatility with Excel, input a time frame, log closing prices, compute inter-day returns, calculate the standard deviation, and then annualize the volatility. Technical indicators like Bollinger Bands show a link between standard deviation and volatility.\n\nThe historical volatility lets you identify risk and profit opportunities. But your investment strategy can dictate whether you should use high volatility—it generally helps day traders find opportunities, but long-term investors who are concerned with short-term market movements may want to avoid it. Securities that are uncharacteristically volatile should return to long-run averages. This is known as mean reversion.","meta_canonical":null}

3. Robots.txt Check

Query:

Response:

4. Spam/Ban Check

Query:

Response:

5. Seen Status Check

ℹ️ Skipped - page is already crawled

📄

INDEXABLE

✅

CRAWLED

1 month ago

🤖

ROBOTS ALLOWED

Page Info Filters

Filter	Status	Condition	Details
HTTP status	PASS	`download_http_code = 200`	HTTP 200
Age cutoff	PASS	`download_stamp > now() - 6 MONTH`	1.7 months ago
History drop	PASS	`isNull(history_drop_reason)`	No drop reason
Spam/ban	PASS	`fh_dont_index != 1 AND ml_spam_score = 0`	ml_spam_score=0
Canonical	PASS	`meta_canonical IS NULL OR = '' OR = src_unparsed`	Not set

Page Details

Property	Value
URL	https://www.investopedia.com/ask/answers/021015/how-can-you-calculate-volatility-excel.asp
Last Crawled	2026-02-24 00:21:39 (1 month ago)
First Indexed	2017-11-12 20:13:40 (8 years ago)
HTTP Status Code	200
Meta Title	How to Calculate Stock Volatility with Excel
Meta Description	Learn to calculate stock volatility in Excel using historical prices and manage risk with confidence.
Meta Canonical	null
Boilerpipe Text	Key Takeaways Historical volatility assesses risk by measuring return dispersion over time. High volatility offers potential profit opportunities for traders. Many investors use volatility in crafting tailored trading strategies. Get personalized, AI-powered answers built on 27+ years of trusted expertise. Historical volatility measures how much a stock’s price has fluctuated over a given period, helping investors assess its risk level. Traders and investors use this data to shape their strategies, such as identifying suitable entry and exit points or choosing between aggressive and conservative positions. In this article, you’ll learn how to calculate and annualize volatility in Excel step by step. As a trader, you can benefit from high volatility because it can offer opportunities in the short term, while low volatility is better if you're using a long-term, risk-averse approach. Getty Images, juststock Calculating Historical Volatility in Excel: A Comprehensive Guide To calculate the volatility of a given security in a Microsoft Excel spreadsheet, first determine the time frame for which the metric will be computed. Determining the Appropriate Timeframe for Volatility Calculation Volatility is a time-bound measurement, meaning that it measures the price swings of an asset or security over a particular period. Depending on the type of trader you are, different time periods would be more appropriate. A day trader, for instance, may only care about weekly volatility while a swing trader may care about monthly. For the purposes of this article, a 10-day time period will be used in the example. Inputting Price Data into Excel After determining your timeframe, the next step is to enter all the closing stock prices for that timeframe into cells B2 through B12 in sequential order, with the newest price at the bottom. (Keep in mind that if you are doing a 10-day timeframe, you will need the data for 11 days to compute the returns for a 10-day period.) Calculating Inter-Day Returns In column C, calculate the inter-day returns by dividing each price by the closing price of the day before and subtracting one. For example, if McDonald's ( MCD ) closed at $147.82 on the first day and at $149.50 on the second day, then the return of the second day would be (149.50/147.82) - 1, or 0.011, indicating that the price on day two was 1.1% higher than the price on day one. Finding Standard Deviation to Assess Volatility Volatility is inherently related to variance, and by extension, to standard deviation , or the degree to which prices differ from their mean. In cell C13, enter the formula "=STDEV.S(C3:C12)" to compute the standard deviation for the period. Important The link between standard deviation and volatility is evident in the types of technical indicators that investors use to chart a stock's volatility, such as Bollinger Bands , which are based on a stock's standard deviation and the simple moving average (SMA) . Converting Period Volatility into an Annualized Figure Historical volatility is usually converted into an annualized figure, so to convert the daily standard deviation calculated above into a usable metric, it must be multiplied by an annualization factor based on the period used. The annualization factor is the square root of however many periods exist in a year. The table below shows the volatility for McDonald's within a 10-day time frame: The example above used daily closing prices, and there are 250-252 trading days per year. Therefore, in cell C14, enter the formula "=SQRT(252)*C13" to convert the standard deviation for this 10-day period to annualized historical volatility. Importance of Volatility for Investors While volatility in a stock can sometimes have a bad connotation, many traders and investors actually seek out higher volatility investments. They do this in the hopes of eventually making higher profits. If a stock or other security does not move, it has low volatility. However, it also has a low potential to make capital gains . On the other hand, a stock or other security with a very high volatility level can have tremendous profit potential. But by the same token, the risk of loss is quite high. To be a trader or investor who capitalizes on volatility, the timing of any trades must be perfect. Even a correct market call could end up losing money if the security's wide price swings trigger either a stop-loss order or a margin call . Why Is Historical Volatility Important? The volatility of a particular asset or security is thought to exhibit mean reversion over time. This means that if a security is uncharacteristically volatile, it should return eventually to its long-run average. Likewise, if it is subdued, its volatility should increase. Calculating historical volatility is how to arrive at this average or mean level. What Does the Volatility of a Stock Mean? Volatility describes the speed and magnitude of price swings over a given period of time (often on an annualized basis). Highly volatile stocks experience large and swift price swings, and they are often considered to be riskier than less volatile stocks. Is Higher or Lower Historical Volatility More Advantageous? For day traders and options traders, high volatility can provide more opportunities to move and out of positions or profit from volatility spikes. For most long-term buy-and-hold investors, however, lower volatility is often preferred. The Bottom Line Historical volatility can help you assess the risk of a security by measuring the dispersion of its returns over time. To calculate historical volatility with Excel, input a time frame, log closing prices, compute inter-day returns, calculate the standard deviation, and then annualize the volatility. Technical indicators like Bollinger Bands show a link between standard deviation and volatility. The historical volatility lets you identify risk and profit opportunities. But your investment strategy can dictate whether you should use high volatility—it generally helps day traders find opportunities, but long-term investors who are concerned with short-term market movements may want to avoid it. Securities that are uncharacteristically volatile should return to long-run averages. This is known as mean reversion.
Markdown	[Skip to content](https://www.investopedia.com/ask/answers/021015/how-can-you-calculate-volatility-excel.asp#main) [Investopedia](https://www.investopedia.com/) [Trade](https://www.investopedia.com/simulator/?inv_to_sim=global_nav) - Please fill out this field. - [Newsletters](https://www.investopedia.com/ask/answers/021015/how-can-you-calculate-volatility-excel.asp) Please fill out this field. - [News](https://www.investopedia.com/news-4427706) News - [Markets](https://www.investopedia.com/markets-news-4427704) - [Companies](https://www.investopedia.com/company-news-4427705) - [Earnings](https://www.investopedia.com/earnings-reports-5218419) - [CD Rates](https://www.investopedia.com/certificates-deposit-news-7111770) - [Mortgage Rates](https://www.investopedia.com/mortgage-rates-5184598) - [Economy](https://www.investopedia.com/economic-news-5218422) - [Government](https://www.investopedia.com/political-news-4689737) - [Crypto](https://www.investopedia.com/cryptocurrency-news-5114163) - [Live Markets News](https://www.investopedia.com/live-markets-news-11713202) - [Personal Finance](https://www.investopedia.com/personal-finance-news-5114159) - [View All](https://www.investopedia.com/news-4427706) - [Investing](https://www.investopedia.com/investing-4427685) Investing - [Stocks](https://www.investopedia.com/stocks-4427785) - [Cryptocurrency](https://www.investopedia.com/cryptocurrency-4427699) - [Bonds](https://www.investopedia.com/bonds-4689778) - [ETFs](https://www.investopedia.com/etfs-4427784) - [Options and Derivatives](https://www.investopedia.com/options-and-derivatives-trading-4689663) - [Commodities](https://www.investopedia.com/commodities-4427780) - [Trading](https://www.investopedia.com/trading-4427765) - [Automated Investing](https://www.investopedia.com/financial-technology-and-automated-investing-4689759) - [Brokers](https://www.investopedia.com/brokers-4689772) - [Fundamental Analysis](https://www.investopedia.com/fundamental-analysis-4689757) - [View All](https://www.investopedia.com/investing-4427685) - [Simulator](https://www.investopedia.com/simulator/?inv_to_sim=global_nav) Simulator - [Login / Portfolio](https://www.investopedia.com/simulator/portfolio) - [Trade](https://www.investopedia.com/simulator/trade/stocks) - [Research](https://www.investopedia.com/simulator/research) - [My Games](https://www.investopedia.com/simulator/games/my-games) - [Leaderboard](https://www.investopedia.com/simulator/games) - [Banking](https://www.investopedia.com/banking-4427754) Banking - [Savings Accounts](https://www.investopedia.com/savings-accounts-4689728) - [Certificates of Deposit (CDs)](https://www.investopedia.com/certificate-of-deposits-4689733) - [Money Market Accounts](https://www.investopedia.com/money-market-account-4689730) - [Checking Accounts](https://www.investopedia.com/checking-accounts-4689732) - [View All](https://www.investopedia.com/banking-4427754) - [Personal Finance](https://www.investopedia.com/personal-finance-4427760) Personal Finance - [Budgeting and Saving](https://www.investopedia.com/budgeting-and-savings-4427755) - [Personal Loans](https://www.investopedia.com/personal-loans-4689729) - [Insurance](https://www.investopedia.com/insurance-4427716) - [Mortgages](https://www.investopedia.com/mortgage-4689703) - [Credit and Debt](https://www.investopedia.com/credit-and-debt-4689724) - [Student Loans](https://www.investopedia.com/student-loans-4689727) - [Taxes](https://www.investopedia.com/taxes-4427724) - [Credit Cards](https://www.investopedia.com/credit-card-4689721) - [Financial Literacy](https://www.investopedia.com/financial-literacy-resource-center-7151950) - [Retirement](https://www.investopedia.com/retirement-planning-4689695) - [View All](https://www.investopedia.com/personal-finance-4427760) - [Economy](https://www.investopedia.com/economy-4689801) Economy - [Government and Policy](https://www.investopedia.com/government-and-policy-4689793) - [Monetary Policy](https://www.investopedia.com/monetary-policy-4689792) - [Fiscal Policy](https://www.investopedia.com/fiscal-policy-4689796) - [Economics](https://www.investopedia.com/economics-4689800) - [View All](https://www.investopedia.com/economy-4689801) - [Reviews](https://www.investopedia.com/financial-product-reviews-7110783) Reviews - [Best Online Brokers](https://www.investopedia.com/best-online-brokers-4587872) - [Best Crypto Exchanges](https://www.investopedia.com/best-crypto-exchanges-5071855) - [Best Savings Rates](https://www.investopedia.com/high-yield-savings-accounts-4770633) - [Best CD Rates](https://www.investopedia.com/best-cd-rates-4770214) - [Best Life Insurance](https://www.investopedia.com/the-best-life-insurance-companies-8763666) - [Best Mortgage Rates](https://www.investopedia.com/mortgage-rates-5094943) - [Best Robo-Advisors](https://www.investopedia.com/the-best-robo-advisors-8764849) - [Best Personal Loans](https://www.investopedia.com/the-best-personal-loans-8761582) - [Best Debt Relief Companies](https://www.investopedia.com/the-best-debt-relief-companies-8763179) - [View All](https://www.investopedia.com/financial-product-reviews-7110783) - [Newsletters](https://www.investopedia.com/ask/answers/021015/how-can-you-calculate-volatility-excel.asp) Follow Us - [News](https://www.investopedia.com/news-4427706) - [Markets](https://www.investopedia.com/markets-news-4427704) - [Companies](https://www.investopedia.com/company-news-4427705) - [Earnings](https://www.investopedia.com/earnings-reports-5218419) - [CD Rates](https://www.investopedia.com/certificates-deposit-news-7111770) - [Mortgage Rates](https://www.investopedia.com/mortgage-rates-5184598) - [Economy](https://www.investopedia.com/economic-news-5218422) - [Government](https://www.investopedia.com/political-news-4689737) - [Crypto](https://www.investopedia.com/cryptocurrency-news-5114163) - [Live Markets News](https://www.investopedia.com/live-markets-news-11713202) - [Personal Finance](https://www.investopedia.com/personal-finance-news-5114159) - [View All](https://www.investopedia.com/news-4427706) - [Investing](https://www.investopedia.com/investing-4427685) - [Stocks](https://www.investopedia.com/stocks-4427785) - [Cryptocurrency](https://www.investopedia.com/cryptocurrency-4427699) - [Bonds](https://www.investopedia.com/bonds-4689778) - [ETFs](https://www.investopedia.com/etfs-4427784) - [Options and Derivatives](https://www.investopedia.com/options-and-derivatives-trading-4689663) - [Commodities](https://www.investopedia.com/commodities-4427780) - [Trading](https://www.investopedia.com/trading-4427765) - [Automated Investing](https://www.investopedia.com/financial-technology-and-automated-investing-4689759) - [Brokers](https://www.investopedia.com/brokers-4689772) - [Fundamental Analysis](https://www.investopedia.com/fundamental-analysis-4689757) - [View All](https://www.investopedia.com/investing-4427685) - [Simulator](https://www.investopedia.com/simulator/?inv_to_sim=global_nav) - [Login / Portfolio](https://www.investopedia.com/simulator/portfolio) - [Trade](https://www.investopedia.com/simulator/trade/stocks) - [Research](https://www.investopedia.com/simulator/research) - [My Games](https://www.investopedia.com/simulator/games/my-games) - [Leaderboard](https://www.investopedia.com/simulator/games) - [Banking](https://www.investopedia.com/banking-4427754) - [Savings Accounts](https://www.investopedia.com/savings-accounts-4689728) - [Certificates of Deposit (CDs)](https://www.investopedia.com/certificate-of-deposits-4689733) - [Money Market Accounts](https://www.investopedia.com/money-market-account-4689730) - [Checking Accounts](https://www.investopedia.com/checking-accounts-4689732) - [View All](https://www.investopedia.com/banking-4427754) - [Personal Finance](https://www.investopedia.com/personal-finance-4427760) - [Budgeting and Saving](https://www.investopedia.com/budgeting-and-savings-4427755) - [Personal Loans](https://www.investopedia.com/personal-loans-4689729) - [Insurance](https://www.investopedia.com/insurance-4427716) - [Mortgages](https://www.investopedia.com/mortgage-4689703) - [Credit and Debt](https://www.investopedia.com/credit-and-debt-4689724) - [Student Loans](https://www.investopedia.com/student-loans-4689727) - [Taxes](https://www.investopedia.com/taxes-4427724) - [Credit Cards](https://www.investopedia.com/credit-card-4689721) - [Financial Literacy](https://www.investopedia.com/financial-literacy-resource-center-7151950) - [Retirement](https://www.investopedia.com/retirement-planning-4689695) - [View All](https://www.investopedia.com/personal-finance-4427760) - [Economy](https://www.investopedia.com/economy-4689801) - [Government and Policy](https://www.investopedia.com/government-and-policy-4689793) - [Monetary Policy](https://www.investopedia.com/monetary-policy-4689792) - [Fiscal Policy](https://www.investopedia.com/fiscal-policy-4689796) - [Economics](https://www.investopedia.com/economics-4689800) - [View All](https://www.investopedia.com/economy-4689801) - [Reviews](https://www.investopedia.com/financial-product-reviews-7110783) - [Best Online Brokers](https://www.investopedia.com/best-online-brokers-4587872) - [Best Crypto Exchanges](https://www.investopedia.com/best-crypto-exchanges-5071855) - [Best Savings Rates](https://www.investopedia.com/high-yield-savings-accounts-4770633) - [Best CD Rates](https://www.investopedia.com/best-cd-rates-4770214) - [Best Life Insurance](https://www.investopedia.com/the-best-life-insurance-companies-8763666) - [Best Mortgage Rates](https://www.investopedia.com/mortgage-rates-5094943) - [Best Robo-Advisors](https://www.investopedia.com/the-best-robo-advisors-8764849) - [Best Personal Loans](https://www.investopedia.com/the-best-personal-loans-8761582) - [Best Debt Relief Companies](https://www.investopedia.com/the-best-debt-relief-companies-8763179) - [View All](https://www.investopedia.com/financial-product-reviews-7110783) - Top Stories - [Average Retirement Savings at 59](https://www.investopedia.com/ideal-retirement-savings-goal-by-age-59-11909464) - [Average Credit Score in Your 40s and 50s](https://www.investopedia.com/the-average-credit-score-in-your-40s-and-50s-how-do-you-stack-up-11908475) - [Compare Your Net Worth to Others Making Similar Incomes](https://www.investopedia.com/how-your-net-worth-compares-to-others-with-the-same-income-you-might-be-surprised-11909297) - [Simple Ways to Earn Money With ChatGPT](https://www.investopedia.com/10-simple-ways-to-earn-money-using-chatgpt-without-any-tech-skills-11888989) Table of Contents Expand Table of Contents - [Calculating Volatility in Excel](https://www.investopedia.com/ask/answers/021015/how-can-you-calculate-volatility-excel.asp#toc-calculating-historical-volatility-in-excel-a-comprehensive-guide) - [Why Volatility Matters to Investors](https://www.investopedia.com/ask/answers/021015/how-can-you-calculate-volatility-excel.asp#toc-importance-of-volatility-for-investors) - [FAQs](https://www.investopedia.com/ask/answers/021015/how-can-you-calculate-volatility-excel.asp#toc-why-is-historical-volatility-important) - [The Bottom Line](https://www.investopedia.com/ask/answers/021015/how-can-you-calculate-volatility-excel.asp#toc-the-bottom-line) # How to Calculate Stock Volatility with Excel By [Claire Boyte-White](https://www.investopedia.com/contributors/53885/) ![]() ![](https://www.investopedia.com/thmb/yuyY5XexZUGW3O-2wc-qMFbgOFc=/200x200/filters:no_upscale:max_bytes$150000$:strip_icc/biopic_2__claire_boytewhitebw-5bfc261bc9e77c00262f61d9.png) [Full Bio](https://www.investopedia.com/contributors/53885/) Claire Boyte-White is the lead writer for NapkinFinance.com, co-author of I Am Net Worthy, and an Investopedia contributor. Claire's expertise lies in corporate finance & accounting, mutual funds, retirement planning, and technical analysis. Learn about our [editorial policies](https://www.investopedia.com/legal-4768893#editorial-policy) Updated February 20, 2026 Reviewed by [Khadija Khartit](https://www.investopedia.com/khadija-khartit-4799776) ![Khadija Khartit]() ![Khadija Khartit](https://www.investopedia.com/thmb/W2re2_rNx_MIW9RPQv9BMFNHVH4=/200x200/filters:no_upscale:max_bytes$150000$:strip_icc/Pic1-KhadijaKhartit-cc5b57fba2bd46ed87bdb3ddbcd2ef69.jpg) Reviewed by Khadija Khartit [Full Bio](https://www.investopedia.com/khadija-khartit-4799776) Khadija Khartit is a strategy, investment, and funding expert, and an educator of fintech and strategic finance in top universities. She has been an investor, entrepreneur, and advisor for more than 25 years. She is a FINRA Series 7, 63, and 66 license holder. Learn about our [Financial Review Board](https://www.investopedia.com/investopedia-financial-review-board-5076269) Fact checked by [Jared Ecker](https://www.investopedia.com/jared-ecker-5219601) ![BW Photo]() ![BW Photo](https://www.investopedia.com/thmb/uVEuJUr0M3SUKG_9PA0z42QdNAs=/200x200/filters:no_upscale:max_bytes$150000$:strip_icc/2G8T0126-BW-5a525a4d8c434885b365bee7b3807f60.jpg) Fact checked by Jared Ecker [Full Bio](https://www.investopedia.com/jared-ecker-5219601) Jared Ecker is a researcher and fact-checker. He possesses over a decade of experience in the Nuclear and National Defense sectors resolving issues on platforms as varied as stealth bombers to UAVs. He holds an A.A.S. in Aviation Maintenance Technology, a B.A. in History, and a M.S. in Environmental Policy & Management. Learn about our [editorial policies](https://www.investopedia.com/legal-4768893#editorial-policy) ### Key Takeaways - Historical volatility assesses risk by measuring return dispersion over time. - High volatility offers potential profit opportunities for traders. - Many investors use volatility in crafting tailored trading strategies. Get personalized, AI-powered answers built on 27+ years of trusted expertise. ASK Historical volatility measures how much a stock’s price has fluctuated over a given period, helping investors assess its risk level. Traders and investors use this data to shape their strategies, such as identifying suitable entry and exit points or choosing between aggressive and conservative positions. In this article, you’ll learn how to calculate and annualize volatility in Excel step by step. As a trader, you can benefit from high volatility because it can offer opportunities in the short term, while low volatility is better if you're using a long-term, risk-averse approach. ![Businessman working with data and graphs in spreadsheet documents for online analysis Microsoft Excel project dashboard accounting digital]() ![Businessman working with data and graphs in spreadsheet documents for online analysis Microsoft Excel project dashboard accounting digital](https://www.investopedia.com/thmb/JyGpB9cH95mPbUP69TtON3khiRc=/1500x0/filters:no_upscale:max_bytes$150000$:strip_icc/GettyImages-1597729432-457d2a5ed5ec4e1a8df44aa9e934f820.jpg) Getty Images, juststock ## Calculating Historical Volatility in Excel: A Comprehensive Guide To calculate the [volatility](https://www.investopedia.com/terms/v/volatility.asp) of a given security in a Microsoft Excel spreadsheet, first determine the time frame for which the metric will be computed. ### Determining the Appropriate Timeframe for Volatility Calculation Volatility is a time-bound measurement, meaning that it measures the price swings of an asset or security over a particular period. Depending on the type of trader you are, different time periods would be more appropriate. A day trader, for instance, may only care about weekly volatility while a swing trader may care about monthly. For the purposes of this article, a 10-day time period will be used in the example. ### Inputting Price Data into Excel After determining your timeframe, the next step is to enter all the closing stock prices for that timeframe into cells B2 through B12 in sequential order, with the newest price at the bottom. (Keep in mind that if you are doing a 10-day timeframe, you will need the data for 11 days to compute the returns for a 10-day period.) ### Calculating Inter-Day Returns In column C, calculate the inter-day returns by dividing each price by the closing price of the day before and subtracting one. For example, if McDonald's ([MCD](https://www.investopedia.com/markets/quote?tvwidgetsymbol=MCD)) closed at \$147.82 on the first day and at \$149.50 on the second day, then the return of the second day would be (149.50/147.82) - 1, or 0.011, indicating that the price on day two was 1.1% higher than the price on day one. ### Finding Standard Deviation to Assess Volatility Volatility is inherently related to variance, and by extension, to [standard deviation](https://www.investopedia.com/terms/s/standarddeviation.asp), or the degree to which prices differ from their mean. In cell C13, enter the formula "=STDEV.S(C3:C12)" to compute the standard deviation for the period. ### Important The link between standard deviation and volatility is evident in the types of technical indicators that investors use to chart a stock's volatility, such as [Bollinger Bands](https://www.investopedia.com/terms/b/bollingerbands.asp), which are based on a stock's standard deviation and the [simple moving average (SMA)](https://www.investopedia.com/terms/s/sma.asp). ### Converting Period Volatility into an Annualized Figure Historical volatility is usually converted into an annualized figure, so to convert the daily standard deviation calculated above into a usable metric, it must be multiplied by an annualization factor based on the period used. The annualization factor is the square root of however many periods exist in a year. The table below shows the volatility for McDonald's within a 10-day time frame: ![]() ![](https://www.investopedia.com/thmb/K0IAl35t0iFaM-7CDaKAX8H889Y=/1500x0/filters:no_upscale:max_bytes$150000$:strip_icc/volatility-5bfd885b46e0fb002640af5a) The example above used daily closing prices, and there are 250-252 trading days per year. Therefore, in cell C14, enter the formula "=SQRT(252)\*C13" to convert the standard deviation for this 10-day period to annualized historical volatility. ## Importance of Volatility for Investors While volatility in a stock can sometimes have a bad connotation, many traders and investors actually seek out higher volatility investments. They do this in the hopes of eventually making higher profits. If a stock or other security does not move, it has low volatility. However, it also has a low potential to make [capital gains](https://www.investopedia.com/terms/c/capitalgain.asp). On the other hand, a stock or other security with a very high volatility level can have tremendous profit potential. But by the same token, the risk of loss is quite high. To be a trader or investor who capitalizes on volatility, the timing of any trades must be perfect. Even a correct market call could end up losing money if the security's wide price swings trigger either a [stop-loss order](https://www.investopedia.com/articles/stocks/09/use-stop-loss.asp) or a [margin call](https://www.investopedia.com/terms/m/margincall.asp). ## Why Is Historical Volatility Important? The volatility of a particular asset or security is thought to exhibit [mean reversion](https://www.investopedia.com/terms/m/meanreversion.asp) over time. This means that if a security is uncharacteristically volatile, it should return eventually to its long-run average. Likewise, if it is subdued, its volatility should increase. Calculating historical volatility is how to arrive at this average or mean level. ## What Does the Volatility of a Stock Mean? Volatility describes the speed and magnitude of price swings over a given period of time (often on an annualized basis). Highly volatile stocks experience large and swift price swings, and they are often considered to be riskier than less volatile stocks. ## Is Higher or Lower Historical Volatility More Advantageous? For day traders and options traders, high volatility can provide more opportunities to move and out of positions or profit from volatility spikes. For most long-term buy-and-hold investors, however, lower volatility is often preferred. ## The Bottom Line Historical volatility can help you assess the risk of a security by measuring the dispersion of its returns over time. To calculate historical volatility with Excel, input a time frame, log closing prices, compute inter-day returns, calculate the standard deviation, and then annualize the volatility. Technical indicators like Bollinger Bands show a link between standard deviation and volatility. The historical volatility lets you identify risk and profit opportunities. But your investment strategy can dictate whether you should use high volatility—it generally helps day traders find opportunities, but long-term investors who are concerned with short-term market movements may want to avoid it. Securities that are uncharacteristically volatile should return to long-run averages. This is known as mean reversion. Read more - [Business](https://www.investopedia.com/business-4689640) - [Corporate Finance](https://www.investopedia.com/corporate-finance-and-accounting-4689821) - [Financial Ratios](https://www.investopedia.com/financial-ratios-4689817) Partner Links ## Related Articles [![Businessman working with data and graphs in spreadsheet documents for online analysis Microsoft Excel project dashboard accounting digital]() ![Businessman working with data and graphs in spreadsheet documents for online analysis Microsoft Excel project dashboard accounting digital](https://www.investopedia.com/thmb/DSO6rMDXoP3V8qJhWIWsGwKEL3Q=/282x188/filters:no_upscale:max_bytes$150000$:strip_icc/GettyImages-1597729432-457d2a5ed5ec4e1a8df44aa9e934f820.jpg) How to Calculate CAGR in Excel: A Simple Formula Guide](https://www.investopedia.com/ask/answers/071014/what-formula-calculating-compound-annual-growth-rate-cagr-excel.asp) [![A desk with paper that says Internal Rate of Return]() ![A desk with paper that says Internal Rate of Return](https://www.investopedia.com/thmb/OSd1TP8D3MtlZpIZbU5p5hqs6Qk=/282x188/filters:no_upscale:max_bytes$150000$:strip_icc/GettyImages-1025425776-419c3d61504148ca9daf14cdd7d5caa7.jpg) Calculate IRR in Excel: Step-by-Step Guide for Investors](https://www.investopedia.com/ask/answers/022615/what-formula-calculating-internal-rate-return-irr-excel.asp) [![]() ![](https://www.investopedia.com/thmb/c1B9xgAFKagsdoN3UNYK5xVzlqc=/282x188/filters:no_upscale:max_bytes$150000$:strip_icc/active-trading-lrg-5bfc2b24c9e77c005876f40d.jpg) Measure Stock Volatility with Historical Data: A Practical Approach](https://www.investopedia.com/articles/basics/09/simplified-measuring-interpreting-volatility.asp) [![Man working from home]() ![Man working from home](https://www.investopedia.com/thmb/rlp08TGSsir5TT-gIBqLeDnOGNQ=/282x188/filters:no_upscale:max_bytes$150000$:strip_icc/GettyImages-926084920-c6562fc8f4144ae796ef089bf7c6af44.jpg) Invest Wisely Using Price-to-Book (P/B) Ratio](https://www.investopedia.com/investing/using-price-to-book-ratio-evaluate-companies/) [![Businessperson working with business document]() ![Businessperson working with business document](https://www.investopedia.com/thmb/ZnPN3BjL-DdWVg_2ePe12ZfZc2A=/282x188/filters:no_upscale:max_bytes$150000$:strip_icc/GettyImages-953844116-3875ca5809a54851b75286564c4ba358.jpg) How to Calculate Return on Assets (ROA) with Examples](https://www.investopedia.com/ask/answers/031215/what-formula-calculating-return-assets-roa.asp) [![JPMorgan Chase bank headquarters building in London]() ![JPMorgan Chase bank headquarters building in London](https://www.investopedia.com/thmb/uAIDNIajcouE-8V77faNmSnoQyE=/282x188/filters:no_upscale:max_bytes$150000$:strip_icc/INV_JPMorganChaseHQinLondon_GettyImages-2196274932-1a9ec636044548949face55c4dab57a0.jpg) Understanding the Capital Adequacy Ratio for Banks](https://www.investopedia.com/ask/answers/051415/what-formula-calculating-capital-risk-weight-assets-ratio-bank.asp) [![]() ![](https://www.investopedia.com/thmb/vWK1g410oV4zbUM9jjhCI2MLdIo=/282x188/filters:no_upscale:max_bytes$150000$:strip_icc/ap578045340213-5bfc2fb246e0fb00260233a5.jpg) Retail Sector P/E Ratios: Understand the Market Valuation](https://www.investopedia.com/ask/answers/071615/what-average-pricetoearnings-ratio-retail-sector.asp) [![Presenting company annual report statement document, papers chart, growing up graph.]() ![Presenting company annual report statement document, papers chart, growing up graph.](https://www.investopedia.com/thmb/Kwx64GZGQgzyekKt0d9HqlgNgiA=/282x188/filters:no_upscale:max_bytes$150000$:strip_icc/GettyImages-958271424-0d5a8e8f1e8246218841e30fb7f98d4b.jpg) How to Calculate the PEG Ratio for Stock Valuation](https://www.investopedia.com/ask/answers/06/pegratioearningsgrowthrate.asp) [![stack of coins and a bar graph]() ![stack of coins and a bar graph](https://www.investopedia.com/thmb/bnbOb5APCr4r71mcqJ7tPtAjzx4=/282x188/filters:no_upscale:max_bytes$150000$:strip_icc/GettyImages-1158986487-249fb6e8fdf1496dab20d2801571a8cb.jpg) Calculating Variance in Gross Margin Percentages by Price and Cost](https://www.investopedia.com/ask/answers/031715/how-do-you-calculate-variance-gross-margin-percentage-due-price-and-cost.asp) [![ROE]() ![ROE](https://www.investopedia.com/thmb/r1ETfzcFQ9YDG93YMp5e4WASK4Y=/282x188/filters:no_upscale:max_bytes$150000$:strip_icc/returnonequity-3e37bbcaf73a47039344551bc172a699.png) Return on Equity (ROE) Calculation and What It Means](https://www.investopedia.com/terms/r/returnonequity.asp) [![ROI]() ![ROI](https://www.investopedia.com/thmb/czZoWKmsG9zkWZlf0xpiD6Y34ds=/282x188/filters:no_upscale:max_bytes$150000$:strip_icc/Roi-be50b2f1f1e447b4af1b917bda2708f9.png) What Is Return on Investment (ROI) and How to Calculate It](https://www.investopedia.com/terms/r/returnoninvestment.asp) [![Debt-Service Coverage Ratio (DSCR)]() ![Debt-Service Coverage Ratio (DSCR)](https://www.investopedia.com/thmb/kx-nlPb-vHeDwkVupz6I6N4wDjE=/282x188/filters:no_upscale:max_bytes$150000$:strip_icc/Debt-service-coverage-ratio-294523bd49304cefabe7af0ad69a28f4.jpg) Debt-Service Coverage Ratio (DSCR): How to Use and Calculate It](https://www.investopedia.com/terms/d/dscr.asp) [![New York Stock Exchange, Wall st, New York, USA]() ![New York Stock Exchange, Wall st, New York, USA](https://www.investopedia.com/thmb/IMIfvnzkzGMCvcS1UlDYHGeqK5U=/282x188/filters:no_upscale:max_bytes$150000$:strip_icc/GettyImages-668600179-4215b1859f4d4181b8a468c116939c86.jpg) Top Income Statement Ratios: Gross, Profit, and Operating Margins Explained](https://www.investopedia.com/ask/answers/102714/what-are-main-income-statement-ratios.asp) [![]() ![](https://www.investopedia.com/thmb/x4KyOC-ODVw96m4ASOAQkWhqXBo=/282x188/filters:no_upscale:max_bytes$150000$:strip_icc/investing1-5bfc2b9046e0fb005119b292.jpg) Evaluating Low P/E Ratio Stocks: Are They Good Investments?](https://www.investopedia.com/ask/answers/05/lowperatiostocksbetterinvestments.asp) [![Cost of Goods Sold (COGS)]() ![Cost of Goods Sold (COGS)](https://www.investopedia.com/thmb/OkRSASAYJttN7Y7WRXDiujU-M1k=/282x188/filters:no_upscale:max_bytes$150000$:strip_icc/Cost-of-Goods-Sold-COGS-d670f0a578d745c0979681c75bd43099.png) Cost of Goods Sold (COGS) Explained With Methods to Calculate It](https://www.investopedia.com/terms/c/cogs.asp) [![Accountant hand holding pen working on calculator and laptop computer.]() ![Accountant hand holding pen working on calculator and laptop computer.](https://www.investopedia.com/thmb/PiesMviXY0E_1HMmrAYhH9tPhbA=/282x188/filters:no_upscale:max_bytes$150000$:strip_icc/GettyImages-865226698-2fef1d5dae1944839ece7a3cd81902ac.jpg) Understanding the Correlation Between Price-to-Book Ratio and ROE](https://www.investopedia.com/ask/answers/010915/does-high-price-book-ratio-correspond-high-roe.asp) [Investopedia](https://www.investopedia.com/) [Newsletter Sign Up](https://www.investopedia.com/ask/answers/021015/how-can-you-calculate-volatility-excel.asp) Follow Us - [News](https://www.investopedia.com/news-4427706) - [Investing](https://www.investopedia.com/investing-4427685) - [Simulator](https://www.investopedia.com/simulator/?inv_to_sim=global_nav) - [Banking](https://www.investopedia.com/banking-4427754) - [Personal Finance](https://www.investopedia.com/personal-finance-4427760) - [Economy](https://www.investopedia.com/economy-4689801) - [Reviews](https://www.investopedia.com/financial-product-reviews-7110783) - [Dictionary](https://www.investopedia.com/financial-term-dictionary-4769738) - [About Us](https://www.investopedia.com/about-us-5093223) - [Editorial Process](https://www.investopedia.com/legal-4768893#toc-editorial-policy) - [Careers](https://www.people.inc/careers) - [Contact Us](https://support.investopedia.com/contactus) - [Privacy Policy](https://www.people.inc/brands-privacy) - [Terms of Service](https://www.people.inc/brands-termsofservice) - [Advertise](https://www.people.inc/advertising) - [\#](https://www.investopedia.com/terms-beginning-with-num-4769350) - [A](https://www.investopedia.com/terms-beginning-with-a-4769351) - [B](https://www.investopedia.com/terms-beginning-with-b-4769352) - [C](https://www.investopedia.com/terms-beginning-with-c-4769353) - [D](https://www.investopedia.com/terms-beginning-with-d-4769354) - [E](https://www.investopedia.com/terms-beginning-with-e-4769355) - [F](https://www.investopedia.com/terms-beginning-with-f-4769356) - [G](https://www.investopedia.com/terms-beginning-with-g-4769357) - [H](https://www.investopedia.com/terms-beginning-with-h-4769358) - [I](https://www.investopedia.com/terms-beginning-with-i-4769359) - [J](https://www.investopedia.com/terms-beginning-with-j-4769360) - [K](https://www.investopedia.com/terms-beginning-with-k-4769361) - [L](https://www.investopedia.com/terms-beginning-with-l-4769362) - [M](https://www.investopedia.com/terms-beginning-with-m-4769363) - [N](https://www.investopedia.com/terms-beginning-with-n-4769364) - [O](https://www.investopedia.com/terms-beginning-with-o-4769365) - [P](https://www.investopedia.com/terms-beginning-with-p-4769366) - [Q](https://www.investopedia.com/terms-beginning-with-q-4769367) - [R](https://www.investopedia.com/terms-beginning-with-r-4769368) - [S](https://www.investopedia.com/terms-beginning-with-s-4769369) - [T](https://www.investopedia.com/terms-beginning-with-t-4769370) - [U](https://www.investopedia.com/terms-beginning-with-u-4769371) - [V](https://www.investopedia.com/terms-beginning-with-v-4769372) - [W](https://www.investopedia.com/terms-beginning-with-w-4769373) - [X](https://www.investopedia.com/terms-beginning-with-x-4769374) - [Y](https://www.investopedia.com/terms-beginning-with-y-4769375) - [Z](https://www.investopedia.com/terms-beginning-with-z-4769376) Investopedia is part of the [People Inc.](https://www.people.inc/) publishing family. Newsletter Sign Up Newsletter Sign Up
Readable Markdown	### Key Takeaways - Historical volatility assesses risk by measuring return dispersion over time. - High volatility offers potential profit opportunities for traders. - Many investors use volatility in crafting tailored trading strategies. Get personalized, AI-powered answers built on 27+ years of trusted expertise. Historical volatility measures how much a stock’s price has fluctuated over a given period, helping investors assess its risk level. Traders and investors use this data to shape their strategies, such as identifying suitable entry and exit points or choosing between aggressive and conservative positions. In this article, you’ll learn how to calculate and annualize volatility in Excel step by step. As a trader, you can benefit from high volatility because it can offer opportunities in the short term, while low volatility is better if you're using a long-term, risk-averse approach. Getty Images, juststock ## Calculating Historical Volatility in Excel: A Comprehensive Guide To calculate the [volatility](https://www.investopedia.com/terms/v/volatility.asp) of a given security in a Microsoft Excel spreadsheet, first determine the time frame for which the metric will be computed. ### Determining the Appropriate Timeframe for Volatility Calculation Volatility is a time-bound measurement, meaning that it measures the price swings of an asset or security over a particular period. Depending on the type of trader you are, different time periods would be more appropriate. A day trader, for instance, may only care about weekly volatility while a swing trader may care about monthly. For the purposes of this article, a 10-day time period will be used in the example. ### Inputting Price Data into Excel After determining your timeframe, the next step is to enter all the closing stock prices for that timeframe into cells B2 through B12 in sequential order, with the newest price at the bottom. (Keep in mind that if you are doing a 10-day timeframe, you will need the data for 11 days to compute the returns for a 10-day period.) ### Calculating Inter-Day Returns In column C, calculate the inter-day returns by dividing each price by the closing price of the day before and subtracting one. For example, if McDonald's ([MCD](https://www.investopedia.com/markets/quote?tvwidgetsymbol=MCD)) closed at \$147.82 on the first day and at \$149.50 on the second day, then the return of the second day would be (149.50/147.82) - 1, or 0.011, indicating that the price on day two was 1.1% higher than the price on day one. ### Finding Standard Deviation to Assess Volatility Volatility is inherently related to variance, and by extension, to [standard deviation](https://www.investopedia.com/terms/s/standarddeviation.asp), or the degree to which prices differ from their mean. In cell C13, enter the formula "=STDEV.S(C3:C12)" to compute the standard deviation for the period. ### Important The link between standard deviation and volatility is evident in the types of technical indicators that investors use to chart a stock's volatility, such as [Bollinger Bands](https://www.investopedia.com/terms/b/bollingerbands.asp), which are based on a stock's standard deviation and the [simple moving average (SMA)](https://www.investopedia.com/terms/s/sma.asp). ### Converting Period Volatility into an Annualized Figure Historical volatility is usually converted into an annualized figure, so to convert the daily standard deviation calculated above into a usable metric, it must be multiplied by an annualization factor based on the period used. The annualization factor is the square root of however many periods exist in a year. The table below shows the volatility for McDonald's within a 10-day time frame: The example above used daily closing prices, and there are 250-252 trading days per year. Therefore, in cell C14, enter the formula "=SQRT(252)\*C13" to convert the standard deviation for this 10-day period to annualized historical volatility. ## Importance of Volatility for Investors While volatility in a stock can sometimes have a bad connotation, many traders and investors actually seek out higher volatility investments. They do this in the hopes of eventually making higher profits. If a stock or other security does not move, it has low volatility. However, it also has a low potential to make [capital gains](https://www.investopedia.com/terms/c/capitalgain.asp). On the other hand, a stock or other security with a very high volatility level can have tremendous profit potential. But by the same token, the risk of loss is quite high. To be a trader or investor who capitalizes on volatility, the timing of any trades must be perfect. Even a correct market call could end up losing money if the security's wide price swings trigger either a [stop-loss order](https://www.investopedia.com/articles/stocks/09/use-stop-loss.asp) or a [margin call](https://www.investopedia.com/terms/m/margincall.asp). ## Why Is Historical Volatility Important? The volatility of a particular asset or security is thought to exhibit [mean reversion](https://www.investopedia.com/terms/m/meanreversion.asp) over time. This means that if a security is uncharacteristically volatile, it should return eventually to its long-run average. Likewise, if it is subdued, its volatility should increase. Calculating historical volatility is how to arrive at this average or mean level. ## What Does the Volatility of a Stock Mean? Volatility describes the speed and magnitude of price swings over a given period of time (often on an annualized basis). Highly volatile stocks experience large and swift price swings, and they are often considered to be riskier than less volatile stocks. ## Is Higher or Lower Historical Volatility More Advantageous? For day traders and options traders, high volatility can provide more opportunities to move and out of positions or profit from volatility spikes. For most long-term buy-and-hold investors, however, lower volatility is often preferred. ## The Bottom Line Historical volatility can help you assess the risk of a security by measuring the dispersion of its returns over time. To calculate historical volatility with Excel, input a time frame, log closing prices, compute inter-day returns, calculate the standard deviation, and then annualize the volatility. Technical indicators like Bollinger Bands show a link between standard deviation and volatility. The historical volatility lets you identify risk and profit opportunities. But your investment strategy can dictate whether you should use high volatility—it generally helps day traders find opportunities, but long-term investors who are concerned with short-term market movements may want to avoid it. Securities that are uncharacteristically volatile should return to long-run averages. This is known as mean reversion.
Shard	148 (laksa)
Root Hash	7273739457760125548
Unparsed URL	com,investopedia!www,/ask/answers/021015/how-can-you-calculate-volatility-excel.asp s443