Stock Option Advisory

Back Testing and Dollar Cost Averaging

OK, so you notice a statement regarding a potential investment that says, “our strategy has been back tested”, and wonder what does that mean. Very simply, back testing is using historical data to determine the results of an investment strategy had it actually been applied in the past.

The first item needed for back testing is a stock investment strategy, and the supply of stock investment strategies is infinite, buy-and hold, covered call investing, bear call spreads, bull put spreads, calendar calls, calendar leap spreads, collar spreads, iron condors, iron butterfly, dogs of the Dow, etc. The second requirement for back testing is historical data required for stock investing research; stock prices, index prices, option prices of interest, and depending on the strategy the historical data may be readily available for free or for a fee, but in some cases the necessary data may not be available at all, or the amount of data to process is unwieldy. The final necessity for back testing is a methodology for analyzing the data. In some cases the analysis can be performed with paper and pen, and in other cases it requires more complicated approaches, software, spreadsheet, etc.

The spreadsheet method has been selected for performing the back testing analysis for this article, as gaining access to spreadsheet software is generally not too difficult or expensive. Custom software and statistical software were declined for use in this analysis as developing custom software for back testing is not in the toolbox of the typical person and having the access and the skills to use sophisticated statistical software is also not something a typical person possesses.

For our strategy, suppose we wanted to know whether investing in the S&P 500 SPDR (Standard & Poor’s Depositary Receipt, ticker symbol SPY) would have had better results over the last 5 years with dollar cost averaging on dips or one single lump sum investment. For the dollar cost averaging strategy, we will invest in the S&P 500 SPDR only when it dips below its 20 day moving average by 4%.

Historical Data
The historical data for the S&P500 SPDR can be retrieved from the Yahoo! Finance website and downloaded to a local computer in csv (comma separated value) format which can then be opened in a spreadsheet program.

To do the analysis, the 20-day moving average will be needed. For this we will develop a new column and use the spreadsheet function “average(,)”. Then a column is created representing the 20-day moving average less 4%. The 20-day moving average less 4% is then compared to each day’s close to determine when to make an investment, and this column is summed to determine then number of times the S&P 500 SPDR drops 4% below its 20-day moving average. This signal is then used to determine when to make each new investment. A column is created to determine how many shares to purchase during each dip and is rounded to the nearest number of whole shares, assuming a purchase of whole shares. A $15 brokerage fee is assessed for each investment. In performing this analysis the price at each day’s close will be used for each purchase and the maximum investment will be $10,000.

Single Lump Investment
The single lump investment method did not fair too well. Five years ago on 8 December 2000, 74 shares of SPY could have been purchased for $133.97 and with a brokerage fee of $15, the total investment would have been $9,928.78. Five years later on 7 December 2005 the investment would be worth $9,329.92 (74 shares @ $126.08/share) representing an investment loss of 6%.

Dollar Cost Average on Dip
The dollar cost average on 4% dips method faired much better than the single lump investment method. For this method, an investment of approximately 3% of the $10,000 will be made on each 4% dip. The first 4% dip is observed on 20 December 2000 in which two shares are purchased at $126.25 for an initial investment of $267.50 including a $15 brokerage fee. The last investment for this method is on 10 May 2004 for three shares at $108.83/share for a final investment of $341.49, with a total cumulative purchase of 88 shares and a total investment of $9670.29. On 7 December 2005, the value of the investment using the dollar cost on 4% dip methodology is now worth $11,095.04, representing a gain on the investment of 14.7%.

20/20 Hindsight
With 20/20 hindsight we can determine a best-case scenario investment by choosing to invest when the S&P 500 SPDR is at its lowest value during the previous 5 years. The lowest close price for the S&P 500 SPDR over the previous five years was encountered on 9 October 2002 with a closing price of $78.1. A purchase of 127 shares with a $15 brokerage fee represents a total investment of $9933.70. On 7 December 2005, this investment would have been worth $16,012.16 representing an investment gain of 61%.

So What?
Of course past performance is not a predictor of future performance, however, back testing can be used as an aid in analyzing and comparing various investment strategies. In addition, once a particular analysis has been developed, it can be easily modified. For example, the dollar cost average on dip method could be analyzed very easily for a 3% dip instead of a 4% dip.

The spreadsheet used for this analysis may be downloaded at:

A free Excel viewer may be downloaded from Microsoft’s web site to view it at (search for Excel viewer).

[tags]back testing, investment strategy, covered call investing, stock investing research, dollar cost average[/tags]


  1. Josh

    Do you guys know of any resources on how to backtest options strategies like verticals, collars, condors, etc?

    1. Josh

      Using spreadsheets/Excel, I should’ve specified.

      1. admin Post author

        Hello Josh! We do offer backtesting tools on the PowerOptions tools. Investors can run historical Searches on any of the 23 strategies we support on PowerOptions, including verticals, collars, iron condors and many more. You can see a brief discussion of our historical suite of tools here:

        In addition to that we also recently released two new services to help streamline historical testing: Strategy Testing Service and Search Strategy Optimization. You can read about those services here:

        Strategy Testing Service:

        Search Strategy Optimization:

        Thanks for your question!

Leave a Reply

Your email address will not be published. Required fields are marked *