The Backtesting Spreadsheet Template for Recording Your Own Test Results

Most backtesting spreadsheets fail for the same reason most trading journals do: the columns do not match the job. A trading journal exists to record what you actually did with your own money. A backtest log exists to answer a narrower, earlier question, whether a strategy would have made money across a sample of past setups big enough to trust. Different question, different columns. Below is a two-tab structure built for that question: a per-trade log where you record each historical setup you replay, and a summary tab that rolls those rows up into a win rate and an expectancy number, after cost. Both tabs are copy-paste text further down this page.

Quick Answer

What a Backtest Log Records (and a Trading Journal Doesn't)

The two get confused because they look similar on a screen: rows of trades, a direction column, an entry and a stop. The difference is what each row means. A row in a trading journal is a trade you actually took, with real money on it, logged as it happened. A row in a backtest log is a historical setup you are replaying after the fact, to find out whether the rule that would have told you to take it is any good. If you are logging fills from your broker, you want the trading journal template, not this one.

The two also answer different-sized questions. A backtest log is judged in aggregate, across dozens or hundreds of rows, because a single historical trade proves nothing about a strategy. A pre-trade grade is judged on one setup, the one in front of you right now, because the backtest already told you the strategy has an edge and the only open question left is whether this instance of it is clean. The full argument for why you want both rather than either one is in backtesting versus pre-trade AI grading. This post is the spreadsheet, not the argument.

The Per-Trade Backtest Log: Every Column

One row per historical setup you replay. Paste the line below into cell A1 of a blank sheet named "Log" and it splits on the tabs into 12 columns. The formulas further down assume this exact order and this exact tab name, since the summary formulas point at Log! directly.

Test Date	Strategy	Ticker	Trade Date	Direction	Entry	Stop	Exit	R-Multiple	Cost (R)	Net R	Notes

Column J, the cost assumption, is the one most homemade backtests skip, and it is the reason this template has it as its own column rather than folding it into the R-multiple. Keeping it separate means you can see both numbers: what the setup would have returned in a frictionless world, and what it actually nets once slippage and commission take their cut.

The Two Formulas: R-Multiple and Net R

Ten columns are typed. Two compute themselves. Paste each formula into row 2 of the matching column and it carries down for every trade you add after that.

I2 (R-Multiple)
=IFERROR(IF(E2="Long",(H2-F2)/ABS(F2-G2),(F2-H2)/ABS(F2-G2)),"")

K2 (Net R)
=IF(I2="","",I2-J2)

The R-multiple formula measures the exit against the stop distance rather than against a fixed dollar amount, which is what makes rows from different tickers and different position sizes comparable on the summary tab. A setup that returned $40 on a tight stop and one that returned $400 on a wide stop can land at the same R-multiple, and that is the point: the number describes how the trade performed relative to the risk it was structured to take, not how large the account swing was. If reward-to-risk as a concept is new, the underlying math is covered in the reward-to-risk breakdown.

The Strategy Summary Tab

Add a second tab named "Summary" with one row per strategy. Every cell on it reads from the Log tab, so it stays current as you add more test rows without you touching it again.

Swap the literal strategy name in each formula for the one you are summarizing, and add a new summary row every time you start testing something new. The win rate formula in particular is worth reading carefully: it counts rows with a positive net R, after cost, not a positive raw R-multiple. A strategy can look like it wins 60% of the time before cost and something noticeably lower after it, and the summary tab is built to surface that gap rather than hide it.

Building It in Excel or Google Sheets

Paste the Log header into A1 of a sheet named "Log," select the row, and in Excel hit Ctrl+T to convert it into a real table so the formulas you enter in row 2 extend automatically as you add rows. Microsoft's documentation on calculated columns in a table covers the mechanic. Google Sheets does not need the table conversion step; just select I2:K2 after pasting the formulas and drag the fill handle down as you log more trades, or wrap them in an ARRAYFORMULA if you want them to populate ahead of the data.

Set Direction as a dropdown with Long and Short as the only options, and set Strategy as a dropdown pointed at a short list on a third tab. Google's guide to creating an in-cell dropdown list covers both range-backed and manually typed versions. The dropdown matters more here than on a trading journal, because every summary formula matches on the literal text in that column, and "Bull Flag" and "bull flag" will silently split into two rows on the summary tab if you type it by hand twice.

Add the summary tab last, once the log has a handful of rows to point at, and reference the Log tab by name in every formula exactly as shown above. If a formula returns a blank instead of a number, the tab name is the first thing to check; a renamed tab breaks every COUNTIF and AVERAGEIF pointed at it silently, with no error message.

Why the Cost Row Changes the Verdict

A backtest run entirely on clean historical prices, with no cost column, is measuring a strategy that does not exist. Every real fill loses a little to the spread you cross going in and out, a little more to slippage on a fast move, and a fixed amount to commission on some brokers. None of those show up on a historical chart, which is exactly why column J exists as its own input rather than being baked silently into the exit price.

Pick a conservative cost assumption for the instrument you are testing and apply it uniformly: a wider assumption for a fast-moving low-float stock or a thin altcoin, a narrower one for a liquid major pair or a large-cap index name. Say a strategy's Log rows average 1.4R gross across forty tested trades. A 0.2R cost assumption is not a rounding error against that number, it is roughly a seventh of the entire edge, and the Net Expectancy column on the summary tab is what tells you whether 1.2R survives being traded live or was mostly an artifact of testing on frictionless prices.

How Many Trades Before It Means Anything

A sample size column exists on the summary tab for a reason beyond bookkeeping: it is the number that tells you how much to trust the win rate next to it. Twenty rows is a starting point for a rough read and nothing more, since the range of outcomes consistent with a real edge and consistent with pure noise overlap heavily at that count. Fifty rows narrows that range enough to act on cautiously. A hundred or more, spanning more than one kind of market condition rather than one clean trending month, is closer to where an expectancy number deserves real weight.

Condition matters as much as count. Forty rows tested entirely during a strong trend tells you how the strategy performs in a strong trend, and nothing about how it performs in a choppy range, even though the sample size column reads the same either way. If you can tag which market condition each row belongs to, a third dropdown column on the Log tab pays for itself the first time a strategy that looked great in one regime falls apart in another.