An Excel backtest is a spreadsheet model that applies fixed trading rules to historical prices and measures the resulting return and risk.

This example uses 20-day and 50-day moving averages, calculates each signal at the close, and applies it on the next row. The finished test should identify.

The key rule is straightforward: a signal based on today's closing price cannot be used to claim a trade at that same close unless the execution model allows a closing fill. In this example, the signal is applied to the following day.

Excel Backtesting at a Glance

Component What you need
Historical data Date, Open, High, Low and Close
Example strategy 20-day and 50-day moving-average trend strategy
Position rule Long when the 20-day average is above the 50-day average
Execution rule Apply the signal one row later
Costs Commission, spread and estimated slippage
Key outputs Total return, CAGR, volatility, Sharpe ratio, profit factor and maximum drawdown
Main limitation Excel is less suitable for high-frequency, multi-asset or complex portfolio testing

Excel can convert a price range into a table through Home > Format as Table or Ctrl + T. Tables make it easier to filter data, copy formulas and manage the worksheet.

1. Prepare Clean Historical Price Data

Create a worksheet called Data with these columns:

Column Heading
A Date
B Open
C High
D Low
E Close
F SMA20
G SMA50
H Signal
I Position
J Gross Return
K Trading Cost
L Net Return
M Equity
N Peak Equity
O Drawdown

Sort the data from oldest date to newest date.

Check the following before you start:

  • Dates are in chronological order.
  • There are no duplicate dates.
  • Prices are numeric rather than text.
  • Missing prices are removed or treated consistently.
  • Open, High, Low and Close use the same adjustment basis.
  • The test period includes rising, falling and sideways conditions.

Daily candles are easier to inspect than intraday data, so they are a sensible starting point for a first Excel backtest.

2. Define the Trading Strategy Precisely

Use this long-only moving-average strategy:

  • Calculate a 20-day simple moving average.
  • Calculate a 50-day simple moving average.
  • Hold the asset when the 20-day average is above the 50-day average.
  • Hold cash when the 20-day average is below or equal to the 50-day average.
  • Apply the signal on the following row.

The strategy itself is only an example. The useful part is turning every rule into a formula that another person could read and reproduce.

3. Calculate the Moving Averages

If the first data row is row 2, enter this formula in F21:

=AVERAGE(E2:E21)

This calculates the first 20-day moving average. Copy the formula down column F.

Enter this formula in G51:

=AVERAGE(E2:E51)

This calculates the first 50-day moving average. Copy the formula down column G.

The 50-day average needs more data than the 20-day average, so the first usable strategy signal appears only after both averages have values.

Excel includes AVERAGE, MAX, STDEV.S and SUMPRODUCT for calculations of this kind. Microsoft provides a reference for Excel functions and their arguments.

4. Convert the Moving Averages Into a Trading Signal

In H2, enter:

=IF(OR(F2="",G2=""),"",--(F2>G2))

Copy the formula down column H.

The formula returns:

  • 1 when the 20-day moving average is above the 50-day moving average.
  • 0 when the 20-day moving average is below or equal to the 50-day moving average.
  • A blank cell when there is not enough data.

Column H is the signal calculated from information available at that row's close.

5. Delay the Signal to Prevent Look-Ahead Bias

Do not use the signal from row 51 to claim that the position was already active during row 51. Apply that signal to the next row.

In I52, enter:

=H51

Copy the formula down column I.

The relationship is:

Position on day t + 1 = Signal calculated on day t

This delay keeps the test from using information before the strategy could have acted on it.

6. Calculate the Strategy Return

This example uses a close-to-close return model. In J52, enter:

=I52*(E52/E51-1)

Copy the formula down.

The calculation is:

Position × percentage change in closing price

When the position is 1, the strategy receives the daily price return. When the position is 0, the return is zero.

This model assumes that the position from the previous signal remains active through the next close-to-close period. It is useful for learning, but it does not model every order or intraday event.

Model Next-Open Execution

If the signal is generated at the close and the trade begins at the next day's open, use an open-based return model.

For a trade that enters at the next open and exits at that day's close, the gross return in J52 would be:

=I52*(E52/B52-1)

This represents:

Next-day close ÷ next-day open - 1

If the strategy holds overnight or uses stop-losses, limit orders or intraday prices, build those rules into a separate execution model. Do not replace the execution price with the closing price unless that reflects the actual strategy.

7. Include Commission, Spread and Slippage

Create an input area in columns Q and R:

Cell Input
Q1 Starting capital
R1 10000
Q2 Cost per position change
R2 0.0005
Q3 Annualisation factor
R3 252

The value 0.0005 represents an illustrative cost of 0.05%. Replace it with an estimate based on the strategy's commission, bid-ask spread and expected slippage.

In K52, enter:

=ABS(I52-N(I51))*$R$2

Copy the formula down.

This charges a cost when the position changes:

  • 0 to 1: entry cost
  • 1 to 0: exit cost
  • 1 to 1: no new position-change cost
  • 0 to 0: no cost

Costs matter most when the strategy trades often. Run the test with several cost assumptions instead of relying on one optimistic estimate.

8. Calculate Net Return and Equity

