Variable Percentage Withdrawal (VPW-adv) for Advanced Users

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
Post Reply
longinvest
Posts: 2876
Joined: Sat Aug 11, 2012 8:44 am

Variable Percentage Withdrawal (VPW-adv) for Advanced Users

Post by longinvest » Sun May 27, 2018 8:51 pm

I have developed a spreadsheet for advanced users of the variable-percentage withdrawal (VPW) method.

Online access:
Read the following instructions before clicking on the link!
  1. Click on the link below.
  2. Sign into your Google account (if not already signed in).
  3. Make a copy of the file as follows: File -> Make a copy...
  4. The copy is yours to modify or download.
  5. Here is the link to the Google Sheets document: Variable Percentage Withdrawal for Advanced Users

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.
As usual, this spreadsheet doesn't take the any past information into account; all calculations are based on current information and future pension promises. As I've explained in the VPW topic, VPW naturally adapts to effective portfolio returns and doesn't attempt to make future return predictions.

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)
Non-portfolio income and portfolio withdrawals are calculated on a before-tax basis. (The net amounts available for spending, after tax, will obviously be smaller. Calculating taxes and residual net amounts is outside the scope of this simple spreadsheet.)

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)
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.

Enjoy!
Last edited by longinvest on Tue May 29, 2018 11:36 pm, edited 2 times in total.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

AlohaJoe
Posts: 3335
Joined: Mon Nov 26, 2007 2:00 pm
Location: Saigon, Vietnam

Re: Variable Percentage Withdrawal (VPW) for Advanced Users

Post by AlohaJoe » Sun May 27, 2018 9:40 pm

This is pretty nice. It is nice to have an easy to use alternative to the LMP/bond-ladder-to-social-security strategy. It is also great to see more & more retirement planning-type tools take a more holistic view. It seems like nowadays everyone agrees that retirement planning needs to take into account Social Security, annuitization, reverse mortgages, pensions, bequests, portfolios, etc. But, short of going to a financial advisor, there aren't really good tools for doing that. (And I'm not convinced that financial advisors really have good tools either yet, based on the articles I read in advisorperspective and fa-mag.)

