How to Calculate Sharpe Ratio for Simulated Trades

By Marcel Hambálek · Senior Trader, For Traders

To calculate the annualised Sharpe Ratio in Excel from a column of daily returns, use =(AVERAGE(D2:D253)/STDEV.S(D2:D253))*SQRT(252), where column D holds daily excess returns (your return minus the daily risk-free rate). Swap SQRT(252) for SQRT(52) on weekly data and SQRT(12) on monthly data.

Key takeaways

• The one-cell Excel formula for an annualised daily Sharpe Ratio is =(AVERAGE(D2:D253)/STDEV.S(D2:D253))*SQRT(252) on a column of daily excess returns.
• Subtract the daily risk-free rate (annual rate ÷ 252), not the annual rate, from each daily return before averaging.
• Use STDEV.S (sample) rather than STDEV.P — on a 60-row evaluation sample it inflates Sharpe by roughly 1% if you get it wrong.
• Annualisation factor is the square root of the number of periods per year: √252 daily, √52 weekly, √12 monthly, √1,638 for hourly on a 6.5-hour session.
• Above 1.0 is solid, above 2.0 is strong, above 3.0 on a small sample usually means overfitting, missing costs, or a return series with the losing days quietly removed.
• Zero-return days must stay in the series — deleting no-trade days shrinks your standard deviation and manufactures a fake Sharpe.

Watch: related video

Quick answer: the Sharpe Ratio formula in Excel

Paste this into an empty cell once your excess returns are in column D: =(AVERAGE(D2:D253)/STDEV.S(D2:D253))*SQRT(252). That's the annualised Sharpe Ratio for a year of daily trades, no add-ins, no VBA. Everything below just explains why that string works and where each number in your sheet needs to sit.

The one-cell version

If you already have a column of excess returns — your daily return minus the daily risk-free rate — the whole Sharpe Ratio formula in Excel collapses into one line:

• =AVERAGE(D2:D253)/STDEV.S(D2:D253) gives you the raw, non-annualised Sharpe Ratio
• Multiply that by SQRT(252) — the standard count of trading days in a year — to annualise it
• Full string: =(AVERAGE(D2:D253)/STDEV.S(D2:D253))*SQRT(252)

Swap the range to match however many rows of data you're testing, and swap SQRT(252) for SQRT(52) on weekly returns or SQRT(12) on monthly returns. Same logic, different sampling frequency.

The four-column layout that produces it

Before the formula means anything, your sheet needs to be structured right. Four columns is all it takes — this is how to find the Sharpe Ratio in Excel without fighting your own spreadsheet later:

Column D is what feeds both AVERAGE and STDEV.S in the master formula. Get D wrong — forget to subtract the risk-free rate, or mix percentages with decimals — and the Sharpe number you get out will look plausible but be quietly meaningless.

The underlying formula in plain maths

Excel is just automating the definition William F. Sharpe published in 1966 and refined in 1994: mean return minus the risk-free rate, divided by the standard deviation of those excess returns. In notation:

Sharpe = (Mean Return − Risk-Free Rate) ÷ StdDev(Excess Returns)

Map that to Excel and the pieces line up cleanly: AVERAGE handles the mean, the risk-free rate subtraction already happened in column D, STDEV.S handles the sample standard deviation, and SQRT scales the whole ratio from daily to annual terms. No black box — just four functions doing exactly what the 1966 formula asked for.

Step 1: Build a clean return series from your trade log

Your Sharpe Ratio is only as good as the return series feeding it, and a raw trade log isn't a return series — it's a list of fills. Before you touch STDEV.S, you need a gap-free daily NAV equity curve with exactly one row per calendar trading day, no missing weekdays, no duplicated closes.

Exporting from MT4/MT5 or a challenge dashboard

