This volatility calculator can be used to establish the volatility of a publicly traded stock based on the inputs you provide. Use this spreadsheet to price options. The historical prices are input on one tab (the download contains an example from yahoo.finance).  Then complete the remaining inputs shown in the picture above and voila, volatility is calculated!

Volatility Calculator Template

System Requirements & Download

Volatility is a critical input utilized in the Black-Scholes model, a common model for pricing options. This calculator is referred to as an Historic Volatility Calculator, because it is solely dependent on historical prices of a company’s stock.

The calculator has been specifically set up to work with the historical prices that you download from yahoo.fiance. If you download historical prices from another website (which is perfectly fine), ensure that the stock price you choose to use is included in either the “Close” or “Adj Close” columns. The inputs shown above allow you to choose either the “Close” or “Adj Close” columns for your calculation. For additional flexibility, select the calculation be performed Weekly or Monthly — again, just ensure the historical date is consistent with the option you choose.

Note, be sure to delete the example information before beginning.

The information you need for the calculation to work are:

• Historical pricing data
• Select the column you want the volatility to be calculated on (this would generally be the “Closing” price)
• Select the frequency of the data – if the detail historical data is daily then you would select “Daily” if it’s monthly the select “Monthly”

The information you should document for the sake of good documentation:

• If this is a calculation you’re doing for a specific company, there’s a place to document the company name at the top left corner
• The period to which the calculation pertains (i.e., fiscal year 12/31/XX)
• The Ticker symbol
• The date the calculation was done
• The source of the historical information

Resources for the Volatility Calculation

Here are some good resources to learn more about volatility:

• From Wikipedia
• Accounting Standards Codification 718 – Share-Based Payment.  You can establish a free account with the FASB and review the codification FASB.org.
• Search google.com, AICPA.org, or your local society of CPA’s.
• Seek the help of a professional – Sometimes its necessary to seek the help of a qualified professional to assist in the specifics of your circumstances.  If your concern is proper accounting and reporting, ensure the professional carries the designation of CPA (Certified Public Accountant).

Disclaimer – the Spreadsheetshoppe.com does not take any responsibility for trades made using the information on this site. The information found here is being provided for educational / informational purposes only, any actions taken as a result of this information is strictly the responsibility of the user. Trading financial instruments of any kind, including but not limited to, options, futures, and stocks carry significant potential risk.  No representation is made that by using the information on this site, one will or is likely to achieve profits or losses indicated.

This website, its owners, its associates and/or its representatives are not registered as securities broker-dealers or investment advisors with any state securities regulatory authority. We recommend consulting with a registered investment advisor, broker-dealer, and/or financial advisor. If you choose to invest with or without seeking advice from such an advisor or entity, then any consequences resulting from your investments are your sole responsibility.

Basically, use common sense and understand that the accuracy or validity of the information / calculations found on this site should not be construed as a recommendation to purchase or sell any type of security, or to provide investment advice.

On October 26, 2015   /   Accounting, Analysis, Audit, Business Tools, Calculator, Downloads, Financial Statements, Portfolio