Understanding the Mathematics of Personal Finance

CALCULATORS

The spreadsheet Ch13Stocks. xls will do correlation calculations and produces some numbers that I haven’t introduced yet. To use this spreadsheet, you must enter some stock prices. You can do this by typing them in (I’ll go through an example below) or if you’re proficient enough with spreadsheet operations, download them from the Web right into the spreadsheet. An important point is that you must enter stock prices that are uniformly distributed in date. The closing price every Friday afternoon for a year, for example, is a good data set. Entering daily information for the month of January and then end-of-month information for the rest of the year won’t yield any meaningful results. The spreadsheet has no way of knowing whether or not you’re feeding it “good” data—it will try to do its thing with whatever you type in. Ch13Stocks. xls is deceptively simple-looking—there are a lot of calculations going on behind the scene.

The Correlation tab in Ch13Stocks. xls lets you look at two sets of stock prices and see how well they are or are not correlated. Table 13.1 shows a few examples of the use of this spreadsheet. These examples use a few data points—my purpose here is to show the use of the spreadsheet.

Examples I and II are the same for this spreadsheet. Order of data entry doesn’t matter. You enter data to the left of the green line, as many data points as you wish, up to 1,000. If you don ’ t enter the same number of points for both stocks, the message, “Please enter the same number of prices for both stocks” appears at the top of the spreadsheet.

The spreadsheet first reports the number of prices entered. Next, it shows the slope of the best-fit line, as in the example above. In this case, the slope is positive, indicating a positive correlation between the two data sets. Next, the spreadsheet reports the correlation coefficient. This coefficient will be positive if the slope is positive, and negative if the slope is negative.

As example III shows, the slope can be any number at all while the correlation coefficient can only vary between -1 and +1. In this instance, there is a fairly good correlation between the two data sets (+0.645), and the bestfit line predicts that stock #2, on the average, varied twice as much as stock #1. Goodness of fit is the

square of the correlation coefficient; hence, it’s a number that varies between 0 and 1. If the correlation coefficient is 0, the goodness of fit is 0 and the two data sets appear to be totally independent of each other. If the correlation coefficient is -1 or +1, the goodness of fit is +1, indicating that there is excellent correlation between the two data sets. The sign of the correlation coefficient indicates negative or positive correlation. There was no need to show goodness of fit here, but it so often accom­panies the fitting of a line to data that I thought I should include it and explain what it is.

Finally, example IV shows a set of data with a negative correlation coefficient (-0.315). The goodness of fit is only 0.100. These data are somewhat correlated; the points don’t seem to “line up” at all (try graphing them).

All of the examples show the mean (average) of both stock prices, followed by their standard deviation. The standard deviations can be misleading because a $100 stock can have a higher standard deviation than a $2 stock while on a percent change basis not be varying as much. This is why I then show the standard deviations divided by (normalized to) the mean. These normalized standardized deviations are a measure of the comparative volatility of the two stocks.