The Sharpe Ratio of a portfolio is its return minus the risk-free rate, divided by the standard deviation of its returns: (Rp - Rf) / σp. It tells you how much excess return the portfolio earned for each unit of volatility; above 1.0 is generally considered good. In Excel, you calculate it from daily portfolio returns (AVERAGE and STDEV), subtract a daily risk-free rate, and multiply by SQRT(252) to annualize, or use the MarketXLS =SharpeRatio() function on a range of tickers and weights. William F. Sharpe introduced the ratio in 1966, and this guide covers the formula, interpretation, the step-by-step Excel method, and how it compares with the Sortino and Treynor ratios. To see the whole comparison already assembled, the portfolio comparison template scores portfolios on Sharpe alongside Sortino ratio, portfolio beta and value at risk in a single sheet.
Try it in your own spreadsheet
Get the free MarketXLS sample workbook (.xlsx).
No account needed. MarketXLS live data requires a paid plan. Preview workbook
Explore more videos
What Is the Sharpe Ratio?
The Sharpe Ratio measures the excess return earned per unit of total risk. The formula is:
Sharpe Ratio = (Rp - Rf) / σp
Where:
- Rp = Portfolio return (average return over the measurement period)
- Rf = Risk-free rate (typically the yield on Treasury bills)
- σp = Standard deviation of portfolio returns (total risk)
A Simple Example
An investor earns a 12% annual return on a portfolio with a standard deviation of 15%. The risk-free rate is 4%.
Sharpe Ratio = (12% - 4%) / 15% = 0.53
This means the portfolio generates 0.53% of excess return for every 1% of volatility.
What Does the Number Mean?
| Sharpe Ratio | Interpretation |
|---|---|
| < 0 | Portfolio underperforms the risk-free rate |
| 0.0 – 0.5 | Poor to mediocre risk-adjusted performance |
| 0.5 – 1.0 | Acceptable — reasonable return for the risk |
| 1.0 – 2.0 | Good — strong risk-adjusted performance |
| 2.0 – 3.0 | Very good — exceptional by most standards |
| > 3.0 | Outstanding — rare and potentially unsustainable |
Most diversified equity portfolios fall in the 0.3–0.8 range. Hedge funds target 1.0+. A Sharpe Ratio above 2.0 over multiple years is exceptional.
Why the Sharpe Ratio Matters
1. Apples-to-Apples Comparison
Raw returns are meaningless without context. A fund returning 20% annually with 40% volatility is not necessarily better than a fund returning 10% with 8% volatility. The Sharpe Ratio reveals which fund delivers more return per unit of risk.
2. Portfolio Optimization
When building a portfolio, the goal is to maximize the Sharpe Ratio — finding the combination of assets that sits on the efficient frontier and delivers the highest risk-adjusted return.
3. Strategy Evaluation
Traders use Sharpe Ratios to evaluate whether a strategy's returns justify the drawdowns and volatility endured.
4. Fund Selection
When comparing mutual funds, ETFs, or managed accounts, the Sharpe Ratio helps identify which managers are generating genuine alpha versus simply taking on more risk.
Calculating Sharpe Ratio Step by Step in Excel
What You Need
- Historical prices for each portfolio holding
- Portfolio weights
- A risk-free rate proxy (e.g., 3-month Treasury bill yield)
Step 1: Pull Historical Prices with MarketXLS
Use =GetHistory() to retrieve daily closing prices for the past year:
=GetHistory("AAPL", "2025-02-14", "2026-02-14", "Daily")
Formula documentation: GetHistory
Repeat for each stock in your portfolio. Place each stock's price series in its own column.
Alternatively, use =Last() for the current price to mark positions to market:
=Last("AAPL")
Formula documentation: Last
Step 2: Calculate Daily Returns
In the column adjacent to each price series, compute the daily percentage return:
=((B3 - B2) / B2)
Or use log returns for more mathematical precision:
=LN(B3/B2)
Drag down for the entire series (~252 trading days).
Step 3: Calculate Weighted Portfolio Returns
For each day, compute the portfolio return by weighting individual stock returns:
=SUMPRODUCT(weights_range, daily_returns_row)
For example, if weights are in cells $H$2:$H$5 and daily returns for that day are in C3:F3:
=SUMPRODUCT($H$2:$H$5, C3:F3)
Drag down to get a column of daily portfolio returns.
Step 4: Compute Average Portfolio Return
=AVERAGE(portfolio_returns)
This gives the average daily portfolio return.
Step 5: Compute Standard Deviation
=STDEV(portfolio_returns)
This gives the daily standard deviation of portfolio returns.
Step 6: Determine the Risk-Free Rate
Convert the annual risk-free rate to a daily rate:
Daily Rf = (1 + Annual_Rf)^(1/252) - 1
For a 4.5% annual risk-free rate:
=(1.045)^(1/252) - 1 = 0.0000175 (approximately 0.00175%)
Step 7: Calculate the Daily Sharpe Ratio
=(Average_Daily_Return - Daily_Rf) / Daily_StdDev
Step 8: Annualize the Sharpe Ratio
Multiply the daily Sharpe Ratio by the square root of 252 (trading days per year):
Annualized Sharpe = Daily_Sharpe × SQRT(252)
For monthly data, multiply by SQRT(12). For weekly data, multiply by SQRT(52).
| Data Frequency | Annualizing Factor |
|---|---|
| Daily | SQRT(252) ≈ 15.87 |
| Weekly | SQRT(52) ≈ 7.21 |
| Monthly | SQRT(12) ≈ 3.46 |
| Quarterly | SQRT(4) = 2.0 |
Using MarketXLS =SharpeRatio() for Instant Results
MarketXLS provides a built-in =SharpeRatio() function that automates the entire calculation:
=SharpeRatio(A1:B5, "12m", 0.045)
Formula documentation: SharpeRatio
Where:
- A1:B5 = Range containing stock symbols in column A and weights in column B
- "12m" = Analysis period (12 months)
- 0.045 = Risk-free rate (4.5%)
The function returns the annualized ex-post Sharpe Ratio for the portfolio.
Optional: Ex-Ante Variation
=SharpeRatio(A1:B5, "12m", 0.045, "ExAnte")
Formula documentation: SharpeRatio
The fourth parameter specifies the calculation variation — "ExPost" (default) uses historical returns, while "ExAnte" uses expected future returns.
Example Portfolio Setup
| A | B |
|---|---|
| AAPL | 0.30 |
| MSFT | 0.25 |
| GOOGL | 0.20 |
| AMZN | 0.15 |
| JNJ | 0.10 |
In cell D1:
=SharpeRatio(A1:B5, "12m", 0.045)
Formula documentation: SharpeRatio
This returns the annualized Sharpe Ratio for this five-stock portfolio over the last 12 months — no manual return calculations, no standard deviation formulas, no annualization math.
Sharpe Ratio vs. Sortino Ratio vs. Treynor Ratio
The Sharpe Ratio is not the only risk-adjusted performance measure. Two common alternatives are the Sortino Ratio and the Treynor Ratio.
Sortino Ratio
Sortino Ratio = (Rp - Rf) / σ_downside
The Sortino Ratio replaces total standard deviation with downside deviation — the standard deviation of only negative returns. This distinguishes harmful volatility (downside) from beneficial volatility (upside).
When to use: When you care specifically about downside risk. A strategy with high upside volatility but little downside will have a much better Sortino Ratio than Sharpe Ratio.
MarketXLS provides:
=SortinoRatio(A1:B5, "12m", 0.045)
Formula documentation: SortinoRatio
Treynor Ratio
Treynor Ratio = (Rp - Rf) / β_p
The Treynor Ratio divides excess return by beta (systematic risk) rather than total risk. It measures return per unit of market risk only, ignoring diversifiable (idiosyncratic) risk.
When to use: For well-diversified portfolios where idiosyncratic risk is negligible. If a portfolio is poorly diversified, the Treynor Ratio can be misleading.
MarketXLS provides:
=TreynorRatio(A1:B5, "12m")
Formula documentation: TreynorRatio
Side-by-Side Comparison
| Feature | Sharpe Ratio | Sortino Ratio | Treynor Ratio |
|---|---|---|---|
| Risk measure | Total volatility (σ) | Downside deviation | Beta (β) |
| What it captures | All risk | Only downside risk | Only systematic risk |
| Best for | General comparison | Asymmetric strategies | Well-diversified portfolios |
| Penalizes upside volatility? | Yes | No | No |
| Requires benchmark? | No (just Rf) | No (just Rf) | Yes (market index) |
| MarketXLS function | =SharpeRatio() | =SortinoRatio() | =TreynorRatio() |
When to Use Which
- Sharpe Ratio — default choice for most comparisons
- Sortino Ratio — better for strategies that generate volatile but predominantly positive returns (e.g., options selling)
- Treynor Ratio — better for evaluating a portfolio within a larger diversified allocation
Additional Portfolio Metrics in MarketXLS
Beyond the three ratios, MarketXLS provides several complementary portfolio analytics:
=CAGR() — Compound Annual Growth Rate
=CAGR(A1:B5, "36m", 100000)
Formula documentation: CAGR
Returns the compound annual growth rate for the portfolio over 36 months with a $100,000 initial investment.
=MaximumDrawdowns() — Worst Peak-to-Trough Decline
=MaximumDrawdowns(A1:B5, "12m", 100000)
Formula documentation: MaximumDrawdowns
Returns the maximum drawdown — the largest peak-to-trough decline — over the specified period.
=Beta() — Individual Stock Beta
=Beta("AAPL")
Formula documentation: Beta
Returns the stock's beta, measuring its sensitivity to market movements.
=StockVolatilityOneYear() — Annualized Volatility
=StockVolatilityOneYear("AAPL")
Formula documentation: StockVolatilityOneYear
Returns the annualized historical volatility for the past year.
=DividendYield() — Dividend Component
=DividendYield("AAPL")
Formula documentation: DividendYield
Returns the current dividend yield, which contributes to total return calculations.
Practical Examples: Building a Sharpe Ratio Dashboard
Example 1: Comparing Two Portfolios
Growth Portfolio:
| Ticker | Weight |
|---|---|
| NVDA | 0.30 |
| AMZN | 0.25 |
| TSLA | 0.25 |
| META | 0.20 |
Defensive Portfolio:
| Ticker | Weight |
|---|---|
| JNJ | 0.25 |
| PG | 0.25 |
| KO | 0.25 |
| PEP | 0.25 |
Calculate for both:
Growth Sharpe: =SharpeRatio(A1:B4, "12m", 0.045)
Defensive Sharpe: =SharpeRatio(A8:B11, "12m", 0.045)
Formula documentation: SharpeRatio
The portfolio with the higher Sharpe Ratio delivers better risk-adjusted returns — regardless of which has the higher raw return.
Example 2: Strategy Optimization
Start with an equal-weight portfolio and iteratively adjust weights to maximize the Sharpe Ratio. Use Excel Solver:
- Set the objective cell to the
=SharpeRatio()formula result - Set changing cells to the weight cells
- Add constraints: weights sum to 1.0, each weight ≥ 0
- Run Solver to maximize
The resulting weights define the maximum Sharpe Ratio portfolio — the tangent portfolio on the efficient frontier.
Example 3: Rolling Sharpe Ratio
Calculate the Sharpe Ratio over rolling 12-month windows to see how risk-adjusted performance evolves over time:
- Pull 3 years of daily data with
=GetHistory() - For each day from month 13 onward, calculate the Sharpe Ratio using the preceding 12 months of returns
- Plot the rolling Sharpe Ratio over time
Periods where the rolling Sharpe drops below zero indicate sustained underperformance relative to the risk-free rate.
Common Mistakes When Using the Sharpe Ratio
| Mistake | Why It Is a Problem |
|---|---|
| Comparing ratios computed over different periods | A 3-year Sharpe is not comparable to a 1-year Sharpe |
| Ignoring the risk-free rate assumption | Using 0% instead of the actual Treasury rate skews results |
| Not annualizing consistently | Monthly Sharpe ≠ annualized Sharpe |
| Using arithmetic mean instead of geometric | Slightly overstates returns for volatile portfolios |
| Assuming higher Sharpe = no risk | Even a Sharpe of 2.0 can experience significant drawdowns |
| Ignoring survivorship bias | Backtested strategies often show inflated Sharpe Ratios |
Limitations of the Sharpe Ratio
-
Assumes normal distribution — Real-world returns are skewed and fat-tailed. A strategy with rare but catastrophic losses (negative skew) may show a misleadingly high Sharpe Ratio.
-
Penalizes upside volatility — Explosive upside moves increase standard deviation, which lowers the Sharpe Ratio even though investors benefit from upside volatility.
-
Time-period sensitive — The Sharpe Ratio can vary dramatically depending on the measurement window. Always specify the period.
-
Ignores non-linear risk — Options strategies with capped upside and open-ended downside can show attractive Sharpe Ratios until a tail event occurs.
-
Serial correlation — For strategies with autocorrelated returns (e.g., trend following), the standard Sharpe calculation can overstate risk-adjusted performance.
For these reasons, always use the Sharpe Ratio alongside other metrics like the Sortino Ratio, maximum drawdown, and Calmar Ratio for a complete risk picture.
Sharpe Ratio in Academic and Professional Context
CAPM and the Capital Market Line
In the Capital Asset Pricing Model (CAPM), the Capital Market Line (CML) represents the set of portfolios that combine the risk-free asset with the market portfolio. The slope of the CML is the Sharpe Ratio of the market portfolio. Any portfolio on the CML is mean-variance efficient; portfolios below the CML are suboptimal.
Information Ratio
The Information Ratio is a close cousin of the Sharpe Ratio that replaces the risk-free rate with a benchmark return and uses tracking error instead of standard deviation:
Information Ratio = (Rp - Rb) / Tracking Error
This measures a manager's ability to generate excess returns relative to their benchmark, per unit of active risk.
Sharpe Ratio in Fund Prospectuses
Many mutual funds and ETFs report their Sharpe Ratio in marketing materials and factsheets. However, be cautious — the measurement period, risk-free rate assumption, and return calculation method can all vary. Always verify the methodology before comparing across funds.
Frequently Asked Questions
What is a good Sharpe Ratio for a portfolio?
A Sharpe Ratio above 1.0 is generally considered good, indicating that the portfolio's excess return exceeds its volatility. Ratios between 0.5 and 1.0 are acceptable for most equity portfolios. Above 2.0 is exceptional and difficult to sustain over long periods.
How is the Sharpe Ratio different from the Sortino Ratio?
The Sharpe Ratio uses total standard deviation (both upside and downside volatility) as its risk measure, while the Sortino Ratio uses only downside deviation. The Sortino Ratio is more appropriate when returns are not symmetrically distributed or when you specifically want to measure downside risk.
Can the Sharpe Ratio be negative?
Yes. A negative Sharpe Ratio means the portfolio returned less than the risk-free rate. This indicates the investor would have been better off holding Treasury bills. However, comparing two negative Sharpe Ratios can be misleading — a ratio of -0.5 is not necessarily "better" than -1.0 in the traditional sense.
How does MarketXLS calculate the Sharpe Ratio?
MarketXLS uses the =SharpeRatio() function, which takes a portfolio range (symbols and weights), an analysis period, and an optional risk-free rate. It retrieves historical adjusted closing prices, computes weighted monthly returns, calculates the mean and standard deviation, and returns the annualized ex-post Sharpe Ratio. The function handles dividend adjustments and weight normalization automatically.
Should I use daily, weekly, or monthly returns?
Monthly returns are most common for fund-level analysis. Daily returns provide more data points and are useful for trading strategies with short holding periods. Regardless of frequency, always annualize the Sharpe Ratio for comparability. The annualizing factor is SQRT(number of periods per year): SQRT(252) for daily, SQRT(52) for weekly, SQRT(12) for monthly.
How do I improve my portfolio's Sharpe Ratio?
There are two ways: increase returns or decrease risk. In practice, the most effective approach is diversification — adding assets with low or negative correlations to existing holdings reduces portfolio volatility without necessarily reducing expected returns. Use Excel Solver with the =SharpeRatio() function to find the optimal weight allocation that maximizes risk-adjusted performance.
Conclusion
The Sharpe Ratio is a widely used single number for evaluating investment performance on a risk-adjusted basis. While it has limitations (particularly its treatment of upside volatility and assumption of normality), it provides a universal language for comparing strategies, funds, and portfolios. By combining it with the Sortino Ratio for downside risk and the Treynor Ratio for systematic risk, you get a comprehensive view of portfolio performance.
In MarketXLS, the =SharpeRatio() function computes the annualized ratio from your portfolio in a single cell, while =SortinoRatio() and =TreynorRatio() provide complementary perspectives. Pull historical data with =GetHistory(), current prices with =Last(), and dividend yields with =DividendYield(), all within your Excel workbook.
Whether you are optimizing a personal portfolio, evaluating fund managers, or backtesting a trading strategy, the Sharpe Ratio belongs in your analytical toolkit. Get started with MarketXLS to run these portfolio analytics in Excel.
For pricing details and subscription options, visit the MarketXLS pricing page.
