The formula
Sharpe ratio = (return − risk-free rate) ÷ standard deviation of returns
The numerator is the excess return, what the investment earned above a safe alternative such as short-term government bills. The denominator is volatility, the standard deviation of the returns, which measures how much they swing around their average. A higher Sharpe ratio means more reward per unit of risk.
Worked example: two funds, one year of monthly returns
Monthly returns are inputs. The risk-free rate sits in one cell (B15) and both Sharpe formulas point to it with an absolute reference.
| A | B | C | |
|---|---|---|---|
| 1 | Month 2026 | Fund A | Fund B |
| 2 | Jan | 2.1% | 0.9% |
| 3 | Feb | -1.4% | 0.2% |
| 4 | Mar | 3.0% | 1.1% |
| 5 | Apr | 0.8% | 0.6% |
| 6 | May | -2.2% | -0.4% |
| 7 | Jun | 1.9% | 0.8% |
| 8 | Jul | 2.6% | 1.0% |
| 9 | Aug | -0.5% | 0.3% |
| 10 | Sep | 1.2% | 0.5% |
| 11 | Oct | -3.1% | -0.9% |
| 12 | Nov | 2.8% | 1.2% |
| 13 | Dec | 1.5% | 0.7% |
| 14 | |||
| 15 | Risk-free rate (per year) | 4.0% | |
| 16 | Annualised return (mean × 12) | 8.70% | 6.00% |
| 17 | Annualised volatility | 7.11% | 2.17% |
| 18 | Sharpe ratio | 0.66 | 0.92 |
| 19 | Compounded 12-month return | 8.80% | 6.15% |
- B16
=AVERAGE(B2:B13)*12Annualised mean return - B17
=STDEV.S(B2:B13)*SQRT(12)Square-root-of-time rule - B18
=(B16-$B$15)/B17Sharpe ratio - B19
=PRODUCT(1+B2:B13)-1What an investor actually earned
Both funds and their returns are fictional, for teaching only. This is not investment advice.
Step 1: annualise the return
With monthly data, the simplest annual return is the average monthly return × 12: =AVERAGE(B2:B13)*12. Fund A returns 8.70% a year on that basis, Fund B 6.00%. This arithmetic version is the one most often used in the Sharpe ratio. The compounded return, =PRODUCT(1+B2:B13)-1, is what an investor actually earned over the twelve months (8.80% and 6.15%). Use one method consistently for every fund you compare.
Step 2: annualise the volatility
Calculate the standard deviation of the monthly returns with STDEV.S, the sample version, because twelve months are a sample of the fund's possible returns. Volatility does not scale with time but with the square root of time, so multiply by SQRT(12): =STDEV.S(B2:B13)*SQRT(12). For weekly data use SQRT(52), for daily data about SQRT(252) trading days. Fund A's annual volatility is 7.11%, Fund B's only 2.17%.
Step 3: the ratio
With the risk-free rate of 4.0% in B15: =(B16-$B$15)/B17. Fund A earns 4.70% above the risk-free rate with 7.11% of volatility, a Sharpe ratio of 0.66. Fund B earns only 2.00% of excess return, but with so little volatility that its Sharpe ratio is 0.92. Per unit of risk, Fund B did better. An investor who wanted Fund A's return could, in theory, have held more of Fund B, or Fund B with some leverage, and taken less risk for it.
The same ratio from monthly excess returns
Some analysts calculate the ratio on monthly figures first and annualise at the end: =(AVERAGE(B2:B13)-$B$15/12)/STDEV.S(B2:B13)*SQRT(12). It looks different, but it is the same number. The monthly excess return × 12 is the annual excess return, and dividing by the monthly volatility × SQRT(12) is the same as dividing by the annual volatility. Use whichever layout is easier to audit, but annualise return and risk with the same frequency.
How to read the result
As a rough guide, a Sharpe ratio above 1 is often described as good and above 2 as very good, but these thresholds depend heavily on the period and the asset class. One calm year can produce flattering numbers. The ratio is most useful for comparing investments over the same period, with the same frequency of data and the same risk-free rate. Real comparisons use three to five years of data, not twelve months.
Limits of the Sharpe ratio
Standard deviation treats a surprise gain exactly like a surprise loss, although investors only mind the losses. The Sortino ratio fixes that by dividing by downside deviation, which counts only negative returns. The Sharpe ratio also assumes returns are roughly symmetric. Strategies that earn small steady gains and occasionally lose a lot can show a high Sharpe ratio until the large loss arrives. Look at the maximum drawdown next to it.
Common mistakes
- Multiplying volatility by 12. It scales with the square root of time: × SQRT(12) for monthly data.
- Mixing periods. A monthly return minus an annual risk-free rate gives nonsense. Either annualise both, or use the monthly risk-free rate (annual ÷ 12) with monthly returns and annualise the ratio with × SQRT(12).
- Using prices instead of returns. The standard deviation of prices measures the price level, not risk. Calculate returns first:
=B3/B2-1. - Comparing different periods. A Sharpe ratio from a calm year and one from a crash year are not comparable.
- STDEV.P instead of STDEV.S for a sample of returns. The difference is small with many observations but noticeable with twelve.
- Calendar days for daily data. Markets trade on roughly 252 days a year, so annualise daily figures with SQRT(252), not SQRT(365).
Frequently asked questions
Is there a Sharpe ratio function in Excel?
No. You combine AVERAGE (or another return measure), STDEV.S, SQRT for annualising and a risk-free rate in one or two cells.
What risk-free rate should I use?
The yield on short-term government bills in the same currency as the investment, over the same period. Use the same rate for every fund you compare.
What is a good Sharpe ratio?
Values above 1 are commonly called good and above 2 very good, but they depend on the period and the asset class. Use the ratio mainly to compare investments over the same period.
What is the difference between the Sharpe and the Sortino ratio?
The Sharpe ratio divides by the standard deviation of all returns. The Sortino ratio divides by downside deviation, which counts only returns below a target, so it penalises losses but not gains.