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: 2905
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.

DOWNLOAD LINKS
Current version: 2.1 (August 14, 2018)
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 Aug 14, 2018 8:11 am, edited 5 times in total.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

AlohaJoe
Posts: 3556
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: 2905
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: 349
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: 2905
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: 79
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: 2905
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: 390
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: 70
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: 2905
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: 2905
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: 2905
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: 390
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: 900
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: 2905
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: 900
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.

User avatar
siamond
Posts: 4032
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond » Sat Jul 21, 2018 10:23 am

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.
Hey longinvest, the download option from Google Sheet still seems to have issues (the resulting spreadsheet has to be 'repaired' by Excel, and who knows what was lost in the process). Did you find time to make a clean downloadable master copy?

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

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

Post by longinvest » Sat Jul 21, 2018 10:32 am

siamond wrote:
Sat Jul 21, 2018 10:23 am
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.
Hey longinvest, the download option from Google Sheet still seems to have issues (the resulting spreadsheet has to be 'repaired' by Excel, and who knows what was lost in the process). Did you find time to make a clean downloadable master copy?
I started working on it, then I realized that I had to do as I usually do starting from scratch using a LibreOffice main copy (to avoid compatibility issues, as you're probably aware). I also wanted to simplify things and expose calculations more openly, so that people could replicate calculations using a pen and sheet of paper, and ... then I got some important day-time job stuff getting in the way (and I also spent lots of quality time with family and friends away from computers). So, I didn't deliver the promised spreadsheet, yet. :annoyed

I'll get it done, hopefully soon, but I don't want to promise a specific date and break my promise again.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

User avatar
siamond
Posts: 4032
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond » Sat Jul 21, 2018 11:43 am

longinvest wrote:
Sat Jul 21, 2018 10:32 am
I'll get it done, hopefully soon, but I don't want to promise a specific date and break my promise again.
No big deal, in all fairness, making a duplicate copy of the Google Sheet is good enough to play around, this is a small spreadsheet.

Could you please spend a little bit of time explaining the underlying logic though? After all, this is for advanced users... I don't mean that you should explain the exact spreadsheet formulas, but providing more details on the computation principles would be very helpful.

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

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

Post by longinvest » Sat Jul 21, 2018 12:52 pm

siamond wrote:
Sat Jul 21, 2018 11:43 am
longinvest wrote:
Sat Jul 21, 2018 10:32 am
I'll get it done, hopefully soon, but I don't want to promise a specific date and break my promise again.
No big deal, in all fairness, making a duplicate copy of the Google Sheet is good enough to play around, this is a small spreadsheet.

Could you please spend a little bit of time explaining the underlying logic though? After all, this is for advanced users... I don't mean that you should explain the exact spreadsheet formulas, but providing more details on the computation principles would be very helpful.
It should (hopefully) be easier to explain with the redesigned spreadsheet...
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

MIretired
Posts: 712
Joined: Fri Sep 06, 2013 12:35 pm

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

Post by MIretired » Sat Jul 21, 2018 2:55 pm

Appreciate the work. The user sheet is visually appealing, too.
I've been playing with it now. (I copied it to my google docs and downloaded as a LibreOffice sheet and works fine, of course.)

Like others--some trouble understanding some of the entries. Like all the SPIA COLA'd inputs/outputs generally, though, so far, I'm not using those, but I got some incite to it.

One cell that doesn't catch rediculous or overvalued inputs: (sheet)AdvVPW.B16 ("target residual port value" (after buying SPIA at age 80)) --if I put in way high #s (anything higher than feasibly possible to happen), it will not notify me. I have to work the # downward until my rec. withdrawal for this year starts changing again as when using realistic reasonable #s. (such as current port =$1mm; enter in (target residual port.) B16= $5mm; and it doesn't say anything, it just calcs as if I entered a max possible target residual port. And so I'm left thinking I'll have $5mm at age 80 after buying the SPIA. lol

And just wondering ; is it just like the standard VPW in regards to the suggested WD this year? And will fluctuate the same way? It just adds the step up in received pension incomes to the formulas?

MIretired
Posts: 712
Joined: Fri Sep 06, 2013 12:35 pm

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

Post by MIretired » Sat Jul 21, 2018 3:10 pm

2nd ?:
Top part of Sheet2, talks about $100,000. SPIA cost for me, and I enter my monthly rec'd payment for that @80 today's dollars.
Does it just scale this to give me a continuous WD rate same as this year(depends on markets every year)(and using my lifelong pensions) down to cells B22,23,24 ?

User avatar
siamond
Posts: 4032
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond » Sat Jul 21, 2018 5:35 pm

longinvest wrote:
Sat Jul 21, 2018 12:52 pm
siamond wrote:
Sat Jul 21, 2018 11:43 am
longinvest wrote:
Sat Jul 21, 2018 10:32 am
I'll get it done, hopefully soon, but I don't want to promise a specific date and break my promise again.
No big deal, in all fairness, making a duplicate copy of the Google Sheet is good enough to play around, this is a small spreadsheet.

Could you please spend a little bit of time explaining the underlying logic though? After all, this is for advanced users... I don't mean that you should explain the exact spreadsheet formulas, but providing more details on the computation principles would be very helpful.
It should (hopefully) be easier to explain with the redesigned spreadsheet...
Seems that you provided a good illustration of the methodology in this post.

This essentially boils down to a net present value computation of the gap and a corresponding extra withdrawal, if I am not mistaken. I reached a fairly similar conclusion for dealing with my early retirement and future cash flows. Still would be good to document the full methodology (and related math) when you find the time. No rush though, your example really helps.

MIretired
Posts: 712
Joined: Fri Sep 06, 2013 12:35 pm

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

Post by MIretired » Sat Jul 21, 2018 10:36 pm

siamond wrote:
Sat Jul 21, 2018 5:35 pm
longinvest wrote:
Sat Jul 21, 2018 12:52 pm
siamond wrote:
Sat Jul 21, 2018 11:43 am
longinvest wrote:
Sat Jul 21, 2018 10:32 am
I'll get it done, hopefully soon, but I don't want to promise a specific date and break my promise again.
No big deal, in all fairness, making a duplicate copy of the Google Sheet is good enough to play around, this is a small spreadsheet.

Could you please spend a little bit of time explaining the underlying logic though? After all, this is for advanced users... I don't mean that you should explain the exact spreadsheet formulas, but providing more details on the computation principles would be very helpful.
It should (hopefully) be easier to explain with the redesigned spreadsheet...
Seems that you provided a good illustration of the methodology in this post.

This essentially boils down to a net present value computation of the gap and a corresponding extra withdrawal, if I am not mistaken. I reached a fairly similar conclusion for dealing with my early retirement and future cash flows. Still would be good to document the full methodology (and related math) when you find the time. No rush though, your example really helps.
Yep. That. And putting in #s shows it's doing that.
And from my previous post ^^, my cells B22,23 become 0s for anything too high in cell B16, so easy to figure.

2pedals
Posts: 471
Joined: Wed Dec 31, 2014 12:31 pm

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

Post by 2pedals » Sun Jul 22, 2018 9:41 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.
Thanks so much, this is really neat work.

GAAP
Posts: 549
Joined: Fri Apr 08, 2016 12:41 pm

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

Post by GAAP » Sun Jul 22, 2018 11:47 am

Minor thought for the future: you might want to add more pensions in the mix.

I have a non-indexed pension, plus social security. My wife has two indexed pensions plus social security. I know couples where each have more than one indexed pension plus social security -- fairly common with government employment, since changing jobs can put you in an entirely different system.

User avatar
siamond
Posts: 4032
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond » Sun Jul 22, 2018 12:06 pm

GAAP wrote:
Sun Jul 22, 2018 11:47 am
Minor thought for the future: you might want to add more pensions in the mix.

I have a non-indexed pension, plus social security. My wife has two indexed pensions plus social security. I know couples where each have more than one indexed pension plus social security -- fairly common with government employment, since changing jobs can put you in an entirely different system.
More generally, I would suggest that the spreadsheet needs to better accommodate for single vs. married couple situations. Knowing that married couples do not have the same age, not the same pension/SS schedule, etc. Then there are significant one-time events to be factored in an NPV computation (e.g. receiving a bequest, downsizing home, large one-time payment like a big wedding, etc).

I did multiple custom models like that for local BH members, and each situation has its own sequence of cash flows, some inflation-adjusted, some partly so, some not at all... Kind of hard to stay general-purpose and reasonably simple. Maybe take a look at the extra income/savings section of cFIREsim, as the author had a similar challenge, and I think did a good job of defining something flexible and understandable.

As a side note, personally, I model the NPV of an arbitrary sequence of (future) cash flows, positive and negative, instead of modeling a gap towards a future fixed income regime. Mathematically, this is exactly the same thing for simple situations, but it seems to me that the former is easier to understand, and better accommodates real-life situations (including one-time events).

latesaver
Posts: 53
Joined: Thu Aug 03, 2017 3:35 pm

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

Post by latesaver » Fri Aug 10, 2018 11:53 am

Why does it make the assumption that retirees will opt for an annuity at age 80?

dflaher
Posts: 20
Joined: Mon Jan 26, 2015 12:08 pm

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

Post by dflaher » Sun Aug 12, 2018 5:25 pm

latesaver wrote:
Fri Aug 10, 2018 11:53 am
Why does it make the assumption that retirees will opt for an annuity at age 80?
I have the same question. If annuities are deemed to provide insurance, then why buy insurance if one doesn't need it (for example if portfolio size can support spending requirements?)

I like the VPW-adv; it presents another way at looking at portfolio withdrawals. I have a few questions about how to use the model and interpret the results:
• Should the amount entered in Cell B8 be the amount an 80 year old would receive now, upon purchasing a $100,000 non-COLA SPIA? Or would it be the amount received at age 80 after purchasing one now (at age 60)?
• How is the annuity’s principal at age 80 determined? Is it the forecasted portfolio balance at age 80, less the desired residual in cell B16?
• How do I interpret cell B24: “Currently projected lifelong income floor at age 80”?

Thanks.

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

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

Post by longinvest » Mon Aug 13, 2018 1:05 pm

I have uploaded version 2.0 of the spreadsheet.

It is available online and to download from the links in the first post.

Main changes:
  • The master spreadsheet has been migrated to LibreOffice Calc. As a result, there are now three versions for user convenience: one online version (Google Sheets) and two downloadable versions (LibreOffice Calc and Microsoft Excel).
  • The presentation of calculations has been improved and simplified.
  • A simulation of unfavorable markets has been integrated in the presentation.
As usual, comments are welcome.

Enjoy!
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

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

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

Post by longinvest » Mon Aug 13, 2018 1:14 pm

Siamond,
siamond wrote:
Sat Jul 21, 2018 5:35 pm
Seems that you provided a good illustration of the methodology in this post.

This essentially boils down to a net present value computation of the gap and a corresponding extra withdrawal, if I am not mistaken. ...
Not exactly. While the simplicity of present value computations is very attractive, in general, it can hide cash-flow problems. As the main concern is, actually, cash-flow during retirement, the spreadsheet takes a lot of care to avoid implying impossible cash flow (e.g. cash-flows requiring debt).

I hope the new version is simpler to understand.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

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

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

Post by longinvest » Mon Aug 13, 2018 1:32 pm

GAAP,
GAAP wrote:
Sun Jul 22, 2018 11:47 am
Minor thought for the future: you might want to add more pensions in the mix.

I have a non-indexed pension, plus social security. My wife has two indexed pensions plus social security. I know couples where each have more than one indexed pension plus social security -- fairly common with government employment, since changing jobs can put you in an entirely different system.
After spending a lot of time thinking about planning problems related to couples, I haven't found a better approach than simply analysing each spouse separately. In other words, it requires two separate spreadsheets. Joint accounts are simply divided by two and each spouse includes the half amount in his spreadsheet. On the bright side, the couple enjoys the sum of both calculations.

One day, at least one of the spouses is likely to end up on her/his own. Joint annuities can lessen the impact of the death of one spouse. Yet, there are scenarios that can't be protected from (other than through excessive underspending) like divorce, or one spouse needing to live separately in a special facility for medical reasons. I think that it is best to simply be intellectually ready for it and simply adjust spending at that point. Doing a separate analysis gives an idea of the impact of such scenarios.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

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

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

Post by longinvest » Mon Aug 13, 2018 1:43 pm

latesaver wrote:
Fri Aug 10, 2018 11:53 am
Why does it make the assumption that retirees will opt for an annuity at age 80?
The new version of the spreadsheet is much softer in suggesting annuitization at 80. Instead, it estimates the impact of not annuitizing and continuing to apply VPW.

Calculations, above age 80, continue to apply VPW on the excess amount over the desired residual portfolio (with the 10% percentage ceiling suggested on the VPW wiki page).
Last edited by longinvest on Mon Aug 13, 2018 1:59 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

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

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

Post by longinvest » Mon Aug 13, 2018 1:50 pm

dflaher wrote:
Sun Aug 12, 2018 5:25 pm
Should the amount entered in Cell B8 be the amount an 80 year old would receive now, upon purchasing a $100,000 non-COLA SPIA? Or would it be the amount received at age 80 after purchasing one now (at age 60)?
It's a Single-Premium Immediate Annuity (SPIA), so it's the amount an 80-year old would receive now. This estimate will improve with time. It's an adaptive method.
dflaher wrote:
Sun Aug 12, 2018 5:25 pm
How is the annuity’s principal at age 80 determined? Is it the forecasted portfolio balance at age 80, less the desired residual in cell B16?
One easy calculation:
  • Portfolio available for annuitization at age 80 = (VPW-percentage-at-current-age / VPW-percentage-at-80) X current-VPW-portfolio
where current-VPW-portfolio is the current portfolio balance minus any funding requirement (pension gaps + residual portfolio). In the new spreadsheet version, it uses amounts from the unfavorable-markets scenario.

The spreadsheet uses standard financial formulas, instead, but the result is the same. The spreadsheet is open and unlocked. Nothing's hidden. Anyone can look at the formulas (and modify them in his/her private copy).
dflaher wrote:
Sun Aug 12, 2018 5:25 pm
How do I interpret cell B24: “Currently projected lifelong income floor at age 80”?
I think that the newer spreadsheet version is clearer.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

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

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

Post by longinvest » Mon Aug 13, 2018 11:02 pm

Here is a screenshot of the Instructions sheet (click on the image to enlarge it):

Image

Here are screenshots of the Advanced VPW sheet (click on images to enlarge them):

Image

Image
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

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

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

Post by longinvest » Tue Aug 14, 2018 5:30 pm

So, how does it work?

I think that it's simpler to explain with an example.

Let's take Joe. Hi is single. He has just retired at age 65 in 2018. He has a $10,000 pension ($833 per month), but the pension doesn't have provisions for cost of living adjustments.

Joe has decided to delay Social Security until age 70, when he'll get $15,000 ($1,250 per month).

Joe has lived below his means, saved, and invested during his work years. Thanks to low fees and good market returns, he has accumulated a $1,000,000 portfolio.

Joe's portfolio has 40% allocated to stocks, 60% allocated to bonds.

Joe doesn't know when he'll die. He knows that very few people make it past age 100 and that it's difficult to predict who in advance. What he knows is that:
  1. he prefers spending more money now when he is still in good health than later, but
  2. he doesn't ever want to end up poor, even if markets behave badly and he lives for an exceptionally long time.
Many years before retirement, Joe has started visiting the Bogleheads forums and has learned about the Variable Percentage Withdrawal (VPW) method. He has downloaded its backtesting spreadsheet. He knows how to use the VPW Table and fully understands how VPW withdrawals fluctuate according to portfolio fluctuations. As his own 40/60 stocks/bonds portfolio has moderate fluctuations, he knows that this would imply similar annual VPW withdrawal fluctuations.

But, VPW withdrawals aren't a retirement plan in themselves. He wants to build a retirement plan that takes into account his work pension, his future Social Security, and that will never leave him poor or without a minimal amount of liquidity (a residual portfolio of approximately $125,000), even in the unlikely case he was to live very long.

Joe downloads the VPW For Advanced Users spreadsheet and reads the Instructions sheet.

INPUT

Joe enters his personal information, for year 2018, into the spreadsheet (yellow fields):
  • Current year: 2018
  • Current age: 65
  • Current portfolio balance: $1,000,000
  • Stock allocation: 40%
  • Target residual portfolio: $125,000
  • Current Pensions Without Cost of Living Adjustment (NO-COLA)
    • Total annual payments: $10,000
  • Future Pensions With Cost of Living Adjustment (COLA)
    • Pension 1 start age: 70
    • Pension 1 annual payment: $15,000
OUTPUT

As a result, the VPW For Advanced Users spreadsheet suggests (in the green field):
  • Portfolio withdrawal in 2018: $50,765
That's it! ... Or is it?

No, that's only the quick "one number" answer that Joe absolutely needed to make his portfolio withdrawal and rebalance his portfolio.

The spreadsheet provides additional information to put this number into its proper context and let Joe assess the robustness of his plan.

Actually, the spreadsheet provides a lot of information, because it shows its detailed calculations. Wouldn't it be nice to get a summary of the most important points? There's no need to ask, the spreadsheet provides its own summary.

SUMMARY

Here's the Summary that the spreadsheet provides, below the portfolio withdrawal suggestion:

Summary
  • In 2018, $10,000 will be received from current pensions. It is suggested to withdraw $50,765 from the portfolio.
  • Given the 40% stock allocation, unfavorable market returns could reduce retirement income by up to $9,162 (15%), from $60,765 (currently) to $51,603 in the future.
  • In case of exceptionally long life (life beyond age 100), retirement income could drop as low as $23,917. This amount could be increased up to $51,763 by using up to $420,724 to buy a 3%-indexed SPIA at age 80.


This is quite convenient. It reminds Joe that he'll be getting $10,000 from his pension in 2018. So, combined with the suggested $50,765 withdrawal, he'll have a total retirement income of $60,765 in 2018. That's before taxes, as usual.

The summary also helps Joe assess the robustness of his plan. It says that unfavorable future markets could reduce total retirement income by up to 15%, down to $51,763 (assuming stocks lost 50% of their value, a 20% loss in a 40/60 stocks/bonds portfolio).

It also informs Joe that if he simply relied on his current pension, Social Security, and the residual $125,000 portfolio, his retirement income could drop down to $23,917 after age 100, as VPW will have depleted the rest of the portfolio at that point. That's unacceptably low to him, even if the chances that he'll still be alive are extremely low. Luckily, the cost of 3%-indexed SPIAs, at age 80, becomes low enough to let him increase this to a minimally sufficient amount without changing his plan or reducing his spending in 2018. How much should he buy? It's too early to say, today. He'll decide in 15 years, at age 80, if he's still alive. Yet, the spreadsheet provides enough information to assess that it's OK to wait before making the decision.

The spreadsheet informs Joe that, at age 80, he could spend as much as $420,724 and increase the $23,917 amount to $51,763, an amount he already considers sufficient in his younger years. That would be too much, for him. He thinks that if he gets older than 100, a minimal retirement income of $40,000 would be sufficient (this would probably cost him $243,000 at age 80).

CONCLUSION

That's what the VPW For Advanced Users spreadsheet is about. It's a tool (1) that calculates a portfolio withdrawal for the current year, based on the VPW method, given a wider retirement plan involving pensions and a residual portfolio, and (2) that provides ample information to assess the robustness of the overall retirement plan.

I didn't get into the detailed calculations illustrated in the spreadsheet. This post was long enough, already.

What do you think? Can it be a useful tool for you?
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

Post Reply