Current version: 2.1 (August 14, 2018)
- Online (Google Sheets): Read the following instructions before clicking on the link!
- Click on the link below.
- Sign into your Google account (if not already signed in).
- Make a copy of the file as follows: File -> Make a copy...
- The copy is yours to modify or download.
- Here is the link: VariablePercentageWithdrawalForAdvancedUsers
- Download (Microsoft Office Excel): VariablePercentageWithdrawalForAdvancedUsers.xlsx
- Download (LibreOffice Calc): VariablePercentageWithdrawalForAdvancedUsers.ods
This spreadsheet calculates a portfolio withdrawal for the current year based on the current portfolio balance, current pensions and future pensions promises with and without cost of living adjustment (COLA), as well as desired residual portfolio.
The spreadsheet is targeted at advanced users who fully understand the risks of filling the gap in future pension payments with withdrawals from a portfolio of fluctuating assets.
This spreadsheet is particular in that it combines various calculations and methods:
- It uses variable-percentage withdrawal (VPW) and targets partial annuitization at age 80.
- It estimates the payment of a fixed-rate-indexed $100,000 single-premium immediate annuity (SPIA) from the easily-found payment of a non-indexed $100,000 SPIA (using an adverse-selection estimate of age 100 at death, on average, for a group of annuitants).
- It accounts for the impact of a fixed cost of living increase on delayed non-COLA pension promises.
- It adjust portfolio withdrawals to compensate for the erosive impact of inflation on non-COLA pensions.
- It manages the portfolio as a whole (it doesn't require segregating money into a fixed income ladder to deal with delayed pensions).
- It aims to keep a residual portfolio, after annuitization at age 80, from which constant-percentage withdrawals (CPW) will be taken.
Every year, during retirement, the following current information must be entered into the spreadsheet to calculate a new portfolio withdrawal amount:
- Current year (e.g. 2018)
- Current monthly payment on a non-indexed $100,000 SPIA for an 80 years old retiree
- Current age
- Current portfolio balance
- Upcoming-year target stock allocation
- Target residual portfolio (note: after annuitization age 80, this amount must obviously be equal to the current portfolio balance)
- Total annual payments of current non-COLA and COLA pensions
- Promised future pensions and start ages, up to 2 non-COLA and 2 COLA future pensions (Social Security is an example of a COLA pension)
While the spreadsheet doesn't provide a backtesting data set, it estimates the immediate impact of a stock crash by simply reducing the portfolio size by 50% of its stock allocation and looking at the impact on total retirement income (pensions + portfolio withdrawal).
In summary, this spreadsheet provides a simple interface to a complex calculation. It eliminates the need to independently consider various parts of a retirement financial plan (non-portfolio income possibly delayed, lack of cost-of living adjustment, withdrawals from a fluctuating portfolio, residual portfolio).
The spreadsheet is open and free. You can modify it and adapt it to your own preferences and needs.
Comments are welcome.