Home › Templates › Trading expectancy spreadsheet

Trading Expectancy Spreadsheet

A free trading expectancy calculator for Excel and Google Sheets. Log each closed trade with its entry, stop, exit, size and fees. The sheet turns every trade into an R-multiple and shows expectancy in $ and in R, win rate, payoff ratio, profit factor, the break-even win rate, a 95% interval for expectancy, expectancy by setup and a cumulative R chart.

Download Excel template (.xlsx) 318 KB · 500-trade log · no macros · Excel 2016+ and Google Sheets · v1.0, 9 Nov 2026

Use it in Google Sheets: choose File → Import → Upload in Sheets and pick the .xlsx, or upload it to Google Drive and open it with Google Sheets. No sign-up, no email gate, no add-in.

Preview: the Example tab and Summary. The 80 trades, setups and symbols are fictional and say nothing about any real strategy or future result.
+0.24Rexpectancy per trade ($108.56)
−0.06R to +0.54R95% interval: includes 0R, so not proven
1.54profit factor
48.7% vs 38.9%win rate vs break-even win rate
−6.46Rlargest drawdown, 4 losses in a row at most
DateSetupSymbolSideEntryStopExitSize1RNet P/LRCum. R
2026-01-09Gap fillDEMOShort14.0814.4514.43810$299.70−$283.50−0.95R−0.95R
2026-01-12Range fadeFICTShort130.08133.35133.25150$490.50−$475.50−0.97R−1.92R
2026-01-13PullbackFUT-XLong5,219.755,214.755,214.502 ×50$500.00−$534.00−1.07R−2.98R
2026-01-16Gap fillSMPLLong179.64175.75186.54130$505.70$896.001.77R−1.21R
2026-01-19BreakoutSMPLLong159.49157.33157.87190$410.40−$309.80−0.75R−1.97R
2026-01-22Range fadeSMPLLong200.86193.90192.0070$487.20−$620.20−1.27R−3.24R
2026-01-26PullbackDEMOShort25.9426.6226.69440$299.20−$330.00−1.10R−4.34R
2026-01-28Range fadeFICTLong53.3451.9951.55220$297.00−$395.80−1.33R−5.68R
2026-02-02PullbackXMPLLong38.0636.7439.66380$501.60$606.001.21R−4.47R
2026-02-06PullbackSMPLLong200.19193.89205.1780$504.00$396.400.79R−3.68R
2026-02-09BreakoutFUT-XShort4,956.254,962.254,942.002 ×50$600.00$1,416.002.36R−1.32R
2026-02-11PullbackDEMOShort12.1512.4512.451,670$501.00−$502.00−1.00R−2.32R
−10R−5R0R5R10R15R20R01020304050607080Trade numberlow point −6.46R after trade 20+19.1R after 80 trades

What trading expectancy tells you

Expectancy is the average result per trade. A positive number means the logged trades made money on average after fees; a negative one means they lost. The usual formula is:

Expectancy = win rate × average win − loss rate × average loss

That is the same number as the plain average of all results, which the sheet shows as a check. In dollars it is skewed by position size: one big trade can dominate. So the sheet also measures each trade in R, the risk you set at entry:

1R = |entry − stop| × size × multiplier  ·  R-multiple = net P/L ÷ 1R

A trade that risked $400 and made $600 is +1.5R. Expectancy in R is the average R-multiple: what each unit of risk returned in the log. Win rate alone says little. A 48.7% win rate makes money only when the average win is large enough versus the average loss, which is why the sheet puts the win rate next to the break-even win rate for your payoff ratio.

What it cannot tell you

Expectancy describes trades that already happened. It does not predict the next ones. Regulators are blunt about the risk in active trading. FINRA's required day-trading disclosure says: "Day trading can be extremely risky" and "You should be prepared to lose all of the funds that you use for day trading" (FINRA Rule 2270). The SEC tells investors: "Don't believe advertising claims that promise quick and sure profits from day trading" (SEC, Day Trading: Your Dollars at Risk). The CFTC warns that "claims of high or guaranteed returns are red flags of fraud" (CFTC Release 8854-24).

