Home › Templates › Backtesting spreadsheet

Backtesting Spreadsheet

A free backtesting spreadsheet for testing one trading strategy by hand: write the rules, log each historical trade in R, and get win rate, profit factor, expectancy, max losing streak, max drawdown, results by setup tag, a 95% sample-size check and an equity curve.

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 add-on, no email needed. The formulas are plain IF, SUM, COUNTIFS, SUMIFS, MIN/MAX and STDEV.

#	Instrument	Dir.	Entry	Stop	Target	Exit	Size	Setup tag	Result R	Equity R	Drawdown R
1	DEMO-B	Long	49.00	47.89	51.22	47.89	1	Pullback	−1.05	−1.05	−1.05
2	DEMO-A	Long	114.60	112.42	118.96	112.42	1	Pullback	−1.05	−2.10	−2.10
3	DEMO-B	Short	47.58	48.55	46.12	48.55	1	Pullback	−1.05	−3.15	−3.15
4	DEMO-A	Short	114.01	115.85	110.33	115.85	0.5	Pullback	−0.55	−3.70	−3.70
5	DEMO-B	Long	49.19	48.08	50.85	50.85	1	Pullback	+1.45	−2.25	−2.25
6	DEMO-A	Short	122.80	124.76	118.88	118.88	1	Range fade	+1.95	−0.30	−0.30
7	DEMO-B	Short	50.47	51.58	47.70	51.58	0.5	Range fade	−0.55	−0.85	−0.85
8	DEMO-A	Short	120.28	122.40	117.10	117.10	1	Range fade	+1.45	+0.60	0.00

• 40trades
• 45.0%win rate
• 1.41profit factor
• +0.207Rexpectancy per trade
• −4.20Rmax drawdown
• 4max losses in a row

What's inside the spreadsheet

How to run a manual backtest with it

1. Write the rules first. Fill in Setup rules before you look at any chart: entry, stop, target, exit, sizing and the test period. Vague rules ("strong trend") let hindsight decide trades for you.
2. Set 1R. Type your risk per trade in B7 (for example 1% of the balance). Results are kept in R, so this only affects the % columns.
3. Go back in time. Scroll your chart to the start of the test period and hide future bars. Most charting platforms have a bar replay mode; otherwise move forward one bar at a time.
4. Log every valid setup. When the rules give a trade, type date, instrument, direction, entry, stop and target before you reveal the next bars. Then step forward to the exit and type the exit price. Log the losers and the boring ones too.
5. Add costs. Put spread plus commission as a fraction of R in Costs (R), for example 0.05. Use Size (R) = 0.5 when your rules say half size.
6. Read the Dashboard as a whole. Look at expectancy together with its 95% range, the max drawdown and the max losing streak. Check the setup-tag table for one tag carrying all the results.
7. Change a rule, start a new copy. Do not edit old trades to fit the new rule. Then test the final rules on a period you have not used yet, or forward test with small size.

Why the log works in R

R is the amount you risk on a full-size trade: the distance from entry to stop. A trade that gains twice that distance is +2R; a full stop-out is −1R. Working in R lets you compare a $49 stock, a $120 stock and a currency pair in one table, and it keeps position size out of the strategy test. To turn R into money, multiply by your risk per trade; the position size calculator does the reverse and turns a risk amount into a share or lot size.

The formulas, explained

Exact formulas from the file (row 5 of Backtest log, data in rows 5–504; Dashboard values in column B). Columns: D = direction, E = entry, F = stop, G = target, H = exit, I = size (R), J = costs (R), K = setup tag, O = result (R).

The Dashboard formulas use full sheet references (for example 'Backtest log'!$O$5:$O$504); they are shortened here. The equity chart reads a helper column (V) that returns #N/A for empty rows, so Excel and Google Sheets leave a gap instead of drawing a line down to zero. That column is greyed out on purpose.

Worked example from the sample data

The Example log holds 40 fictional trades from 9 Jan 2025 to 21 Jul 2025. Costs are 0.05R on every trade and risk is 1% of a $10,000 example balance.

Trade 1: a long stop-out

Long DEMO-B at 49.00, stop 47.89, target 51.22. Risk per unit = 49.00 − 47.89 = 1.11. Planned R:R = (51.22 − 49.00) ÷ 1.11 = 2.00. The exit was the stop, so the result is (−1.11 ÷ 1.11) × 1 − 0.05 = −1.05R, or −1.05% of the account.

Trade 4: a short at half size

Short DEMO-A at 114.01 with the stop at 115.85; stopped out. Size (R) is 0.5, so the result is −1 × 0.5 − 0.05 = −0.55R. That was the fourth loss in a row, and the equity line is at −3.70R.

Trade 5: a target hit

Long DEMO-B at 49.19, stop 48.08 (risk 1.11), exit at the target 50.85. Result: (50.85 − 49.19) ÷ 1.11 − 0.05 = +1.45R.

Trade 18: a time exit

Rule 5 closes a trade after 10 bars. Long DEMO-A at 121.18, closed at 121.33: (121.33 − 121.18) ÷ 2.00 − 0.05 = +0.025R. Costs nearly cancelled the gain, but it still counts as a win because it is above 0R.

The dashboard

By setup tag and direction (from the Example dashboard):

"Range fade" looks best, but it rests on 10 trades. "Breakout" looks worst on 7. Groups this small can flip with a handful of new trades, so treat the table as a list of questions to test, not answers.

How many trades is enough?

