Calculator Description

I built my Deluxe Calculator V1.1A08a as a series of modifications of the Retire Early Safe Withdrawal Calculator Version 1.61, dated November 7, 2002.

Basic Inputs

The calculator uses historical sequences of investment returns. It determines what would have happened with retirement portfolios throughout the past.

Many inputs are self-explanatory.

The calculator constructs historical sequences of 60 years. When you select shorter periods, your choice only affects data summaries.

The stock allocation does not apply when varying allocations in accordance with P/E10 (stock switching). When you select the switching model, the model treats all of its holdings as a special type of Fixed Income investment. The correct stock allocation in box B6 is 0% when switching.

[As an aside, if you put a different stock allocation into cell B6, the calculator treats your portfolio as consisting of stocks as in cell B6 and the special fixed income series. For example, it you were to put 60% as a stock allocation into cell B6 and select switching by entering 6 in cell B7, your portfolio would have a stock allocation of 60% plus 40% of whatever fraction of the stock switching model assigns to stocks. It would have 40% of whatever the switching model assigns as its non-stock component.]

The Fixed Income Series includes 5-year and 30-year Treasury Bonds. They were not always available in the past. The model assigns an interest rate of zero percent (as its default value) for those years that the Treasury did issued no bonds.

The Initial Withdrawal Rate is a percentage of the portfolio's initial balance. We normally adjust withdrawals to match inflation. You can choose not to do so.

We normally maintain a fixed allocation. We normally rebalance portfolio allocations each year. You can choose not to do so. When using the switching model, the model assigns a new allocation internally each year, independent of the contents of cell B16.

Investment expenses are percentages of a portfolio's current balance. They rise and fall according to the current balance, not inflation. The typical default level is 0.20%. These days, you can buy index funds with even lower expenses.

You can use investment expenses to examine what would happen if you were to withdrawal a constant percentage of your portfolio's current balance every year. Such an approach guarantees that a portfolio's balance never falls to zero. However, the balance can fall to a very low level. [Be careful about regular expenses. There is no separate place to enter them. I usually set them at zero. I gather data with zero expenses with my other conditions as well in order to make comparisons.]

You can combine withdrawals based upon your portfolio's initial balance and its current balance.

I normally leave the Front-End/Back-End selection at its 50% default setting.

I normally leave the Shiller Data at its default selection of January. The Shiller Data selection choices apply to S&P500 nominal prices and dividends. The model uses January data for inflation adjustments and P/E10 levels.

Remember to fill in cell H9 when using TIPS or ibonds. The calculator uses a single coupon rate. It applies in all of the years of a sequence. It treats TIPS and ibonds as single-year trading instruments without any expenses, capital gains and/or capital losses. It adjusts principal for a single year of inflation. That is, it does not make any final adjustment to principal at maturity (as with TIPS). Nor does it apply any early withdrawal penalty (as with ibonds).

I have corrected the calculations during deflation. With ibonds, the principal amount never decreases. Ibonds always pay interest. That is, ibonds returns never fall to zero dollars (nominal) during deflation. In terms of buying power (i.e., in real dollars), they return their interest coupon plus the amount of deflation.

Additional Inputs

I added the entry for Percentage Gains Removed. It acts as an expense. It removes a fraction of any increase in a portfolio's current balance from that of the year before. It removes nothing if there is a decrease. I make my calculations based on the balance at the beginning of each year. [Withdrawals for regular expenses are based upon both the balance at the beginning of a year and the balance at the end of the same year.]

Normally, you would set the percentage gains removed equal to zero.

I got my inspiration for this approach from Gummy's Sensible Withdrawals strategy. My algorithm differs in that I remove a fraction of any gains immediately. This fraction is in addition to the baseline withdrawal amount (which is a percentage of the portfolio's initial balance). Gummy waits until his fraction of gains exceeds a baseline withdrawal amount before he makes any increase how much he withdraws.

