Sharpe Ratio in Excel: Sortino, Treynor & Information Ratio (2026)

July 21, 2026 · VeloraAI Team
Data Analysis Financial Modeling Formulas

A fund that returned 22% last year sounds better than one that returned 14%. Then you learn the first ran 35% volatility and the second ran 9%, and the ranking inverts — the smaller number was doing more with less. That is the entire reason risk-adjusted return metrics exist, and the Sharpe ratio in Excel is the calculation every allocator, LP, and PM runs before they trust a headline number. If your track record spreadsheet only reports total return, you are handing your investors half a story.

This guide walks through the five ratios that actually get quoted in performance reviews — Sharpe, Sortino, Treynor, Information Ratio, and Calmar — with the exact Excel formulas, the annualization math that trips people up, and a portfolio dashboard layout you can copy today.

What Is the Sharpe Ratio and Why Does It Matter?

The Sharpe ratio measures how much excess return a portfolio earns per unit of total risk. It was defined by William F. Sharpe in 1966 as (portfolio return − risk-free rate) / standard deviation of returns. A Sharpe of 1.0 means the strategy earned one percentage point of excess return for every percentage point of volatility. Above 1.0 is respectable; above 2.0 is exceptional and rare on an annualized basis.

The metric matters because it lets you compare apples to grapefruit: a levered long/short book, an unlevered dividend portfolio, and a venture fund all reduce to a single number that says risk-adjusted, who is actually good? Sharpe is the default row on virtually every institutional tearsheet, hedge-fund flash report, and CFA Level II performance-attribution exam.

ℹ️ Note: Sharpe punishes upside volatility the same as downside volatility. A strategy with a huge positive spike will look worse, not better. That is Sortino's whole reason to exist — see below.

How Do You Calculate the Sharpe Ratio in Excel?

You calculate the Sharpe ratio in Excel with three formulas: AVERAGE for the mean return, STDEV.S for the sample standard deviation, and a subtraction for the risk-free rate. Divide (average return − risk-free rate) / standard deviation, then annualize by multiplying by SQRT(periods per year). For daily returns use SQRT(252); for monthly use SQRT(12).

The two-cell version

Assume column B holds 252 daily returns and cell E1 holds the daily risk-free rate (e.g. the 3-month T-bill divided by 252). The annualized Sharpe ratio is:

=(AVERAGE(B2:B253) - E1) / STDEV.S(B2:B253) * SQRT(252)

That is the whole formula. The subtleties — which risk-free rate, sample vs population standard deviation, arithmetic vs geometric mean — are what separate a defensible number from one that gets torn apart in a due-diligence call.

Step-by-step build

  1. Load daily (or monthly) returns into column B, starting at B2.
  2. Pull the risk-free rate: =BINDINGS.YIELD doesn't exist; use the current 3-month T-bill quoted rate (e.g. 4.30%) and divide by the number of periods per year to get the per-period rate.
  3. Compute excess return per period: =B2 - $E$1 in column C.
  4. Sharpe (per-period): =AVERAGE(C2:C253) / STDEV.S(C2:C253).
  5. Annualize: multiply the per-period Sharpe by SQRT(252) for daily data, SQRT(12) for monthly, SQRT(4) for quarterly.

💡 Pro Tip: Always use STDEV.S (sample) rather than STDEV.P (population) for a return series. You are estimating true volatility from a finite sample, not calculating it for the entire population of possible returns. The difference matters most with fewer than 60 observations.

The Five Risk-Adjusted Return Ratios Compared

Sharpe is famous, but it is not always the right tool. Here is when to reach for each of its cousins.

Ratio Numerator Denominator Best For Excel Function
Sharpe Excess return Total std dev General portfolio ranking STDEV.S
Sortino Excess return Downside std dev Skewed / option-heavy books SQRT(SUMSQ) + IF
Treynor Excess return Portfolio beta Diversified portfolios vs a benchmark SLOPE
Information Ratio Active return Tracking error Active managers vs a benchmark STDEV.S of alpha
Calmar Annualized return Max drawdown Trend-following / CTAs MIN(1 - cum/max) 
graph TD
    A[Return Series] --> B{What are you measuring?}
    B -->|Total risk vs risk-free| C[Sharpe Ratio]
    B -->|Downside risk only| D[Sortino Ratio]
    B -->|Systematic risk vs market| E[Treynor Ratio]
    B -->|Active risk vs benchmark| F[Information Ratio]
    B -->|Tail / drawdown risk| G[Calmar Ratio]
    C --> H[Rank / Report]
    D --> H
    E --> H
    F --> H
    G --> H