A backtest measures a sample, not the strategy's true edge. The Dashboard's sample-size check uses the normal-approximation confidence interval for a proportion that the NIST/SEMATECH e-Handbook of Statistical Methods (section 7.2.4.1) gives as "the confidence expression most frequently used": p̂ ± z × √(p̂(1 − p̂) ÷ n), with z = 1.96 for 95%. NIST also warns these limits "may not be accurate enough" when the sample is very small. Here is how wide the 95% range is for a measured 50% win rate:

In the example, the 45.0% win rate over 40 trades has a 95% range of 29.6% to 60.4%. The expectancy of +0.207R has a standard error of 0.213R (standard deviation 1.35R ÷ √40), so its 95% range is −0.211R to +0.624R. Because that range includes zero, the verdict cell reads "95% range includes 0 R: no clear edge yet". The 40 example trades cannot tell a small edge apart from luck.

The check assumes each trade is independent and that the data and rules are clean. It cannot detect the biases below, and a "95% range above 0R" result from a biased test means nothing.

Biases that make backtests look better than they are

The CFA Institute's "Backtesting and Simulation" refresher reading says analysts "need to pay attention to several behavioral issues in backtesting, including survivorship bias and look-ahead bias." In a manual spreadsheet test, these are the usual ones:

• Look-ahead bias: using information you would not have had at the time, such as the bar's close for an entry made intra-bar, or a level you only saw after scrolling past it. Fix: hide future bars and type entry, stop and target before you step forward.
• Survivorship bias: testing only instruments that still exist today. Stocks that were delisted, merged or went bust drop out of most screeners. Research on mutual funds by Elton, Gruber and Blake found that "funds that disappear tend to do so due to poor performance" (The Review of Financial Studies 9(4), 1996, pp. 1097–1120). The same logic applies to a stock list picked from today's index members.
• Overfitting (trying many variations): Bailey, Borwein, López de Prado and Zhu write that "the higher the number of configurations tried, the greater is the probability that the backtest is overfit." In their example, with only 5 years of data "no more than 45 independent model configurations should be tried", or a strategy with an in-sample annualized Sharpe ratio of 1 is almost guaranteed even when the expected out-of-sample Sharpe ratio is zero ("Pseudo-Mathematics and Financial Charlatanism", Notices of the AMS 61(5), May 2014). Keep a count of the rule versions you test, and keep a final period untouched.
• Cherry-picking setups: skipping trades that "didn't look right" after you saw the outcome. Log every setup the rules allow.
• Ignoring costs: spread, commission and slippage. Even 0.05R per trade costs 2R over 40 trades.

Regulators make the same point about any backtest. The SEC's Investor Bulletin: Performance Claims (15 Sept 2022) says: "Remember that back-tested performance is hypothetical and does not reflect actual performance." It also says "past performance cannot predict how an investment strategy will perform in the future."

Common mistakes

• Leaving blank rows in the log. The loss-streak formula resets on a blank row. Keep trades in consecutive rows.
• Moving the stop in the log after the fact. The stop you planned defines 1R. If you trail a stop, keep the original stop in column F and type the actual exit price.
• Judging by win rate alone. A 45% win rate can be profitable and a 70% win rate can lose money; expectancy combines both sides. See why win rate alone doesn't matter.
• Reading the setup-tag table as proof. Small groups swing a lot. Check each tag's trade count first.
• Mixing strategies in one file. One file per strategy and rule version keeps the statistics meaningful.
• Typing over grey cells. Grey columns are formulas. If you break one, copy it down again from the row above.

FAQ

How do I backtest a trading strategy in Excel?

Write the entry, stop, target and exit rules first. Then scroll a chart back to the start of your test period, move forward one bar at a time, and log every trade the rules give you: date, direction, entry, stop, target and exit. A formula turns each trade into an R multiple, and COUNTIF, SUMIF and MIN/MAX formulas give win rate, profit factor, expectancy and drawdown. The free template on this page does all of that.

How many trades do you need for a backtest?

There is no single number, but more trades give a narrower range of plausible results. With the usual 95% normal approximation, a 50% win rate measured on 30 trades could plausibly be anywhere from about 32% to 68%; on 300 trades the range is about 44% to 56%. The template's sample-size check shows these ranges for your own log.

What is expectancy in trading?

Expectancy is the average result per trade. In R it is net R divided by the number of trades, which equals win rate × average win plus loss rate × average loss. In the example log it is +0.207R per trade over 40 fictional trades.

What is a good profit factor?

Profit factor is gross profit divided by gross loss. Above 1 means the trades made more than they lost; below 1 means they lost money. There is no official threshold for 'good', and a high profit factor on a small or overfitted sample can disappear in live trading.

What is look-ahead bias in backtesting?

Look-ahead bias is using information that was not available at the time of the trade, for example a daily close you could not have known at entry, or a chart you scrolled past. Moving forward bar by bar with future bars hidden reduces it. The CFA Institute lists look-ahead bias and survivorship bias among the issues analysts must watch for in backtesting.

Is backtested performance the same as real performance?

No. The SEC's Investor.gov says back-tested performance is hypothetical and does not reflect actual performance. Real trading adds slippage, missed fills, changing markets and your own decisions under pressure.

Related tools and templates

• Trading journal toolLog live trades after the backtest
• Trading journal templateSpreadsheet for real trades
• Trading planWrite rules before you trade
• Position size calculatorTurn 1R into shares or lots
• Risk/reward calculatorCheck R:R before entry
• Why win rate alone doesn't matterWin rate vs expectancy
• All templatesFree spreadsheets

Educational template only, not financial or investment advice. Backtested results are hypothetical and do not predict future results. Example data is fictional: the strategy, the instruments DEMO-A, DEMO-B and DEMO-FX, all prices, dates and results are made up. Quotes from Investor.gov, NIST, the CFA Institute and the cited papers were retrieved on 25 October 2026.