Bitcoin Example
Further to my previous blog, Understanding Historical Volatility, I have outlined below a step-by-step guide to calculating the historical volatility of Bitcoin (BTC) for the year 2021.
In this example, I have used the daily log-returns of BTC prices from January to December 2021. I have also chosen to use Excel instead of high-level programming such as Python, as I believe this approach will be accessible to a broader audience.
To calculate Bitcoin’s historical volatility for 2021 using daily log-returns and to visualise the log-normal distribution, follow these steps:
1. Download Historical Price Data
Obtain Bitcoin’s daily closing prices for 2021 from Yahoo Finance:
•Navigate to the Bitcoin USD (BTC-USD) Historical Data page.
•Set the date range from 1 January 2021 to 31 December 2021.
•Click “Apply” and then “Download” to save the data as a CSV file.
2. Import Data into Excel
•Open Excel and load the downloaded CSV file.
•Ensure columns are appropriately labelled, with dates and closing prices clearly identified.
3. Calculate Daily Log-Returns
•Insert a new column titled “Log_Returns.”
•In the first cell under “Log_Returns” (e.g., cell C2), enter the formula:
=LN(B2/B1)
Here, B2 is the current day’s closing price, and B1 is the previous day’s closing price.
•Drag the formula down to compute log-returns for all rows.
4. Compute Daily Volatility
•Use the STDEV.S function to calculate the standard deviation of the log-returns:
=STDEV.S(C2:C252)
This provides the daily volatility.
5. Annualise Volatility
•Multiply the daily volatility by the square root of 252 (the typical number of trading days in a year):
=C253 * SQRT(252)
Replace C253 with the cell containing the daily volatility. This yields the annualised volatility.
6. Visualise the Log-Normal Distribution
•Create a histogram of the log-returns to observe their distribution.
•Overlay a normal distribution curve using the calculated mean and standard deviation of the log-returns.
7. Interpret the Data
The results using simulated data are as follows:
•Daily Volatility: 0.0393 (or 3.93%)
•Annualised Volatility: 0.6245 (or 62.45%)
It is important to interpret the standard deviation distribution. The percentages of the log-returns falling within each standard deviation range are as follows:
•Within 1 Standard Deviation: 70.60%
•Within 2 Standard Deviations: 95.88%
•Within 3 Standard Deviations: 99.45%
These values are annotated below in the log-normal distribution plot for reference. The percentages align closely with expectations from the empirical rule for normal distributions, indicating that the returns approximately follow a normal distribution.
Interpreting the Plot
The plot helps us understand the probability of Bitcoin’s daily log-returns falling within specific ranges based on standard deviations from the mean. Here’s how we interpret it:
1.70.6% of the time:
•The daily price return of Bitcoin falls within ±1 standard deviation of the mean.
•This translates to a range of approximately 0% to ±3.93% on a daily basis.
2.95.9% of the time:
•The daily price return falls within ±2 standard deviations of the mean.
•This range is approximately 0% to ±7.86% on a daily basis.
3.99.5% of the time:
•The daily price return falls within ±3 standard deviations of the mean.
•This range is approximately 0% to ±11.79% on a daily basis.
Moreover, from the interpretation of the plot, 99.5% of the time, daily returns fall within ±3 standard deviations (approximately -11.79% to +11.79%). This leaves 0.5% of the time for returns outside this range, equally split between the upper and lower tails of the distribution:
•0.25% probability that the daily return is less than -11.79%.
•0.25% probability that the daily return is greater than +11.79%.
This is referred to as tail risk in trading. As the log-normal distribution is not exactly a normal distribution, the chart demonstrates the kurtosis properties of a log-normal distribution, with “fatter” tails. This indicates that tail risk may be higher than what a normal distribution would suggest.
Conclusion
By following these steps, you can calculate Bitcoin’s historical volatility for 2021 and visualise the distribution of its daily log-returns, providing insights into its price behaviour during that period. These percentages align closely with the empirical rule for normal distributions, reinforcing that Bitcoin’s daily log-returns exhibit a pattern consistent with a log-normal distribution. This interpretation offers valuable insights into Bitcoin’s volatility and the likelihood of extreme price movements. It is a historical indication of the daily risk you could be facing by investing in Bitcoin.
Caio Marchesani