In L52, calculate the net return:

=J52-K52

Set the initial equity in M51:

=$R$1

In M52, calculate the new equity:

=M51*(1+L52)

Copy the formula down.

The equity curve compounds the strategy's net returns after estimated trading costs.

9. Calculate Peak Equity and Drawdown

In N51, enter:

=M51

In N52, enter:

=MAX(N51,M52)

Copy the formula down.

In O52, calculate drawdown:

=M52/N52-1

Copy the formula down.

Drawdown is negative when equity falls below its previous high. The maximum drawdown is the lowest value in column O.

10. Calculate the Main Backtest Metrics

Assume the last data row is 1000.

Total Return

=M1000/$R$1-1

This measures the total percentage gain or loss.

CAGR

=(M1000/$R$1)^(365/(A1000-A51))-1

This annualises the return using the number of calendar days between the first and last dates.

Annualised Volatility

=STDEV.S(L52:L1000)*SQRT($R$3)

STDEV.S estimates the standard deviation of the return series. Microsoft documents STDEV and related standard-deviation functions for this type of calculation.

Sharpe Ratio

For a basic version with a zero risk-free rate:

=AVERAGE(L52:L1000)/STDEV.S(L52:L1000)*SQRT($R$3)

A fuller calculation subtracts the relevant risk-free return from each strategy return before calculating the average and standard deviation.

Maximum Drawdown

=MIN(O52:O1000)

Profit Factor

=SUMIF(L52:L1000,">0",L52:L1000)/ABS(SUMIF(L52:L1000,"<0",L52:L1000))

This calculates profit factor from daily returns. A trade-level profit factor requires a separate log containing the profit or loss from each completed trade.

Percentage of Time Invested

=AVERAGE(I52:I1000)

For a long-only strategy, this estimates the percentage of periods spent in the market.

11. Compare the Strategy With a Buy-and-Hold Benchmark

A strategy can make money and still underperform a simple buy-and-hold investment.

Add a benchmark return column using:

=E52/E51-1

Then calculate a benchmark equity curve:

=Previous benchmark equity*(1+benchmark return)

Compare the strategy and benchmark on:

  • Total return
  • CAGR
  • Maximum drawdown
  • Volatility
  • Time invested
  • Performance after costs

A trend-following strategy may produce a lower total return than buy-and-hold while reducing drawdown. The comparison should make that trade-off visible.

12. Test the Backtest for Errors

Review the spreadsheet manually before relying on its results.

Check for Look-Ahead Bias

Confirm that:

  • The signal uses only data available by the signal date.
  • The position is delayed by at least one row.
  • No future high, low or close appears in an earlier formula.
  • Indicators do not include prices from beyond the date on which the signal is generated.

Test Different Market Periods

Separate the data into:

  • An in-sample period for developing the rules.
  • An out-of-sample period for testing the fixed rules.

Do not keep changing the rules after seeing the out-of-sample result. Once you use that result to guide new decisions, the period is no longer a clean test.

Test Costs and Parameter Sensitivity

Change:

  • The moving-average periods.
  • The assumed transaction cost.
  • The test start and end dates.
  • The execution price.
  • The market or asset being tested.

If the strategy works only with one exact parameter combination or a very low cost assumption, the result may be fitted to historical noise.

Common Excel Backtesting Mistakes

Trading at the Price That Created the Signal

This is the most common error. When the signal uses the closing price, delay the trade unless the execution rule supports a closing fill.

Ignoring Transaction Costs

Gross returns can look attractive while net returns turn negative after commission, spread and slippage.

Using Too Little Data

A test covering only one favourable market phase says little about how the rules behave elsewhere.

Confusing Daily Win Rate With Trade Win Rate

The percentage of profitable days is not the same as the percentage of profitable trades. A trade can remain open for several days, so use a separate trade log when trade-level statistics matter.

Optimising Until the Equity Curve Looks Perfect

Testing many parameter combinations raises the chance of selecting a result that benefited from historical randomness rather than a repeatable rule.

When Excel Is Suitable, and When It Is Not

Excel is suitable for:

  • Daily or weekly strategies.
  • One or a few assets.
  • Moving-average, breakout and momentum rules.
  • Learning how signals, positions and returns work.
  • Reviewing each calculation manually.

Use a dedicated backtesting platform or programming language when you need:

  • Tick or high-frequency data.
  • Thousands of instruments.
  • Options, futures margin or portfolio-level risk.
  • Complex order types.
  • Intrabar stop-loss and take-profit logic.
  • Walk-forward optimisation or large parameter searches.

What a Credible Excel Backtest Should Show

A useful backtest should report:

  1. The exact entry and exit rules.
  2. The historical data period and market.
  3. The execution-price assumption.
  4. Commission, spread and slippage assumptions.
  5. Total return and CAGR.
  6. Maximum drawdown.
  7. Volatility and risk-adjusted performance.
  8. A comparison with buy-and-hold.
  9. In-sample and out-of-sample results.
  10. The full trade or daily-return ledger.

Backtested performance is hypothetical. SEC materials warn that backtests can benefit from hindsight and that hypothetical performance does not guarantee future results.