The only real feedback I have is there is a lot of room to make it more complicated if you feel up to it :D For instance, on Bogleheads people often recommend laddered annuities (maybe age 70, 75, 80, 85) instead of all-at-once. QLACs have given rise to another thing to consider with deferred immediate annuities in tax-deferred accounts. As mentioned above, house equity is another possible longevity hedge (which IMHO works well with VPW's tendency to sometimes draw down portfolios, something some people are irrationally afraid of).

It might also be interesting to show some compare/contrast examples of this approach versus other common approaches like LMP and "make it up as we go along".

longinvest
Posts: 2876
Joined: Sat Aug 11, 2012 8:44 am

Re: Variable Percentage Withdrawal (VPW) for Advanced Users

Post by longinvest » Sun May 27, 2018 10:06 pm

AlohaJoe,

Thanks for the feedback!

Personally, I'm not a fan of deferred annuities, fixed annuities, laddering annuities, or reverse mortgages.

I'll keep my money and buy the immediate annuity if and when I need it, assuming I'm still alive. I'll pay whatever it costs within the limits of what I can afford at that time.

As for the need to take a reverse mortgage, it would be clear indication that I can't afford my home anymore. I would sell, invest the proceeds, and rent a place I can afford with the increased portfolio withdrawals. Living below one's means is fundamental to the Bogleheads philosophy and I'm allergic to personal debt.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

aaronl
Posts: 346
Joined: Thu Apr 03, 2008 8:54 pm

Re: Variable Percentage Withdrawal (VPW) for Advanced Users

Post by aaronl » Mon May 28, 2018 1:52 am

longinvest wrote:
Sun May 27, 2018 10:06 pm
As for the need to take a reverse mortgage, it would be clear indication that I can't afford my home anymore. I would sell, invest the proceeds, and rent a place I can afford with the increased portfolio withdrawals. Living below one's means is fundamental to the Bogleheads philosophy and I'm allergic to personal debt.
I can imagine situations where a person has a large fraction of their net worth tied up in home equity, and wants to draw down the home equity in retirement along with the rest of the portfolio. Selling the home and renting a different one is one way to do this, but I'm not convinced it's optimal. Selling has high transaction costs and could be tax-inefficient. Moreover, since this hypothetical person has a disproportionate share of their wealth tied up in housing, there's a good chance he or she lives in California, and if so, Prop 13 becomes a factor.

I am not familiar enough with reverse mortgages to recommend them outright, but the concept seems sensible for someone intending to draw down home equity at an advanced age. The risk for the borrower seems much lower than a conventional mortgage, where failing to make payments could lead to foreclosure. It also seems less risky than the alternative strategy of selling and then renting, since you do not have to rely on the portfolio to fund rent every month for the rest of your life (in this sense, home ownership, even with a reverse mortgage involved, is a kind of longevity insurance).

longinvest
Posts: 2876
Joined: Sat Aug 11, 2012 8:44 am

Re: Variable Percentage Withdrawal (VPW) for Advanced Users

Post by longinvest » Mon May 28, 2018 6:24 am

I've started a new topic to discuss reverse mortgages: viewtopic.php?f=2&t=250405.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

ionball
Posts: 74
Joined: Wed Jan 31, 2018 12:17 pm

Re: Variable Percentage Withdrawal (VPW) for Advanced Users

Post by ionball » Mon May 28, 2018 6:51 am

Thanks for providing this resource! It is a great enhancement to the basic VPW spreadsheet and addresses the some important variables in my financial plan.

longinvest
Posts: 2876
Joined: Sat Aug 11, 2012 8:44 am

Re: Variable Percentage Withdrawal (VPW) for Advanced Users

Post by longinvest » Mon May 28, 2018 7:33 am

A surprising side effect of including future pension promises into the portfolio withdrawal calculation and making a completely fresh calculation every year is that it can reduce, somewhat, the volatility of portfolio withdrawals. It's a different and riskier tradeoff than segregating one's money into two parts, a rebalanced portfolio and a non-rolling ladder to fill the gap in payments until the start of a future pension.

It's a mild impact. For a $1,000,000 50/50 stocks/bonds portfolio at age 65, a future $20,000 per year Social Security pension at age 70, and a target $250,000 residual portfolio, the impact of a 50% loss in stocks leads to a 25% portfolio loss. It results in a reduction of 20% of the withdrawal amount instead of a reduction of 25% when calculating a plain VPW withdrawal amount without accounting for future pensions.

I noted, in the VPW topic, that withdrawal smoothing is inefficient. In case of bad markets during the gap period, a ladder would likely provide a smoother outcome and a higher lifelong income stream. In case of good markets during the gap period, the integrated withdrawal calculation is likely to provide a higher lifelong income stream. There's no magic; it's a risk and reward tradeoff. That's why I insist that it's a spreadsheet for advanced users who fully understand the risks.

This particular mild smoothing side effect is different from classical smoothing approaches. It doesn't take into account past withdrawal information (it doesn't calculate averages using past withdrawal amounts) nor does it involve future return predictions (it doesn't adjust withdrawals based on metrics like current price earning ratios or current bond yields).
ionball wrote:
Mon May 28, 2018 6:51 am
Thanks for providing this resource! It is a great enhancement to the basic VPW spreadsheet and addresses the some important variables in my financial plan.
Thanks for the comment.
Last edited by longinvest on Tue May 29, 2018 11:43 pm, edited 1 time in total.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

Cheyenne
Posts: 369
Joined: Sun Jun 14, 2015 6:46 am

Re: Variable Percentage Withdrawal (VPW) for Advanced Users

Post by Cheyenne » Mon May 28, 2018 7:48 am

Thanks for providing this and sorry for asking a spreadsheet technical question. I copied it to my computer and filled in the numbers and now cells B34 - B37 display #NUM!. The formula appears to be correct. Any suggestions? Thanks again.

KarenC
Posts: 65
Joined: Mon Apr 27, 2015 7:25 am

Re: Variable Percentage Withdrawal (VPW) for Advanced Users

Post by KarenC » Mon May 28, 2018 9:21 am

longinvest wrote:
Sun May 27, 2018 8:51 pm
I have developed a spreadsheet for advanced users of the variable-percentage withdrawal (VPW) method. […]
This is great—thank you!

One question: Would future social security be modeled as a future COLA pension? (Your example data suggests as much, but I just want to be sure.)
"How much you know is less important than how clearly you understand where the borders of your ignorance begin." — Jason Zweig

longinvest
Posts: 2876
Joined: Sat Aug 11, 2012 8:44 am

Re: Variable Percentage Withdrawal (VPW) for Advanced Users

Post by longinvest » Mon May 28, 2018 10:42 am

Cheyenne,
Cheyenne wrote:
Mon May 28, 2018 7:48 am
Thanks for providing this and sorry for asking a spreadsheet technical question. I copied it to my computer and filled in the numbers and now cells B34 - B37 display #NUM!. The formula appears to be correct. Any suggestions? Thanks again.
This usually happens when a non-numerical value is put into another input cell (yellow), or when a calculation cell is inadvertently modified. I've configured the online version to give a warning when a non-input cell is about to be modified. Unfortunately, I don't think that cell protections are preserved in the automatically-generated downloadable version.

I suggest trying to use the online version with the same input data to see if there's still a problem.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

longinvest
Posts: 2876
Joined: Sat Aug 11, 2012 8:44 am

Re: Variable Percentage Withdrawal (VPW) for Advanced Users

Post by longinvest » Mon May 28, 2018 10:47 am

Karen,

Thanks for the comment.
KarenC wrote:
Mon May 28, 2018 9:21 am
One question: Would future social security be modeled as a future COLA pension? (Your example data suggests as much, but I just want to be sure.)
Yes, future Social Security should be added as a future COLA pension. For those already receiving Social Security, it should be considered as part of current COLA pensions.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

zip605
Posts: 17
Joined: Wed Feb 27, 2013 5:51 pm
Location: Chicago area

Re: Variable Percentage Withdrawal (VPW) for Advanced Users

Post by zip605 » Mon May 28, 2018 10:53 am

Why not call it VPWA so as not to confuse it with the traditional version.
zip605

longinvest
Posts: 2876
Joined: Sat Aug 11, 2012 8:44 am

Re: Variable Percentage Withdrawal (VPW) for Advanced Users

Post by longinvest » Mon May 28, 2018 11:17 am

zip605 wrote:
Mon May 28, 2018 10:53 am
Why not call it VPWA so as not to confuse it with the traditional version.
The VPW part isn't really different from the traditional presentation.

Users of the plain VPW can do the combination by themselves. To get the same result, they have to use separate VPW calculations (pension bridge, main withdrawal) and a CPW calculation on a side portfolio amount. It's just more pieces to think about independently. The advantage of the traditional approach is that it's more transparent for a user; if the user doesn't fully understand it, he simply won't be able to build a complex model combining multiple parts.

The advanced spreadsheet combines numerous calculations with VPW to suggest a single withdrawal amount. While it's simpler to use, fully understanding how it works is harder. I don't want users to use a method that they don't fully understand, as this could lead to behavioral mistakes like not staying the course when another spreadsheet or method appears (I predict that lots of new methods will be developed and discussed in the future), or not staying the course in bad or good times (e.g. not rebalancing, not taking appropriate withdrawals, etc.) because one didn't understand the implications of the spreadsheet's calculations.

You'll note that I have effectively started a separate topic to discuss this spreadsheet. It isn't the traditional VPW backtesting spreadsheet, and it doesn't provide a simple VPW table that can be used with a single sheet of paper and a pen.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

Cheyenne
Posts: 369
Joined: Sun Jun 14, 2015 6:46 am

Re: Variable Percentage Withdrawal (VPW) for Advanced Users

Post by Cheyenne » Tue May 29, 2018 6:51 am

I suggest trying to use the online version with the same input data to see if there's still a problem.
Thank you.

spammagnet
Posts: 882
Joined: Wed Apr 27, 2016 9:42 pm

Re: Variable Percentage Withdrawal (VPW-adv) for Advanced Users

Post by spammagnet » Tue May 29, 2018 10:09 pm

longinvest wrote:
Sun May 27, 2018 8:51 pm
I have developed a spreadsheet for advanced users of the variable-percentage withdrawal (VPW) method.
...
The spreadsheet is open and free. You can modify it and adapt it to your own preferences and needs.
Attempts to download as Excel consistently results in errors when opening the downloaded file:

Removed Records: Formula from /xl/worksheets/sheet1.xml part
Removed Records: Formula from /xl/worksheets/sheet2.xml part

Does anyone have any suggestions as to how to prevent it? All I did is use 'Download as' on the file menu.

Windows 10 Pro, MS Office Pro Plus 2016

Thanks

longinvest
Posts: 2876
Joined: Sat Aug 11, 2012 8:44 am

Re: Variable Percentage Withdrawal (VPW-adv) for Advanced Users

Post by longinvest » Tue May 29, 2018 10:12 pm

That's annoying.

I guess I'll have to do as I did with my other spreadsheets: develop the master copy with LibreOffice Calc and then export it to Google Sheets and Microsoft Excel. I'll try to do that next weekend.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

spammagnet
Posts: 882
Joined: Wed Apr 27, 2016 9:42 pm

Re: Variable Percentage Withdrawal (VPW-adv) for Advanced Users

Post by spammagnet » Tue May 29, 2018 11:34 pm

longinvest wrote:
Tue May 29, 2018 10:12 pm
That's annoying.

I guess I'll have to do as I did with my other spreadsheets: develop the master copy with LibreOffice Calc and then export it to Google Sheets and Microsoft Excel. I'll try to do that next weekend.
Thanks. I appreciate your investment in time on behalf of strangers.

Post Reply