In the last post I set out the formula for historical volatility and worked it on ten made-up prices. This time I have done it on a real year of Bitcoin, the whole of 2021, so that the numbers mean something. I have used Excel rather than Python for this one, because more people have Excel open than have a Python environment set up, and the calculation is short enough that a spreadsheet is the honest tool for it.
The data
I took the daily closing prices of BTC-USD from Yahoo Finance, from 31 December 2020 to 31 December 2021. Bitcoin trades every day of the year, so that is 366 closes and, once you take the day-on-day change, 365 daily returns. The price started the year at about $29,000, reached a closing high of about $67,600 in early November, and ended at about $46,300, up roughly 60 per cent over the year with two large drawdowns in between. It was, by any standard, a wild year, which is exactly why it makes a good example.
If you want to reproduce this, go to the BTC-USD historical data page on Yahoo Finance, set the range to the year you want, and download the CSV. The only columns you need are the date and the close.
The spreadsheet
With dates in column A and closes in column B, starting on row 2, the steps are:
Daily log returns. In C3 enter =LN(B3/B2) and fill it down to the last row. The first row has no return because there is no previous day.
Daily volatility. At the bottom of the column, =STDEV.S(C3:C367). That is the sample standard deviation of the 365 returns. For 2021 it comes out at 4.20 per cent.
Annualised volatility. Multiply by the square root of the number of trading days in a year. For equities you would use 252, but Bitcoin does not close for weekends, so I use 365: =D368*SQRT(365), where D368 is whatever cell holds the daily figure. That gives 80.3 per cent for the year. If you use 252 you get 66.7 per cent instead, which is one reason quoted figures for crypto volatility vary so much between sources. It is worth knowing which convention a number was built on before you compare it with anything.
The mean daily return, =AVERAGE(C3:C367), is 0.13 per cent. Over a year in which the price rose 60 per cent that may look low, and it is a useful reminder of how little of the story the average tells: the daily returns were spread so widely that the drift is almost lost in the noise, and for most purposes you can ignore it.
What the distribution looks like
The point of going to this trouble is not the single number. It is what the shape of the returns tells you about the risk of holding the asset, and for that you need to see the distribution. I put the 365 returns into a histogram with half-percent bins and drew over it the normal distribution that has the same mean and standard deviation.
If the returns were normally distributed you would expect about 68 per cent of days to fall within one standard deviation of the mean, 95 per cent within two, and 99.7 per cent within three. The actual figures for 2021 were:
| Range | Daily move | Normal distribution | Bitcoin, 2021 |
|---|---|---|---|
| Within 1 sd | about ±4.2% | 68.3% of days | 72.9% of days |
| Within 2 sd | about ±8.4% | 95.4% | 94.2% |
| Within 3 sd | about ±12.6% | 99.7% | 98.9% |
Two things stand out. More days than expected sit close to the middle: nearly three quarters of the year Bitcoin moved less than 4.2 per cent, where a normal distribution would say two thirds. And more days than expected sit far out in the tails. A normal distribution with this standard deviation would produce a move beyond three standard deviations about once a year. In 2021 it happened four times. The largest single day was a rise of 17.2 per cent on 8 February, and the largest fall was 14.8 per cent on 19 May. Those are moves of four and three and a half standard deviations, and under a normal distribution you would wait decades to see the first of them.
That combination, a sharper peak and fatter tails than the bell curve, is what statisticians call excess kurtosis, and it is the normal state of affairs in financial returns. For 2021 the kurtosis of Bitcoin’s daily returns was about 4.5, against 3 for a normal distribution. You can see it in the chart: the orange curve is too low in the middle and too low at the edges, and makes up for it by being too high on the shoulders.
Why this matters for anyone holding Bitcoin
The practical reading is this. On a typical day in 2021, Bitcoin moved less than the headline volatility figure suggests. But when it moved a lot, it moved further than a normal distribution would have warned you, and it did so several times more often. A risk model that assumes normal returns, which is what the simplest versions of value at risk do, would have told you that a move of more than 12 per cent in a day was a once-a-year event. There were four of them, every one of them a move of 14 per cent or more.
That is tail risk, and the whole reason to draw the chart rather than just quote the 80 per cent. The standard deviation tells you about the ordinary days. The tails tell you about the days that decide whether you are still in the trade.
In the next post I compare this backward-looking figure with implied volatility, which is what the options market thought the volatility was going to be, and explain why the two are usually different.