The data show that Gummy's approach is better. It would be difficult to implement with this calculator.

The dividend reinvestments and interest reinvestments are self-explanatory. Using them requires a great deal of care. Consider, for example, what happens with TIPS during deflation. The principal amount decreases because of deflation. The interest coupon is paid from this reduced principal. When you reinvest only a portion of the interest, part of your reinvestment makes up for the reduction in principal (as measured in nominal dollars). This results in your making a contribution (or partial contribution) to your portfolio instead of a withdrawal. [To avoid this problem would require adding rows to the calculator, which might cause problems.]

I have added to the Stock Switching Model. To stay within space limitations, I have assumed a stock allocation of 100% whenever P/E10 is below the lowest threshold. I have assumed a stock allocation of 0% whenever P/E10 is above the highest threshold. The result is five allocations from four thresholds, but with only three allocations available for programming.

Be sure to select the type of Fixed Income investments when switching. The original calculator always used commercial paper.

Original Data Summary Areas

I am relatively unfamiliar with the original data summary areas.

The data summaries in rows 26 through 61, columns A through I, include only sequences that were completed by the end of 2002. They exclude all partial sequences. This can be important. The 40-year data summaries exclude all sequences that began later than 1962. The most dangerous years for high stock allocations were the late 1960s.

The data summaries in rows 65 to 83, columns A through I, convert the Calculation Results Matrix (Nominal Values) of rows 26 through 41 into annualized returns. The annualized return r tells you how much an initial balance would have grown after N years if reinvested always at the same rate. It is the solution to this equation: (final balance)=[(1+r)^N]*(initial balance).

The data summary in rows 10 through 144, columns S through AK, tell you the portfolio nominal and real balances at years 10, 20, 30, 40, 50 and 60.

Augmented Data Summaries

I have augmented the original data summaries in rows 10 through 144, columns S through AK, with tables in columns BA through BG, BH through BJ, CN through CT, CW through DC, DF through DL and DO through DU.

I use the data summary in columns BA through BG whenever I calculate Historical Surviving Withdrawal Rates. It assigns the number 1 whenever the portfolio's balance is negative AND inside a specified range of years. The range of years is from M1 (the lower limit) to M2 (the upper limit).

I have supplied a data summary about what would have happened at the end of year 2000 in columns BH through BJ, rows 82 through 144.

I have converted nominal dollar amounts at years 10, 20, 30, 40, 50 and 60 from rows S through AK into annualized returns in columns CN through CT. I have converted them into real annualized returns in columns CW through DC. I have multiplied the annualized returns by 100. The values are percentages. [The values are NOT in the percentage format.]

If a portfolio has a negative or zero balance, I report its annualized return as -100.

I have the nominal annualized gain multipliers in columns DF through DL. I have put real annualized gain multipliers in columns DO through DU. Each gain multiplier G is 1+r, where r is the return.

If a portfolio has a negative or zero balance, I report its gain multiplier as 0.

New Data Summaries

I have many data summaries in rows 1 through 9.

I use the data summary in rows 1 through 9, columns K through P, whenever I calculate Historical Surviving Withdrawal Rates. I routinely increase withdrawal rates in increments, then look in this data summary. Whenever the number of failures has increased, I scroll to columns BA through BG, which identifies which years have failed.

My entries in cells M1 and M2 allow me to specify the years of interest. Most often, I record 30-year Historical Surviving Withdrawal Rates for all sequences beginning in 1921-1980. I usually base my analysis on 1923-1980.

I have summarized which portfolios would have failed by the end of 2000 in columns R through T and BG through BI.

The other data summaries found in rows 1 though 9 extract information at years 10, 20, 30, 40, 50 and 60 from the data summary tables. You enter your threshold levels in row 1. You choose real or nominal dollar amounts in row 2.

