Backtest Log Template: R From Prices and a Tested-vs-Live Comparison
Log each trade of a test and get R from the prices alone, then compare your backtest, forward-test and live averages side by side.
3 Nov 2026 · 6 min read
On this page
This is a log for the trades of a test, with the sums built in: type the direction, entry, stop and exit of each trade and it works out R, then compares your backtest, forward test and live trades side by side. It does not supply price data or run a test. It records trades you simulated or took elsewhere and adds them up.
Download it
- Backtest log template, Excel (.xlsx): Test details, Log (2,000 rows) and Compare, with the formulas.
- Backtest log template, .csv: the typed columns of the Log, for any other tool.
No sign-up. It needs Excel 2019 or later, Google Sheets or LibreOffice.
The three sheets
- Test details. Ten prompts to fill in before you run the test: the entry, stop and exit rules in words, the instruments and timeframe, the data source and period, the costs you assume, how many trades you planned, whether the rules changed during the test, and what would make you stop trusting it. A test whose rules were written after the result is a description, not a test.
- Log. One row for each trade: test name, source (Backtest, Forward test or Live), date, symbol, direction, entry, stop, exit, and the cost of the trade in R. Gross R and net R are calculated.
- Compare. For each source: trades, win rate, expectancy per trade after costs, profit factor and the average cost, plus the gap between the backtest and the other two.
R from prices, so there is no pip value to set
Most spreadsheets ask for lot size and pip value to get a result in money. This one works from prices, because R is a ratio: how far the trade went compared with how far the stop was. For a long trade that is the exit minus the entry, divided by the entry minus the stop. For a short trade it is the entry minus the exit, divided by the stop minus the entry. The same sums work on a currency pair, a metal or an index.
In the file, gross R is =IF(COUNT(F2:H2)<3,"",IF(F2=G2,"",IF(E2="Long",(H2-F2)/(F2-G2),IF(E2="Short",(F2-H2)/(G2-F2),"")))), and net R is gross R minus the cost you typed, in R. A cost in R is the spread, commission and slippage for the trade divided by what you risked. Leave it blank and the sheet treats it as zero, which flatters the result, so type a cost.
A worked example
Five made-up trades, to show the arithmetic. They are not from any test or any trader, and the table is not a suggestion of what to trade.
| Source | Direction | Entry | Stop | Exit | Gross R | Cost | Net R |
|---|---|---|---|---|---|---|---|
| Backtest | Long | 1.1000 | 1.0900 | 1.1300 | +3.00R | 0.10R | +2.90R |
| Backtest | Short | 1.1000 | 1.1200 | 1.0700 | +1.50R | 0.10R | +1.40R |
| Backtest | Long | 1.1000 | 1.0900 | 1.0850 | −1.50R | 0.10R | −1.60R |
| Forward test | Long | 1.1000 | 1.0900 | 1.1200 | +2.00R | 0.20R | +1.80R |
| Forward test | Short | 1.1000 | 1.1200 | 1.1300 | −1.50R | 0.20R | −1.70R |
- First row: a long from 1.1000 with a stop at 1.0900 risks 0.0100. The exit at 1.1300 gained 0.0300, which is 3.00R gross and 2.90R after a 0.10R cost.
- Compare sheet: the three backtest rows average +0.90R a trade after costs and the two forward-test rows average +0.05R, a gap of +0.85R. The sheet reports the gap and nothing more; five rows cannot tell you why.
Why a Source column
A backtest is the easiest of the three to flatter: costs are assumed, fills are perfect, and rules get nudged until the curve looks right. Logging the same strategy as Backtest, Forward test and Live in one place puts the three averages next to each other. When the backtest is far ahead of the rest, the places to look first are the costs you assumed, the fills, any rule changed along the way, and how few trades each average rests on. The guide to backtesting by hand covers a procedure for the test itself.
What it does not do
- It has no price data and no replay. Take your trades from whichever tool you test in, or from your own charts.
- It does not check your rules. It cannot tell whether a stop was placed where your rules say.
- It does not tell you how many trades are enough. See how many trades before your stats mean anything.
We're working on a free journal and a backtester, with no date yet. Today Trepeat is a trade copier for MT4, MT5 and cTrader, currently on demo accounts, and you can add notes, a grade and screenshots to any copied trade once it closes, with a calendar of your results.
We sell no replay tool and no price data, and Trepeat's journal has no backtest log today, so this file is one, kept by you; see the journal documentation for what it records. For a record of trades you took, the trading journal template is the companion.
Written by the Trepeat team.