MetaTrader 4 CSV export (Account History → right-click → Save as Report) and MetaTrader 5 trade history export both give you closed trades with open time, close time, symbol, lots, and profit — not a daily balance column. Same story with a For Traders Challenge trade-history export: you get individual fills timestamped to the second, not an end-of-day NAV. That's the raw material, not the finished input. You have to convert "trades with timestamps" into "one balance per day" yourself.

From closed trades to a daily equity curve

Add a helper column that extracts the date (not datetime) from each trade's close time: =INT(CloseTime). Then build a daily NAV column using SUMIFS to sum that day's realized P/L onto the prior day's balance:

Once you have NAV for every date, the daily return is simple:

Drag that down the full column and you've got your daily return series — the input the Sharpe formula from the intro actually expects.

Why zero-return days must stay in the series

Weekends and holidays get excluded entirely — that's why a full year of daily returns lands at roughly 252 rows, not 365. But weekday days where you didn't trade still belong in the series, marked as 0.00%, not deleted. Traders instinctively want to strip out "flat" days because they feel like noise. Don't. Deleting no-trade days shrinks your standard deviation artificially (you're removing low-variance observations), which inflates your Sharpe Ratio and makes a mediocre system look better than it traded. Keep every weekday row, funded or flat.

Simple returns vs log returns

For daily-sized returns, log returns vs simple returns is mostly a rounding argument — the difference typically shows up in the third decimal place, so most traders default to simple returns for daily Sharpe work. Log returns earn their keep when you're compounding over long horizons or comparing across assets with very different volatility, since they're additive across time and symmetrical for gains/losses. If you're building a sharpe ratio backtest on simulated trades across weeks or months, switch to log returns; for a daily read on a single challenge account, simple returns are fine.

Step 2: Pick your risk-free rate (and convert it correctly)

The standard proxy for the risk-free rate for Sharpe Ratio work is the 3-month US T-bill yield, and you get it free from FRED (Federal Reserve Economic Data) — series DTB3 for the daily secondary-market rate, or TB3MS if you only need a monthly average. Pull the number, convert it to a daily figure, and subtract it from every row of returns before you touch AVERAGE or STDEV.S.

Which rate to use in 2026

Use whatever DTB3 printed on FRED for the dates your equity curve covers — not today's headline rate. If you're scoring trades from January to June 2026, pull the average DTB3 over that exact window. Matching the rate to your test period is what makes the Sharpe comparable across different accounts and different traders; using a static "current" number for a backtest from 2023 just introduces noise.

Where to pull it from: FRED series DTB3

Go to the FRED site, search DTB3, and export the CSV for your date range. It's a daily annualized yield quoted in percent — that's your starting point before conversion. Don't use the 10-year yield or SOFR unless your strategy specifically holds duration risk overnight for weeks; for short-dated intraday and swing systems, the 3-month bill is the accepted convention.

Annual to daily: divide by 252, don't subtract the annual rate

Here's the conversion, and it's simple arithmetic that gets botched constantly. If DTB3 shows an annual rate of 3.80%, your daily risk-free rate is:

3.80% ÷ 252 = 0.01508% per day.

That 0.01508% (0.0001508 as a decimal) is what you subtract from each daily return to build your excess-return column. The single most common error in this whole workflow is subtracting the annual 3.80% directly from a daily return that might be +0.20% — you'll get -3.60% "excess return" on every single row, and your Sharpe Ratio comes out deeply negative even on a genuinely profitable book. If your Sharpe looks catastrophically wrong after adding a risk-free rate, check this first.

When you can set the risk-free rate to zero

If you're flat at the close every day — no positions carried overnight, no funding charged or earned — you're not exposed to the risk-free rate at all, and setting RF = 0 is defensible for a pure intraday strategy. It simplifies the formula to =(AVERAGE/STDEV.S)*SQRT(252) with no subtraction step.

