 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

Table 13.1 Examples of Ch13Stocks. xls, Correlation Tab

 Example I: Stock prices: Stock 1 Stock 2 Number of prices entered: 3 10.00 12.00 Slope of best-fit line: 1.000 11.00 10.00 Correlation coefficient: 0.500 12.00 14.00 Goodness of fit: 0.250 Mean 1: 11.00 Mean 2: 12.00 Standard deviation 1: 1.00 Standard deviation 2: 2.00 (Standard deviation 1)/(Mean 1): 9.09% (Standard deviation 2)/(Mean 2): 16.67% Example II: Stock prices: Stock 1 Stock 2 Number of prices entered: 3 10.00 10.00 Slope of best - fit line: 1.000 11.00 14.00 Correlation coefficient: 0.500 12.00 12.00 Goodness of fit: 0.250 Mean 1: 11.00 Mean 2: 12.00 Standard deviation 1: 1.00 Standard deviation 2: 2.00 (Standard deviation 1)/(Mean 1): 9.09% (Standard deviation 2)/(Mean 2): 16.67% Example III: Stock prices: Stock 1 Stock 2 Number of prices entered: 3 10.00 12.00 Slope of best-fit line: 2.000 12.00 16.00 Correlation coefficient: 0.645 11.00 9.9 Goodness of fit: 0.417 Mean 1: 11.00 Mean 2: 12.63 Standard deviation 1: 1.00 Standard deviation 2: 3.10 (Standard deviation 1)/(Mean 1): 9.09% (Standard deviation 2)/(Mean 2): 24.53% Example IV: Stock prices: Stock 1 Stock 2 Number of prices entered: 7 10.00 10.00 Slope of best-fit line: -0.393 11.00 11.00 Correlation coefficient: -0.315 12.00 6.00 Goodness of fit: 0.100 13.00 3.00 Mean 1: 13.00 14.00 8.00 Mean 2: 7.71 15.00 9.00 Standard deviation 1: 2.16 16.00 7.00 Standard deviation 2: 2.69 (Standard deviation 1)/(Mean 1): 16.62% (Standard deviation 2)/(Mean 2): 34.88%

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.

Добавить комментарий

## Understanding the Mathematics of Personal Finance

### PAYING OFF A LOAN VERY SLOWLY

This section uses a little math, but I’ll go through it slowly in small steps. As with the previous section, this section is not necessary if you don’t want to …

### Taxation and Inflation

John Lennon once said, “Life is what happens to you while you’re busy making other plans.” Both taxation and inflation, it seems, are parts of life—they happen to you while …

### PARI-MUTUEL BETTING

The pari-mutuel machine system used at race tracks offers a different approach to gambling. A roulette wheel owner is never sure what his or her daily operating cost will be; …

## Как с нами связаться:

Украина:
г.Александрия
тел./факс +38 05235  77193 Бухгалтерия
+38 050 512 11 94 — гл. инженер-менеджер (продажи всего оборудования)

+38 050 457 13 30 — Рашид - продажи новинок
e-mail: msd@msd.com.ua
Схема проезда к производственному офису:
Схема проезда к МСД

Партнеры МСД

## Контакты для заказов шлакоблочного оборудования:

+38 096 992 9559 Инна (вайбер, вацап, телеграм)
Эл. почта: inna@msd.com.ua

За услуги или товары возможен прием платежей Онпай: Платежи ОнПай