Financial Analysis and Modeling Using Excel and VBA

Chapter 16. Analyzing Market History

In investing, we often want to measure and show the performance of various asset classes both over time and relative to each other and compare their risks. In this chapter, we develop a number of models to do such analysis and plot the results in a few different types of charts commonly used to display them.

Review of Theory and Concepts

INDEXES

The historical returns for various asset classes such as stocks and bonds are generally measured using indexes. For example, historical returns on stocks are most often measured using the S&P 500 index, which represents the average price of (approximately) the 500 largest U.S. stocks. An index represents the average for its components; we use it to talk about what happened to the components on average.

In the S&P 500 index, stocks are not weighted equally; rather, they ate weighted by their market values. The effect is that a 10% change in the price of the stock of a larger company has a larger impact on the index vis- à -vis a 10% change in the price of the stock of a smaller company. Another way to visualize this is to think of the S&P 500 as the value of a portfolio of 500 stocks, in which the money is distributed among the different stocks in proportion to their market values and not equally.

Because the S&P 500 index is based on approximately the 500 largest stocks and is further weighted by the market values of the component stocks, it primarily represents the performance of the stocks of the very ...