Worth noting too: prop evaluations run on simulated capital you never actually funded, so there's no real opportunity cost to a risk-free alternative — which is why many traders default to RF = 0 when comparing challenge attempts internally. Nothing wrong with that shortcut, just label your output "Sharpe (RF=0)" so nobody downstream mistakes it for a rate-adjusted number when they're benchmarking it against a live fund's Sharpe.

Step 3: STDEV.S or STDEV.P — which one and why it matters

Use STDEV.S, not STDEV.P, for your Sharpe Ratio calc — your return series is a sample of a strategy's true (unknown) distribution, never the whole population, so the n−1 denominator is the statistically correct one.

Sample vs population in one sentence

Population standard deviation (STDEV.P, dividing by n) is for when you hold every possible data point that could ever exist; sample standard deviation (STDEV.S, dividing by n−1) is for when you're estimating from a subset — and a 40-day evaluation window is always a subset of the strategy's lifetime returns.

The size of the difference on a 60-row evaluation sample

STDEV.P divides by a larger denominator (n instead of n−1), which shrinks the standard deviation of returns slightly — and since Sharpe divides by that number, a smaller denominator inflates the ratio. The inflation factor is √(n/(n−1)), and it shrinks fast as your sample grows:

Worth noting: if you're working off an old spreadsheet template that just says STDEV(), you haven't inherited an error — legacy STDEV is functionally identical to STDEV.S. Microsoft only added the .S suffix to disambiguate it from STDEV.P when both functions were introduced side by side.

The verdict for traders

Get this right, but don't overrate it. On a small sample Sharpe ratio built from a 40-day evaluation, the STDEV.S vs STDEV.P choice moves your headline number by roughly 0.8% — that's noise compared to the swing you'd get from one large outlier trade or a different risk-free assumption. Neither function rescues a strategy with a genuinely poor return-to-drawdown profile; a Sharpe of 0.3 calculated with STDEV.P is still a Sharpe of roughly 0.3, not a 1.2. Use STDEV.S because it's the statistically defensible choice, not because it will flatter your number — and treat any Sharpe Ratio quoted to two decimal places from a sub-60-day sample with a healthy dose of skepticism, regardless of which formula built it.

Step 4: How to annualise the Sharpe Ratio (√252 and the rest)

To annualize sharpe ratio from daily data, multiply your daily Sharpe by SQRT(252) — that's =(AVERAGE(returns)/STDEV.S(returns))*SQRT(252) in Excel. The 252 isn't arbitrary; it's the number of trading days left once you strip weekends and US market holidays out of a calendar year, and it's the default day-count convention across equities, futures and most FX desks.

Where 252 comes from

Start with 365 calendar days. Subtract roughly 104 weekend days and you're at 261. Knock off nine or ten US market holidays — New Year's, MLK Day, Presidents' Day, Good Friday, Memorial Day, Juneteenth, Independence Day, Labor Day, Thanksgiving, Christmas — and you land at approximately 252 trading days. It shifts a day or two year to year depending on the calendar, which is why some desks use 250 or 253 as a rounder shorthand. None of that matters enough to argue about.

The full annualisation factor table

That hourly row assumes a standard equity cash session. If you're running 24/5 FX or crypto that trades round the clock, your hourly bar count per year is different — recalculate the period count first, then take the square root. Don't borrow the 40.47 figure from an equities strategy and slap it on a gold or crypto intraday system.

Why you multiply by the square root, not the period count

This trips up more traders than it should. Mean return scales linearly with time — ten days of average daily return really is ten times one day's average return. Standard deviation doesn't behave the same way; under the standard assumption of independent, identically distributed returns, it scales with the square root of time. Since Sharpe Ratio is mean divided by standard deviation, the linear numerator and the square-root denominator combine so the whole ratio scales by SQRT(n). That's the entire justification for the sharpe ratio annualized sqrt 252 convention — it isn't a magic multiplier, it's the mathematical consequence of how volatility compounds versus how returns compound.

Why annualising fewer than 60 observations is meaningless

