NoFeeBoards.com! No Fees! No Ads! No Spam!

Author Message
JWR1945
***** Legend

Joined: 26 Nov 2002
Posts: 1697
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. Row References 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 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
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 9 Hours Page 1 of 1

 Jump to: Select a forum General----------------NFBTown CenterTest Posts Personal finance----------------FIRE BoardIndex Funds BoardSWR Research GroupLBYMGeneral FinanceNewbies Board Community----------------WorkspacePolitically CorrectYour HealthGummy Stuff
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum