Request for assistance (with Excel)

Research on Safe Withdrawal Rates

Moderator: hocus2004

Post Reply
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Request for assistance (with Excel)

Post by JWR1945 »

Request for assistance (with Excel)

I have been working with Retire Early Safe Withdrawal Rate Calculator (version 1.61) this morning. I have verified that it is hard coded to use only Commercial Paper as the Fixed Income component when switching portfolio allocations according to P/E10. I would like to change it to use TIPS.

I am pretty sure that I know what to do manually. I would appreciate it greatly if someone could tell me what to do to automate the process. I would also appreciate verification that what I am doing is right and/or being told what additional changes are needed. I believe that there is a bug even after I make my changes.

What I am doing is changing the formula on line (row) 182. I am highlighting each cell on line 182. Then I am changing the formula at the top by replacing cells numbered 177 to 180. That is, if I am highlighting cell D182, I go to the formula bar at the top and change D177 to D180 (in two places). Then I move over to the next cell E182 and highlight it. I change the formula from E177 to E180 (twice) and continue. All of this is one cell at a time. All of this is manual.

My early investigations are quite promising. I changed all of the cells from 1965-1980 and looked at TIPS with 1% interest (above inflation) and 2.8% interest (above inflation). Both improved the Historical Database Rates substantially. Withdrawals of 5% are showing a high level of safety if you invest heavily in 2.8% TIPS (60% or 70% of your portfolio) whenever P/E10 is above bargain levels. That is, load up on TIPS whenever P/E10 is above 12 or 13.

All assistance will be appreciated.

Have fun.

John R.
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Post by JWR1945 »

Excel Assistance

I have just learned how to modify the Retire Early Safe Withdrawal [Rate] Calculator easily.

The references are: Start with Excel Help. Then select Contents and Index, then Contents. Then select Creating Formulas and Auditing Workbooks. Select Editing Formulas. Then follow the procedures in Edit a Formula and Move or Copy a Formula.

In addition, be sure to learn about the Fill Handle and how to Select All.

This information would have helped me greatly in the past.

Have fun.

John R.
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Post by JWR1945 »

Switching plus TIPS mod

Here is how you can modify the Retire Early Safe Withdrawal [Rate] Calculator to use TIPS as your fixed income component when switching stock allocations.

First you must download the software. intercst has made it available at the Retire Early Home Page website: http://rehphome.tripod.com/re60.html

The calculator is in a zip file. Non-commercial users may download a free Free ZIP wizard from Coffee Cup software: http://www.coffeecup.com/freestuff/ That is what I use. I learned about in one of Kim Komando's Tip of the Day emails: http://www.komando.com/newsletter.asp

This is the November 7, 2002 version of the calculator (version 1.61). Save this copy as-is. It includes the switching of stock allocations, but it uses commercial paper as the fixed income component. It is worth keeping.