Data on retail results points the same way. A study of everyone who began day trading Brazilian equity futures from 2013 to 2015 and kept going for at least 300 days found "97% of them lost money" (Chague, De-Losso and Giovannetti, Day trading for a living?). ESMA reported that "74-89% of retail accounts typically lose money" on CFDs across EU jurisdictions (ESMA, 2018). A spreadsheet cannot change those odds. It can show you honestly what your own log says, and how little a short log proves.

What's inside the spreadsheet

TabWhat you typeWhat it calculates
Start hereNothingSteps, formulas in plain words, how to read the 95% interval, colour key, limits, sources, disclaimer
SummaryYour setup names (up to 12)Your log next to the Example: trades, wins, losses, scratches, win and loss rate, average win and loss in $ and R, expectancy in $ and R, profit factor, payoff ratios, break-even win rate, standard deviation, standard error, 95% interval, t statistic, trades needed to clear 0R, total R, largest drawdown, longest losing streak, fees in R. Expectancy by setup and by direction with their own intervals. Cumulative R charts and a by-setup chart
Trade logUp to 500 closed trades: date, setup, symbol, Long or Short, entry, initial stop, exit, size, multiplier (optional), round-trip fees, notesPer trade: row status, 1R in $, gross and net P/L, R-multiple, win/loss/scratch, cumulative R and $, peak, drawdown in R, losing streak, fees in R
ExampleAlready filled: 80 fictional trades, 4 fictional setups, stocks and a fictional futures contractThe same tab with every result visible

How to use it

  1. Open Summary and type your setup names in the yellow cells of the "Expectancy by setup" table. The Setup column in the log offers them as a drop-down list.
  2. Open Trade log. For each closed trade, oldest first, type the date, setup, Long or Short, entry price, initial stop, exit price and size. Add the total fees for the round trip.
  3. Use the initial stop, the one you set at entry. A stop moved later changes 1R after the fact and makes R-multiples look better or worse than the plan was.
  4. For futures or options, type the multiplier: the $ value of a 1.00 price move per contract (for example 50). Leave it blank for shares, coins and forex units. For forex in a currency other than the account's, convert P/L before logging or use the multiplier as the conversion rate.
  5. Check that Row status says OK. Rows with an error say what to fix and are left out of every total. Then read the Summary tab.

Already have a journal? Copy the columns across from your trading journal or backtesting spreadsheet. Backtest and live trades are best kept in separate copies of the file.

The formulas

Expectancy (R) = win rate × average win (R) − loss rate × average loss (R)

Profit factor = gross profit ÷ gross loss  ·  Payoff ratio = average win ÷ average loss

Break-even win rate = average loss ÷ (average win + average loss) = 1 ÷ (1 + payoff ratio)

Standard error = SD of R ÷ √n  ·  95% interval = expectancy ± t(0.975, n − 1) × SE

Win rate counts every OK trade, including scratches. A scratch is a trade with net P/L of exactly 0; it adds nothing to either side. The break-even win rate assumes trades either win or lose, so the sheet compares it with the win rate among decided trades (wins ÷ (wins + losses)).

Exact formulas from the Trade log tab (row 8). In the file the per-trade ones are wrapped in IF($M8="OK", …, "") so a row stays blank until its inputs are valid.

FieldFormula
Multiplier used (N8)=IF($J8="",1,$J8)
Initial risk, 1R (O8)=ABS($F8-$G8)*$I8*$N8
Gross P/L (P8)=IF($E8="Long",1,-1)*($H8-$F8)*$I8*$N8
Net P/L (Q8)=$P8-N($K8)
R-multiple (R8)=$Q8/$O8
Result (S8)=IF($Q8>0,"Win",IF($Q8<0,"Loss","Scratch"))
Cumulative R (U8)=$U7+N($R8)
Drawdown from peak (X8)=$U8-$W8

Summary formulas, Example column (cell references point to other Summary rows):

