In an effort to expand the amount of free historical stock market data available to the public, I’ve researched the inner workings of index funds and created a spreadsheet that reconstructs a variety of US stock indices from scratch all the way back to 1927. The data is available for anyone to download, and this page is intended to be a central hub for explanations and updates.
/// Stock Index Calculator Download ///
- How an index fund works
- How Fama-French data works
- Translating Fama-French data to index fund terms
- Calculation details
- Results & Comparisons
The Stock Index Calculator is based on source data from the Fama-French data library, and translates the data into returns that mirror a real-world index methodology. It does this by first reconstructing simulated markets for a given index using capitalization-based size and value filters, and then weighting the reported returns for each Fama-French factor-based portfolio based on the simulated market. The final results are not precise and are no substitute for actual data from a specific fund, but they track the general performance histories reasonably well and are suitable for general backtesting purposes independent of any specific index provider.
How An Index Fund Works
In its most general sense, an index fund tracks the performance of a given subset of the market with no active management involved. It sets a strict rules-based definition of “the market” it is following and builds its portfolio accordingly. Index providers that construct and track indices include organizations like CRSP, MSCI, and Russell. Companies like Vanguard purchase the index data from one of these companies and use it to build their funds. For example, VTSMX is currently based on the CRSP US total market index.
However, many investors might be surprised to learn that each index provider defines “the market” differently. For example, S&P defines large caps as the top 500 companies by market cap. The Russell 1000 Large Cap index tracks the largest 1000 companies. Rather than using company count, indices like CRSP define large cap as the top 85% of the market by capitalization. These differing definitions of “the market” mean that two sources claiming to track the same index may have different returns.
When you get to things like growth and value, it gets even more complicated. Each group defines these terms differently, and they all have proprietary formulas that use a wide variety of metrics (including manager discretion) to decide what classifies as value and growth. Where most do agree, however, is that the value and growth market is not split by company count but by market capitalization. By most definitions*, about half of the market is considered growth in any given year and the other half is considered value, regardless of how overvalued or undervalued the market is as a whole. “Blend” is simply the total market including both growth and value. Different indices also have different float band rules to prevent frequent turnover, but in general the 50/50 rule is a decent approximation.
(*) The notable exceptions are S&P and Morningstar. When you see “blend” in a Morningstar style box, those are the middle third of companies not considered growth nor value.
For the purposes of this exercise, I’ve chosen to use the CRSP percentage-based market definitions. I chose this because 1) this is the source that Vanguard currently uses, 2) it works better for older returns (defining size by company count breaks down when there weren’t that many companies available), and 3) it lends itself to the data we have to work with. CRSP defines large cap as the top 85% of the market by total capitalization, mid cap as 70-85%, and small cap as 85%-98%. Their definition of growth and value involves 11 different factors that we’ll never be able to match, but a reasonably decent proxy that we do have access to is book-to-market. Following with common index practice (or as close as we can accurately model with the given data), half the market is considered growth and half is value.
How Fama-French Data Works
The underlying source data is from the amazing data library maintained by Dr. Kenneth French, professor of finance at the Tuck School of Business at Dartmouth College. The data itself is pretty dense and academic, and notably is short on simple returns information. Even if you know where to look, the data that is supplied for things like large cap growth and small cap value has been noted to not track modern indices particularly well, so many people tend to look elsewhere for more useful historical information.
The thing is, the problem is not with the Fama-French data but with how it is interpreted. The methods that index funds use to construct an index are quite different from the research interests of Dr. French, but the underlying information is all there if one knows what to do with it.
The file we’re working with is called “100 Portfolios Formed on Size and Book-to-Market (10 x 10)”. I find that it’s helpful to picture the data as a grid:
Each horizontal bucket represents a size decile, with the largest companies in decile 1 and the smallest in decile 10. Each vertical bucket represents a valuation decile (defined simply by book-to-market) with the most growth companies in decile 1 and the most value companies in decile 10.
The important thing to understand here is that the two sets of deciles are populated in different ways. The size deciles are populated by total market capitalization, and are NOT distributed evenly. On average, decile 1 represents 64% of the total market, while decile 10 represents less than 1% of the market. These definitions are driven by the NYSE to maintain historical standards in the data. The valuation deciles aren’t distributed at all, and generally represent the simple B/M ratio in absolute terms. For example, at the height of the market crash in 2001, there was only ONE company in size decile 1 that qualified in any of the top three value deciles.
The Fama-French data file includes a ton of information sorted into this grid, but for our needs we’re going to use the “Average Value Weighted Returns”, “Sum of BE”, and “Sum of BE/Sum of ME”.
Translating Fama-French Data Into Index Fund Terms
We discussed earlier how CRSP defines both size and value in terms of total market capitalization. So to use the Fama-French data, we first have to scale it to the associated market of each square. The total market size data is not directly provided, but it can be easily calculated from what we have. We’re looking for Sum of ME, and:
Sum of ME = (Sum of BE)/(Sum of BE/Sum of ME)
Scaling the previous grid by market size of each individual square (using the historical average market sizes) gives us this:
Here you can see a few things. First, the top few deciles by size dominate the overall market. And second, larger companies tend to skew more growth than smaller companies. What isn’t immediately evident is that these proportions are not at all constant, and change every year. We’ll account for that in a moment, but for now it’s a lot easier to overlay CRSP definitions onto the grid.
The highlighted section represents Large Cap Growth, which touches 11 different Fama-French data points. Each breakpoint line can similarly be used to identify which data points correspond to a certain index.
The calculator addresses these various size and value factors by calculating five filters based on market capitalization: Large, Mid, Small, Value, and Growth. Lets start with the size factors.
For every cell of the Fama-French data (scaled for market size), the calculator determines if it falls in or out of the desired range for the index. If it is out, 0% is included. if it is in, 100% is included. If the CRSP breakpoint falls in the middle of a decile, an appropriate percentage is allocated to the modeled market. A full matrix is thus developed to translate market data by size, and the matrix changes every year as the market percentages of the deciles vary over time.
The value and growth filters are created similarly. For every year and every size decile, half of the market is classified as growth and half as value. When the 50% breakpoint falls in the middle of a decile, the full decile is allocated to the closest category. This is because while the distribution of market cap within a size decile is predictable, distribution within a value decile is unpredictable and trying to split it adds more error than it eliminates.
Note that valuation deciles can swing dramatically. In one year the single highest growth decile may cover 10% of the market and in another it may cover 50%. To be as accurate as possible, the distribution is recalculated every year. Regardless of absolute valuation score, the calculations assume (just like most index funds) that half the market is growth and half is value.
Once we have these filters, the final math is relatively straightforward and uses a matrix approach.
The desired filters are applied to the calculated Fama-French market size data to create a simulated market. The market is then weighted by percentage, and the resulting percentages are multiplied by the Fama-French return data to get weighted returns for each component of the index. Adding up all of the weighted returns gives the total return of the year.
Results & Comparisons
Here’s how the calculated index returns track known indices over the past 20 years. All index data is from the Bogleheads wiki here, here, and here. The red “PC” series represents the PortfolioCharts calculations. Click to enlarge.
The blend calculations are all pretty spot-on, indicating that the size calculations are sound. The value and growth numbers are generally pretty good, but they do vary a little due to the limited data we have to determine value. Still, all indices clearly have different returns and the reconstructed numbers generally fall within an acceptable range of variability and track the trends well enough for casual study.
Is the data perfect? Of course not! But the goal is not to provide a substitute for the precise institutional sources that I’m sure are well worth the price. For a free data series that fills in the gaps and greatly extends the history coverage of modern index funds for general backtesting purposes, I feel like it’s pretty reasonable.
The data carries absolutely no guarantee of accuracy. It is merely an approximation of past returns based on freely available sources, and it may contain errors.
The exact methods of real-world index funds are far more complicated than can be modeled here with the provided data. The goal is be reasonably realistic, not precise.
The numbers you find here may not accurately represent the performance of any individual index fund. Always thoroughly research any fund you purchase.
While I’ve put a lot of work into this and feel pretty good about the results, I want the numbers to be as accurate and helpful as possible and am very open to input. If anyone has any questions, comments, corrections, or recommended improvements, please don’t hesitate to contact me.