Make a new copy of the downloaded software. First, to select all on the spreadsheet, point your mouse arrow to the gray corner position just above 1 and before A and click once. Then copy the worksheet. (Under Edit, select copy. Then click once anywhere in the worksheet. Open a blank worksheet. Then paste. (When you open the blank worksheet, box A1 will be highlighted. You don't have to do anything special. But it is necessary for box A1 to be highlighted.) It takes a few seconds for the paste function to complete its task. After you can read the text, click anywhere on the spreadsheet. The text is initially white on black. After you click, it is black on white (normal).

Scroll down to A182 Stock Switch by P/E. Move over two places to cell C182 and click. At the top (in the Formula Bar), you will read =IF( and so forth. You will see two references to C177 in that formula. Change it to C180 in both places. Row 177 has commercial paper interest rate information and row 180 has the comparable TIPS information.

Now for the not-too-hard-once-you-know-what-you-are-doing part. The first time that I did this, I simply corrected every cell in row 182.

The lower right hand corner of a cell is called the fill handle. Click on the fill handle for cell C182. There is a little box in that lower right hand corner and the mouse symbol changes from a broad plus to a narrow plus, as if it were for aiming. Click and hold down on the fill handle. Drag the mouse to the last cell (EK182). If you happen to lift up your mouse button, you can continue from where you left off by returning to the fill handle and clicking and holding once more. You do not have to start over.

Lift your finger from the mouse button. Then press ENTER. Click anywhere that is free on the worksheet.

You now have a Retire Early Safe Withdrawal [Rate] Calculator that has been modified so that it uses TIPS instead of commercial paper when using switching stock allocations. Save it (and give it a name).

Remember that only F19, B20 and I20 are active at this time. Anything that you put into I19 or F20 will be ignored.

Have fun.

John R.
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Post by JWR1945 »

Switching, TIPS and % mod

Here is how you can further modify the Retire Early Safe Withdrawal [Rate] Calculator to make withdrawals based on a percentage of the current balance instead of the initial balance. I have made these modifications to the version that I modified first. The (portfolio allocation) switching algorithm in that version uses TIPS instead of commercial paper for the fixed income component. I anticipate making a new one from that, which will once again work with commercial paper. (That gives four versions. Two have switching with commercial paper. Two have switching with TIPS. In each pair, one version has withdrawals based upon the initial balance and the other has withdrawals based upon the current balance. The as-is (unmodified by me) version of the Retire Early Safe Withdrawal [Rate] Calculator uses commercial paper and the initial balance.)

There may be an easier way to do this. But this is what I actually did.

First, make a copy of an existing calculator. The copy will be the new one, the modified version.

Highlight (i.e., click on) cell C200. Read the formula bar. It says $B$4*$B$9*$B$10. Change the $B$4 (which is the initial balance) to C199 (which is the current balance for the year in column C).

You have already highlighted cell C200. Now find its fill handle (the little box in the lower right hand corner). Press down on it and move it to the right until you reach box BJ200 at the very end of the sequence (sixty years later).

Copy. (In Edit, press copy. This will copy the row from C200 through BJ200.)

Scroll down to the next sequence of withdrawals. Highlight box D218. It is the cell in the following sequence of years that corresponds to cell 200. Press Paste Special. (In Edit, press Paste Special, not Paste and not Paste Hyperlink.) A gray box titled Paste Special will open up. In that box, under Paste, select formulas. (That is important. Do not select All.) Then press OK.

You have now changed the code in cell D218 to read D217*$B$9*$B$10.

Continue highlighting the same cell for every one of the remaining start years. You do not have to copy more than once. But you must Paste Special the formulas every time.

When you have finished this, highlight cell C213. Change the formula from $B$4*$B$9*(1-$B$10) to C199*$B$9*(1-$B$10). Find the fill handle and copy the row from C213 through BJ213. Highlight cell D231 and Paste Special (formulas) into that row. Continue to do the same for the corresponding entry for every start year.

There is a final step that you must do to turn off the area that is active for copying. I do not know the best way to do this, but I do know one way. Find an empty cell and highlight it. The in Edit, select Clear and then select All. This will stop the active region. (If you make a mistake, such as forgetting to locate an empty cell, there is an undo Clear at the top of Edit that I have found helpful.)

Save (and name) your new calculator. It is identical to what you started with, except that it uses the current balance (instead of the initial balance) for calculating withdrawal amounts.

Have fun.

John R.
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Post by JWR1945 »

Analyzing Data

1. I have found these procedures helpful in analyzing Retire Early Safe Withdrawal [Rate] Calculator data.
2. First make a list of the column letters that correspond to each start year. You can find the sequence of years on row 148 and also on row 197. The column letters are at the top of your screen. For example, the year 1902 corresponds to column AG. For example, the year 1921 corresponds to column AZ.
3. Make a list of the row numbers corresponding to the start of each year. For example, retirements begun in 1872 start on line 199. Retirements begun in 1901 start on line 721.
4. Make a list of start years and finish years of interest to you. For example, if you are looking at 30-year portfolio lifespans, add thirty to each start year. The 1872 portfolio finishes in 1902, the 1911 portfolio finishes in 1941 and so forth. The final year with actual data is 2002 (in column EC). I end my partially completed portfolios in 2002.
5. Make a list of start years and the location of each one's finish year in terms of an Excel cell location. Combine the row number of the start year with the column letters of the finish year. For example, a retirement begun in 1872 started on line (row) 199. Thirty years later was 1902. Column AG corresponds to 1902. Write down AG199. For example, a retirement begun in 1921 started on line (row) 1081. Thirty years later was 1951, which corresponds to column CD. Write down CD1081.
6. To go to a cell location, press F5 and then enter your desired cell location. (F5 takes you to the Go To pop up box. You enter your data in the line Reference. Then you press OK inside the pop up box or ENTER from your keyboard.)
7. To return to the beginning of the worksheet (with A1 in the upper left hand corner), press CTRL+HOME.

Have fun.

John R.
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Post by JWR1945 »

I have not yet verified these new calculators. They may have bugs.

These are changes to the Retire Early Safe Withdrawal [Rate] Calculator, Version 1.61, November 7, 2002, that give it the full two threshold, three stock allocation switching capabilities suggested by its user interface.

The original program had a problem because it needed too many arguments. I have introduced a new row (number 2550) that calculates the stock return. This allows me to simplify the code in row 182 so as to use fewer arguments.

The first step is to copy the original calculator so that you always have it available. You do this by opening a new (blank) Excel worksheet and the original calculator at the same time. Next, highlight any cell in the original calculator and type in CTRL+A. That selects the entire calculator. Copy it and paste it on the new worksheet. Click on any cell after a copy is made to complete the operation. Click outside of the work area on the original calculator to restore it back to normal.

Basic Modification

1. These are the changes for switching with commercial paper.

2. Go to cell C2550. (Press F5, type in c2550 and press enter.) Type in the following on the formula bar (at the top, right after the = sign): =+((C184-B184)/B184*100)+(C185/C184*100)

3. Press enter and then click on any other cell.

4. Highlight (i.e., click once on) cell C2550. Place your cursor on the fill handle (in the lower right hand corner of cell C2550). Click the mouse and drag the fill handle to the right until the last column (EK2550). Then press enter and click on an empty cell. Most likely, you will want to press enter several times before you get to the end of the row (or if you overshoot the end of the row). That is OK. When you are finished, press enter and click on an empty cell.

5. I wrote the words Stock gain in cell A2550 (optional).

6. In cell C182 (in the Stock Switch by P/E row), write the following in the formula bar: =IF($F$19<B186,(IF($I$19<B186,+(C177*(1-$I$20)+$I$20*C2550),+(C177*(1-$F$20)+$F$20*C2550))),+(C177*(1-$B$20)+$B$20*C2550))

7. Highlight cell (i.e., click once on) C182. Find the fill handle in the lower right hand corner. Click on it (and hold it down) and drag the cursor to the right to fill in the entire row. Then release the mouse. Press enter. Then press on an empty cell.

8. I wrote the words Special V1.0 in cell E1 for a convenient reference. Cell E1 is next to the words Version 1.61 in the basic calculator.

9. This completes the basic modification. It switches based upon two P/E10 thresholds and it provides three allocations. The logic is from left to right. If you put a lower threshold into cell I19 than you have at F19, the logic should respond to the threshold of cell F19. (I haven't checked this out.)

10. This version of the calculator switches among allocations of the S&P 500 index and commercial paper.

The TIPS Modification

1. Make a copy of the modified calculator. (Select the entire worksheet by pressing CTRL+A, copy, paste, enter and click on an empty cell.)

2. Highlight (i.e., click once on) cell C182. In the formula bar, replace all references to row 177 (for commercial paper) with references to row 180 (TIPS). That is, change C177 to C180 in three places in the formula bar. The formula should now read:
=IF($F$19<B186,(IF($I$19<B186,+(C180*(1-$I$20)+$I$20*C2550),+(C180*(1-$F$20)+$F$20*C2550))),+(C180*(1-$B$20)+$B$20*C2550))

3. Press enter and click on an empty cell.

4. Highlight cell C182 again. Locate its fill handle in the lower right hand corner of the cell. Click down (and hold) on the fill handle and drag the cursor to the right (to cell EK182). Press enter and then click on an empty cell.

5. I wrote Special V2.0 TIPS in cell E1, next to the words Version 1.61.

The Ibonds Modification

1. Make a copy of the modified calculator. (Select the entire worksheet by pressing CTRL+A, copy, paste, enter and click on an empty cell.)

2. Highlight (i.e., click once on) cell C182. In the formula bar, replace all references to row 177 (for commercial paper) with references to row 181 (ibonds). That is, change C177 to C181 in three places in the formula bar. The formula should now read:
=IF($F$19<B186,(IF($I$19<B186,+(C181*(1-$I$20)+$I$20*C2550),+(C181*(1-$F$20)+$F$20*C2550))),+(C181*(1-$B$20)+$B$20*C2550))

3. Press enter and click on an empty cell.

4. Highlight cell C182 again. Locate its fill handle in the lower right hand corner of the cell. Click down (and hold) on the fill handle and drag the cursor to the right (to cell EK182). Press enter and then click on an empty cell.

5. I wrote Special V3.0 Ibonds in cell E1, next to the words Version 1.61.

Have fun.

John R.
Mike
*** Veteran
Posts: 278
Joined: Sun Jul 06, 2003 4:00 am

Post by Mike »

Thank you John, your directions were so clear that I was able to modify a copy of the spreadsheet to work with commercial paper in just a few minutes. Instead of writing in the formulas manually, I just copied and pasted your formulas from the post.
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Post by JWR1945 »

Thanks, Mike.

I will be verifying that my changes do not do anything beyond what is intended (i.e., switching of allocations). I will do this by plugging in switching threshold values that should result in no switches or very limited switches. I will compare those results with those of an unmodified calculator.

After I have verified that my changes have introduced no new bugs, I will place a copy of my post at the Motley Fool as a courtesy to intercst, the creator of the Retire Early Safe Withdrawal Calculator. The calculator is very valuable and quite useful. I do not wish to withhold any improvements. I do wish to make sure that my changes really do make improvements instead of introducing new bugs.

Have fun.

John R.
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Post by JWR1945 »

I have now verified that the calculators work properly. Checking them out was easier and harder than I had expected.

It is very easy to verify that switching works as planned because of the detailed summaries that are available. As long as all of the numbers are exactly the same, everything has been taken into account.

What you do is come up with conditions that force the calculator to switch or not to switch and compare them with the basic calculator. In the case of commercial paper, I was also able to make comparisons with the two level switching that comes with the available calculator.

My checkout was much more extensive than I had planned. I had originally used a P/E10 level of 40 as being too high to be significant. Silly me. That threshold was exceeded in 1999 and in 2000. As a result, I ended up looking at the modified code in quite a bit of detail.

To make your own comparisons, set some thresholds to 4 or lower and some others at 60 or higher. They will never be crossed. Doing this allows you to place a selected stock allocation (such as 80%) into each of the three boxes, but one at a time.

Making use of the two level capability in the basic calculator (which is limited to using commercial paper as the fixed income component) allows you to fill in some gaps. You can show that the two switching thresholds work, again one at a time. You do this by assigning some reasonable allocations (such as 80% below threshold and 20% above threshold) in pairs.

Have fun.

John R.
User avatar
BenSolar
*** Veteran
Posts: 242
Joined: Mon Nov 25, 2002 5:46 am
Location: Western NC

Post by BenSolar »

JWR1945 wrote:I have now verified that the calculators work properly.
Thanks, John. :great: Excellent work! :D
"Do not spoil what you have by desiring what you have not; remember that what you now have was once among the things only hoped for." - Epicurus
MacDuff
* Rookie
Posts: 31
Joined: Wed Jun 18, 2003 8:20 pm
Location: Centralia, WA

Post by MacDuff »

Bonjour fellow seekers of truth and filthy lucre!

Yesterday I made a histogram with a cumulative probability line on Shiller's PE 10 data, and today's real PE10 of about 26 is greater than 95% of the monthly PE10's in his database. It also appears that almost all if not all of the values greater than today's were achieved in Bubble Une. Currently I feel that we are in Bubble Deux.

So I decided to look into buying some puts. I lost some meaningful money late in Bubble Une with this same strategy. I finally got tired of bleeding and stopped rolling, about 3 weeks before the NASDAQ went over the falls.

My new strategy is to let others play the at-the-money puts; I am looking at out-of-the-money puts, only for a potentially large score. To do some analysis, I went back to Shiller's data, and created a table of monthly real returns, going all the way back. Would you believe that the range is from -27% to +51%? In a month?

I would like to do quarterly returns, but I can't figure out how to automate the process of filling column b with the differences from column a-- apr-jan, jul-apr, oct-jul, jan-oct?

I have a level of S&P for every month, and I want only the non-overlapping returns. So with 1564 months of data, I should wind up with about 521 quarterly returns. I want to do this because inspection shows that when the po*p hits the fan, large losses can follow large losses. No random walk here. Same on the upside. 1933 is a year to behold.

So my problem is how to do this. Is there a way to set up the spreadsheet? If not, does anyone know a free source of quarterly data going back a long time?

I would hugely appreciate any help, and will report my findings here. I am also learning to make box-plots of all this data, and will try to post them with a link.

Yours, Mac
User avatar
BenSolar
*** Veteran
Posts: 242
Joined: Mon Nov 25, 2002 5:46 am
Location: Western NC

Post by BenSolar »

MacDuff wrote:I would hugely appreciate any help, and will report my findings here. I am also learning to make box-plots of all this data, and will try to post them with a link.
Sounds very interesting, MacDuff. I think I can help but it might be late tomorrow ...
"Do not spoil what you have by desiring what you have not; remember that what you now have was once among the things only hoped for." - Epicurus
MacDuff
* Rookie
Posts: 31
Joined: Wed Jun 18, 2003 8:20 pm
Location: Centralia, WA

Post by MacDuff »

BenSolar wrote:
MacDuff wrote:I would hugely appreciate any help, and will report my findings here. I am also learning to make box-plots of all this data, and will try to post them with a link.
Sounds very interesting, MacDuff. I think I can help but it might be late tomorrow ...
Thank you , Ben. I'll check back then. I did find a way to make a box and whisker plot with Excel. You follow a recipe which creats a boxplot from the scatterplot capability, which is one of Excel's supported plots. Now I have to figure out how to let you all see it.

What jumps out is that monthly returns of the S&P are certainly not a normal distribution. There is extreme kurtosis, with some very extreme outliers. The basic descriptive stats are as follows:

Min.....................-26.47%
10th percentile......-4.4%
1st quartile...........-1.8%
Median................. 0.5%
3rd quartile........... 2.6%
90th percentile...... 4.5%
Max.....................51.4%

A very tight orderly distribution, until something happens. Then bang! off the chart!

Mac
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Post by JWR1945 »

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.
Last edited by JWR1945 on Wed Dec 03, 2003 8:46 am, edited 1 time in total.
User avatar
BenSolar
*** Veteran
Posts: 242
Joined: Mon Nov 25, 2002 5:46 am
Location: Western NC

Post by BenSolar »

JWR1945 wrote:
BenSolar wrote:
MacDuff wrote:I would hugely appreciate any help, and will report my findings here. I am also learning to make box-plots of all this data, and will try to post them with a link.
Sounds very interesting, MacDuff. I think I can help but it might be late tomorrow ...
Thank you , Ben. I'll check back then.
:oops: I haven't found the time to dig up the spreadsheet I had worked on that had the example I wanted to give. Sorry for the delay.

Looks like John has stepped in with good instructions. Does that handle your request?

Your line of research looks very interesting ... Thanks for sharing.
"Do not spoil what you have by desiring what you have not; remember that what you now have was once among the things only hoped for." - Epicurus
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Post by JWR1945 »

For MacDuff:

In terms of prices only (i.e., without dividend reinvestments), you can use my previous approach but change the formulas in columns B and J. (You may want to leave columns C, D, K and L blank or use them to calculate something else.

To calculate quarterly gain multipliers, highlight cell B2008. In the formula at the top, type in:
=E2008/E2004
and then press enter. Use the fill handle and pull everything down to the last line on the spreadsheet.

To calculate real quarterly gain multipliers (i.e., taking inflation into account), highlight cell J2008. In the formula at the top, type in:
=M2008/M2004
and then press enter. Use the fill handle to pull everything down to the last line on the spreadsheet.

You may want to calculate percentage returns instead of (or in addition to) gain multipliers. If so, highlight the cell in row 2008 in the column that you want to use and type in:
=100*(E2008/E2004-1)
and press enter for the nominal quarterly percentage price increases.
Type in:
=100*(M2008/2004-1)
and press enter for the real quarterly percentage price increases.

In both cases, use the fill handle as before to carry the formulas all of the way to the bottom of the spreadsheet.

Have fun.

John R.

P.S. Remember to type in the equals sign in all cases.
MacDuff
* Rookie
Posts: 31
Joined: Wed Jun 18, 2003 8:20 pm
Location: Centralia, WA

Post by MacDuff »

Thank you John and Ben. I started to post last Wednesday, and a storm rolled in off the Pacific and knocked out my power for a couple days. I am just getting back in gear. I apologize for seeming ungrateful for the help you have given.

I will attempt to use John's instructions, and see if I can get my spreadsheet set up correctly.

I have another question re: Excel. Have any/either of you used a Monte Carlo engine add-in to Excel? I remember reading about one, but I must have lost my reference to it.

I am reading an interesting book by Nassim Taleb wherein he talks about the various experiments he has done with Monte Carlo simulation. I would like to try some of this too.

Mac
User avatar
BenSolar
*** Veteran
Posts: 242
Joined: Mon Nov 25, 2002 5:46 am
Location: Western NC

Post by BenSolar »

MacDuff wrote:I have another question re: Excel. Have any/either of you used a Monte Carlo engine add-in to Excel? I remember reading about one, but I must have lost my reference to it.

I am reading an interesting book by Nassim Taleb wherein he talks about the various experiments he has done with Monte Carlo simulation. I would like to try some of this too.
I wasn't even aware of any such feature. Sounds neat.
"Do not spoil what you have by desiring what you have not; remember that what you now have was once among the things only hoped for." - Epicurus
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Post by JWR1945 »

MacDuff, I am not familiar with such Monte Carlo add-ons. They should be quite useful.

I think that raddr and gummy have developed the best of today's Monte Carlo calculators. raddr has defined Mean Reversion precisely and incorporated it into his calculator. gummy not only randomly samples the (empirically determined) price distribution, but he also randomly samples historical sequences of inflation rates.

That having been said, there remains a tremendous opportunity for you and/or others to improve upon today's Monte Carlo Safe Withdrawal Rate calculators. This has come up in my ongoing email correspondence with hocus. The key feature is separating dividends and other reliable income streams from highly volatile price changes.

Have fun.

John R.
Post Reply