Optimising Risk And Return With The Sharpe Ratio

An unfortunate truth about investing is that we can’t earn decent investment returns without taking risk. On the one end of the risk spectrum, we could leave our money in a bank account earning less than 1% interest a year. Not much risk here, but no returns either.
At the more extreme end of the risk scale is spinning a roulette wheel at the local casino. The risks are huge—meaning the chance of losing our initial ‘investment’ is very high—but so are the potential rewards if luck happens to go our way.
Investor risk profiles
Investors have different risk profiles depending on things like:
- How much spare change they can afford to lose.
- How close they are to retirement.
- Whether they have a steady job or business that generates enough cash each month to pay their living expenses.
Those with more years of investing left ahead of them may wish to take on more investment risk because they have more time to recover if things go wrong. These investors might allocate more of their portfolios to riskier investments such as cryptocurrencies, startup companies and growth stocks.
Those who are lucky enough to have already retired, however, may want to live on a steady income stream from the investments they have already accumulated. Their biggest risk is losing their retirement savings—meaning they are typically more inclined to play it safe. These types of investors often allocate more of their portfolios to interest paying bonds and stable dividend-paying stocks.
More return for a given level of risk
Regardless of our individual risk tolerances, we would all like to own investments which earn a higher return for a given level of risk. This is where the Sharpe ratio comes into play.
The Sharpe ratio measures the return of an investment over its risk (volatility). Before we get to that, let’s first talk a bit about volatility…
A closer look at volatility:
Volatility is the standard way to measure the risk of an investment over a period of time (such as a day, week, month, or year).
- The value of more volatile investments changes considerably (up or down) over time.
- The value of less volatile investments hardly changes (up or down) over time.
The above points tell us why volatility alone is not the best measure of investment risk: since volatility measures how much the value of an asset changes up or down over time, an asset that goes up 40% in a year could have the same yearly volatility as an asset that goes down 40%.
In other words, volatility doesn’t penalise downside risk nor reward upside risk—it treats them exactly the same!
Enter the Sharpe Ratio…
As mentioned earlier, the Sharpe ratio measures how much return an investment has earned for a given level of volatility. So going back to our earlier example, the asset that lost 40% in a year would have a negative Sharpe ratio, whereas the asset that gained 40% would have a positive one.
Unlike volatility, the Sharpe ratio penalises investments that lose value and rewards investments that gain value.
The Sharpe ratio formula:
The mathematical formula for the Sharpe ratio is as follows:
SR = average % excess return of investment / standard deviation of investment
Let’s now look at each of the two parts that make up this equation:
% Excess return:
The excess return of an investment is the difference between its percentage return and the return of a ‘risk-free’ asset. The risk free asset theoretically has no risk. Going back to the example at the start of this post, if an investor held cash in their bank account, they may be able to earn 1% a year on that cash without much risk.
In this scenario, if Apple stock returned 15% for the year, the excess return would be 15% less the 1% risk-free return, or 14%.
Most finance textbooks use the returns of government-backed bonds such as UK Gilts or US Treasury Bills as the risk-free rate when calculating the Sharpe ratio. Since governments can print money at will to pay investors interest payments, their bonds are widely considered to have approximately zero risk.
To keep things simple for our purposes, we will use 1% as the ‘risk free’ rate of return.
Standard deviation:
To calculate the Sharpe ratio we divide the average % excess return by the standard deviation of returns. We use the standard deviation to measure volatility.
Wikipedia defines standard deviation as “a measure that is used to quantify the amount of variation or dispersion of a set of data values.”
In other words —the higher the standard deviation, the more variation in prices, and the more volatile the asset!
The example in the box below explains how we can easily calculate the standard deviation in Excel:
Example: Calculating the monthly standard deviation
In this example, we will use Excel to calculate the monthly standard deviations (volatility) during 2017 of two assets that had very different levels of volatility over that time:
- Bitcoin, which was highly volatile in 2017.
- The S&P 500 Index, which was not very volatile in 2017.
To start with, we first enter the prices of bitcoin for the last business day of each month in 2017 into our spreadsheet. Note that we could use weekends and holidays for our prices too as cryptos trade 24/7. But since we will be comparing our results with stocks—which don’t trade on weekends or holidays—we will use business days only.
Bitcoin 2017 monthly % returns. Data sourced from coinmarketcap.com.
Side note: I obtained the above prices for bitcoin through the ‘historical data’ tab in coinmarketcap.com. This was a copy and paste job, but there are smarter ways to do this such as using a free API to automatically pull prices in from cryptocompare.com. Using an API can be much better if you plan on building sophisticated investment spreadsheets that automatically update each day.
Notice in the table above how the prices of bitcoin changed by large amounts each month in 2017. In other words, the prices were volatile.
But how volatile were they exactly? We can measure the standard deviation of the percentage returns for bitcoin each month to get a good idea…
To calculate the monthly standard deviation of bitcoin in Excel, we use the ‘STDEV’ function. To do this, type “=STDEV” in a blank cell and select the cell range:
Calculating bitcoin monthly standard deviation of returns in 2017.
Doing the above gives us a monthly standard deviation of returns of 29.40%. This means that in an average month in 2017, the percentage return for bitcoin deviated from the 2017 average monthly percentage return by 29.40%.
In other words, bitcoin was volatile in 2017. No surprises here.
To drive this point home, let’s now do the same thing for an investment that was far less volatile in 2017—the S&P 500 Index. We can get the “historical prices of the S&P 500 from Yahoo Finance.
Monthly standard deviation of S&P in 2017. Data source from Yahoo Finance.
As you can see, the monthly returns of the S&P were far less volatile than bitcoin’s were in 2017. Again, no surprises here.
Back to the Sharpe ratio…
The example above tells us that bitcoin was considerably more volatile than the S&P during 2017. Of course, this is something we already knew.
But how did the two investments compare in terms of risk return trade-offs in 2017? In the box below, we will calculate the Sharpe ratios for each of these.
Calculating the Sharpe ratio: Bitcoin vs. S&P 500
Recall from earlier that the Sharpe ratio is calculated as follows:
SR = average % excess return of investment / standard deviation of investment
We already know the standard deviations of each investment, so we now need to calculate the average % excess returns.
We can easily do this in Excel by subtracting the monthly % risk free return from bitcoin’s monthly return. Note that we are assuming a yearly risk free rate of 1%. However, if we use compounded average returns, then our monthly risk free rate is 0.08%, rather than 1% divided by 12 months.
In other words, 8% returned each month will give us 1% if we compound those returns on top of each other for 12 months. There’s a bit of maths here, but you can download a copy of the Sharpe ratio spreadsheet I used for this example at the end of this post to make things easier. The spreadsheet includes all the formulas.
2017 Monthly % excess returns BTC.
Now that we have the monthly % excess returns, we can calculate bitcoin’s 2017 Sharpe ratio in excel:
BTC Sharpe ratio for 2017.
The numbers above show a monthly Sharpe ratio of 0.97. However, most analysts will use the annualised Sharpe ratio as their preferred format. To annualise the monthly Sharpe ratio, we don’t multiply it by 12, but rather, by the square root of 12. We do this to stop the effect of positve and negative monthly returns cancelling each other out. Again, this is all in the spreadsheet at the end.
Mutiplying 0.97 by the square root of 12 gives us an annual Sharpe ratio of 3.36 for Bitcoin in 2017.
We can now run the same process to get the 2017 Sharpe ratio for the S&P 500:
S&P 500 Sharpe ratio 2017.
The above table shows that the S&P had a higher Sharpe ratio than bitcoin in 2017. What does this mean? It means that ‘pound for pound’ the S&P performed better than bitcoin on a risk adjusted basis in 2017.
It’s worth noting that a Sharpe ratio of 1 is considered reasonable, 2 is very good, and 3 is exceptional. This means that in 2017, both bitcoin and the S&P were exceptional investments on a risk adjusted basis. The reasons for this are:
- Bitcoin was volatile, but it had enormous returns.
- The S&P had decent returns, but very low volatility.
It is important to note that these numbers apply to 2017 only, which was an exceptional year for both of these assets. If we were to instead use data from 2008—the year Lehman Brothers collapsed—the S&P would have had a Sharpe ratio of negative 2.46 using the same assumption of a 1% risk free rate.
If we use the returns of the bitcoin bear market in 2014, we get a Sharpe ratio of negative 0.89.
Tell Us Where To Send Your Sharpe Ratio Spreadsheet

Success! Your spreadsheet should be in your inbox. If you don't get it for whatever reason, email me at [email protected].
Jonathan Hobbs, CFA, is an author, entrepreneur and financial blogger. He invests in stocks, mutual funds, startup companies, gold and cryptocurrencies. He believes strongly that nobody can predict the future of financial markets, so he prefers to simplify his approach to investing with basic investment rules and strategies that work well over time.