I have supplied key the cell locations for you to have access to the details. Simply press the special function key F5 (Go To) and enter the cell address. You can use special function key F5 to return to your starting point. You can press CTRL+Home to return to the start of the spreadsheet (cell A1).

After you jump to a table, scrolling to the right takes you to a summary display. (The real and nominal selection does not work. It is for information only.)

Scroll to the left for the basic information. The actual data summary tables are in columns A through BJ. Other columns show information in intermediate forms.

In columns F through I, rows 12 through 17, I have highlighted the tables starting in rows 2600, 2800, 3000, 3200 and 3400. They contain the nominal balances, annualized nominal returns, inflation multipliers (for converting current balances into those of the initial year), real balances and annualized real returns.

I frequently extract data from the real balances found in A3200 and the annualized real returns in A3400. I copy information from the calculator and paste it into an analysis spreadsheet.

Another valuable set of tables is found in region of A6100. It is the total amount withdrawn each year in real dollars. If you scroll to column EY, you will see 4-year moving averages. More often, I scroll to the area around column HG. It has 5-year moving averages.

You can locate other tables by scrolling through the data summaries in rows 1 through 9.

Key Calculations

The calculator uses NOMINAL dollar amounts for all of its basic calculations. In some cases, it converts these into REAL dollar amounts for data analysis.

The calculator treats all non-stock investments as single-year trading instruments without capital gains, capital losses or fees. The calculator does not allow you to lock in a favorable interest rate. It does not penalize you when interest rates rise. It simply assigns an interest rate and converts it to a single year of income.

Rows 149 through 174 contain Professor Robert Shiller's S&P500 data (nominal prices and dividends).

Rows 177 through 179 contain data for commercial paper, 5-year Treasury bonds and 30-year Treasury bonds. Rows 180 and 181 contain CALCULATIONS for the interest from TIPS and ibonds. The calculations use the interest rate from cell H8 and the inflation index values from row 190. Row 190 contains either the CPI from row 199 or the PPI from row 189, depending upon the selection in cell B12. (If cell B12 contains anything other than 1, the calculator uses the CPI.)

The ibond calculation is complex. The first question is whether there is deflation. If the inflation index decreases, the principal amount of an ibond (in nominal dollars) remains unchanged. If the inflation index increases, the principal amount increases.

You always get your interest payment. It is the coupon percentage (from cell H8) times the principal amount. Your interest payment NEVER decreases (in nominal dollars). You never pay money back from accrued interest in times of deflation.

The stock switching algorithm is found in row 182.

It was necessary to include the calculations in rows 2550, 2552 and 2553 to implement switching. This is because of programming restrictions of Microsoft Excel spreadsheets. It was necessary for me to introduce the calculations in row 2550 to reach the three allocations (with two P/E10 thresholds) originally planned for the Retire Early Safe Withdrawal Calculator. I was necessary to add rows 2552 and 2553 to reach five allocations with four P/E10 levels. NOTE: I assign a stock allocation of 100% when P/E10 is less than its lowest threshold. I assign a stock allocation of 0% when P/E10 exceeds its highest threshold.

The S&P Real P/E Ratio of row 186 uses Professor Robert Shiller's January values of P/E10.

There is a separate set of calculations for each historical sequence. You read off the balances from the last line of each set. The sequences are aligned so that the initial balance (i.e., year zero) is the first entry the line and the last entry is the balance at the end of 60 years.

The annual withdrawal amount appears (twice) in the first year of a sequence. The formula changes (in both cases) starting in the second year of the sequence. The new formula calculates the single-year adjustment for inflation. It refers to only the previous year's index level. It does NOT refer back to the initial inflation index level. [In addition, the model includes logic that allows you to exclude the inflation adjustment.]

The annual expense formulas are similar. The first year has no logic for capital gains. The second year and later years do. Expenses appear twice (as do withdrawals). Regular expenses are based upon the current balance immediately before they are charged. Those expenses specifically related to capital gains are based upon the portfolio's starting balances of the current year and the previous year. The amount of capital gains removed is calculated only once. But it is taken out in two increments.

