Jonathan Hobbs

almost 2 years ago • 4 mins

To measure an asset’s volatility (crypto or otherwise), you can use Microsoft Excel or Google Sheets to calculate its standard deviation.

You’ll then need to work out the compound annual growth rate (CAGR), and divide that by the yearly standard deviation to get the Sharpe ratio.

The Sharpe ratio is a good way of measuring return over risk, but remember: it doesn’t predict the future.

To measure an asset’s volatility (crypto or otherwise), you can use Microsoft Excel or Google Sheets to calculate its standard deviation.

You’ll then need to work out the compound annual growth rate (CAGR), and divide that by the yearly standard deviation to get the Sharpe ratio.

The Sharpe ratio is a good way of measuring return over risk, but remember: it doesn’t predict the future.

This week, you might’ve taken part in our crypto challenge to pick any one of the top 100 digital assets, and analyze whether it’s low, medium, or high-risk. But as we bring it to an end, I’d like to talk you through a simple strategy you might want to use next time around: how to find the crypto that offers the highest return for the lowest risk.

There are many ways to gauge the risk of an investment, but the most common is **standard deviation** – a fancy term for price volatility. In a nutshell, standard deviation measures how much an investment’s price strays from its average price over a certain number of periods. There’s a fairly complex formula to work that out, but I’ll spare you the details. Instead, I’ll show you how to calculate it in Microsoft Excel and Google Sheets (it’s the same process with each) at the click of a button.

First, you’ll need price data for each coin. You can get that from a number of sources, but I find Yahoo Finance is top notch (it’s also free). The screenshot below shows how to get the weekly prices for bitcoin (or any other financial asset), which I did for the past three years.

Next, paste the data into a Google Sheet or an Excel spreadsheet (which is what I’ve used here). You can use the **closing prices** – the last traded price for the week – as your input to calculate the weekly percent returns for bitcoin. Add it to a new column, like so:

Now, drag the formula down for each cell so you have percent returns for each week – you’ll be using these to calculate the standard deviation. And be sure to convert the cells into percentage format.

Finally, use the **STDEV.P function** to work out the standard deviation of the weekly returns:

I did this for six cryptos over the three-year period (April 2019 to April 2022), and then ranked them by standard deviation (highest risk to lowest). To spice things up, I also included some non-crypto investments to see how the results compare.

Unsurprisingly, dogecoin had the highest weekly volatility, with GameStop’s stock a close second. Bitcoin was the tamest crypto of the bunch, with a similar weekly volatility to Tesla’s stock.

The next step is to calculate the average yearly return of each investment: I’ve used **compound annual growth rates (CAGRs)** for that**.** To get the CAGRs, you first need to divide the most recent price of each asset by the starting price to get the three-year returns. Here’s an example of how that’s done with bitcoin.

On the latest date (25 April 2022), bitcoin’s price was $38,117.46. Three years before, it was $5,285.14. You can get bitcoin’s total return over the three years using this formula:

**% Return = ($38,117.46 / $5,285.14) – 1 = 6.21**

In percentage terms, that gives a return of 621.2% over the three years. Then use this formula to get the CAGR:

**CAGR = -1 = 9.32 (or 93.2%)**

Note that when using Excel or Google Sheets, you may find it easier to use the relevant cells containing the numbers (rather than the actual numbers).

After some Excel number crunching, we reach these CAGRs for each investment:

Dogecoin has led the pack over the last three years, returning an average of 280.7% per year. Of course, it had the highest volatility too, so doge investors would’ve been put through the wringer on the way there.

The Sharpe ratio measures an investment's return over its risk: the higher it is, the more return you get per unit of risk taken. You can work it out by dividing the CAGR of an investment by its *yearly* standard deviation. So since I’ve used weekly standard deviations, you’ll need to multiply that by the square root of 52 (number of weeks in a year) to get the yearly volatility.

The Sharpe ratio usually includes a minor “risk-free rate” in the calculation (which is subtracted from the CAGR), but we’ll set that to zero to keep the math simple. And this won’t affect how the results compare with one another.

**Dogecoin** might’ve had the highest return over the three years, but it fared worse than every coin per unit of risk except Ripple’s **XRP**, which has a Sharpe ratio of just 0.2. Tesla compensated investors the most for risk, with a Sharpe ratio of 2.4.

The Sharpe ratio can give you a good indication of an investment’s expected return over risk, but keep in mind that it uses *past* data to do that – and as we know, past performance doesn’t guarantee future results. That said, I think we can all agree what’s more likely in future: that **ether** and **bitcoin** offer more return per unit of risk than dogecoin and XRP.

**Disclaimer:** These articles are provided for information purposes only. Occasionally, an opinion about whether to buy or sell a specific investment may be provided. The content is not intended to be a personal recommendation to buy or sell any financial instrument or product, or to adopt any investment strategy as it is not provided based on an assessment of your investing knowledge and experience, your financial situation or your investment objectives. The value of your investments, and the income derived from them, may go down as well as up. You may not get back all the money that you invest. The investments referred to in this article may not be suitable for all investors, and if in doubt, an investor should seek advice from a qualified investment advisor.