MeasureFormula
Win rate=IF(C6>0,C10/C6,"")
Average win (R)=IF(C10>0,SUMIFS('Example'!$R$7:$R$506,'Example'!$S$7:$S$506,"Win")/C10,"n/a")
Expectancy (R)=IF(C6>0,IF(C10>0,C13*C24,0)-IF(C11>0,C14*C25,0),"")
Profit factor=IF(C6>0,IF(C21>0,C20/C21,"n/a: no losses"),"")
Payoff ratio (R)=IF(AND(ISNUMBER(C24),ISNUMBER(C25)),C24/C25,"n/a")
Break-even win rate=IF(ISNUMBER(C27),1/(1+C27),"n/a")
Standard deviation of R=IF(C6>=2,STDEV('Example'!$R$7:$R$506),"n/a: need 2+ trades")
Standard error=IF(C6>=2,C31/SQRT(C6),"n/a")
t value, 95%=IF(C6>=2,TINV(0.05,C6-1),"n/a")
95% interval, low=IF(C6>=2,C26-C33*C32,"n/a")
Trades needed to clear 0R=IF(AND(C6>=2,N(C26)<>0),ROUNDUP((1.96*C31/C26)^2,0),"n/a")

Expectancy by setup uses COUNTIFS and SUMIFS on the setup and status columns, for example =IF($A71="",0,COUNTIFS(Example!$C$7:$C$506,$A71,Example!$M$7:$M$506,"OK")). The setup's standard deviation comes from a helper column of R²: =IF(B71<2,"",SQRT(MAX(0,(SUMIFS(Example!$T$7:$T$506,Example!$C$7:$C$506,$A71,Example!$M$7:$M$506,"OK")-B71*G71^2)/(B71-1)))).

The interval follows the NIST/SEMATECH handbook definition of confidence limits for the mean: Ȳ ± t × s ÷ √N, "where Ȳ is the sample mean, s is the sample standard deviation, N is the sample size" (NIST/SEMATECH e-Handbook, 1.3.5.2). TINV(0.05, n−1) returns that two-sided t value in Excel, Google Sheets and LibreOffice.

Worked example from the sample data

One trade with a multiplier and fees

2026-02-09: short 2 contracts of the fictional FUT-X (setup: Breakout) at 4,956.25, initial stop 4,962.25, exit 4,942.00, multiplier 50, fees $9.00.

A losing short

2026-01-09: short 810 shares of the fictional DEMO at 14.08, stop 14.45, exit 14.43, no fees. 1R = 0.37 × 810 = $299.70. Net P/L = −$283.50, so R = −0.946R. The exit came 0.02 before the stop, so the loss was a little under 1R.

All 80 trades

The equity line in the preview rose to +19.1R, but only after a drawdown of −6.46R and a streak of 4 losses. The risk of ruin calculator shows how the risk per trade turns streaks like that into account drawdowns.

Expectancy by setup

The Summary tab repeats the key measures for each setup and for Long vs Short trades, each with its own 95% interval. Example tab:

SetupTradesWin rateExpectancy (R)Expectancy ($)Profit factor95% intervalSheet note
Breakout3033.3%+0.21R$108.381.47−0.39R to +0.80RInterval includes 0R
Pullback2853.6%+0.26R$83.911.45−0.20R to +0.72RSmall sample: treat as unproven
Range fade1656.2%−0.07R−$31.670.83−0.53R to +0.39RSmall sample: treat as unproven
Gap fill666.7%+1.13R$598.455.53−0.61R to +2.87RSmall sample: treat as unproven
Long5749.1%+0.26R$116.091.59−0.10R to +0.62RInterval includes 0R
Short2343.5%+0.18R$89.901.44−0.40R to +0.76RSmall sample: treat as unproven
−1.0R−0.5R0R+0.5R+1.0R+1.5R+2.0R+2.5R+3.0RBreakout (n = 30)Pullback (n = 28)Range fade (n = 16)Gap fill (n = 6)All trades (n = 80)Dot = expectancy (R per trade). Bar = 95% interval.

Gap fill has the highest average, +1.13R per trade, from just 6 trades; its interval runs from −0.61R to +2.87R. Range fade is slightly negative at −0.07R. Every setup's interval includes 0R. With samples this small, the ranking can flip with a few more trades.

