Building a simple backtest in a spreadsheet

What you will learn

• The standard column layout of a spreadsheet backtest.
• The formulas for signal, position, strategy return, equity curve and drawdown.
• How to shift the position by one row to avoid look-ahead bias.
• Five checks that catch most spreadsheet errors.

Explanation

A spreadsheet backtest is slow for large tests, but every number is visible, which makes it the best place to learn. You will test the moving-average rule from lesson 2, here with a 3-month simple moving average (SMA) so that the table stays short.

The column layout

Row 1 holds the headings; month 1 is in row 2, so month m is in row m+1. Cell K1 holds the cost per position change (here 0.2%).

In German-language spreadsheet versions, function names and separators differ, for example MITTELWERT instead of AVERAGE, WENN instead of IF, and semicolons instead of commas.

The key idea: the one-row shift

Column E copies the signal from the previous row. The signal is known only at the close of month m, so it can earn the return of month m+1 at the earliest. If you multiply the signal of the same row by the return of the same row, the test "knows" the month's result before it decides. This single mistake makes many spreadsheet backtests look far too good.

Equity and drawdown

The equity curve is the account value over time, here as an index starting at 100. Returns are compounded: each month multiplies the previous value. The drawdown is the percentage fall from the highest equity reached so far (the running peak); detailed drawdown and recovery maths were covered in the course "Risk & Trade Management".

Worked example

Hypothetical example: monthly closes €100, €104, €108, €106, €101, €97, €99, €104, €109, €112. Cost: 0.2% per position change (a simple stand-in for spread and fees, lesson 8). Taxes and cash interest are excluded. The equity starts at 100 at the close of month 3, the first month with an SMA; the position before that is 0.

Checks for three rows:

• Month 4: position 1 (from month 3), entry cost applies: −1.85% − 0.20% = −2.05%. Equity: 100 × (1 − 0.0205) = 97.95.
• Month 6: position changes to 0, exit cost only: −0.20%. Equity: 93.33 × 0.998 ≈ 93.14. Drawdown: 93.14 ÷ 100 − 1 = −6.86%.
• Month 9: re-entry: +4.81% − 0.20% = +4.61%. Equity: 93.14 × 1.0461 ≈ 97.43.

Result: equity 100.11 (+0.11%) with 3 position changes and a maximum drawdown of −6.86%. Buy-and-hold from month 3 to 10: €112 ÷ €108 − 1 = +3.70%. With the simpler formula =IF(B5>C5,1,0), the tie in month 4 would have meant exiting one month earlier, and the result would have been about +5.07%. One edge case changed the outcome by about 5 percentage points. Eight months prove nothing about the rule; the purpose here is the mechanics.

Common mistake

Practice activity

Five checks for any spreadsheet backtest. Apply them on paper to the table above and write down what you would see if the test were correct.

1. Count position changes and compare with the costs charged.
2. Recalculate one random row by hand.
3. Set the cost to 0 and the position to 1 everywhere: the result must equal buy-and-hold.
4. Check that the first position row uses a signal from an earlier row.
5. Check that the drawdown is never positive.

Key takeaways

• Use separate columns for signal and position, with the position shifted one row down.
• Compound returns into an equity curve and calculate drawdown from the running peak.
• Charge costs whenever the position changes, not every month.
• Run the five checks before trusting any spreadsheet result.

Next step

In the next lesson you will turn an equity curve like this one into standard performance metrics: CAGR, drawdown, Sharpe ratio and profit factor.