Stocks are rebalanced when cell B16 equals 1. When selected, stocks receive the constant allocation found in cell B6 times the total balance after the initial withdrawal. Bonds receive an allocation of one minus the value in cell B6. This precludes varying allocations. Instead, the calculator treats switching as a special type FIXED INCOME investment.

If cell B16 is anything other than 1, the calculator removes withdrawals and expenses first. Then it assigns the same allocations as from the end of the previous year. It calculates gains and losses from both stocks and bonds and totals the two balances. Finally, the calculator removes end of the year withdrawals and expenses.

Hints

You can highlight an entire spreadsheet by clicking the blank gray rectangle above the 1 and to the left of the A. You can copy it and then paste it into a new spreadsheet. You can paste it if you click on cell A1 of the new spreadsheet, if you click on the gray rectangle on the new spreadsheet or if you do not click anywhere on the new spreadsheet. If you highlight any cell (other than cell A1), you will not be able to paste.

Making copies allows you to make mistakes without any big penalties.

You can read the calculator's programming by highlighting a cell. Look at the top. Any calculation begins with an = equals sign.

A dollar sign $ simply means that a cell location (row, column or both) remains constant when a program is copied or when it is filled in. For example, cell B4 contains the portfolio's initial balance. You can find many references to cell $B$4, not cell B4. The stock allocation is in cell B6. You see many references to cell $B$6, not cell B6.

If you read row 203, which calculated the stock balances for the 1871 sequence, the first entry is =IF($B$16=1,+$B$6*C202,+B206/B212*C202) in cell C203. The second entry is =IF($B$16=1,+$B$6*D202,+C206/C212*D202) in cell D203. Notice that everything except column letters remains the same. The column letters for B16 and B6 stay the same because they have a $ dollar sign in front of them ($B$16 and $B$6). As you click each cell to the right, you can see how each column letter has changed by exactly one increment except for $B$16 and $B$6. Cells B16 and $B$16 remain unchanged. [Cells B6 and $B$6 refer to the same locations.] But $B$16 and $B$6 are always in column B. They stay put while the other column letters change.

Highlight a cell (i.e., click it so that it has a thick box around it). Now move your cursor around the edge of the cell. You may see an arrow, which is the standard mouse symbol. You may see a fat plus sign for an individual cell. You will see a narrow plus sign when you hold your pointer over the lower right hand side of a highlighted cell. This is the fill handle. If you press and hold your left mouse switch down, you can copy the formula into adjacent cells WHILE FILLIN IN CHANGES to the formula. You stop by lifting your left mouse button and then clicking on any cell (whatsoever).

The formulas in row 203 were made by writing (or pasting) =IF($B$16=1,+$B$6*C202,+B206/B212*C202) into cell C203 and then using the fill handle. That is, after entering the formula, the programmer left clicked cell C203. He found the fill handle, clicked down on his left mouse switch, moved his mouse to the right to drag or fill in the formula until he reached cell BJ203. He could have done this in stages. Then he lifted his mouse button and left clicked a cell, possibly cell BJ203, to complete the process.

The formula in cell BJ203 is =IF($B$16=1,+$B$6*BJ202,+BI206/BI212*BJ202). All of the rows have stayed put. Cells $B$16 and $B$6 have stayed put (because of the $ dollar signs). All of the rows have moved from B to BI and from C to BJ.

The fill handle works vertically as well as horizontally.

To write the program for row 221, it is only necessary to copy and paste (using the regular paste function, not paste special) the formula from row 203 into the proper cells in row 221. The Excel spreadsheet automatically changes the rows and columns.

