**MacDuff**: I have not figured out how to make your spreadsheet yet. I have done something similar to what you have asked for. This is not complete. Consider this to be a progress report.

First, I copied Dr. Shiller's spreadsheet from:

http://www.econ.yale.edu/~shiller/data.htm
Then I put a shortcut to it on my desktop.

Then I opened up a new spreadsheet and called it Shiller Modified Dec 2003 data.

That basic copy runs to row 1603. I selected the entire spreadsheet. (I used CTRL+A, but pressing the gray square in the position that corresponds to A0 does the same thing.) Then I copied it. (Click on Edit and then Copy.)

I pressed F5 (which gives me a Go To box), wrote a2000 and pressed enter. (You may write A2000 if you prefer.) I highlighted box A2000 and pasted another copy starting at row 2000 and worked from there. It wasn't really necessary. I just wanted to be able to have an easy way to redo everything if I really screwed up.

Function key F5 is very useful. I use CTRL+Home to return to A1. You could use F5 and enter a1 (or A1) just as easily.

I placed three columns new to the left of the S&P Comp. Price. I labeled them Gain Multiplier Price Only in column B, Gain Multiplier Reinvested D in column C and Price when Reinvested S&P 500 in column D. To insert a column, highlight a cell in a column just right of where you want the new one to appear. Use Insert and then Columns in Excel.

I placed three columns to the left of the (S&P 500 Real Price, which was then in column J and is now in column M). I labeled them Real Gain Multiplier Price Only for column J, Real Gain Multiplier Reinvested D for column K and Real Price when Reinvested for column L.

For my calculations, I was only interested in annual returns, not quarterly returns. This is one way that our calculators will differ.

I copied the values in E2004 through E2015 to D2004 and D2015. This starts the Price when reinvested equal to the S&P 500 Composite prices throughout the first year (i.e., 1871). I copied the values in M2004 through M2015 to L2004 through L2015. That makes the real price when reinvested equal to the real S&P 500 price (or index value) during the first year (i.e., 1871). I had trouble at this step. Excel wanted to copy the formula (to adjust for inflation) and I wanted to copy only the data. I think that I ended up using Paste Special and selecting Values (instead of All) in the special box that appeared. I am not sure.

I highlighted cell B2016 and wrote this formula:

=E2016/E2004

I highlighted cell C2016 and wrote this formula:

=D2016/D2004

I highlighted cell D2016 and wrote this formula:

=((D2004)*(E2016/E2004)*(1+(F2016/E2016)))

I highlighted cell J2016 and wrote this formula:

=M2016/M2004

I have corrected this formula. I had originally typed M2014 instead of M2004.

I highlighted cell K2016 and wrote this formula:

=L2016/L2004

I highlighted cell L2016 and wrote this formula:

=((M2004)*(M2016/M2004)*(1+(F2016/E2016)))

The most difficult step in doing this was to remember to include an equals sign in the formulas.

Notice that in calculating the Real Price when Reinvested (i.e., reinvesting dividends), I used the dividend yield as determined from columns E and F. Any inflation adjustment would affect both the dividends and the price equally. Using data from columns N and O might have introduced possible round-off error (although it is likely to be insignificant).

The final step is to highlight each relevant starting cell in row 2016 (i.e., cells B2016, C2016, D2016, J2016, K2016 and L2016). Locate the fill handle on the lower right hand corner, click down (but do not release) on your mouse and fill in the cells below. You should do this is several steps. When you release the mouse button, you will see a highlighted area. Click on an empty cell. All of the numbers in the highlighted area will be correct. Repeat until you get to the bottom of the data. Repeat for each one of the new columns.

I have not checked out my formulas. They should give me the price increases relative to the year before. Gain multipliers equal the ratio of two prices and they equal (1+r), where r is the return (or interest) expressed as a decimal fraction for the period covered. To get the percentage return, subtract 1 and then multiply by 100%. When reinvesting, I used the current dividend yield and apply it to the current total (or price) in the reinvested dividends column. The balance with dividends reinvested differs from the price by itself. The dividend yield applies to the current balance.

I have not found an easy way to thin the table in Excel. I know that it can be done. I just don't know how to do it myself.

If I am not mistaken, the dividends and earnings numbers are for the entire preceding year. There might be a problem with quarterly returns, depending upon what you are trying to do.

This is definitely a work in progress. For example, it is unnecessary for me to save the original 1999 rows of the data. Think of those rows as my security blanket.

Have fun.

John R.