How Do You Calculate the Sortino Ratio in Excel?

The Sortino ratio replaces total standard deviation with downside deviation — the standard deviation of returns below a minimum acceptable return (MAR), typically 0% or the risk-free rate. Because it ignores upside volatility, it rewards strategies that swing higher without being penalized for it.

The Excel formula

Assume returns in B2:B253 and MAR in cell F1 (per-period). Downside deviation is:

=SQRT(SUMPRODUCT((B2:B253<$F$1)*(B2:B253-$F$1)^2) / COUNT(B2:B253))

Then Sortino:

=(AVERAGE(B2:B253) - $F$1) / [downside deviation] * SQRT(252)

Why Sortino beats Sharpe for skewed strategies

Options-selling books, merger-arb funds, and short-vol strategies all have positively skewed pain (many small wins, occasional large losses). Their Sharpe looks good because downside variance is low most of the time. Their Sortino tells the same story but does not penalize the rare upside bursts long-vol strategies enjoy — making Sortino the fairer comparison across skew regimes.

⚠️ Warning: Downside deviation is undefined if no returns fall below the MAR. Guard the denominator with IFERROR or a floor value, or your Sortino cell will show #DIV/0! on strong-performing quarters.

How Do You Calculate the Treynor Ratio in Excel?

The Treynor ratio divides excess return by beta rather than total volatility. It answers: for each unit of systematic (market) risk taken, how much excess return did the portfolio deliver? Treynor is only meaningful for well-diversified portfolios, because it ignores idiosyncratic risk that diversification is assumed to have washed out.

Compute beta first, then Treynor

Assume portfolio returns in B2:B253 and benchmark returns (e.g. S&P 500) in C2:C253.

Beta = SLOPE(B2:B253, C2:C253)
Treynor = (AVERAGE(B2:B253) - E1) / Beta * 252

Note the annualization: because beta is unitless, you multiply the whole ratio by the number of periods per year (not the square root). That is a common Excel bug — analysts annualize Treynor the same way they annualize Sharpe and get a number that is 16× off.

Example: If your fund earned 14% annualized with a beta of 0.8 and the risk-free rate is 4%, Treynor = (14% − 4%) / 0.8 = 12.5%. Compare to the market's Treynor of (market return − risk-free) at beta 1.0. If the market delivered 10% and the risk-free rate is 4%, market Treynor = 6% — your fund earned more than double per unit of systematic risk.

For a deeper build of beta itself (raw, unlevered, adjusted Blume beta), see our beta calculation in Excel guide.

How Do You Calculate the Information Ratio in Excel?

The Information Ratio (IR) measures a manager's active return per unit of active risk. It replaces the risk-free rate with a benchmark return and total standard deviation with the standard deviation of the return difference (a.k.a. tracking error). A large-cap manager beating the S&P 500 by 2% with 4% tracking error has an IR of 0.5 — solid, but not spectacular.

The Excel formula

With portfolio returns in B2:B253 and benchmark returns in C2:C253:

Active return: =B2 - C2   (fill down as column D)
Alpha (mean):  =AVERAGE(D2:D253)
Tracking err:  =STDEV.S(D2:D253)
IR (per-period): =Alpha / Tracking err
IR (annualized): =Alpha / Tracking err * SQRT(252)

What is a good Information Ratio?

Grinold and Kahn's Active Portfolio Management pins the benchmarks: IR above 0.5 is good, above 0.75 is very good, and above 1.0 is world-class over multi-year windows. Most active equity funds sit between 0.2 and 0.4 net of fees, which is one reason index funds keep winning inflows.