The first cell in row 221 is D221 and its formula is =IF($B$16=1,+$B$6*D220,+C224/C230*D220). Column D appears because it is the column of the first cell in the new row. Similarly, for row 221 in this sense: row 221 is one cell below row 220. The formula in cell D221 refers to cell D220. The formula in cell C203 refers to C202, which is also one cell down. Continue comparing formulas. The reference to C224 is one column to the left and three rows down (from letter D to letter C and from number 221 to number 224). This is the same relationship as between cell C203 and cell B206.

This is a powerful programming feature of Excel.

The formula in cell D200 is =IF($B$14<>1,C200,C200*D$190/C$190). Row 190 is called the inflation series. It has the inflation index (either the CPI or PPI, depending upon the selection in cell B12). Clearly, when we paste the formula from cell D200 into cell E218, we would like the formula to keep the references to row 190. It does. Here is the formula in cell E218: =IF($B$14<>1,D218,D218*E$190/D$190). By having dollar signs in the right locations (i.e., just before the numbers), we can keep the row constant while allowing the column to change.

Most of the mathematical formulas are readable. For example, <= means less than or equal. The symbol <> means (simultaneously, both greater than and lower than, that is:) not equal. The logical AND require that all conditions be true simultaneously. The symbol for multiplication is *. The symbol for division is /. The symbol for an exponent is ^. For example, 5^2 is 5*5, which is 25.

The logical IF has several variations available to programmers. What you see most often is IF(condition, action when the condition is true, action when the condition is false). Cell B190 has a simple example for the inflation series: =IF($B$12=1,+B189,+B188). The inflation series is the PPI (from row 189) whenever cell B12 is exactly one. If cell B12 contains anything else (such as 2, 1.1, 3 or 400), the inflation series is the CPI (from row 188).

Similarly, the formula in cell W6 is =IF($Z$2>1,ES2605,ES3205). An entry of 1 provides you with data using real balances. An entry of 2 provides you with information using nominal balances. But now we see that 0.2 and -3 will also use real dollars. We see that both 1.1 and 63378 provide results based on nominal dollars. That is, we know what happens when you enter something other than a 1 or 2 into cell W6.

Here is a relatively simple formula using more than one IF statement at a time. It is from cell DH16: =POWER(IF($W16<0,0,$W16)*IF($B$4>0,1/$B$4,0),1/20). The first IF statement is zero whenever cell W16 is negative. This happens often. W16 is the (nominal) balance of the 1872 sequence at year 20. Otherwise, the value is whatever is in cell W16. The second IF statement protects against a negative or zero initial balance. The product of the two IF statements is the final balance from cell W16 divided by the initial balance from cell B4. The function POWER raises this 1/20th power, which is the same as the 20th root. The formula answers the equation: (final balance/initial balance)=g^20, when g is a gain multiplier, which is (1+the annualized return).

You can put IF statements inside of other IF statements (nesting). This can get complicated. This is from cell B183, the formula for the FI Interest Rate: =IF($B$7>1,(IF($B$7>2,(IF($B$7>3,
(IF($B$7>4,(IF($B$7>5,+B182,+B181)),+B180)),
+B179*$B$23)),+B178*$B$23)),+B177*$B$23).

This particular statement is written as a negative. To understand it, start by assuming that all of the IF conditions are true. If cell B7 has anything bigger than 5 (the suggested entry is 6), all of the IF statements are true and the answer is the value found in cell B182. Reducing the value in cell B7 to 5 (or lower, but above 4), the answer is in cell B181. Reducing the value in B7 to 4 (or lower, but above 3), the answer is in cell B180. Reducing the value in B7 to 3 (or lower, but above 2), the answer is the product from cells B179 and B23 (or B179*$B$23. Reducing the value in B7 to 2 (or lower, but above 1), the answer is the product from cells B178 and B23. Reducing the value in B7 to 1 (or anything lower, including negative values), the answer is the product from cells B177 and B23. [NOTE: B23 is the percentage of interest that you reinvest. Normally, you would set B23 equal to 100.]

Have fun.

John Walter Russell
December 30, 2005