This article was revised on April 17, 1999.
Over the past couple of months Retire Early has answered the question,"What is the maximum safe withdrawal rate in retirement?" and shown that high fees and commissions can severely reduce your retirement income. (See, "Are your mutual fund fees so high you can't retire?") Several readers have asked if there is any way to calculate their own safe withdrawal rate. To allow readers to assess their own situation, the Retire Early Safe Withdrawal Calculator is now available for download.
The Retire Early study on safe withdrawal rates uses a data series developed by Professor Robert J. Shiller of Yale University that tracks stock market returns from 1871 to 1998. This data appears in Chapter 26 of Shiller's book Market Volatility and on his web site. (See, Historical Stock Market Returns.)
Shiller's data includes nine series. The first is an annual series of January values of the Standard and Poors Composite Stock Price Index from 1871 to 1998. The second series is the dividend yield on the S&P Composite Index. The third series is the earnings on the S&P Composite Index. Shiller spliced data from several sources to assemble a data series covering the 1871 to 1998 time span.
Series 4 shows nominal interest rates in the form of 4-6 month prime commercial paper. Series 5 is the January value of the Producer Price Index - All Commodities with a 1982 base year. Series 6 and 7 are variants of the Producer Price Index used in some of Shiller's other published works. Series 8 is the Consumption Deflator with a base year of 1972. Series 9 is Real per Capita Consumption.
The Retire Early study used Series 1 and 2, the S&P Composite Index and the dividend yield on the S&P Composite Index, Series 4, nominal interest rates, and Series 5, the Producer Price Index as a measure of inflation. A Microsoft Excel spreadsheet was constructed to determine the "survivablity" of a 75% stock/25% fixed income portfolio for each 30 year pay out period using the same methodology as the Trinity Study. As a check, the results for the Shiller data series for the years 1926 to 1995 were compared to the Trinty study. They appear in the table below.
Terminal value is the value of the portfolio after the final annual withdrawal has been made at the end of the payout period. Survivability is the probability that funds will remain in the portfolio at the end of the pay out period. Higher withdrawal rates have lower survivability. The highest withdrawal rate that leaves at least $1 in each pay out period considered in the maximum "100% safe" withdrawal rate.
The results for the two studies are similiar. The Trinity study uses long term, high grade corporate bonds as its fixed income series, while the Retire Early study uses 4-6 month commercial paper. This may explain why the terminal values for the Trinity study have a wider range than the corresponding Retire Early study values.
The annual withdrawals in these two instances above were not inflation adjusted. Results for the survivability of inflation adjusted withdrawals were also compared. They were similiar and appear in the table below.
Download Free Software
Readers interested in running their own "safe withdrawal" scenarios are invited to download a copy of the Retire Early Safe Withdrawal Calculator. It's a Microsoft Excel workbook (Version 7.0 for Windows 95) that allows the user to input his own asset allocation and withdrawal rate. The calculator returns the percent survivablity for the portfolio and the terminal values of the portfolio at the end of 10,20,30,40, and 50 year pay out periods.
Note: The re50r2.xls spreadsheet first released in December 1998 is no longer available on the server. It's been updated and replaced by a revised edition re2000.xls, click here.
Remember, you need PKUNZIP.EXE to unzip this file and return it to Excel format. (re50r2.xls size=2,544 kb)
1) Enter the desired values for the input fields in blue.
2) Click on the icon "Click to CALCULATE"
3) Read Survivability and Terminal Values in field below the "Click to CALCULATE" icon.
Note (1): The Retire Early Safe Withdrawal Calculator lists 3 choices for the fixed income series, but only selection 1, "4-6 month commercial paper" has data for 1871-1998. If you choose selection 2 , 5-yr US Treasury Notes or 3, US Treasury Bonds errors will result. I'm currently looking for a source of US Government securities data that goes back to 1871. A future update may include this data.
Note (2): Two inflation series are supported by the spreadsheet. Selection 1 "PPI" is the Producer Price Index (1982=100) from Professor Shiller's data series. Selection 2 "CPI-U/PPI" uses Shiller's data for the years 1871-1912 and the CPI-U for the years 1913-1998. The CPI-U yields higher total inflation for the years 1913-1998, 1,648% vs. 1,037% for the PPI. Readers interested in downloading the CPI-U data series can find it at the Bureau of Labor Statistics Consumer Price Index Home Page.
filename = re50.html
Copyright © 1999 John P. Greaney, All rights reserved.