graph LR
    A[Portfolio Return] --> C[Active Return = Port - Benchmark]
    B[Benchmark Return] --> C
    C --> D[Mean = Alpha]
    C --> E[Std Dev = Tracking Error]
    D --> F[Information Ratio = Alpha / Tracking Error]
    E --> F
    F --> G[Annualize: × SQRT 252 daily, × SQRT 12 monthly]

How Do You Calculate the Calmar Ratio and Max Drawdown in Excel?

The Calmar ratio divides annualized return by the maximum drawdown over the same window. Trend-followers, CTAs, and crypto strategies live and die by drawdown, so Calmar is the metric their LPs actually watch.

Step 1: Build the cumulative equity curve

Assume daily returns in column B. In C1 enter 1 (starting NAV). In C2:

=C1 * (1 + B2)

Fill down. Column C is now the cumulative NAV path.

Step 2: Running max and drawdown

In D2:

=MAX($C$1:C2)

In E2 (drawdown as a negative number):

=C2/D2 - 1

Fill both down. Max drawdown is:

=MIN(E2:E253)

Step 3: Calmar

Calmar = Annualized return / ABS(Max drawdown)

A Calmar above 1.0 means the strategy makes more in a year than it loses in its worst peak-to-trough drop — the minimum bar most CTA allocators demand.

💡 Pro Tip: Report duration of drawdown alongside the depth. A 20% drawdown that recovers in 3 months is very different from a 20% drawdown that takes 3 years to work off. Add a MATCH on the recovery date to your dashboard.

The Annualization Rules That Trip Everyone Up

Half the risk-adjusted-return errors on Wall Street analyst tests come from wrong annualization. Memorize this table:

Metric Per-Period → Annualized Why
Return (arithmetic) × N Sums linearly
Return (geometric) (1 + r)^N − 1 Compounds
Standard deviation × SQRT(N) Variance sums, std dev is its root
Sharpe / Sortino / IR × SQRT(N) Numerator × N, denom × SQRT(N), net √N
Treynor × N Beta is unitless — no √
Beta No annualization Ratio of covariances

For daily data N = 252 (trading days). For monthly data N = 12. For weekly data N = 52. Always label the frequency on the tearsheet — a reader should never have to guess.

⚠️ Warning: SQRT(N) scaling assumes returns are i.i.d. (independent and identically distributed). Real returns show serial correlation, especially in illiquid strategies (private credit, real estate, hedge fund fund-of-funds). If autocorrelation is high, annualized Sharpe overstates the true metric — often by 30-50%. Consider a Newey-West adjustment or report both raw and desmoothed Sharpe.

Building the Risk-Adjusted Returns Dashboard

A working portfolio-performance sheet has five zones. Lay it out this way and the whole team knows where to look:

  1. Inputs (yellow): risk-free rate, benchmark ticker, MAR, annualization factor, currency.
  2. Return series (blue): date, portfolio return, benchmark return, active return, cumulative NAV.
  3. Rolling metrics (green): 12-month rolling Sharpe, 12-month rolling volatility, drawdown curve.
  4. Summary ratios (grey): Sharpe, Sortino, Treynor, IR, Calmar, max drawdown, longest DD, best/worst month.
  5. Attribution (white): contribution to return by sub-strategy or sleeve.
graph TD
    A[Inputs: Rf, MAR, Benchmark] --> B[Return Series]
    B --> C[Excess & Active Returns]
    C --> D[Sharpe / Sortino / IR / Treynor]
    B --> E[Cumulative NAV]
    E --> F[Drawdown Curve]
    F --> G[Calmar / Max DD]
    D --> H[Summary Tearsheet]
    G --> H
    H --> I[Rolling 12M Panels]

Rolling Sharpe with a spill formula

If you have monthly returns in B2:B121 (10 years) and want a 12-month rolling Sharpe in column D from row 13 onward:

=(AVERAGE(B2:B13) - $E$1) / STDEV.S(B2:B13) * SQRT(12)

Anchor the risk-free rate with $E$1 and drag down. A rolling Sharpe chart is often more informative than a single lifetime number — it shows whether the alpha decayed, when it decayed, and whether the volatility regime changed.

