Joined: 26 Nov 2002
Location: Crestview, Florida
|Posted: Sat Jan 29, 2005 9:01 am Post subject: The Internal Timing of our Calculators
|The Internal Timing of our Calculators
This documents the where and when of time-related calculations in our calculators.
This is as much for me than for others, if not more so. I misplaced the entry of Gummy's data on my first try. Getting everything right is a tedious process.
Rows 148 and 197 list the years, starting with 1871 in cells B148 and B197 and ending in 2010 in cells EK148 and EK197.
These rows are conveniently located. Historical data are listed between them.
Row 177 has the interest rates of commercial paper. B177 is the interest rate for 1871. C177 is the interest rate for 1872. And so on.
Row 184 lists (nominal) stock prices from Professor Shiller's database. January is used with the default setting. Typically, B184 has the January 1871 (nominal) stock price that Professor Shiller's lists for 1871.01. C184 has the January 1872 stock price and so forth.
Row 185 lists stock (nominal) dividend amounts. You use them along with the current stock price to calculate the current dividend yield. The dividend yield times the balance equals the dividend amount.
Row 186 lists Professor's P/E10. [Professor Shiller has not provided values for 1871 through 1880, but there are dummy values in our calculators.] Cell B186 corresponds to P/E10 for January 1871. Cell C186 corresponds to P/E10 for January 1872 and so on.
Row 188 lists CPI values. Row 189 lists PPI values. One of these is selected, usually the CPI, and placed in row 190. Row 190 has the index values of the selected inflation series. Cell B190 has the index value for January 1871. Cell C190 has the index value for January 1872. And so forth.
Row 187 shows the percentage interest rate relative to the previous year. There is no entry in column B. Cell C187 shows the inflation that took place during 1871. It is located in column C, which corresponds to January 1872. The formula is =C190/B190-1. If you multiply this entry in cell C187 by an amount from January 1871, which is in column B, you determine its inflated value in January 1872.
TIPS and I-Bonds are in rows 180 and 181 respectively. Their entries in column B are set equal to what is calculated in column C. The actual formulas start in column C. The TIPS formula in column C180 is =(($H$8*$B$23*(C190/B190)+(C190/B190-1))*100). Notice the ratio (C190/B190) for the inflation term, actually for 1+inflation. It is lined up the same way as the inflation rate formula in row 187. That is, the calculations in cells C180 and C181 show what happened during 1871. It is listed in column C, which corresponds to January 1872.
The 1871 Sequence
Now let us drop down to rows 199 through 215. These are the calculations for the 1871 historical sequence. These calculations extend for 60 years total.
The starting balance for the 1871 historical sequence is listed in cell B215. This is what we would list as being the balance in year N = 0. The balance in cell C215 is what we would identify as being the balance in year N = 1. Column C is for 1872, as shown in cells C148 and C197.
Notice that what happens during 1871 is listed in column C above row 215 (and cell C215).
Cell C203 lists the stock allocation that takes place at the beginning of 1871. Cell C204 is the price increase (or decrease) that takes place in 1871. The formula for cell C204 is =((C$184-B$184)/B$184)*C203. Notice the references to B$184. [This is the value in cell B184. The dollar sign is a special programming feature. It fixes the programming reference row at 184. Normally, the code uses relative locations.] Cell B184 is the 1871 stock price. This calculation determines how much the price has changed during the year 1871 and multiplies it by the stock balance in cell C203, which is the stock allocation for 1871.
Similarly, the dividend amount is calculated in cell C205. The formula is =C$185/C$184*C203. The ratio (C$185/C$184) is the dividend yield in January 1872. A current dividend yield times today's price tells you the dividend amount that you have received in the past year. The stock balance from January 1871 times January 1872 dividend yield tells us the dividend amount that was paid during the year 1871.
The bond interest paid in cell C209 is very similar to the dividend yield. The formula is =C208*C$183/100, which is the bond balance from January 1871 times the current interest rate.
This key timing relationship is worth repeating: what happens during 1871 is listed in column C above row 215 (and cell C215). Cell C215 is the balance at the end of the first year, N = 1. It is the balance in January 1872.
Switching stock allocations takes place as a Fixed Income calculation. That is, you set the stock allocation to zero and select Fixed Income as type 6. The selection logic is in row 183, which identifies the FI Interest Rate. When using type 6, the logic in cell C183 selects the value in cell C182.
Row 182 is labeled Stock Switch by P/E.
Cell C182 calculates =IF($B$19<B186,IF($F$19<B186,C2552,+(C2553*(1-$B$20)+$B$20*C2550)),C2550). The references to cells C2550, C2552 and C2553 are for intermediate calculations. They are needed because of programming limitations imposed by the software. These intermediate calculations produce the total return of the combined portfolio using all programmed allocations.
The important reference in cell C182 is to cell B186. B186 has the 1871 value of P/E10.
That is, the switching logic in column C compares the 1871 (or column B) P/E10 to various thresholds.
The Fixed Income calculation for the 1871 sequence is in cell C209, Interest Paid. The formula is =C208*C$183/100, where C208 is the balance of the Fixed Income type. With switching, you set the stock allocation to zero percent, which puts the entire balance into cell C208. Cell C183 (or C$183) has the selection logic, which refers to cell C182, which is total return of the combined portfolio for the selected allocation. Critical to this is the timing reference. The value of P/E10 is in cell B186 and column B has the January 1871 value.
Introducing Annual Return Data
The calculator was written to use prices, dividend amounts and interest rates. It was not written for using a combined total return.
When we introduce data in the form of a combined total return for a year, we must shift it over by one column. That is, if we had a total return for the year 1871, we would have to place it in column C instead of column B. Remember that the column C calculations above row 215 refer to what happens in 1871, not 1872.
When we superimpose total return data on the commercial paper in row 177, we make the one-year shift to the right (relative to what is shown on rows 148 and 197) and paste the new data on top.
The years listed in rows 148 and 197 apply to the last line in a set of calculations. All of the calculations above that line refer back to the previous year.
When we superimpose new total return data on the stock data, we freeze prices and modify dividend entries.
With total return data for years 1928-2000, we keep all of the price data up to and including what is shown (on rows 148 and 197) for 1928. Remember that the 1928 price corresponds to January 1928, not later in the year. The January 1928 price is needed when calculating price gains that take place during 1927. We use Paste Special (putting a radio button for Values) to paste the January 1928 price into the cell for 1929. We use the fill handle to drag this price through the end of the row (to cell EK184).
By freezing prices we force all of the price gains to be zero. That is, when we freeze prices, only dividend row entries contribute to stock market returns.
We multiply all of the total return data by this same (frozen) price. That is, if the total return data are for 1928-2000, we multiply each entry by the January 1928 price and then paste the results as dividend amounts in row 185 for the years 1929-2001. [As before, entries before the last row in the calculations within a historical sequence refer to what happened during the prior year.] I used the fill handle to make dummy data. I dragged the final entry from 2001 to 2010.
This is reference material for anyone who needs to look into the computation details of our calculators. Our calculators are modified versions of the Retire Early Safe Withdrawal Calculator, Version 1.61 dated November 7, 2002.