I often get asked how to validate an investment idea without paying for fancy software or hiring a quant team. One of my favorite ways to do that is to backtest a straightforward momentum strategy in Excel using free data sources. It’s practical, transparent, and — crucially — reproducible. In this article I’ll walk you through the exact steps I use, including where to get data, how to structure your spreadsheet, the formulas to calculate signals and returns, and simple metrics to evaluate performance.
What I mean by a “simple momentum strategy”
When I say momentum, I’m referring to a rule that favors assets that have performed well over a recent lookback period. For this example we'll implement a long-only, monthly-rebalanced strategy that selects the top 3 out of 10 assets based on their 12-month total return (skip the most recent month to avoid short-term reversal). That’s a common, intuitive approach used in many academic papers and practitioner portfolios.
Free data sources I use
Here are reliable, free places I pull historical prices from:
For this tutorial I assume you downloaded monthly adjusted close prices from Yahoo Finance for each ticker (or used Stooq if you need older data). Save each asset’s price series into a single Excel sheet, aligned by date.
Spreadsheet structure I use
I like a clean layout so the logic is obvious and auditable. Here’s a minimal layout:
| Column A | Date (month-end) |
| Column B..K | Adjusted Close for Asset 1..10 |
| Column L..U | 12-month momentum scores (calculated) |
| Column V | Rank / Top-n flags |
| Column W | Portfolio weight per asset |
| Column X | Monthly portfolio return |
For clarity, label each asset column with ticker and source date. Freeze the top row so headings stay visible as you scroll.
Step 1 — Calculate monthly returns
Convert adjusted closes into monthly returns. If your prices are on a monthly frequency (month-end), use this formula in row 2 (assuming prices start in row 2):
=(B3/B2)-1
Drag across all asset columns and down. Keep one sheet with raw prices and a separate sheet with returns — I find that separation reduces mistakes.
Step 2 — Compute the 12-month momentum signal
We’ll calculate a 12-month cumulative return skipping the most recent month. That is, momentum = price(t-1) / price(t-13) - 1. If column B is prices for Asset A and row i is the current month:
=B(i-1)/B(i-13)-1
Put these momentum values in dedicated columns. If you prefer returns rather than cumulative price change, you can use the product of (1 + monthly return) over months t-12 to t-2 minus 1, but the simpler price ratio is fine for month-end data.
Step 3 — Rank and select top assets
Each rebalance date (monthly here), rank the assets by momentum descending and select the top 3. In Excel you can use RANK.EQ (or RANK) to get ranks. For example, assuming momentum values are in L2:L11 for a date:
=RANK.EQ(L2,$L$2:$L$11,0)
Create a flag column: if rank <= 3 then 1 else 0. That flag becomes your long position indicator.
Step 4 — Assign weights
I prefer equal-weighting across chosen assets. If you pick 3 assets, each gets 1/3 of the portfolio on the next day’s open (or month close depending on data). In Excel, compute weight as:
=IF(flag=1,1/COUNTIF(flag_range,1),0)
Put weights for each asset in the weight columns. This approach implicitly assumes you can hold fractional shares and that portfolio rebalancing occurs at the monthly bar.
Step 5 — Compute portfolio returns including transaction costs
Monthly portfolio return is the weighted sum of asset returns for the month. If returns are in row i and weights in row i (weights determined at previous month-end), then:
=SUMPRODUCT(return_range,weight_range)
Transaction costs: apply a round-trip cost per turnover event. One practical way is to estimate turnover as the sum of absolute difference in weights versus previous month divided by 2 (approximate traded value). Then cost = turnover * cost_per_unit. For example, with 50 bps round-trip cost (0.005):
=turnover * 0.005
Subtract that cost from the gross portfolio return.
Step 6 — Performance metrics I calculate in Excel
Once you have a monthly series of portfolio returns, compute:
These formulas are standard and easy to implement in Excel. I also like to chart the equity curve and rolling 12-month return to visualize stability.
Simple example table (skeleton)
| Date | TICK1 Price | TICK1 Mom | TICK1 Flag | TICK1 Weight | Portfolio Return |
| 2020-01-31 | 100 | 0.12 | 1 | 0.33 | — |
| 2020-02-29 | 102 | 0.10 | 1 | 0.33 | 0.014 |
Practical checks and robustness tests I always run
Backtests can be misleading if you don’t check a few things:
Common mistakes I avoid
When I teach this to colleagues, these errors come up repeatedly:
Extensions you can try
If you want to expand this simple framework, consider:
Backtesting a strategy in Excel is not glamorous, but it’s powerful. It forces you to be explicit about assumptions and makes debugging straightforward. For many investors and advisors, an Excel backtest is the best first step before committing capital or automating in a trading platform. If you want, I can export a template workbook skeleton (with example formulas and charts) you can use as a starting point — tell me which tickers you want to test and I’ll tailor it.