Common Sharpe Ratio Mistakes in Financial Models

These mistakes turn up in almost every real fund tearsheet audit:

  • Wrong risk-free rate. Analysts default to 0% or the 10-year Treasury. Use the yield on the T-bill matching the return frequency — 3-month for monthly returns, overnight for daily.
  • Mixing arithmetic and geometric returns. Sharpe traditionally uses arithmetic means, but the annualized "return" you report is usually geometric. Be consistent and explicit in a footnote.
  • Cherry-picking the window. A Sharpe reported on a 5-year bull-market slice is not the Sharpe over a full cycle. Report the metric across multiple windows (1Y, 3Y, 5Y, ITD).
  • Ignoring survivorship bias. If your track record excludes strategies you shut down, the surviving Sharpe is inflated. Institutional DDQs explicitly ask you to include closed strategies.
  • Applying Sharpe to illiquid returns. Private-market NAVs are smoothed — reported volatility is artificially low, and Sharpe artificially high. Desmoothed Sharpe using the Geltner or Getmansky-Lo-Makarov correction is the honest number.
  • Comparing across return frequencies. A monthly Sharpe and a daily Sharpe are not interchangeable, even after annualization. State the frequency explicitly.

Frequently Asked Questions

What is a good Sharpe ratio for a portfolio?

Above 1.0 is generally considered good, above 2.0 is very good, and above 3.0 is exceptional and often unsustainable. The S&P 500's long-run Sharpe sits around 0.4-0.5. Most active mutual funds score below 0.5 net of fees. Hedge funds typically target 1.0+; quant systematic strategies often target 2.0+. Anything above 3.0 on more than a two-year window deserves a hard look for return-smoothing or leverage effects.

Should I use daily or monthly returns for the Sharpe ratio?

Use monthly returns for most reporting — daily returns overstate volatility for portfolios with any bid-ask bounce, illiquidity, or asynchronous pricing, which pushes Sharpe down artificially. Daily is appropriate for liquid, marked-to-market portfolios (single-name equities, futures) where the Sharpe estimate has less noise thanks to the larger sample size. Whatever you pick, annualize consistently with SQRT(N).

What is the difference between Sharpe and Sortino ratios?

The Sharpe ratio divides excess return by total standard deviation, so it penalizes both upside and downside volatility equally. The Sortino ratio divides by downside deviation only, measured against a minimum acceptable return. Sortino is more appropriate for strategies with skewed return distributions (options-selling, merger-arb) where upside spikes are a feature, not a risk. When returns are roughly symmetric, Sortino and Sharpe rank strategies almost identically.

Can I calculate the Sharpe ratio for a portfolio with negative excess returns?

Yes, mechanically — but the resulting negative Sharpe is nearly uninterpretable for ranking. Two strategies with the same negative excess return but different volatilities will invert their Sharpe ordering compared to intuition (the more volatile strategy looks less bad). Israelsen's modified Sharpe ratio corrects this by adjusting the denominator's exponent. For due-diligence purposes, most allocators simply report negative Sharpes with an asterisk and rely on drawdown metrics instead.

How is the Sharpe ratio used in AI-driven portfolio construction?

Modern portfolio-construction workflows use Sharpe (or Information Ratio) as an objective function inside an optimizer — the algorithm searches asset weights that maximize expected Sharpe subject to constraints. AI-assisted tools can now generate the return-covariance inputs from natural-language prompts, saving hours on model prep. Tools like VeloraAI let analysts describe a portfolio in plain English and get a full risk-adjusted return dashboard built inside Excel — no manual formula wiring required.

From Sharpe to a Complete Performance Report

A Sharpe ratio is one cell. A performance report is that cell plus rolling metrics, drawdown analysis, factor attribution, benchmark comparisons, and a footnoted methodology page — the artifact your LPs, board, or investment committee actually read. Every additional ratio in this guide (Sortino, Treynor, IR, Calmar) adds a dimension of risk your Sharpe alone cannot see.

Wire up the dashboard once, refresh it monthly, and you have converted a spreadsheet of returns into an evidence-based case for how good your portfolio actually is. That is the difference between reporting a number and defending one.