Here's the part most calculators skip. The SQRT(252) annualisation factor doesn't just scale your Sharpe estimate — it scales your estimation error right alongside it. If your daily Sharpe was computed from 30 rows, you're multiplying a noisy number by 15.87, and the noise comes along for the ride. The standard error of an annualised Sharpe Ratio built from 60 daily observations works out to roughly ±0.65. So an evaluation result showing "Sharpe 1.9" over a 60-day window is statistically indistinguishable from a Sharpe of 1.0 — the confidence interval swallows the difference entirely. Treat any annualised figure from a short sample as a rough signal, not a verdict on edge.

Why your Sharpe Ratio changes when you switch from daily to monthly returns

Same trades, same P&L, three different Sharpe Ratios — because the √n annualisation in the formula assumes each return is independent of the last, and trading returns almost never are. This is the single most common source of "why does my Sharpe Ratio not match theirs" confusion, and it's rarely explained past the formula itself.

The same equity curve scored three ways

Take one 12-month simulated equity curve — 252 daily closes, resampled into 52 weekly and 12 monthly returns. Run the annualised Sharpe on all three and you get three different numbers from identical underlying P&L:

Nothing changed about the strategy. What changed is the return frequency Sharpe Ratio calculation reacts to — and for a trend-following book that strings winning days together, monthly will almost always flatter the daily number.

Autocorrelation: the real culprit

The √n scaling in the Sharpe formula is only valid if returns are independent draws — no memory from one period to the next. Autocorrelation returns break that assumption in two opposite directions:

• Positive autocorrelation (trend-following, swing setups that ride a leg for days) — winning days cluster together, which suppresses daily volatility relative to the volatility you'd measure at monthly resolution. Daily vol looks artificially smooth, so the daily Sharpe understates the strategy and the monthly Sharpe overstates it.
• Negative autocorrelation (mean-reversion, scalping, fading extended moves) — gains and losses alternate day to day, inflating daily volatility relative to monthly. Here it's the daily Sharpe that looks better than the monthly one.

Layer on top of that: 12 monthly observations is a thin sample. A single strong month can drag the average and the Sharpe with it — you're not just changing frequency, you're changing how noisy the estimate is. Fewer data points, wider confidence interval, easier to get a Sharpe that looks great by accident.

Which frequency to report

Match the frequency to your actual holding period — don't shop for the flattering number. A scalper reporting monthly Sharpe is hiding intraday whipsaw; a swing trader reporting daily Sharpe is punishing themselves for volatility that's irrelevant to how they actually hold risk.

1. Day trader or scalper closing positions same-session — use daily returns.
2. Swing trader holding days to weeks — weekly returns, or daily with an autocorrelation-adjusted Sharpe if you want to be rigorous.
3. Position trader or portfolio-level book held over a full evaluation cycle — monthly is defensible, but say so.

Whatever you pick, write it down next to the number: "Sharpe 1.4, daily returns, annualised, RF = 3.80%." That single line is what separates a Sharpe Ratio someone can trust from one they should ignore — and it's exactly the kind of discipline that shows up in how you size and report performance once you're managing a funded account rather than a spreadsheet.

How to calculate the Sharpe Ratio in Python with pandas

Once your backtest log runs past a few thousand rows, Excel starts lagging, autocomplete breaks your formulas, and one dragged cell reference can quietly corrupt the whole sheet. The Python equivalent of the Excel formula is five lines: read the CSV, resample to daily NAV, take pct_change(), subtract the daily risk-free rate, then divide the mean excess return by its standard deviation and annualise with np.sqrt(252). Same math, no dragged formulas.

The five-line pandas version

This is the core calc — the sharpe ratio python pandas snippet you'll reuse for every backtest:

