🕷️ Crawler Inspector

URL Lookup

Direct Parameter Lookup

Raw Queries and Responses

1. Shard Calculation

Query:

Response:

Calculated Shard: 148 (from laksa036)

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/articles/investing/102715/computing-historical-volatility-excel.asp\')), getAhrefsUnparsedNoserviceFromURL(\'https://www.investopedia.com/articles/investing/102715/computing-historical-volatility-excel.asp\'))) FORMAT JSONEachRow'

Response:

{"found_url":"https:\/\/www.investopedia.com\/articles\/investing\/102715\/computing-historical-volatility-excel.asp","crawl_time":1775764868,"first_indexed_time":1511434547,"http_code":200,"src_unparsed":"com,investopedia!www,\/articles\/investing\/102715\/computing-historical-volatility-excel.asp s443","src_root_hash":"7273739457760125548","history_drop_reason":null,"meta_title":"Computing Historical Volatility in Excel","meta_descriptions":["We examine how annualized historical volatility is computed from daily log returns, variance, and standard deviation."],"attrs_boilerpipe_text":"The value of financial assets varies on a daily basis. Investors need an indicator to quantify these changes that are often difficult to predict. Supply and demand are the two principal factors that affect changes in asset prices. In return, price moves reflect an amplitude of fluctuations, which are the causes of proportional profits and losses. From an investor's perspective, the uncertainty surrounding such influences and fluctuations is called risk.\nThe price of an option depends on its underlying ability to move, or in other words its ability to be volatile. The more likely it is to move, the more expensive its premium will be closer to expiration. Thus, computing the volatility of an underlying asset helps investors to price\nderivatives\nbased on that asset.\nKey Takeaways\nPricing options contracts and other derivatives directly involves being able to compute an asset's volatility, or the speed of price fluctuations.\nVolatility is derived from the variance of price movements on an annualized basis.\nThis calculation can be complex and time-consuming, but using Excel calculating an asset's historical volatility can be done quickly and accurately.\nMeasuring the Asset's Variation\nOne way to measure an asset's variation is to quantify the daily returns (percent move on a daily basis) of the asset. This brings us to the definition and concept of historical volatility. Historical volatility is based on historical prices and represents the degree of variability in the returns of an asset. This number is without a unit and is expressed as a percentage.\nComputing Historical Volatility\nIf we call P (t) the price of a financial asset (\nforeign exchange asset\n, stocks,\nforex pair\n, etc.) at time t and P (t-1) the price of the financial asset at t-1, we define the daily return r (t) of the asset at time t by:\nr (t) = ln (P (t) \/ P (t-1))\nwhere Ln (x) = natural logarithm function.\nThe\ntotal return\n R at time t is:  \nR = r1 + r2 + r3 + 2 + ... +rt-1+ rt,\nwhich is equivalent to:\nR = Ln (P1 \/ P0) + ... Ln (Pt-1 \/ Pt-2) + Ln (Pt \/ Pt-1) \nWe have the following equality:\nLn (a) + Ln (b) = Ln (a*b)\nSo, this gives:\nR = Ln [(P1 \/ P0* (P2 \/ P1)* ... (Pt \/ Pt-1]\nR = Ln [(P1. P2 ... Pt-1. Pt) \/ (P0. P1. P2 ... Pt-2. Pt-1)]\nAnd, after simplification, we have:\nR = Ln (Pt \/ P0).\nThe yield is usually computed as the difference in relative price changes. This means that if an asset has a price of P (t) at time t and P (t + h) at time t + h> t, the return (r) is:\nr = (P (t + t) -P (t)) \/ P (t) = [P (t + h) \/ P (t)] – 1\nWhen the return is small, such as just a few percent, we have:\nr ≈ Ln (1 + r)\nWe can substitute r with the logarithm of the current price since:\nr ≈ Ln (1 + r)\nr ≈ Ln (1 + ([P (t + h) \/ P (t)] - 1))\nr ≈ Ln (P (t + h) \/ P (t))\nFrom a series of closing prices for instance, it is enough to take the logarithm of the ratio of two consecutive prices to compute daily returns r (t).\nThus, one can also compute the total return R by using only the initial and final prices.\nAnnualized Volatility\nTo fully appreciate the different volatilities over a period of a year, we multiply this volatility by a factor that accounts for the variability of the assets for one year.  \nTo do this we use the\nvariance\n. The variance is the square of the deviation from the average daily returns for one day.\nTo compute the square number of the deviations from the average daily returns for 365 days, we multiply the variance by the number of days (365). The annualized standard deviation is found by taking the square root of the result:\nVariance = σ²daily = [Σ (r (t)) ² \/ (n - 1)]\nFor the annualized variance, if we assume that the year is 365 days, and every day has the same daily variance, σ²daily, we obtain:\nAnnualized Variance = 365. σ²daily\nAnnualized Variance = 365. [Σ (r (t)) ² \/ (n - 1)]\nFinally, as the volatility is defined as the square root of variance:\nVolatility = √ (variance annualized)\nVolatility = √ (365. Σ²daily)\nVolatility = √ (365 [Σ (r (t)) ² \/ (n - 1)].)\nSimulation\nThe Data\nWe simulate from the Excel function =\nRANDBETWEEN\na stock price that varies daily between values of 94 and 104.\nComputing the Daily Returns\nIn column E, we enter \"Ln (P (t) \/ P (t-1)).\"\nComputing the Square of Daily Returns\nIn column G, we enter \"(Ln (P (t) \/ P (t-1)) ^2.”\nComputing the Daily Variance\nTo compute the variance, we take the sum of the squares obtained and divide by the (number of days -1). So:\nIn cell F25, we have \"= sum (F6: F19).\"\nIn cell F26, we compute \"= F25 \/ 18\" since we have 19 -1 data points for this calculation.\nComputing the Daily Standard Deviation\nTo compute the standard deviation on a daily basis, we compute the square root of the daily variance. So:\nIn cell F28, we compute \"= Square.Root(F26).\"\nIn cell G29, cell F28 is shown as a percentage.\nComputing the Annualized Variance\nTo compute the annualized variance from the daily variance, we assume that each day has the same variance, and we multiply the daily variance by 365 with weekends included. So:\nIn cell F30, we have \"= F26* 365.\" \nComputing the Annualized Standard Deviation\nTo compute the annualized standard deviation, we only need to compute the square root of the annualized variance. So:\nIn cell F32, we have \"= ROOT (F30).\"\nIn cell G33, cell F32 is shown as a percentage.\nThis square root of the annualized variance gives us the historical volatility.","attrs_markdown":"\n\n[Skip to content](https:\/\/www.investopedia.com\/articles\/investing\/102715\/computing-historical-volatility-excel.asp#main)\n\n[Investopedia](https:\/\/www.investopedia.com\/)\n\n[Live](https:\/\/www.investopedia.com\/stock-market-today-dow-jones-s-and-p-500-04092026-11946208)\n\n- Please fill out this field.\n- [Newsletters](https:\/\/www.investopedia.com\/articles\/investing\/102715\/computing-historical-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\/articles\/investing\/102715\/computing-historical-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- [Could the Healthiest Countries Be the Perfect Retirement Spots?](https:\/\/www.investopedia.com\/could-the-world-s-healthiest-countries-be-perfect-spots-to-retire-11945240)\n- [Warren Buffett's Timeless Wisdom on Investing Pitfalls](https:\/\/www.investopedia.com\/timeless-wisdom-from-buffett-how-to-avoid-common-investment-pitfalls-11945227)\n- [How ChatGPT Can Help with Retirement Planning](https:\/\/www.investopedia.com\/chatgpt-can-help-with-retirement-planning-but-experts-say-it-has-limits-11945243)\n- [Why Is Inflation So Stubbornly High? Blame the Stock Market](https:\/\/www.investopedia.com\/why-is-inflation-so-stubbornly-high-blame-the-stock-market-11944367)\n\nTable of Contents\n\nExpand\n\nTable of Contents\n\n- [Measuring the Asset's Variation](https:\/\/www.investopedia.com\/articles\/investing\/102715\/computing-historical-volatility-excel.asp#toc-measuring-the-assets-variation)\n- [Computing Historical Volatility](https:\/\/www.investopedia.com\/articles\/investing\/102715\/computing-historical-volatility-excel.asp#toc-computing-historical-volatility)\n- [Annualized Volatility](https:\/\/www.investopedia.com\/articles\/investing\/102715\/computing-historical-volatility-excel.asp#toc-annualized-volatility)\n- [Simulation in Excel](https:\/\/www.investopedia.com\/articles\/investing\/102715\/computing-historical-volatility-excel.asp#toc-simulation)\n\n# Computing Historical Volatility in Excel\nBy\n\n[Caroline Banton](https:\/\/www.investopedia.com\/contributors\/99535\/)\n\n[Full Bio](https:\/\/www.investopedia.com\/contributors\/99535\/)\n\nCaroline Banton has 6+ years of experience as a writer of business and finance articles. She also writes biographies for Story Terrace.\n\nLearn about our  [editorial policies](https:\/\/www.investopedia.com\/legal-4768893#editorial-policy)\n\nUpdated January 31, 2022\n\nReviewed by [Julius Mansa](https:\/\/www.investopedia.com\/julius-mansa-4799781)\n\nFact checked by\n\n[Suzanne Kvilhaug](https:\/\/www.investopedia.com\/suzanne-kvilhaug-5197828)\n\n![Suzanne Kvilhaug]()\n\n![Suzanne Kvilhaug](https:\/\/www.investopedia.com\/thmb\/znbFHeELuyfkeore2XU_GHw2QaM=\/200x200\/filters:no_upscale\\(\\):max_bytes\\(150000\\):strip_icc\\(\\)\/SuzannesHeadshot-3dcd99dc3f2e405e8bd37271894491ac.jpg)\n\nFact checked by Suzanne Kvilhaug\n\n[Full Bio](https:\/\/www.investopedia.com\/suzanne-kvilhaug-5197828)\n\nSuzanne is a content marketer, writer, and fact-checker. She holds a Bachelor of Science in Finance degree from Bridgewater State University and helps develop content strategies.\n\nLearn about our  [editorial policies](https:\/\/www.investopedia.com\/legal-4768893#editorial-policy)\n\nClose\n\nThe value of financial assets varies on a daily basis. Investors need an indicator to quantify these changes that are often difficult to predict. Supply and demand are the two principal factors that affect changes in asset prices. In return, price moves reflect an amplitude of fluctuations, which are the causes of proportional profits and losses. From an investor's perspective, the uncertainty surrounding such influences and fluctuations is called risk.\n\nThe price of an option depends on its underlying ability to move, or in other words its ability to be volatile. The more likely it is to move, the more expensive its premium will be closer to expiration. Thus, computing the volatility of an underlying asset helps investors to price [derivatives](https:\/\/www.investopedia.com\/terms\/d\/derivative.asp) based on that asset.\n\n### Key Takeaways\n- Pricing options contracts and other derivatives directly involves being able to compute an asset's volatility, or the speed of price fluctuations.\n- Volatility is derived from the variance of price movements on an annualized basis.\n- This calculation can be complex and time-consuming, but using Excel calculating an asset's historical volatility can be done quickly and accurately.\n## Measuring the Asset's Variation\nOne way to measure an asset's variation is to quantify the daily returns (percent move on a daily basis) of the asset. This brings us to the definition and concept of historical volatility. Historical volatility is based on historical prices and represents the degree of variability in the returns of an asset. This number is without a unit and is expressed as a percentage.\n\n## Computing Historical Volatility\nIf we call P (t) the price of a financial asset ([foreign exchange asset](https:\/\/www.investopedia.com\/terms\/forex\/f\/foreign-exchange-markets.asp), stocks, [forex pair](https:\/\/www.investopedia.com\/terms\/forex\/e\/eur-usd-euro-us-dollar-currency-pair.asp), etc.) at time t and P (t-1) the price of the financial asset at t-1, we define the daily return r (t) of the asset at time t by:\n\n> r (t) = ln (P (t) \/ P (t-1))\n\nwhere Ln (x) = natural logarithm function.\n\nThe [total return](https:\/\/www.investopedia.com\/terms\/t\/totalreturn.asp) R at time t is:\n\n> R = r1 + r2 + r3 + 2 + ... +rt-1+ rt,\n\nwhich is equivalent to:\n\n> R = Ln (P1 \/ P0) + ... Ln (Pt-1 \/ Pt-2) + Ln (Pt \/ Pt-1)\n\nWe have the following equality:\n\n> Ln (a) + Ln (b) = Ln (a\\*b)\n\nSo, this gives:\n\n> R = Ln \\[(P1 \/ P0\\* (P2 \/ P1)\\* ... (Pt \/ Pt-1\\]\n\n> R = Ln \\[(P1. P2 ... Pt-1. Pt) \/ (P0. P1. P2 ... Pt-2. Pt-1)\\]\n\nAnd, after simplification, we have:\n\n> R = Ln (Pt \/ P0).\n\nThe yield is usually computed as the difference in relative price changes. This means that if an asset has a price of P (t) at time t and P (t + h) at time t + h\\> t, the return (r) is:\n\n> r = (P (t + t) -P (t)) \/ P (t) = \\[P (t + h) \/ P (t)\\] – 1\n\nWhen the return is small, such as just a few percent, we have:\n\n> r ≈ Ln (1 + r)\n\nWe can substitute r with the logarithm of the current price since:\n\n> r ≈ Ln (1 + r)\n\n> r ≈ Ln (1 + (\\[P (t + h) \/ P (t)\\] - 1))\n\n> r ≈ Ln (P (t + h) \/ P (t))\n\nFrom a series of closing prices for instance, it is enough to take the logarithm of the ratio of two consecutive prices to compute daily returns r (t).\n\nThus, one can also compute the total return R by using only the initial and final prices.\n\n## Annualized Volatility\nTo fully appreciate the different volatilities over a period of a year, we multiply this volatility by a factor that accounts for the variability of the assets for one year.\n\nTo do this we use the [variance](https:\/\/www.investopedia.com\/terms\/v\/variance.asp). The variance is the square of the deviation from the average daily returns for one day.\n\nTo compute the square number of the deviations from the average daily returns for 365 days, we multiply the variance by the number of days (365). The annualized standard deviation is found by taking the square root of the result:\n\n> *Variance = σ²daily = \\[Σ (r (t)) ² \/ (n - 1)\\]*\n\nFor the annualized variance, if we assume that the year is 365 days, and every day has the same daily variance, σ²daily, we obtain:\n\n> Annualized Variance = 365. σ²daily  \n> Annualized Variance = 365. \\[Σ (r (t)) ² \/ (n - 1)\\]\n\nFinally, as the volatility is defined as the square root of variance:\n\n> Volatility = √ (variance annualized)\n\n> Volatility = √ (365. Σ²daily)\n\n> Volatility = √ (365 \\[Σ (r (t)) ² \/ (n - 1)\\].)\n## Simulation\n### The Data\nWe simulate from the Excel function =*RANDBETWEEN* a stock price that varies daily between values of 94 and 104.\n\n![]()\n\n![](https:\/\/www.investopedia.com\/thmb\/5jwo7uqyn3Z6GtVASaouBvC6evE=\/1500x0\/filters:no_upscale\\(\\):max_bytes\\(150000\\):strip_icc\\(\\)\/volatiliteexcel1-5bfd8dd7c9e77c0051849180)\n### Computing the Daily Returns\n- In column E, we enter \"Ln (P (t) \/ P (t-1)).\"\n### Computing the Square of Daily Returns\n- In column G, we enter \"(Ln (P (t) \/ P (t-1)) ^2.”\n### Computing the Daily Variance\nTo compute the variance, we take the sum of the squares obtained and divide by the (number of days -1). So:\n\n- In cell F25, we have \"= sum (F6: F19).\"\n- In cell F26, we compute \"= F25 \/ 18\" since we have 19 -1 data points for this calculation.\n### Computing the Daily Standard Deviation\nTo compute the standard deviation on a daily basis, we compute the square root of the daily variance. So:\n\n- In cell F28, we compute \"= Square.Root(F26).\"\n- In cell G29, cell F28 is shown as a percentage.\n### Computing the Annualized Variance\nTo compute the annualized variance from the daily variance, we assume that each day has the same variance, and we multiply the daily variance by 365 with weekends included. So:\n\n- In cell F30, we have \"= F26\\* 365.\"\n### Computing the Annualized Standard Deviation\nTo compute the annualized standard deviation, we only need to compute the square root of the annualized variance. So:\n\n- In cell F32, we have \"= ROOT (F30).\"\n- In cell G33, cell F32 is shown as a percentage.\n\n![]()\n\n![](https:\/\/www.investopedia.com\/thmb\/3qWSSoH1A5L7hNs8aztC64Sr6DE=\/1500x0\/filters:no_upscale\\(\\):max_bytes\\(150000\\):strip_icc\\(\\)\/volatiliteexcel2-5bfd8ddd4cedfd002601674c)\n\n![]()\n\n![](https:\/\/www.investopedia.com\/thmb\/h7uJu8PwLFgm851XjMxfTHFfcPw=\/1500x0\/filters:no_upscale\\(\\):max_bytes\\(150000\\):strip_icc\\(\\)\/volatiliteexcel3-5bfd8ddb46e0fb00264bab6c)\n\n![]()\n\n![](https:\/\/www.investopedia.com\/thmb\/RDHZEbOAiJz6tj9DS4xE3cSw86E=\/1500x0\/filters:no_upscale\\(\\):max_bytes\\(150000\\):strip_icc\\(\\)\/volatiliteexcel4-5bfd8dd946e0fb00264bab06)\n\nThis square root of the annualized variance gives us the historical volatility.\n\nCompare Accounts\n\nAdvertiser Disclosure\n\n×\n\nThe offers that appear in this table are from partnerships from which Investopedia receives compensation. This compensation may impact how and where listings appear. Investopedia does not include all offers available in the marketplace.\n\nRead more\n\n- [Investing](https:\/\/www.investopedia.com\/investing-4427685)\n- [Fundamental Analysis](https:\/\/www.investopedia.com\/fundamental-analysis-4689757)\n- [Tools](https:\/\/www.investopedia.com\/tools-for-fundamental-analysis-4689755)\n\nPartner Links\n\nTake the Next Step to Invest\n\nAdvertiser Disclosure\n\n×\n\nThe offers that appear in this table are from partnerships from which Investopedia receives compensation. This compensation may impact how and where listings appear. Investopedia does not include all offers available in the marketplace.\n\n## Related Articles\n[![Midsection Businessmen Analyzing Charts on Laptop in Office]() ![Midsection Businessmen Analyzing Charts on Laptop in Office](https:\/\/www.investopedia.com\/thmb\/SpCHWst3yj8olBH_m-9gAwBY8w4=\/282x188\/filters:no_upscale\\(\\):max_bytes\\(150000\\):strip_icc\\(\\)\/GettyImages-1128046391-8a72db46d1924dcd96f8d16d186642ab.jpg) Company Earnings and EPS: Everything Investors Need to Know](https:\/\/www.investopedia.com\/articles\/basics\/03\/052303.asp)\n\n[![Stock analyst reviews charts on laptop and large monitor.]() ![Stock analyst reviews charts on laptop and large monitor.](https:\/\/www.investopedia.com\/thmb\/q5mvCZ4IUxey_IQNvAHCWnGpNIs=\/282x188\/filters:no_upscale\\(\\):max_bytes\\(150000\\):strip_icc\\(\\)\/GettyImages-1170740969-60152fa9648f4f36b21260120576dc93.jpg) Abnormal Earnings Valuation Model: A Comprehensive Guide](https:\/\/www.investopedia.com\/terms\/a\/abnormal-earnings-valuation-model.asp)\n\n[![Man in office looking at a laptop while holding document displaying bar charts. ]() ![Man in office looking at a laptop while holding document displaying bar charts. ](https:\/\/www.investopedia.com\/thmb\/WqP4EdC-VAbYrz79Z4bKsAhBZoI=\/282x188\/filters:no_upscale\\(\\):max_bytes\\(150000\\):strip_icc\\(\\)\/GettyImages-2198172840-95dee682613046eaa8acd75c62006242.jpg) Understanding Stock Ratings: Buy, Sell, and Hold Explained](https:\/\/www.investopedia.com\/financial-edge\/0512\/understanding-analyst-ratings.aspx)\n\n[![]() ![](https:\/\/www.investopedia.com\/thmb\/Hoii-PdHzx5yO3HPZi9hv1hoYgI=\/282x188\/filters:no_upscale\\(\\):max_bytes\\(150000\\):strip_icc\\(\\)\/156416606-5bfc2b8b46e0fb00517bdff7.jpg) The Impact of R\\&D Spending on Profitability](https:\/\/www.investopedia.com\/articles\/fundamental-analysis\/10\/research-development-rorc.asp)\n\n[![Photo and illustration composite representing the idea of factor investing, which includes protecting and feeding investments based on different market conditions. ]() ![Photo and illustration composite representing the idea of factor investing, which includes protecting and feeding investments based on different market conditions. ](https:\/\/www.investopedia.com\/thmb\/jLlP5EcI_vvKOnebZVCgAI5A9eU=\/282x188\/filters:no_upscale\\(\\):max_bytes\\(150000\\):strip_icc\\(\\)\/WhatFactorInvestingIsandHowtheStrategyWorks_final-bcc8802cd7f34cbc921694dce9b69533.png) Understanding Factor Investing: A Strategy for Market Savvy Investors](https:\/\/www.investopedia.com\/terms\/f\/factor-investing.asp)\n\n[![Single line graph]() ![Single line graph](https:\/\/www.investopedia.com\/thmb\/r-26sHWg162krcFB3Ze01s8jF6o=\/282x188\/filters:no_upscale\\(\\):max_bytes\\(150000\\):strip_icc\\(\\)\/single-line-graph-1069749760-6ea493d21f3d4d74a3e206d2be1bed4d.jpg) The Role of the Characteristic Line in Financial Analysis](https:\/\/www.investopedia.com\/terms\/c\/characteristicline.asp)\n\n[![]() ![](https:\/\/www.investopedia.com\/thmb\/zHwxMAlt_wpjyaA1DJE5j1KLbjE=\/282x188\/filters:no_upscale\\(\\):max_bytes\\(150000\\):strip_icc\\(\\)\/wall_street_179681980-5bfc2b9746e0fb0083c07d29.jpg) How to Determine a Good Operating Margin for Your Business](https:\/\/www.investopedia.com\/ask\/answers\/010915\/what-good-operating-margin-business.asp)\n\n[![Best Options Trading Platforms for April 2025]() ![Best Options Trading Platforms for April 2025](https:\/\/www.investopedia.com\/thmb\/bkPAv2gPz5CXfhvXY-IcaSPZuwg=\/282x188\/filters:no_upscale\\(\\):max_bytes\\(150000\\):strip_icc\\(\\)\/the-best-brokers-for-options-trading-8763492-final-f71d90edcfba40419fc3b229687ade8f.png) Best Options Trading Platforms for April 2026](https:\/\/www.investopedia.com\/the-best-brokers-for-options-trading-8763492)\n\n[![Mortgage backed security]() ![Mortgage backed security](https:\/\/www.investopedia.com\/thmb\/XHbQ5du_HbJoZ--eWAiFV6Sbje0=\/282x188\/filters:no_upscale\\(\\):max_bytes\\(150000\\):strip_icc\\(\\)\/GettyImages-1126388141-4be8c45e513b4143bae857c68522c54d.jpg) Weighted Average Loan Age (WALA) in Mortgage-Backed Securities](https:\/\/www.investopedia.com\/terms\/w\/wala.asp)\n\n[![A businessman using a digital tablet during seminar.]() ![A businessman using a digital tablet during seminar.](https:\/\/www.investopedia.com\/thmb\/yTnAZCpF4bIEhWqYVMXVddq-AOI=\/282x188\/filters:no_upscale\\(\\):max_bytes\\(150000\\):strip_icc\\(\\)\/GettyImages-1166085449-89b15dc007ac4cd780fd07355b5e7e27.jpg) Understanding Objective Probability: Definitions and Examples](https:\/\/www.investopedia.com\/terms\/o\/objective-probability.asp)\n\n[![Investment analysis: close-up of calculator, pen and paper with notations]() ![Investment analysis: close-up of calculator, pen and paper with notations](https:\/\/www.investopedia.com\/thmb\/5uqrkV8o--AVJ88O_DWKMzoaiVY=\/282x188\/filters:no_upscale\\(\\):max_bytes\\(150000\\):strip_icc\\(\\)\/calculator-385506_19201-00890bd139e34a178d67afb0e8b5a903.jpg) Understanding Multiple Discriminant Analysis (MDA)](https:\/\/www.investopedia.com\/terms\/m\/multiple-discriminant-analysis.asp)\n\n[![A man reviewing the best options trading courses]() ![A man reviewing the best options trading courses](https:\/\/www.investopedia.com\/thmb\/kTXj4n-dWBpj4CAbtTMdO0vAEB4=\/282x188\/filters:no_upscale\\(\\):max_bytes\\(150000\\):strip_icc\\(\\)\/GettyImages-1331251417-0e29ff8f71db4016a85d27c7277d6a28.jpg) Best Options Trading Courses for April 2026](https:\/\/www.investopedia.com\/the-best-options-trading-courses-11705486)\n\n[![]() ![](https:\/\/www.investopedia.com\/thmb\/zHwxMAlt_wpjyaA1DJE5j1KLbjE=\/282x188\/filters:no_upscale\\(\\):max_bytes\\(150000\\):strip_icc\\(\\)\/wall_street_179681980-5bfc2b9746e0fb0083c07d29.jpg) Understanding Appreciative Inquiry in Leadership and Innovation](https:\/\/www.investopedia.com\/terms\/a\/appreciative-inquiry.asp)\n\n[![]() ![](https:\/\/www.investopedia.com\/thmb\/OoO_Zkg9RO88dDS6Qwf3v-oH8w8=\/282x188\/filters:no_upscale\\(\\):max_bytes\\(150000\\):strip_icc\\(\\)\/145238812-5bfc2b9446e0fb005144dd0b.jpg) Trading Strategies Using Gaussian Statistical Models](https:\/\/www.investopedia.com\/articles\/financial-theory\/10\/gaussian-models-statistics.asp)\n\n[![]() ![](https:\/\/www.investopedia.com\/thmb\/fLgQjdeHiZE6CKSjiFcprXkrt2E=\/282x188\/filters:no_upscale\\(\\):max_bytes\\(150000\\):strip_icc\\(\\)\/122574802-5bfc2b8d46e0fb0026016ef5.jpg) Naked Position Definition, Risks, and Strategies](https:\/\/www.investopedia.com\/terms\/n\/nakedposition.asp)\n\n[![Market Analysis]() ![Market Analysis](https:\/\/www.investopedia.com\/thmb\/WkVUVNxiQqQxrELPsFsoUAz2ZGo=\/282x188\/filters:no_upscale\\(\\):max_bytes\\(150000\\):strip_icc\\(\\)\/market-analyze-173938198-025acedc82f94b7d9c85bc5bfd39a154.jpg) Understanding the Hull-White Model for Derivative Pricing](https:\/\/www.investopedia.com\/terms\/h\/hullwhite-model.asp)\n\n[Investopedia](https:\/\/www.investopedia.com\/)\n\n[Newsletter Sign Up](https:\/\/www.investopedia.com\/articles\/investing\/102715\/computing-historical-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":"The value of financial assets varies on a daily basis. Investors need an indicator to quantify these changes that are often difficult to predict. Supply and demand are the two principal factors that affect changes in asset prices. In return, price moves reflect an amplitude of fluctuations, which are the causes of proportional profits and losses. From an investor's perspective, the uncertainty surrounding such influences and fluctuations is called risk.\n\nThe price of an option depends on its underlying ability to move, or in other words its ability to be volatile. The more likely it is to move, the more expensive its premium will be closer to expiration. Thus, computing the volatility of an underlying asset helps investors to price [derivatives](https:\/\/www.investopedia.com\/terms\/d\/derivative.asp) based on that asset.\n\n### Key Takeaways\n- Pricing options contracts and other derivatives directly involves being able to compute an asset's volatility, or the speed of price fluctuations.\n- Volatility is derived from the variance of price movements on an annualized basis.\n- This calculation can be complex and time-consuming, but using Excel calculating an asset's historical volatility can be done quickly and accurately.\n## Measuring the Asset's Variation\nOne way to measure an asset's variation is to quantify the daily returns (percent move on a daily basis) of the asset. This brings us to the definition and concept of historical volatility. Historical volatility is based on historical prices and represents the degree of variability in the returns of an asset. This number is without a unit and is expressed as a percentage.\n\n## Computing Historical Volatility\nIf we call P (t) the price of a financial asset ([foreign exchange asset](https:\/\/www.investopedia.com\/terms\/forex\/f\/foreign-exchange-markets.asp), stocks, [forex pair](https:\/\/www.investopedia.com\/terms\/forex\/e\/eur-usd-euro-us-dollar-currency-pair.asp), etc.) at time t and P (t-1) the price of the financial asset at t-1, we define the daily return r (t) of the asset at time t by:\n\n> r (t) = ln (P (t) \/ P (t-1))\n\nwhere Ln (x) = natural logarithm function.\n\nThe [total return](https:\/\/www.investopedia.com\/terms\/t\/totalreturn.asp) R at time t is:\n\n> R = r1 + r2 + r3 + 2 + ... +rt-1+ rt,\n\nwhich is equivalent to:\n\n> R = Ln (P1 \/ P0) + ... Ln (Pt-1 \/ Pt-2) + Ln (Pt \/ Pt-1)\n\nWe have the following equality:\n\n> Ln (a) + Ln (b) = Ln (a\\*b)\n\nSo, this gives:\n\n> R = Ln \\[(P1 \/ P0\\* (P2 \/ P1)\\* ... (Pt \/ Pt-1\\]\n\n> R = Ln \\[(P1. P2 ... Pt-1. Pt) \/ (P0. P1. P2 ... Pt-2. Pt-1)\\]\n\nAnd, after simplification, we have:\n\n> R = Ln (Pt \/ P0).\n\nThe yield is usually computed as the difference in relative price changes. This means that if an asset has a price of P (t) at time t and P (t + h) at time t + h\\> t, the return (r) is:\n\n> r = (P (t + t) -P (t)) \/ P (t) = \\[P (t + h) \/ P (t)\\] – 1\n\nWhen the return is small, such as just a few percent, we have:\n\n> r ≈ Ln (1 + r)\n\nWe can substitute r with the logarithm of the current price since:\n\n> r ≈ Ln (1 + r)\n\n> r ≈ Ln (1 + (\\[P (t + h) \/ P (t)\\] - 1))\n\n> r ≈ Ln (P (t + h) \/ P (t))\n\nFrom a series of closing prices for instance, it is enough to take the logarithm of the ratio of two consecutive prices to compute daily returns r (t).\n\nThus, one can also compute the total return R by using only the initial and final prices.\n\n## Annualized Volatility\nTo fully appreciate the different volatilities over a period of a year, we multiply this volatility by a factor that accounts for the variability of the assets for one year.\n\nTo do this we use the [variance](https:\/\/www.investopedia.com\/terms\/v\/variance.asp). The variance is the square of the deviation from the average daily returns for one day.\n\nTo compute the square number of the deviations from the average daily returns for 365 days, we multiply the variance by the number of days (365). The annualized standard deviation is found by taking the square root of the result:\n\n> *Variance = σ²daily = \\[Σ (r (t)) ² \/ (n - 1)\\]*\n\nFor the annualized variance, if we assume that the year is 365 days, and every day has the same daily variance, σ²daily, we obtain:\n\n> Annualized Variance = 365. σ²daily  \n> Annualized Variance = 365. \\[Σ (r (t)) ² \/ (n - 1)\\]\n\nFinally, as the volatility is defined as the square root of variance:\n\n> Volatility = √ (variance annualized)\n\n> Volatility = √ (365. Σ²daily)\n\n> Volatility = √ (365 \\[Σ (r (t)) ² \/ (n - 1)\\].)\n## Simulation\n### The Data\nWe simulate from the Excel function =*RANDBETWEEN* a stock price that varies daily between values of 94 and 104.\n\n### Computing the Daily Returns\n- In column E, we enter \"Ln (P (t) \/ P (t-1)).\"\n### Computing the Square of Daily Returns\n- In column G, we enter \"(Ln (P (t) \/ P (t-1)) ^2.”\n### Computing the Daily Variance\nTo compute the variance, we take the sum of the squares obtained and divide by the (number of days -1). So:\n\n- In cell F25, we have \"= sum (F6: F19).\"\n- In cell F26, we compute \"= F25 \/ 18\" since we have 19 -1 data points for this calculation.\n### Computing the Daily Standard Deviation\nTo compute the standard deviation on a daily basis, we compute the square root of the daily variance. So:\n\n- In cell F28, we compute \"= Square.Root(F26).\"\n- In cell G29, cell F28 is shown as a percentage.\n### Computing the Annualized Variance\nTo compute the annualized variance from the daily variance, we assume that each day has the same variance, and we multiply the daily variance by 365 with weekends included. So:\n\n- In cell F30, we have \"= F26\\* 365.\"\n### Computing the Annualized Standard Deviation\nTo compute the annualized standard deviation, we only need to compute the square root of the annualized variance. So:\n\n- In cell F32, we have \"= ROOT (F30).\"\n- In cell G33, cell F32 is shown as a percentage.\n\nThis square root of the annualized variance gives us the historical volatility.","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

6 days 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`	0.2 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/articles/investing/102715/computing-historical-volatility-excel.asp
Last Crawled	2026-04-09 20:01:08 (6 days ago)
First Indexed	2017-11-23 10:55:47 (8 years ago)
HTTP Status Code	200
Meta Title	Computing Historical Volatility in Excel
Meta Description	We examine how annualized historical volatility is computed from daily log returns, variance, and standard deviation.
Meta Canonical	null
Boilerpipe Text	The value of financial assets varies on a daily basis. Investors need an indicator to quantify these changes that are often difficult to predict. Supply and demand are the two principal factors that affect changes in asset prices. In return, price moves reflect an amplitude of fluctuations, which are the causes of proportional profits and losses. From an investor's perspective, the uncertainty surrounding such influences and fluctuations is called risk. The price of an option depends on its underlying ability to move, or in other words its ability to be volatile. The more likely it is to move, the more expensive its premium will be closer to expiration. Thus, computing the volatility of an underlying asset helps investors to price derivatives based on that asset. Key Takeaways Pricing options contracts and other derivatives directly involves being able to compute an asset's volatility, or the speed of price fluctuations. Volatility is derived from the variance of price movements on an annualized basis. This calculation can be complex and time-consuming, but using Excel calculating an asset's historical volatility can be done quickly and accurately. Measuring the Asset's Variation One way to measure an asset's variation is to quantify the daily returns (percent move on a daily basis) of the asset. This brings us to the definition and concept of historical volatility. Historical volatility is based on historical prices and represents the degree of variability in the returns of an asset. This number is without a unit and is expressed as a percentage. Computing Historical Volatility If we call P (t) the price of a financial asset ( foreign exchange asset , stocks, forex pair , etc.) at time t and P (t-1) the price of the financial asset at t-1, we define the daily return r (t) of the asset at time t by: r (t) = ln (P (t) / P (t-1)) where Ln (x) = natural logarithm function. The total return R at time t is: R = r1 + r2 + r3 + 2 + ... +rt-1+ rt, which is equivalent to: R = Ln (P1 / P0) + ... Ln (Pt-1 / Pt-2) + Ln (Pt / Pt-1) We have the following equality: Ln (a) + Ln (b) = Ln (ab) So, this gives: R = Ln [(P1 / P0 (P2 / P1)* ... (Pt / Pt-1] R = Ln [(P1. P2 ... Pt-1. Pt) / (P0. P1. P2 ... Pt-2. Pt-1)] And, after simplification, we have: R = Ln (Pt / P0). The yield is usually computed as the difference in relative price changes. This means that if an asset has a price of P (t) at time t and P (t + h) at time t + h> t, the return (r) is: r = (P (t + t) -P (t)) / P (t) = [P (t + h) / P (t)] – 1 When the return is small, such as just a few percent, we have: r ≈ Ln (1 + r) We can substitute r with the logarithm of the current price since: r ≈ Ln (1 + r) r ≈ Ln (1 + ([P (t + h) / P (t)] - 1)) r ≈ Ln (P (t + h) / P (t)) From a series of closing prices for instance, it is enough to take the logarithm of the ratio of two consecutive prices to compute daily returns r (t). Thus, one can also compute the total return R by using only the initial and final prices. Annualized Volatility To fully appreciate the different volatilities over a period of a year, we multiply this volatility by a factor that accounts for the variability of the assets for one year. To do this we use the variance . The variance is the square of the deviation from the average daily returns for one day. To compute the square number of the deviations from the average daily returns for 365 days, we multiply the variance by the number of days (365). The annualized standard deviation is found by taking the square root of the result: Variance = σ²daily = [Σ (r (t)) ² / (n - 1)] For the annualized variance, if we assume that the year is 365 days, and every day has the same daily variance, σ²daily, we obtain: Annualized Variance = 365. σ²daily Annualized Variance = 365. [Σ (r (t)) ² / (n - 1)] Finally, as the volatility is defined as the square root of variance: Volatility = √ (variance annualized) Volatility = √ (365. Σ²daily) Volatility = √ (365 [Σ (r (t)) ² / (n - 1)].) Simulation The Data We simulate from the Excel function = RANDBETWEEN a stock price that varies daily between values of 94 and 104. Computing the Daily Returns In column E, we enter "Ln (P (t) / P (t-1))." Computing the Square of Daily Returns In column G, we enter "(Ln (P (t) / P (t-1)) ^2.” Computing the Daily Variance To compute the variance, we take the sum of the squares obtained and divide by the (number of days -1). So: In cell F25, we have "= sum (F6: F19)." In cell F26, we compute "= F25 / 18" since we have 19 -1 data points for this calculation. Computing the Daily Standard Deviation To compute the standard deviation on a daily basis, we compute the square root of the daily variance. So: In cell F28, we compute "= Square.Root(F26)." In cell G29, cell F28 is shown as a percentage. Computing the Annualized Variance To compute the annualized variance from the daily variance, we assume that each day has the same variance, and we multiply the daily variance by 365 with weekends included. So: In cell F30, we have "= F26* 365." Computing the Annualized Standard Deviation To compute the annualized standard deviation, we only need to compute the square root of the annualized variance. So: In cell F32, we have "= ROOT (F30)." In cell G33, cell F32 is shown as a percentage. This square root of the annualized variance gives us the historical volatility.
Markdown	[Skip to content](https://www.investopedia.com/articles/investing/102715/computing-historical-volatility-excel.asp#main) [Investopedia](https://www.investopedia.com/) [Live](https://www.investopedia.com/stock-market-today-dow-jones-s-and-p-500-04092026-11946208) - Please fill out this field. - [Newsletters](https://www.investopedia.com/articles/investing/102715/computing-historical-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/articles/investing/102715/computing-historical-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 - [Could the Healthiest Countries Be the Perfect Retirement Spots?](https://www.investopedia.com/could-the-world-s-healthiest-countries-be-perfect-spots-to-retire-11945240) - [Warren Buffett's Timeless Wisdom on Investing Pitfalls](https://www.investopedia.com/timeless-wisdom-from-buffett-how-to-avoid-common-investment-pitfalls-11945227) - [How ChatGPT Can Help with Retirement Planning](https://www.investopedia.com/chatgpt-can-help-with-retirement-planning-but-experts-say-it-has-limits-11945243) - [Why Is Inflation So Stubbornly High? Blame the Stock Market](https://www.investopedia.com/why-is-inflation-so-stubbornly-high-blame-the-stock-market-11944367) Table of Contents Expand Table of Contents - [Measuring the Asset's Variation](https://www.investopedia.com/articles/investing/102715/computing-historical-volatility-excel.asp#toc-measuring-the-assets-variation) - [Computing Historical Volatility](https://www.investopedia.com/articles/investing/102715/computing-historical-volatility-excel.asp#toc-computing-historical-volatility) - [Annualized Volatility](https://www.investopedia.com/articles/investing/102715/computing-historical-volatility-excel.asp#toc-annualized-volatility) - [Simulation in Excel](https://www.investopedia.com/articles/investing/102715/computing-historical-volatility-excel.asp#toc-simulation) # Computing Historical Volatility in Excel By [Caroline Banton](https://www.investopedia.com/contributors/99535/) [Full Bio](https://www.investopedia.com/contributors/99535/) Caroline Banton has 6+ years of experience as a writer of business and finance articles. She also writes biographies for Story Terrace. Learn about our [editorial policies](https://www.investopedia.com/legal-4768893#editorial-policy) Updated January 31, 2022 Reviewed by [Julius Mansa](https://www.investopedia.com/julius-mansa-4799781) Fact checked by [Suzanne Kvilhaug](https://www.investopedia.com/suzanne-kvilhaug-5197828) ![Suzanne Kvilhaug]() ![Suzanne Kvilhaug](https://www.investopedia.com/thmb/znbFHeELuyfkeore2XU_GHw2QaM=/200x200/filters:no_upscale\(\):max_bytes\(150000\):strip_icc\(\)/SuzannesHeadshot-3dcd99dc3f2e405e8bd37271894491ac.jpg) Fact checked by Suzanne Kvilhaug [Full Bio](https://www.investopedia.com/suzanne-kvilhaug-5197828) Suzanne is a content marketer, writer, and fact-checker. She holds a Bachelor of Science in Finance degree from Bridgewater State University and helps develop content strategies. Learn about our [editorial policies](https://www.investopedia.com/legal-4768893#editorial-policy) Close The value of financial assets varies on a daily basis. Investors need an indicator to quantify these changes that are often difficult to predict. Supply and demand are the two principal factors that affect changes in asset prices. In return, price moves reflect an amplitude of fluctuations, which are the causes of proportional profits and losses. From an investor's perspective, the uncertainty surrounding such influences and fluctuations is called risk. The price of an option depends on its underlying ability to move, or in other words its ability to be volatile. The more likely it is to move, the more expensive its premium will be closer to expiration. Thus, computing the volatility of an underlying asset helps investors to price [derivatives](https://www.investopedia.com/terms/d/derivative.asp) based on that asset. ### Key Takeaways - Pricing options contracts and other derivatives directly involves being able to compute an asset's volatility, or the speed of price fluctuations. - Volatility is derived from the variance of price movements on an annualized basis. - This calculation can be complex and time-consuming, but using Excel calculating an asset's historical volatility can be done quickly and accurately. ## Measuring the Asset's Variation One way to measure an asset's variation is to quantify the daily returns (percent move on a daily basis) of the asset. This brings us to the definition and concept of historical volatility. Historical volatility is based on historical prices and represents the degree of variability in the returns of an asset. This number is without a unit and is expressed as a percentage. ## Computing Historical Volatility If we call P (t) the price of a financial asset ([foreign exchange asset](https://www.investopedia.com/terms/forex/f/foreign-exchange-markets.asp), stocks, [forex pair](https://www.investopedia.com/terms/forex/e/eur-usd-euro-us-dollar-currency-pair.asp), etc.) at time t and P (t-1) the price of the financial asset at t-1, we define the daily return r (t) of the asset at time t by: > r (t) = ln (P (t) / P (t-1)) where Ln (x) = natural logarithm function. The [total return](https://www.investopedia.com/terms/t/totalreturn.asp) R at time t is: > R = r1 + r2 + r3 + 2 + ... +rt-1+ rt, which is equivalent to: > R = Ln (P1 / P0) + ... Ln (Pt-1 / Pt-2) + Ln (Pt / Pt-1) We have the following equality: > Ln (a) + Ln (b) = Ln (a\b) So, this gives: > R = Ln \[(P1 / P0\ (P2 / P1)\* ... (Pt / Pt-1\] > R = Ln \[(P1. P2 ... Pt-1. Pt) / (P0. P1. P2 ... Pt-2. Pt-1)\] And, after simplification, we have: > R = Ln (Pt / P0). The yield is usually computed as the difference in relative price changes. This means that if an asset has a price of P (t) at time t and P (t + h) at time t + h\> t, the return (r) is: > r = (P (t + t) -P (t)) / P (t) = \[P (t + h) / P (t)\] – 1 When the return is small, such as just a few percent, we have: > r ≈ Ln (1 + r) We can substitute r with the logarithm of the current price since: > r ≈ Ln (1 + r) > r ≈ Ln (1 + (\[P (t + h) / P (t)\] - 1)) > r ≈ Ln (P (t + h) / P (t)) From a series of closing prices for instance, it is enough to take the logarithm of the ratio of two consecutive prices to compute daily returns r (t). Thus, one can also compute the total return R by using only the initial and final prices. ## Annualized Volatility To fully appreciate the different volatilities over a period of a year, we multiply this volatility by a factor that accounts for the variability of the assets for one year. To do this we use the [variance](https://www.investopedia.com/terms/v/variance.asp). The variance is the square of the deviation from the average daily returns for one day. To compute the square number of the deviations from the average daily returns for 365 days, we multiply the variance by the number of days (365). The annualized standard deviation is found by taking the square root of the result: > Variance = σ²daily = \[Σ (r (t)) ² / (n - 1)\] For the annualized variance, if we assume that the year is 365 days, and every day has the same daily variance, σ²daily, we obtain: > Annualized Variance = 365. σ²daily > Annualized Variance = 365. \[Σ (r (t)) ² / (n - 1)\] Finally, as the volatility is defined as the square root of variance: > Volatility = √ (variance annualized) > Volatility = √ (365. Σ²daily) > Volatility = √ (365 \[Σ (r (t)) ² / (n - 1)\].) ## Simulation ### The Data We simulate from the Excel function =RANDBETWEEN a stock price that varies daily between values of 94 and 104. ![]() ![](https://www.investopedia.com/thmb/5jwo7uqyn3Z6GtVASaouBvC6evE=/1500x0/filters:no_upscale\(\):max_bytes\(150000\):strip_icc\(\)/volatiliteexcel1-5bfd8dd7c9e77c0051849180) ### Computing the Daily Returns - In column E, we enter "Ln (P (t) / P (t-1))." ### Computing the Square of Daily Returns - In column G, we enter "(Ln (P (t) / P (t-1)) ^2.” ### Computing the Daily Variance To compute the variance, we take the sum of the squares obtained and divide by the (number of days -1). So: - In cell F25, we have "= sum (F6: F19)." - In cell F26, we compute "= F25 / 18" since we have 19 -1 data points for this calculation. ### Computing the Daily Standard Deviation To compute the standard deviation on a daily basis, we compute the square root of the daily variance. So: - In cell F28, we compute "= Square.Root(F26)." - In cell G29, cell F28 is shown as a percentage. ### Computing the Annualized Variance To compute the annualized variance from the daily variance, we assume that each day has the same variance, and we multiply the daily variance by 365 with weekends included. So: - In cell F30, we have "= F26\* 365." ### Computing the Annualized Standard Deviation To compute the annualized standard deviation, we only need to compute the square root of the annualized variance. So: - In cell F32, we have "= ROOT (F30)." - In cell G33, cell F32 is shown as a percentage. ![]() ![](https://www.investopedia.com/thmb/3qWSSoH1A5L7hNs8aztC64Sr6DE=/1500x0/filters:no_upscale\(\):max_bytes\(150000\):strip_icc\(\)/volatiliteexcel2-5bfd8ddd4cedfd002601674c) ![]() ![](https://www.investopedia.com/thmb/h7uJu8PwLFgm851XjMxfTHFfcPw=/1500x0/filters:no_upscale\(\):max_bytes\(150000\):strip_icc\(\)/volatiliteexcel3-5bfd8ddb46e0fb00264bab6c) ![]() ![](https://www.investopedia.com/thmb/RDHZEbOAiJz6tj9DS4xE3cSw86E=/1500x0/filters:no_upscale\(\):max_bytes\(150000\):strip_icc\(\)/volatiliteexcel4-5bfd8dd946e0fb00264bab06) This square root of the annualized variance gives us the historical volatility. Compare Accounts Advertiser Disclosure × The offers that appear in this table are from partnerships from which Investopedia receives compensation. This compensation may impact how and where listings appear. Investopedia does not include all offers available in the marketplace. Read more - [Investing](https://www.investopedia.com/investing-4427685) - [Fundamental Analysis](https://www.investopedia.com/fundamental-analysis-4689757) - [Tools](https://www.investopedia.com/tools-for-fundamental-analysis-4689755) Partner Links Take the Next Step to Invest Advertiser Disclosure × The offers that appear in this table are from partnerships from which Investopedia receives compensation. This compensation may impact how and where listings appear. Investopedia does not include all offers available in the marketplace. ## Related Articles [![Midsection Businessmen Analyzing Charts on Laptop in Office]() ![Midsection Businessmen Analyzing Charts on Laptop in Office](https://www.investopedia.com/thmb/SpCHWst3yj8olBH_m-9gAwBY8w4=/282x188/filters:no_upscale\(\):max_bytes\(150000\):strip_icc\(\)/GettyImages-1128046391-8a72db46d1924dcd96f8d16d186642ab.jpg) Company Earnings and EPS: Everything Investors Need to Know](https://www.investopedia.com/articles/basics/03/052303.asp) [![Stock analyst reviews charts on laptop and large monitor.]() ![Stock analyst reviews charts on laptop and large monitor.](https://www.investopedia.com/thmb/q5mvCZ4IUxey_IQNvAHCWnGpNIs=/282x188/filters:no_upscale\(\):max_bytes\(150000\):strip_icc\(\)/GettyImages-1170740969-60152fa9648f4f36b21260120576dc93.jpg) Abnormal Earnings Valuation Model: A Comprehensive Guide](https://www.investopedia.com/terms/a/abnormal-earnings-valuation-model.asp) [![Man in office looking at a laptop while holding document displaying bar charts. ]() ![Man in office looking at a laptop while holding document displaying bar charts. ](https://www.investopedia.com/thmb/WqP4EdC-VAbYrz79Z4bKsAhBZoI=/282x188/filters:no_upscale\(\):max_bytes\(150000\):strip_icc\(\)/GettyImages-2198172840-95dee682613046eaa8acd75c62006242.jpg) Understanding Stock Ratings: Buy, Sell, and Hold Explained](https://www.investopedia.com/financial-edge/0512/understanding-analyst-ratings.aspx) [![]() ![](https://www.investopedia.com/thmb/Hoii-PdHzx5yO3HPZi9hv1hoYgI=/282x188/filters:no_upscale\(\):max_bytes\(150000\):strip_icc\(\)/156416606-5bfc2b8b46e0fb00517bdff7.jpg) The Impact of R\&D Spending on Profitability](https://www.investopedia.com/articles/fundamental-analysis/10/research-development-rorc.asp) [![Photo and illustration composite representing the idea of factor investing, which includes protecting and feeding investments based on different market conditions. ]() ![Photo and illustration composite representing the idea of factor investing, which includes protecting and feeding investments based on different market conditions. ](https://www.investopedia.com/thmb/jLlP5EcI_vvKOnebZVCgAI5A9eU=/282x188/filters:no_upscale\(\):max_bytes\(150000\):strip_icc\(\)/WhatFactorInvestingIsandHowtheStrategyWorks_final-bcc8802cd7f34cbc921694dce9b69533.png) Understanding Factor Investing: A Strategy for Market Savvy Investors](https://www.investopedia.com/terms/f/factor-investing.asp) [![Single line graph]() ![Single line graph](https://www.investopedia.com/thmb/r-26sHWg162krcFB3Ze01s8jF6o=/282x188/filters:no_upscale\(\):max_bytes\(150000\):strip_icc\(\)/single-line-graph-1069749760-6ea493d21f3d4d74a3e206d2be1bed4d.jpg) The Role of the Characteristic Line in Financial Analysis](https://www.investopedia.com/terms/c/characteristicline.asp) [![]() ![](https://www.investopedia.com/thmb/zHwxMAlt_wpjyaA1DJE5j1KLbjE=/282x188/filters:no_upscale\(\):max_bytes\(150000\):strip_icc\(\)/wall_street_179681980-5bfc2b9746e0fb0083c07d29.jpg) How to Determine a Good Operating Margin for Your Business](https://www.investopedia.com/ask/answers/010915/what-good-operating-margin-business.asp) [![Best Options Trading Platforms for April 2025]() ![Best Options Trading Platforms for April 2025](https://www.investopedia.com/thmb/bkPAv2gPz5CXfhvXY-IcaSPZuwg=/282x188/filters:no_upscale\(\):max_bytes\(150000\):strip_icc\(\)/the-best-brokers-for-options-trading-8763492-final-f71d90edcfba40419fc3b229687ade8f.png) Best Options Trading Platforms for April 2026](https://www.investopedia.com/the-best-brokers-for-options-trading-8763492) [![Mortgage backed security]() ![Mortgage backed security](https://www.investopedia.com/thmb/XHbQ5du_HbJoZ--eWAiFV6Sbje0=/282x188/filters:no_upscale\(\):max_bytes\(150000\):strip_icc\(\)/GettyImages-1126388141-4be8c45e513b4143bae857c68522c54d.jpg) Weighted Average Loan Age (WALA) in Mortgage-Backed Securities](https://www.investopedia.com/terms/w/wala.asp) [![A businessman using a digital tablet during seminar.]() ![A businessman using a digital tablet during seminar.](https://www.investopedia.com/thmb/yTnAZCpF4bIEhWqYVMXVddq-AOI=/282x188/filters:no_upscale\(\):max_bytes\(150000\):strip_icc\(\)/GettyImages-1166085449-89b15dc007ac4cd780fd07355b5e7e27.jpg) Understanding Objective Probability: Definitions and Examples](https://www.investopedia.com/terms/o/objective-probability.asp) [![Investment analysis: close-up of calculator, pen and paper with notations]() ![Investment analysis: close-up of calculator, pen and paper with notations](https://www.investopedia.com/thmb/5uqrkV8o--AVJ88O_DWKMzoaiVY=/282x188/filters:no_upscale\(\):max_bytes\(150000\):strip_icc\(\)/calculator-385506_19201-00890bd139e34a178d67afb0e8b5a903.jpg) Understanding Multiple Discriminant Analysis (MDA)](https://www.investopedia.com/terms/m/multiple-discriminant-analysis.asp) [![A man reviewing the best options trading courses]() ![A man reviewing the best options trading courses](https://www.investopedia.com/thmb/kTXj4n-dWBpj4CAbtTMdO0vAEB4=/282x188/filters:no_upscale\(\):max_bytes\(150000\):strip_icc\(\)/GettyImages-1331251417-0e29ff8f71db4016a85d27c7277d6a28.jpg) Best Options Trading Courses for April 2026](https://www.investopedia.com/the-best-options-trading-courses-11705486) [![]() ![](https://www.investopedia.com/thmb/zHwxMAlt_wpjyaA1DJE5j1KLbjE=/282x188/filters:no_upscale\(\):max_bytes\(150000\):strip_icc\(\)/wall_street_179681980-5bfc2b9746e0fb0083c07d29.jpg) Understanding Appreciative Inquiry in Leadership and Innovation](https://www.investopedia.com/terms/a/appreciative-inquiry.asp) [![]() ![](https://www.investopedia.com/thmb/OoO_Zkg9RO88dDS6Qwf3v-oH8w8=/282x188/filters:no_upscale\(\):max_bytes\(150000\):strip_icc\(\)/145238812-5bfc2b9446e0fb005144dd0b.jpg) Trading Strategies Using Gaussian Statistical Models](https://www.investopedia.com/articles/financial-theory/10/gaussian-models-statistics.asp) [![]() ![](https://www.investopedia.com/thmb/fLgQjdeHiZE6CKSjiFcprXkrt2E=/282x188/filters:no_upscale\(\):max_bytes\(150000\):strip_icc\(\)/122574802-5bfc2b8d46e0fb0026016ef5.jpg) Naked Position Definition, Risks, and Strategies](https://www.investopedia.com/terms/n/nakedposition.asp) [![Market Analysis]() ![Market Analysis](https://www.investopedia.com/thmb/WkVUVNxiQqQxrELPsFsoUAz2ZGo=/282x188/filters:no_upscale\(\):max_bytes\(150000\):strip_icc\(\)/market-analyze-173938198-025acedc82f94b7d9c85bc5bfd39a154.jpg) Understanding the Hull-White Model for Derivative Pricing](https://www.investopedia.com/terms/h/hullwhite-model.asp) [Investopedia](https://www.investopedia.com/) [Newsletter Sign Up](https://www.investopedia.com/articles/investing/102715/computing-historical-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	The value of financial assets varies on a daily basis. Investors need an indicator to quantify these changes that are often difficult to predict. Supply and demand are the two principal factors that affect changes in asset prices. In return, price moves reflect an amplitude of fluctuations, which are the causes of proportional profits and losses. From an investor's perspective, the uncertainty surrounding such influences and fluctuations is called risk. The price of an option depends on its underlying ability to move, or in other words its ability to be volatile. The more likely it is to move, the more expensive its premium will be closer to expiration. Thus, computing the volatility of an underlying asset helps investors to price [derivatives](https://www.investopedia.com/terms/d/derivative.asp) based on that asset. ### Key Takeaways - Pricing options contracts and other derivatives directly involves being able to compute an asset's volatility, or the speed of price fluctuations. - Volatility is derived from the variance of price movements on an annualized basis. - This calculation can be complex and time-consuming, but using Excel calculating an asset's historical volatility can be done quickly and accurately. ## Measuring the Asset's Variation One way to measure an asset's variation is to quantify the daily returns (percent move on a daily basis) of the asset. This brings us to the definition and concept of historical volatility. Historical volatility is based on historical prices and represents the degree of variability in the returns of an asset. This number is without a unit and is expressed as a percentage. ## Computing Historical Volatility If we call P (t) the price of a financial asset ([foreign exchange asset](https://www.investopedia.com/terms/forex/f/foreign-exchange-markets.asp), stocks, [forex pair](https://www.investopedia.com/terms/forex/e/eur-usd-euro-us-dollar-currency-pair.asp), etc.) at time t and P (t-1) the price of the financial asset at t-1, we define the daily return r (t) of the asset at time t by: > r (t) = ln (P (t) / P (t-1)) where Ln (x) = natural logarithm function. The [total return](https://www.investopedia.com/terms/t/totalreturn.asp) R at time t is: > R = r1 + r2 + r3 + 2 + ... +rt-1+ rt, which is equivalent to: > R = Ln (P1 / P0) + ... Ln (Pt-1 / Pt-2) + Ln (Pt / Pt-1) We have the following equality: > Ln (a) + Ln (b) = Ln (a\b) So, this gives: > R = Ln \[(P1 / P0\ (P2 / P1)\* ... (Pt / Pt-1\] > R = Ln \[(P1. P2 ... Pt-1. Pt) / (P0. P1. P2 ... Pt-2. Pt-1)\] And, after simplification, we have: > R = Ln (Pt / P0). The yield is usually computed as the difference in relative price changes. This means that if an asset has a price of P (t) at time t and P (t + h) at time t + h\> t, the return (r) is: > r = (P (t + t) -P (t)) / P (t) = \[P (t + h) / P (t)\] – 1 When the return is small, such as just a few percent, we have: > r ≈ Ln (1 + r) We can substitute r with the logarithm of the current price since: > r ≈ Ln (1 + r) > r ≈ Ln (1 + (\[P (t + h) / P (t)\] - 1)) > r ≈ Ln (P (t + h) / P (t)) From a series of closing prices for instance, it is enough to take the logarithm of the ratio of two consecutive prices to compute daily returns r (t). Thus, one can also compute the total return R by using only the initial and final prices. ## Annualized Volatility To fully appreciate the different volatilities over a period of a year, we multiply this volatility by a factor that accounts for the variability of the assets for one year. To do this we use the [variance](https://www.investopedia.com/terms/v/variance.asp). The variance is the square of the deviation from the average daily returns for one day. To compute the square number of the deviations from the average daily returns for 365 days, we multiply the variance by the number of days (365). The annualized standard deviation is found by taking the square root of the result: > Variance = σ²daily = \[Σ (r (t)) ² / (n - 1)\] For the annualized variance, if we assume that the year is 365 days, and every day has the same daily variance, σ²daily, we obtain: > Annualized Variance = 365. σ²daily > Annualized Variance = 365. \[Σ (r (t)) ² / (n - 1)\] Finally, as the volatility is defined as the square root of variance: > Volatility = √ (variance annualized) > Volatility = √ (365. Σ²daily) > Volatility = √ (365 \[Σ (r (t)) ² / (n - 1)\].) ## Simulation ### The Data We simulate from the Excel function =RANDBETWEEN a stock price that varies daily between values of 94 and 104. ### Computing the Daily Returns - In column E, we enter "Ln (P (t) / P (t-1))." ### Computing the Square of Daily Returns - In column G, we enter "(Ln (P (t) / P (t-1)) ^2.” ### Computing the Daily Variance To compute the variance, we take the sum of the squares obtained and divide by the (number of days -1). So: - In cell F25, we have "= sum (F6: F19)." - In cell F26, we compute "= F25 / 18" since we have 19 -1 data points for this calculation. ### Computing the Daily Standard Deviation To compute the standard deviation on a daily basis, we compute the square root of the daily variance. So: - In cell F28, we compute "= Square.Root(F26)." - In cell G29, cell F28 is shown as a percentage. ### Computing the Annualized Variance To compute the annualized variance from the daily variance, we assume that each day has the same variance, and we multiply the daily variance by 365 with weekends included. So: - In cell F30, we have "= F26\* 365." ### Computing the Annualized Standard Deviation To compute the annualized standard deviation, we only need to compute the square root of the annualized variance. So: - In cell F32, we have "= ROOT (F30)." - In cell G33, cell F32 is shown as a percentage. This square root of the annualized variance gives us the historical volatility.
Shard	148 (laksa)
Root Hash	7273739457760125548
Unparsed URL	com,investopedia!www,/articles/investing/102715/computing-historical-volatility-excel.asp s443