Splitting a log into many setups and keeping the best one is a trap. Bailey, Borwein, López de Prado and Zhu show that "high performance is easily achievable after backtesting a relatively small number of alternative strategy configurations" and that "the higher the number of configurations tried, the greater is the probability that the backtest is overfit" (Pseudo-mathematics and financial charlatanism, 2014). The sheet labels any setup with fewer than 30 trades "Small sample: treat as unproven". Thirty is a common rule of thumb, not a guarantee.

Sample size: how sure can you be?

The standard error falls with the square root of the number of trades. Four times as many trades halve the width of the interval. NIST puts it simply: "one way to obtain more precise estimates for the mean is to increase the sample size" (NIST/SEMATECH e-Handbook). The table uses the Example's standard deviation of 1.35R:

Tradest value (95%)Interval half-width
102.262±0.96R
202.093±0.63R
302.045±0.50R
502.010±0.38R
801.990±0.30R
1001.984±0.27R
2001.972±0.19R
5001.965±0.12R

With 100 trades and this spread, an expectancy would need to be above about 0.27R for the interval to clear 0R. Two more cautions. The interval assumes each trade is independent and drawn from the same conditions; markets change. And NIST notes that "a 95 % confidence interval does not mean that there is a 95 % probability that the interval contains the true mean." It describes how often the method captures the true mean over many samples.

Common mistakes

Limits

FAQ

How do you calculate trading expectancy in Excel?

Put one closed trade per row with its net profit or loss. Expectancy = win rate × average win − loss rate × average loss, which is the same as the average result per trade: =AVERAGE of the P/L column. To get it in R, first divide each trade's net P/L by the risk you set at entry (|entry − stop| × size), then average those R-multiples. This template does both, plus profit factor, the break-even win rate and a 95% interval.

What is a good expectancy in trading?

Any expectancy above zero after fees means the logged trades made money on average; below zero means they lost. There is no universal good number, and a high figure from a short log can be luck. Check the 95% interval: if it includes 0R, the trades so far do not show that the true expectancy is positive. Past results do not predict future results.

What is an R-multiple?

An R-multiple is a trade's result divided by its initial risk. If you risk $200 (the distance from entry to stop times size) and make $300, the trade is +1.5R; a $200 loss is −1R. R puts trades of different sizes and markets on one scale, so expectancy in R shows the average result per unit of risk.

What is the difference between expectancy and profit factor?

Expectancy is the average result per trade (in $ or R). Profit factor is gross profit divided by gross loss. Above 1.0 profit factor and above 0 expectancy say the same thing: the log made money. Expectancy tells you how much per trade; profit factor tells you how many dollars were won per dollar lost, and says nothing about how many trades it took.

How do you calculate the break-even win rate?

Break-even win rate = average loss ÷ (average win + average loss) = 1 ÷ (1 + payoff ratio). With an average win of 2R and an average loss of 1R, you need to win more than 1 ÷ 3 = 33.3% of decided trades for expectancy to be positive before any change in sizes or costs.

How many trades do you need to trust your expectancy?

No fixed number works for everyone. The standard error of expectancy is the standard deviation of R divided by the square root of the number of trades, so the interval narrows slowly: four times the trades halves its width. The sheet shows the interval and an estimate of how many trades it would take for the interval to clear 0R if the mean and spread stayed the same, which they may not.

Related tools and templates

Educational template only, not investment advice. Example data is fictional: the setups, symbols, prices and results are made up to show the formulas and say nothing about any real strategy or future result. No win rate or return is promised or implied. Sources: NIST/SEMATECH e-Handbook of Statistical Methods (nist.gov); FINRA Rule 2270 (finra.org); SEC, Day Trading: Your Dollars at Risk (sec.gov); CFTC Release 8854-24 (cftc.gov); ESMA (esma.europa.eu); Chague, De-Losso and Giovannetti (RePEc); Bailey, Borwein, López de Prado and Zhu (PDF). Retrieved 9 November 2026.