That ddof=1 matters more than it looks. Pandas' Series.std() already defaults to ddof=1 — the sample standard deviation, equivalent to Excel's STDEV.S. But if you reach for NumPy's np.std() instead of the pandas method, the default flips to ddof=0, the population standard deviation — the numpy std ddof trap that silently reproduces the same STDEV.P error we flagged in the Excel section. It shrinks your denominator and inflates your Sharpe, and nothing in the output warns you it happened. Always pass ddof=1 explicitly when you use NumPy directly, or just stick to the pandas method.

Reading your MT5 CSV into a return series

MT5's exported statement gives you a running balance per trade, not a clean daily series, so you need the resample-then-pct_change step before any Sharpe math makes sense. Set your timestamp column as the index, resample to daily frequency with .last() to grab end-of-day NAV, drop the NaNs from weekends and holidays, then run pct_change(). Skip this step and you'll compute returns trade-to-trade instead of day-to-day — which inflates the sample size and understates volatility, giving you a Sharpe that has nothing to do with the daily-returns convention prop firms and allocators actually expect.

Rolling Sharpe for regime checks

A single full-period Sharpe hides a strategy that worked for six months and died. A rolling sharpe ratio catches that:

Plot it. If the line is flat around 1.2 for the first half of your backtest and drifts toward zero (or negative) in the second half, you're not looking at a stable edge — you're looking at an edge that decayed, propped up by one flattering average. That's the number worth reporting to yourself before you size up on a funded account.

What is a good Sharpe Ratio for trading?

A Sharpe below 0.5 is noise — don't pay an evaluation fee to trade a strategy that lives there. Between 0.5 and 1.0 is workable but fragile. Above 1.0 is solid. Above 2.0 is strong. And above 3.0 on a sample under a year almost always means something in your data or your process is broken, not that you found an edge everyone else missed.

The interpretation tiers

When a high Sharpe is a red flag

If you're asking what is a good Sharpe ratio for trading and your number sits above 3.0 on a few months of data, go looking for the leak before you celebrate. The usual suspects:

• Survivorship in the trade log — dropped instruments or accounts that blew up aren't in your sample anymore.
• Deleted losing days, whether intentional or from a broken export.
• Look-ahead bias in the backtest — the model sees data it wouldn't have had live (next bar's close used to trigger this bar's signal is a classic one).
• Gross returns with no commission or swap baked in — a Sharpe on paper profits, not tradeable ones.
• Overfitting in backtests — parameters tuned on the exact window you're now scoring, so of course it looks perfect.

How costs move the number

Slippage and commissions aren't a rounding error — they're often the difference between a fundable Sharpe and a fantasy one. Take a high-frequency XAUUSD strategy backtested on clean fills: add a realistic 0.4 pips of slippage per side plus round-turn commission, and it's common to see the reported Sharpe cut roughly in half. The shorter your average hold, the worse the hit — a scalp that nets 3 pips per trade eats a much bigger percentage of its edge in costs than a swing trade netting 80 pips.

Here's the part that matters more once you're inside a live evaluation: a prop desk grading your account cares far less about a smooth Sharpe than about your max drawdown and whether you respected the daily loss limit. A 0.9 Sharpe with disciplined drawdown control passes far more evaluations than a dazzling 2.8 that blew past risk rules on the way there.

Sharpe Ratio: what it tells you and what it hides

Pros

• One number that makes strategies with different volatility profiles directly comparable
• Calculable in a single Excel cell from a column of returns — no add-ins, no subscriptions
• Universally understood, so a prop desk, allocator or trading partner knows exactly what you mean
• Scale-independent: works identically on a $10,000 simulated account and a $200,000 one
• Rewards consistency, which is the behaviour an evaluation actually selects for

Cons / risks

• Punishes large winning days exactly as hard as large losing days
• Assumes roughly normal, independent returns — trading returns are usually neither
• Wildly unstable on short samples; a 30-day evaluation Sharpe has a standard error near ±1.0
• Says nothing about the depth or duration of your worst drawdown, which is what breaks accounts
• Trivially gamed by selling options or martingale sizing — smooth until it isn't

Frequently Asked Questions