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 a variable-percentage withdrawal (VPW) calculation specifically targeted for annuitization at age 80 based on current annuity prices.
- 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's easy to estimate the immediate impact of a stock crash by simply reducing the portfolio size by half its stock allocation and looking at the impact on the calculated withdrawal amount:
- new portfolio balance = old portfolio balance X (1 - stock allocation/2)
The spreadsheet is open and free. You can modify it and adapt it to your own preferences and needs.
Comments are welcome.