Using P/E10

This tells you how to use P/E10 with my calculators.

I use P/E10 to vary allocations. This is known as Switching (allocations). I also use P/E10 extensively in data analysis.

P/E10

Our calculators use January values of P/E10 from Professor Robert Shiller’s web site. They appear in row 186 as the S&P "Real" P/E Ratio. (I built my calculators starting from the Retire Early Safe Withdrawal Calculator. I do not know how John Greaney came up with values for 1871-1880.) Years are listed on rows 148 and 197.

Professor Shiller’s Web Site

P/E10 in Switching

When I vary allocations in accordance with P/E10, I select Fixed Income Series number 6. Cell B7 is 6. I set the standard Stock Allocation equal to 0%. Cell B6 is 0%.

I select among commercial paper, TIPS or ibonds as the Fixed Income component when switching. The selection goes into cell F22. If I select TIPS or ibonds, I put the interest rate into cell H8.

I put the switching allocations of stocks into row 20 and the thresholds into row 19. The stock allocations at the lowest and highest values of P/E10 are not shown. When P/E10 is less than the threshold in cell B19, the stock allocation is 100%. When P/E10 is greater than the threshold in cell I19, the stock allocation is 0%.

The stock allocation is 100% when P/E10 is less than the threshold shown in cell B19.

Cell B20 has the stock allocation when P/E10 is between the thresholds shown in cells B19 and F19.

Cell F20 has the stock allocation when P/E10 is between the thresholds shown in cells F19 and H19.

Cell I20 has the stock allocation when P/E10 is between the thresholds shown in cells H19 and I19.

The stock allocation is 0% when P/E10 is higher than the threshold shown in cell I19.

The detailed switching logic is in row 182, Stock Switch by P/E. To interpret this logic, read from left to right and start by assuming that each condition is FALSE. The first logical condition is that the lowest threshold is less than P/E10. To be FALSE, P/E10 must be less than or equal to the lowest threshold. [I have reversed the order of the threshold and P/E10.] The lowest threshold is in cell B19. The condition that applies is at the end of the IF statement. It is the value in row 2550, which is the total return (nominal) of stocks. The next increment considers whether the next to lowest threshold is less than P/E10. To be FALSE, P/E10 must be less than or equal to the next lowest threshold. The next to lowest threshold is in cell F19. And so on. You read the thresholds from left to right. The allocations occur when P/E10 is less than or equal to the threshold.

Notice that once a threshold is established, no threshold to its right can change what happens at lower levels of P/E10. If the threshold in B19 were 12 and if the threshold in F19 were 9, the stock allocation would be 100% whenever P/E10 is less than or equal to 12. F19 would have no influence. The allocation in cell B20 would be ignored.

P/E10 in Analysis

I frequently use the percentage earnings yield 100E10/P, which is 100/[P/E10], in analysis.

To construct a list of P/E10 and 100E10/P, follow these instructions.

Open a new spreadsheet.

Make a column of Years from 1871-2005. That is, write 1871 into cell A11 (or some other convenient cell). Write =A11+1 in cell A12. Click the check mark or press ENTER. Cell A12 should display the value 1872. Locate the fill handle. That is, move the mouse pointer over the lower right hand corner of cell A12. The wide plus sign will change to a narrow plus sign (or cross hairs). Press and hold down the left mouse button. Pull (drag) the mouse cursor down. The formula will follow, putting 1873 into cell A13, 1874 into cell A14 and so forth. When you have finished dragging the formula down, let up on the left mouse button. Then click the left mouse button anywhere, possibly at the last cell that you highlighted while dragging down the formula. You can carry out the click and drag process in several steps. You do not have to drag the date all of the way down to 2005 on your first try.

Highlight all of the cells on row 186 from column B (corresponding to year 1871) to column EF (corresponding to year 2005). Row 197 lists the years to make this easier. You can highlight the cells by clicking the mouse button on one cell (when there is a fat plus sign), always holding the mouse button down, and then moving the fat plus sign to new cells. When you have a lot of cells to highlight, as in this case, it is better to click the first cell B186 and then scroll to the right to cell EF186. Press the shift key and then click on cell EF186. This will highlight cells B186, EF186 and everything in between.

Next click the word Edit on top (Menu), then Copy. Bring up the new spreadsheet and click on the cell just to the right of year 1871. This would be cell B11. Scroll down to the cell corresponding with the year 2005, which would be B145, press shift and click on cell B145. Release the Shift key. Then click the word Edit, then Paste Special. When the new box appears, click the word TRANSPOSE. This converts a row (from left to right) into a column (from top to bottom). Many times, but not this time, you have to Paste Special and then select Values. Otherwise, you paste formulas. Formulas often depend upon (relative) cell locations.

Finally, calculate the percentage earnings yield. In row C11, write =100/B11. This divides 100 by the number in cell B11. The equals sign means that there is a calculation. Otherwise, the cell would display 100/B11 as written text, not as a number. There is never any actual calculation without an equals sign.

Use the fill handle to extend the calculation all the way down to year 2005.

Later, you will put numbers such as Historical Surviving Withdrawal Rates or portfolio balances into columns to the right of the percentage earnings yield. You can analyze results by making Charts.

First, highlight an area of interest. This might be Earning Yields in column C for the rows for years 1921-1980 (or 1923-1980). You might have balances at Years 10, 15 and 20 in columns D, E and F, respectively. Then from the Menu on top, select Insert, then Charts. Follow the instructions. (I make x-y scatter plots.) Right click selected data points to format data points, titles, axes and other chart features. Right click a data point and then select ADD TRENDLINE. When you do so, you will be able to select the type of curve to fit the data (usually, a linear curve). Pressing the OPTIONS tab (at the top of the ADD TRENDLINE dialogue box), you can click boxes to display equations and R-squared. This gives you enough information to analyze your results.

More Data Analysis Tips

I frequently use the box in rows 1-9, column L and M, when calculating Historical Surviving Withdrawal Rates. I also use the analysis area in columns BA through BG, rows 65 through 124 (for 1921-1980) or rows 15 through 124 (for 1871-1980). Typically, I am interested in 30-year Historical Surviving Withdrawal Rates (column BD). Typically, I increase withdrawal rates in increments of 0.1% and I record the value of each sequence’s first failure. I subtract 0.1% for the Historical Surviving Withdrawal Rate. Cell M6 tells me the number of failures, from which I learn the number of new failures. Column BD lets me know the years in which failures have occurred, from which I determine the years for which there are new failures.

Have fun.

John Walter Russell
January 16, 2006