Variable Percentage Withdrawal (VPW)

[longinvest]

There are two approaches to bridge Social Security. There's the older approach of using a dedicated CD/TIPS ladder or savings account as explained in the post Delay Social Security to age 70 and Spend more money at 62. That's what you illustrated in your example, above, with a dedicated $200,000 bridge.

A simpler approach is to use the new VPW Accumulation And Retirement Worksheet with a single retirement portfolio. The investor could, for example, put the entire $1,000,000 into a 40/60 stocks/bonds portfolio, like the Vanguard LifeStrategy Conservative Growth Fund (VSCGX), and simply use the worksheet to determine withdrawals. This newer approach is illustrated in details, creating monthly retirement income, in the thread A Simple Bogleheads Retirement Using Variable Percentage Withdrawals (VPW Forward Test).

Using the simpler approach, the real life dedicated bridge funding is subject to stock market variability and should be included in assessing downside risk. Can you confirm that using VPW to assess the risk associated with a 50% decline in equities that VPW correctly subjects the bridge funding to the risk of loss?

There's a detailed explanation of the calculations of new worksheet in this post of the VPW forward test thread.

Am I correct that VPW now applies the loss to the entire portfolio before subtracting out (reserving) the bridge funding and then computes the potential after loss withdrawal on the remaining portfolio?

In other words, if one used the older approach and funded the bridge in real life with TIPS or a CD ladder, and then suffered a 50% equities decline, the withdrawal risk would be less than VPW indicates because in real life the bridge would not be subject to the decline?

I'm pretty sure VPW does this but I'm looking for confirmation that I correctly understand that math behind the VPW spreadsheet.

The old and new approaches differ; it shouldn't be surprising if they result into slightly different outcomes.

But, if we want to compare both approaches, we must do it in a fair manner. Let's do that.

Let's assume that we have a 62 years old retiree with a $1,000,000 portfolio who delays Social Security to age 70 to receive $25,000/year.

Old Approach (separate portfolios)

The retiree puts (8 X $25,000) = $200,000 into bonds (CD/TIPS ladder, or savings account) to bridge the gap in Social Security payments until age 70. We'll call this the bridge bond portfolio.

The retiree puts the remaining ($1,000,000 - $200,000) = $800,000 into a 50/50 stocks/bonds portfolio. We'll call this the VPW portfolio.

Note that the overall $1,000,000 portfolio has, effectively, a 40/60 stocks/bonds allocation at age 62.

The VPW portfolio, though, has a 50/50 stocks/bonds allocation. At age 62, the VPW percentage for a 50/50 stocks/bonds portfolio, in the VPW Table , is 4.6%.

So, at age 62, the retiree withdraws $25,000 from the bridge $200,000 bond portfolio, and ($800,000 X 4.6%) = $36,800 from the $800,000 50/50 VPW portfolio, for a total of $61,800 available for taxes and expenses.

If stocks lost -50% of their value just before the withdrawal, the VPW portfolio would shrink to $600,000. The retiree would withdraw $25,000 from the bridge bond portfolio, and ($600,000 X 4.6%) = $27,600 from the VPW portfolio, for a total of $52,600. In other words, as stocks could easily lose half of their value at any time, at least (($61,800 - $52,600) / 12) = $767/month must be budgeted for optional discretionary spending that could be eliminated without affecting the retiree's comfort.

New Approach (single portfolio)

The retiree puts the entire $1,000,000 into a 40/60 stocks/bonds portfolio (similar to the overall portfolio allocation when using the old approach, to keep the comparison fair) and uses the Retirement sheet of the VPW Accumulation And Retirement Worksheet to determine withdrawals.

At age 62, the spreadsheet suggests to take a $61,120 withdrawal and warns about a $734/month impact for a -50% stocks loss.

Comparison

The outcomes aren't perfectly identical. The initial withdrawal differs by 1% ($61,800 with the old approach and $61,120) and the monthly impact of a -50% stocks loss differs by $33/month (a drop of -$767 in monthly income with the old approach and -$734 with the new approach).

But, I think it's fair to say that both approaches yield similar-enough outcomes. I don't think that the differences would be noticeable in real life.

[longinvest]

Forum member 50ismygoal is having problems to use the Google Sheets version of the VPW Accumulation And Retirement Worksheet on an iPad:

Longinvest, for some reason I can’t be the worksheet to work, ...

After clicking on the worksheet's link, you need to:

1. Sign into your Google account (if not already signed in).
2. Make a copy of the file as follows: File -> Make a copy...
3. Once this is made, the copy is yours to modify.

Does this help?

Perhaps because I’m doing this on an iPad, I can’t seem to make a go of it.

I'm seeking the help of an iPad user who could explain how to copy and modify the worksheet in this device.

Thanks.

[Rolyatroba]

I'm seeking the help of an iPad user who could explain how to copy and modify the worksheet in this device.

There seems to be an app for this...haven't tried myself though.
https://support.google.com/docs/answer/ ... DiOS&hl=en

[longinvest]

I'm seeking the help of an iPad user who could explain how to copy and modify the worksheet in this device.

There seems to be an app for this...haven't tried myself though.
https://support.google.com/docs/answer/ ... DiOS&hl=en

Thanks for the link.

[Emilyjane]

I use it successfully on the sheets app on my iPad, following your above instructions. Initially tried to open from google drive app, didn’t work till I realized I needed the sheets app.

[50ismygoal]

I figured out how to use Sheets via Safari with this link. But I’m confused about the numbers. The calculator says my withdrawal amount is much higher than I’m planning on or needing, but the percentage shown (4.1%) is closer to my expectations. The withdrawal amount jumps significantly when I enter SS info, to be started in 18 years. But the withdrawal amount is much higher than the 4.1% would provide. If the VPN sheet is saying I can withdraw this larger amount, I would think the percentage listed would jump commensurately. Hence my confusion. Any help would be appreciated.

[longinvest]

50ismygoal, I explained how pensions are mathematically handled this earlier post:

Can you please point me to where I can see how the pensions are handled (e.g. Mathematically, how do the pensions affect the withdrawal rate?)

Bnjneer,

I'll use the example of the forward test described in this post, which involves two pensions, a delayed one and a fixed one.

If you download the new VPW Accumulation And Retirement Worksheet, you'll be able to enter the data of the forward test and look at the "Calculations" section below the main screen of the Retirement sheet.

Mainly, the VPW worksheet is doing three calculations: a calculation for the delayed $2,000 Social Security pension, a calculation for the fixed $1,000 pension, and a VPW withdrawal calculation. Let's do that:

Social Security

The retiree will get $2,000/month in 5 years. That's $24,000/year. The VPW Table tells us that the percentage for a 5-year withdrawal with a 60/40 stocks/bonds scenario (e.g. age 95) is 21.5%.

So, we need to put aside (on paper) ($24,000 / 21.5%) = $111,628 for Social Security bridge withdrawals.

Fixed Pension

The $1,000/month work pension isn't indexed to inflation. To dampen the erosion of inflation, only 65.7% of the pension is spent and the rest (34.3%) is invested into the portfolio. That's ($1,000 X 12 X 34.3%) = $4,116/year.

If you want to understand how the 65.7% was determined, I've explained the calculation process in this post. The simpler formula used in the spreadsheet was derived by forum member #Cruncher and is stated in this post.

VPW Withdrawal

The retiree has a $1,000,000 portfolio, but we've put aside (on paper) $111,628 as bridge for Social Security. So, we're left with $888,372 for VPW withdrawals. At age 65 with a 60/40 stocks/bonds portfolio, the percentage in the VPW Table is 5.0%. This gives us ($888,372 X 5.0%) = $44,419.

Putting It All Together

On an annual basis, the retiree plans to withdraw $24,000 in replacement of future Social Security payments, to invest $4,116 to dampen the ravages of inflation on his fixed work pension, and to take a $44,419 VPW withdrawal. This sums up to ($24,000 - $4,116 + $44,419) = $64,303. On a monthly basis, this is ($64,303 / 12) = $5,358.

The small $2 difference with the VPW Worksheet's suggested $5,356 is due to rounding.

[Emilyjane]

Longinvest,

I really like your VPW approach to retirement spending.
I’m wondering if you would comment on my “simple” (somewhat different than your simple plan) approach to the mechanics of using it. Are there any pitfalls I am missing?

I’ve chosen to invest my traditional IRA in Vanguard’s Moderate Growth Lifestyle Fund (60/40). I also have some Total Bond Fund, enough to bring total AA to 50/50 in early retirement. I have my somewhat smaller Roth IRA invested in Aggressive Growth Lifestyle Fund (80/20).

Automatic withdrawal monthly from Mod Growth Lifestyle fund in traditional IRA that is set up that is equal to slightly less than the monthly amount that would be available per your spread sheet in event of a 50% market drop. I would switch the automatic withdrawal to the bond fund in the event of a significant (>20%) market drop. Each month I calculate the difference between the automatic withdrawal and current allowed withdrawal number and transfer that amount to a “slush fund” that resides in my Roth. It is in Prime Money Market. That fund is available for “extra” expenses, such as overseas vacations, gifts to kids, or a new car.

For example, 63 yo with 50/50 portfolio of $1,000,000. For simplicity’s sake, already taking SS and no pension. Calculated monthly portfolio withdrawal at this time is $3887, calculated monthly available after a big loss is $2915. I would set up automatic withdrawal of $2700 (in case big loss is even worse!), then this month, transfer $3887-$2700= $1187 to Roth or checking account. Repeat monthly.

I like that the automatic transfer to my checking account feels like a “retirement paycheck” and I feel comfortable having that amount of money on a regular basis. I like having the slush fund and “saving up” for fancy vacations, but not coming to think of that money as being consistently available. Side benefit, I get a “raise” every birthday as long as the market hasn’t dropped, great birthday present.

I think this could be done with yearly or quarterly transfers too, for someone who wanted to use the spreadsheet less often, but I like the monthly plan. I also like the lifestyle funds so that I do not have to decide which specific fund to withdraw from.

Thanks for any thoughts you have, and for all the work you've done on this approach.

[longinvest]

Emilyjane,

This looks fine.

The VPW worksheet is mostly about the higher-level aspects of portfolio withdrawals (e.g. how much to withdraw from "the portfolio", how much flexibility is required, what's the impact of a -50% stocks loss). It doesn't get into the details of how the portfolio is spread across various accounts and how to handle the lower-level cash flows.

If I understand correctly, you're saying that handling the (more or less) "require flexibility" amount separately feels better to you, and you're proposing to set up a regular "base" withdrawal (constant for 12 months, after which it's changed according to the new reality for the next 12 months) and make separate manual monthly withdrawals for the remaining fluctuating difference between the worksheet's suggested amount and this base amount.

In other words, your approach follows the VPW worksheet's suggestion while using a cash-flow process that matches your personal preferences. This seems perfectly reasonable to me.

[MA405]

I'm seeking the help of an iPad user who could explain how to copy and modify the worksheet in this device.

There seems to be an app for this...haven't tried myself though.
https://support.google.com/docs/answer/ ... DiOS&hl=en

This worked for me on iPad using the native Apple Numbers app for spreadsheets:
1. From the Wiki page select MS Excel download link that takes you to Dropbox page;
2. When the page loads Select "Or continue to Website" on the bottom of the page;
3. When the next page loads, on the right side of the screen click on the download icon (arrow down pointing to underscore);
4. Select "Direct download";
5. Spreadsheet will open. Select "Export" icon on top (square box with arrow out);
6. Select "Save to Files" from the bottom row of icons;
7. Select "iCloud Drive" and a folder within that drive, click Add;
7. Launch Numbers app, navigate to the folder where you saved the file and open the worksheet (the icon will have a large letter E on it);
8. Numbers will import the Excel spreadsheet and open it;
9. The Accumulation and Retirement sheets will show calculation errors. These errors will go away if you delete formulas in cells B6 on both sheets. Not sure of the significance of these formulas, but the spreadsheet and all calculations seem to work fine without them, all calculation results seem to be exactly same as when using Excel on PC. Maybe longinvest could advise if the formulas in cells B6 are critical and should never be deleted. In that case the Numbers app won't work.

[longinvest]

Maybe longinvest could advise if the formulas in cells B6 are critical and should never be deleted. In that case the Numbers app won't work.

Cell B6 detects the presence of errors to prevent the worksheet from presenting senseless calculation results.

On an iPad or an iPhone, it's preferable to install the Sheets app using the instructions linked in Rolyatroba's post.

Those on an Apple computer can install the free open source LibreOffice Calc software instead. Here's the link to the LibreOffice Calc version of the worksheet: https://www.bogleheads.org/wiki/Variabl ... ffice_Calc

[longinvest]

For new readers:

Variable Percentage Withdrawal (VPW) is a method which adapts withdrawal amounts to the retiree's age, asset allocation, and portfolio returns during retirement. It allows the retiree to spend most of the portfolio using return-adjusted withdrawals. By adapting withdrawals to market returns, VPW will never prematurely deplete the portfolio.

VPW is designed to be used along with stable income from Social Security (possibly delayed to age 70), a pension (if any), and (if necessary) an inflation-indexed SPIA*. The VPW Accumulation And Retirement Worksheet gracefully handles combining VPW with current and delayed pensions (with and without cost-of-living adjustments).

* Single Premium Immediate Annuity.

A detailed illustration of how to use the VPW Accumulation And Retirement Worksheet to generate monthly income during retirement is presented in the following thread: viewtopic.php?t=284519.

Here are some key excerpts from three posts of the linked thread which explain how to use the worksheet and how it works:

Retirement funding can be simple. It's sufficient to combine income from Social Security (possibly delayed to age 70) and a pension (if any) with portfolio withdrawals from a balanced Bogleheads portfolio using a sensible approach.
[...]
this thread will document [the] monthly retirement income of a hypothetical person who retires [...] at age 65 with a $1,000,000 globally-diversified balanced portfolio. The retiree has a fixed $1,000 per month pension and is delaying Social Security to age 70 to receive $2,000 per month.
[...]
Monthly portfolio withdrawal amounts will be determined using the new VPW Accumulation And Retirement Worksheet which takes into account current and future pensions (like delayed Social Security), with and without cost-of-living adjustments.
[...]
Monthly total retirement income will consist of:

1. Monthly $1,000 payment from work pension.
2. The monthly VPW withdrawal [...].
3. Eventually, starting at age 70 [...], a $2,000 monthly payment from Social Security.

[...]
We enter the retiree's information into the Retirement sheet of the VPW Accumulation And Retirement Worksheet and we get:



The VPW Worksheet suggests to take a $5,356 withdrawal and tells us that total annual retirement income is currently estimated at $76,276. In case of unfavorable market returns where stocks would lose 50%, the portfolio would lose -$300,000 and retirement income would be reduced by -20% to $61,286. The retiree is OK with that.

Here's how the VPW Accumulation And Retirement Worksheet got to its suggestion of a $5,356 monthly withdrawal at the end of June 2019.

Mainly, the VPW worksheet is doing three calculations: a calculation for the delayed $2,000 Social Security pension, a calculation for the fixed $1,000 pension, and a VPW withdrawal calculation. Let's do that:

Social Security

The retiree will get $2,000/month in 5 years. That's $24,000/year. The VPW Table tells us that the percentage for a 5-year withdrawal with a 60/40 stocks/bonds scenario [...] is 21.5%.

So, we need to put aside (on paper) ($24,000 / 21.5%) = $111,628 for Social Security bridge withdrawals.

Fixed Pension

The $1,000/month work pension isn't indexed to inflation. To dampen the erosion of inflation, only 65.7% of the pension is spent and the rest (34.3%) is invested into the portfolio. That's ($1,000 X 12 X 34.3%) = $4,116/year.

To understand how the 65.7% was determined, I've explained the calculation process in this post. The simpler formula used in the spreadsheet was derived by forum member #Cruncher and is stated in this post.

VPW Withdrawal

The retiree has a $1,000,000 portfolio, but we've put aside (on paper) $111,628 as bridge for Social Security. So, we're left with $888,372 for VPW withdrawals. At age 65 with a 60/40 stocks/bonds portfolio, the percentage in the VPW Table is 5.0%. This gives us ($888,372 X 5.0%) = $44,419.

Putting It All Together

On an annual basis, the retiree plans to withdraw $24,000 in replacement of future Social Security payments, to invest $4,116 to dampen the ravages of inflation on the fixed work pension, and to take a $44,419 VPW withdrawal. This sums up to ($24,000 - $4,116 + $44,419) = $64,303. On a monthly basis, this is ($64,303 / 12) = $5,358.

The small $2 difference with the VPW Worksheet's suggested $5,356 is due to rounding.

Note that total retirement income also includes the $1,000 work pension payment for a total of ($64,303 + (12 X $1,000)) = $76,303 on an annual basis available for taxes and expenses.

The VPW worksheet also calculates a "Required Flexibility" that must be maintained by the retiree. To do so, it first applies a -50% loss to the stocks allocation and then repeats its withdrawal calculations.

The total portfolio balance at the end of June was $1,000,000 and its asset allocation was 60/40 stocks bonds. A -50% stock loss results into a (-50% X 60%) = -30% portfolio loss. That's a (-30% X $1,000,000) = -$300,000 portfolio loss, reducing the portfolio to ($1,000,000 - $300,000) = $700,000 after the loss.

Some of the previous calculations remain unchanged:

Mainly, the VPW worksheet is doing three calculations: a calculation for the delayed $2,000 Social Security pension, a calculation for the fixed $1,000 pension, and a VPW withdrawal calculation. Let's do that:

Social Security

The retiree will get $2,000/month in 5 years. That's $24,000/year. The VPW Table tells us that the percentage for a 5-year withdrawal with a 60/40 stocks/bonds scenario [...] is 21.5%.

So, we need to put aside (on paper) ($24,000 / 21.5%) = $111,628 for Social Security bridge withdrawals.

Fixed Pension

The $1,000/month work pension isn't indexed to inflation. To dampen the erosion of inflation, only 65.7% of the pension is spent and the rest (34.3%) is invested into the portfolio. That's ($1,000 X 12 X 34.3%) = $4,116/year.

To understand how the 65.7% was determined, I've explained the calculation process in this post. The simpler formula used in the spreadsheet was derived by forum member #Cruncher and is stated in this post.

VPW Withdrawal

The retiree has a $700,000 portfolio, but we've put aside (on paper) $111,628 as bridge for Social Security. So, we're left with $588,372 for VPW withdrawals. At age 65 with a 60/40 stocks/bonds portfolio, the percentage in the VPW Table is 5.0%. This gives us ($588,372 X 5.0%) = $29,419.

Putting It All Together

On an annual basis, the retiree plans to withdraw $24,000 in replacement of future Social Security payments, to invest $4,116 to dampen the ravages of inflation on the fixed work pension, and to take a $29,419 withdrawal. This sums up to ($24,000 - $4,116 + $29,419) = $49,303. On a monthly basis, this is ($49,303 / 12) = $4,109 which is ($4,109 - 5,358) = -$1,249 smaller than the previously calculated (without applying a stock loss) withdrawal amount.

Note that total retirement income also includes the $1,000 work pension payment for a total of ($49,303 + (12 X $1,000)) = $61,303 on an annual basis available for taxes and expenses after the loss.

The retiree must maintain the flexibility to easily cut expenses by up to approximately -$1,250/month because stocks could easily lose 50% of their value within a short term period. In other words, at least $1,250 must be budgeted for optional discretionary spending that could be eliminated without affecting the retiree's comfort.

The worksheet takes care of doing all of these calculations and provides simple-to-understand information:

• The suggested portfolio withdrawal amount.
• The required flexibility that must be maintained at all times in the budget.
• The portfolio loss amount in case of a -50% stocks loss and the after-loss portfolio size.
• Annual income available for taxes and expenses as well as an estimate in case of a -50% stocks loss.

Using the worksheet during retirement is very easy:

1. Every year, quarter, or month (depending on the selected withdrawal frequency), enter or update the age, portfolio, and pension data in yellow cells.
2. Withdraw the amount suggested in green cells.
3. Put aside enough money for taxes.
4. Enjoy retirement with the rest of the money (including pension payments).
5. Always budget sufficient optional discretionary spending (that could be eliminated without affecting comfort) to match the required flexibility amount.

At age 80, the worksheets estimates and displays an Income Floor After 100. If necessary, at that age it'll be important to consider using part (but not all) of the remaining portfolio to buy an inflation-indexed SPIA so that the income floor is sufficient to live comfortably, independently of future portfolio withdrawals to dampen the low-probability but severe financial risk associated with life beyond age 100. The withdrawal percentage also stops growing when it reaches 10% to conserve liquidity all lifelong.

[fourniks]

Sorry if this has been posted before. For ALL of the age questions/drop-downs in the Retirement tab, should those ages correspond to the youngest spouse?

Thanks,

Four

[longinvest]

Sorry if this has been posted before. For ALL of the age questions/drop-downs in the Retirement tab, should those ages correspond to the youngest spouse?

Yes, of course. It's also important to consider what will happen after the passing of one spouse and plan for the projected residual retirement income (pensions and portfolio withdrawals) to be sufficient for the survivor. One of the two Social Security payments will stop, for example.

[retiringwhen]

Sorry if this has been posted before. For ALL of the age questions/drop-downs in the Retirement tab, should those ages correspond to the youngest spouse?

Yes, of course. It's also important to consider what will happen after the passing of one spouse and plan for the projected residual retirement income (pensions and portfolio withdrawals) to be sufficient for the survivor. One of the two Social Security payments will stop, for example.

To address the survivor scenario, I just reduced the amount of the smaller SS benefit by half (it is very small to start with) so the the VPW kind of undervalues it early and overvalues it after first spouse passes. That was simple and justifiable, I am sure there are other ways that are more thought out.

[longinvest]

I'm answering here a question asked by forum member CrazyPuli on another thread:

I have a question regarding the SS bridge fund. I am at a 70/30 allocation.

Is that bridge amount also at 70/30 or is it additional bonds? If I did that I would start at 60/40 and work my way back to 70/30 over 10 years. (I am retiring at 60 and plan on starting SS at 70.)

CrazyPuli, there are two approaches to filling the 10-year gap in payments between retirement and the start of Social Security.

Here's what our wiki's VPW page says:

When using the VPW Table or the older VPW Backtesting Spreadsheet, missing payments between retirement and the start of a pension such as Social Security (possibly delayed to age 70) can be provided by using a simple CD ladder or short-term bond fund. For the purposes of VPW calculations, the money set aside in this CD ladder or short-term bond fund should not be considered as part of the portfolio.

The newer VPW Accumulation And Retirement Worksheet gracefully handles delayed pensions such as Social Security (possibly delayed to age 70) with an undivided portfolio.

If you intend to make withdrawal amount calculations by hand with the VPW Table, it's simpler to put the bridge money into a CD ladder or short-term bond fund. This approach was first suggested by Cut-Throat in this post: Delay Social Security to age 70 and Spend more money at 62.

The newer approach is to use the VPW Accumulation And Retirement Worksheet instead. The worksheet works with the retiree's portfolio and requires no separate investment to fill the gap until the start of a delayed pension. As a bonus, it can also deal with pensions which lack cost-of-living adjustments.

The use of a single portfolio with the new VPW Accumulation And Retirement Worksheet is much simpler and it's illustrated in the thread: A Simple Bogleheads Retirement Using Variable Percentage Withdrawals (VPW Forward Test).

[Unladen_Swallow]

Longinvest,

I hadn't read up on VPW the last couple of years and saw you had added many features. VPW has been one of my most important takeaways and tools in the years I have lurked on this forum. Thank you!

Question:

The old Spreadsheet had an entry for how many years of retirement (or maybe it was final age) was desired. The portfolio went to zero at that time. I don't see this input in the new Retirement worksheet. Have I overlooked anything?

Not retired yet, but I'm in early planning.

- Unladen Swallow

[longinvest]

The old Spreadsheet had an entry for how many years of retirement (or maybe it was final age) was desired. The portfolio went to zero at that time. I don't see this input in the new Retirement worksheet. Have I overlooked anything?

Unladen_Swallow, the new spreadsheet implements the method described in our wiki, while also taking into account current and delayed pensions (with and without cost-of-living adjustments). In particular, it uses the equivalent of the VPW table (which is based on a last withdrawal age of 99) to determine withdrawals but, starting at age 80, it explicitly informs the retiree about the residual income after 100 (that's pensions without any portfolio withdrawals) suggesting to use part (not all) of the portfolio to buy a cost-of-living adjusted SPIA if it's insufficient. It also caps withdrawals at a maximum of 10% in old age, to preserve liquidity all lifelong.

* Single Premium Immediate Annuity.

In addition, the new worksheet has an option for monthly withdrawals, which is really cool when combined with a small cash cushion as illustrated in this thread.

[Unladen_Swallow]

The old Spreadsheet had an entry for how many years of retirement (or maybe it was final age) was desired. The portfolio went to zero at that time. I don't see this input in the new Retirement worksheet. Have I overlooked anything?

Unladen_Swallow, the new spreadsheet implements the method described in our wiki, while also taking into account current and delayed pensions (with and without cost-of-living adjustments). In particular, it uses the equivalent of the VPW table (which is based on a last withdrawal age of 99) to determine withdrawals but, starting at age 80, it explicitly informs the retiree about the residual income after 100 (that's pensions without any portfolio withdrawals) suggesting to use part (not all) of the portfolio to buy a cost-of-living adjusted SPIA if it's insufficient. It also caps withdrawals at a maximum of 10% in old age, to preserve liquidity all lifelong.

* Single Premium Immediate Annuity.

In summary, the new spreadsheet will never bring the portfolio completely down to zero, But it remains the responsibility of the retiree to make sure there's sufficient income from pensions (and cost-of-living adjusted SPIA, after age 80, if necessary).

Thank you longinvest. This is my takeaway:

- VPW version 1.4 is set up with yearly withdrawal rates assuming 99yrs is last withdrawal age (per VPW table). Number of years in retirement (or age of final withdrawal) is not an input anymore.
- After 80, withdrawals are capped at 10% (guaranteeing an income floor is recommended). Therefore the portfolio never goes to zero.
- Am I right to also infer that I cannot make Social Security input cell "zero"? When I tried to do that, it gave me an error.

I am still reading the various VPW posts I missed in the last year or two. I have some questions relating to early retirement with access to a limited portion of total portfolio for a period of time. But perhaps my questions have already been asked and answered. If you or others have some thoughts, I'll be happy to hear. Until then, back to reading what I missed.

Unladen_swallow

[longinvest]

- After 80, withdrawals are capped at 10% (guaranteeing an income floor is recommended). Therefore the portfolio never goes to zero.

It will get quite small, though, if the retiree survives past age 100. Here's what our wiki says:

Around age 80, if you're still alive, it is important to consider using part (but not all) of your remaining portfolio to buy an inflation-indexed Single Premium Immediate Annuity (SPIA), so that total non-portfolio income (including Social Security, pension, and other lifelong income) is sufficient to live comfortably, independently of future portfolio withdrawals. This aims to reduce the financial risks associated with living past age 100.

About this:

- Am I right to also infer that I cannot make Social Security input cell "zero"? When I tried to do that, it gave me an error.

First, let me insist that VPW is meant to be used along with non-portfolio income. You should never forget that VPW cannot create returns that markets don't deliver; it's important to combine VPW with pensions (current or delayed).

Second, if you really want to remove "Defined Benefit Pension #1", you must empty all its yellow cells, including the cell containing the "Name" of the pension. This will make errors disappear.

back to reading what I missed.

Enjoy!

[sandramjet]

Around age 80, if you're still alive, it is important to consider using part (but not all) of your remaining portfolio to buy an inflation-indexed Single Premium Immediate Annuity (SPIA),

And since those don't actually exist anymore, perhaps a revision is needed?

[longinvest]

Around age 80, if you're still alive, it is important to consider using part (but not all) of your remaining portfolio to buy an inflation-indexed Single Premium Immediate Annuity (SPIA),

And since those don't actually exist anymore, perhaps a revision is needed?

CPI-indexed SPIAs don't exist anymore but, since 2012, the Federal Reserve has adopted a 2% inflation target and has clearly expressed its intention to keep inflation on a 2% target to allow the public "to make accurate longer-term economic and financial decisions".

So, technically speaking, a 2%-indexed SPIA is an inflation-indexed one; it's indexed to "promised inflation". But I'll add clarifications to the wiki.

[Unladen_Swallow]

It will get quite small, though, if the retiree survives past age 100. Here's what our wiki says:

Around age 80, if you're still alive, it is important to consider using part (but not all) of your remaining portfolio to buy an inflation-indexed Single Premium Immediate Annuity (SPIA), so that total non-portfolio income (including Social Security, pension, and other lifelong income) is sufficient to live comfortably, independently of future portfolio withdrawals. This aims to reduce the financial risks associated with living past age 100.

Concurs with my understanding. Yes, an SPIA is certainly one of our considerations were we to live past 80.

First, let me insist that VPW is meant to be used along with non-portfolio income. You should never forget that VPW cannot create returns that markets don't deliver; it's important to combine VPW with pensions (current or delayed).

Agree completely. My reason for removing the SS input was 1) getting familiar with VPW worksheet and 2) playing with early retirement options. More below.

If early retirement is feasible, we wish to draw from our Taxable account to fund expenses until 59.5. After which time we would use remaining taxable and our tax deferred portfolio to fund the rest of our lives.

If we treat the entire portfolio as one in VPW, the suggested withdrawal amounts would be based on the whole portfolio while we make withdrawals only from a specific portion of it until 59.5 Is there a possibility that the Taxable account is drawn too low too soon in response to unfortunate market swings? I am using various numbers to see if it is realistic, plausible, possible, or unrealistic scenario.

To that end, perhaps treat it as 2 portfolios or some other strategy/safeguards?

The goal would not be necessarily to drawdown taxable to zero prior to 59.5, instead to just not deplete it prior to 59.5.

Not sure if I'm clear as mud

Second, if you really want to remove "Defined Benefit Pension #1", you must empty all its yellow cells, including the cell containing the "Name" of the pension. This will make errors disappear.

Excellent! Thank you.

[longinvest]

Here's part of a post where I explained that the VPW Accumulation And Retirement Worksheet can be used as part of an overall lifelong approach that implements all of the 10 Bogleheads investment principles:

The 10 principles of our Bogleheads investment philosophy are logical and work:

1. Develop a workable plan (don't use illogical plans)
2. Invest early and often (live below your means so that there's money to invest, and invest it immediately)
3. Never bear too much or too little risk (use a balanced portfolio)
4. Diversify (use broad total-market funds, and invest both domestically and internationally)
5. Never try to time the market (don't wait for market bottom, and don't set your asset allocation based on market valuations)
6. Use index funds when possible (use cap-weighted index funds or, try to mimic cap-weighted market exposure when your employer's plan doesn't have broad index funds)
7. Keep costs low (use low-cost funds, avoid commissions, and don't give away a percent of your portfolio to a financial advisor)
8. Minimize taxes (use tax-sheltered accounts)
9. Invest with simplicity (Occam's razor: the simplest solution is most likely the right one)
10. Stay the course (once your asset allocation is chosen and your plan is set in motion, follow through with your plan and rebalance your portfolio)

It's pretty simple, but not always easy due to a long list of potential behavioral pitfalls... unless one "cheats" as follows.

An easy way to put in practice all of the 10 principles is to simply:

• Live below one's means and invest the difference in a low-cost globally-diversified balanced index One-Fund Portfolio which is automatically rebalanced.
• Invest in tax-sheltered accounts and only use taxable accounts when no tax-sheltered space is left available.
• Use the VPW Accumulation And Retirement Worksheet as a guide for portfolio contributions (during accumulation) and portfolio withdrawals (during retirement).

I wish you a Happy New Year 2020!

[longinvest]

On another thread, forum member Lazareth asked two questions:

1. The VPW-Accumulation-And-Retirement-Worksheet produces an "Annual Portfolio Withdrawal" amount highlighted in a green cell, B14, and instructs the user to withdraw that amount. However it also produces an "Initial Portfolio Withdrawal" amount on line 61, cell B61. Is there an explanation of that, of how the user utilizes that number?
2. Can someone point me to an explanation of the pension bridge cost calculations ?

I think that both questions are answer in this earlier post.

[NearlyRetired]

Hi longinvest

Firstly, thanks for putting the effort into producing this spreadsheet - it is very powerful, yet simple to use (something I know that takes a lot of time and effort to achieve).

I have had a bit of a look under the bonnet, as it were, and have a few questions for you, if I may:

• If I wanted to use a 45/55 equity/bond split, presumably I just need to change one of the rows in the lists tab A4:B10 to modify a description and corresponding equity percentage?

• Being UK based I am interested in how usable it is for over here. Again, looking at the lists tab, the only thing I can see that would be US/Canada centric would be the growth trends - A132:B135, and specifically A132:B133 - (the US inflation target is the same as here @ 2%) is that correct?

• If the last point is correct, how easy/difficult would it be to find the information for those two figures - I'm not sure where to look and whether those are real or nominal returns (I presume nominal hence the need for inflation figures)?

I plan to use this sheet for when I retire this month and going forward, if I can put in some UK specific figures, as I think this is a really good way of managing withdrawals.

[longinvest]

• If I wanted to use a 45/55 equity/bond split, presumably I just need to change one of the rows in the lists tab A4:B10 to modify a description and corresponding equity percentage?

NearlyRetired, I would just pick 40/60 (or 50/50). It's all very approximate. There's no need for false precision. Modifying the list tab without breaking calculations can be quite tricky; I wouldn't recommend that.

• Being UK based I am interested in how usable it is for over here. Again, looking at the lists tab, the only thing I can see that would be US/Canada centric would be the growth trends - A132:B135, and specifically A132:B133 - (the US inflation target is the same as here @ 2%) is that correct?

• If the last point is correct, how easy/difficult would it be to find the information for those two figures - I'm not sure where to look and whether those are real or nominal returns (I presume nominal hence the need for inflation figures)?

The growth trend is for a global portfolio, so it's good for any country. In other words, it shouldn't be changed.

The inflation target should only be changed for countries with a different one (e.g. when it's not 2%). The inflation target is used for dampening the loss in purchase power, over time, of fixed pensions. If a country has no inflation target, it's probably best to avoid using a fixed (nominal) pension. (It's always best to use cost-of-living adjusted pensions, when possible). As the UK's target is 2%, no change is required.

I plan to use this sheet for when I retire this month and going forward, if I can put in some UK specific figures, as I think this is a really good way of managing withdrawals.

In summary, there's no need to change anything for the UK, and 40/60 is close enough to 45/55.

[NearlyRetired]

• If I wanted to use a 45/55 equity/bond split, presumably I just need to change one of the rows in the lists tab A4:B10 to modify a description and corresponding equity percentage?

NearlyRetired, I would just pick 40/60 (or 50/50). It's all very approximate. There's no need for false precision. Modifying the list tab without breaking calculations can be quite tricky; I wouldn't recommend that.

• Being UK based I am interested in how usable it is for over here. Again, looking at the lists tab, the only thing I can see that would be US/Canada centric would be the growth trends - A132:B135, and specifically A132:B133 - (the US inflation target is the same as here @ 2%) is that correct?

• If the last point is correct, how easy/difficult would it be to find the information for those two figures - I'm not sure where to look and whether those are real or nominal returns (I presume nominal hence the need for inflation figures)?

The growth trend is for a global portfolio, so it's good for any country. In other words, it shouldn't be changed.

The inflation target should only be changed for countries with a different one (e.g. when it's not 2%). The inflation target is used for dampening the loss in purchase power, over time, of fixed pensions. If a country has no inflation target, it's probably best to avoid using a fixed (nominal) pension. (It's always best to use cost-of-living adjusted pensions, when possible). As the UK's target is 2%, no change is required.

I plan to use this sheet for when I retire this month and going forward, if I can put in some UK specific figures, as I think this is a really good way of managing withdrawals.

In summary, there's no need to change anything for the UK, and 40/60 is close enough to 45/55.

Thanks for the speedy response longinvest....... I'll start using this then from next month

[smectym]

Longinvest,

I really like your VPW approach to retirement spending.
I’m wondering if you would comment on my “simple” (somewhat different than your simple plan) approach to the mechanics of using it. Are there any pitfalls I am missing?

I’ve chosen to invest my traditional IRA in Vanguard’s Moderate Growth Lifestyle Fund (60/40). I also have some Total Bond Fund, enough to bring total AA to 50/50 in early retirement. I have my somewhat smaller Roth IRA invested in Aggressive Growth Lifestyle Fund (80/20).

Automatic withdrawal monthly from Mod Growth Lifestyle fund in traditional IRA that is set up that is equal to slightly less than the monthly amount that would be available per your spread sheet in event of a 50% market drop. I would switch the automatic withdrawal to the bond fund in the event of a significant (>20%) market drop. Each month I calculate the difference between the automatic withdrawal and current allowed withdrawal number and transfer that amount to a “slush fund” that resides in my Roth. It is in Prime Money Market. That fund is available for “extra” expenses, such as overseas vacations, gifts to kids, or a new car.

For example, 63 yo with 50/50 portfolio of $1,000,000. For simplicity’s sake, already taking SS and no pension. Calculated monthly portfolio withdrawal at this time is $3887, calculated monthly available after a big loss is $2915. I would set up automatic withdrawal of $2700 (in case big loss is even worse!), then this month, transfer $3887-$2700= $1187 to Roth or checking account. Repeat monthly.

////I like that the automatic transfer to my checking account feels like a “retirement paycheck”////and I feel comfortable having that amount of money on a regular basis. I like having the slush fund and “saving up” for fancy vacations, but not coming to think of that money as being consistently available. Side benefit, I get a “raise” every birthday as long as the market hasn’t dropped, great birthday present.

I think this could be done with yearly or quarterly transfers too, for someone who wanted to use the spreadsheet less often, but I like the monthly plan. I also like the lifestyle funds so that I do not have to decide which specific fund to withdraw from.

Thanks for any thoughts you have, and for all the work you've done on this approach.

Good point EmilyJane: For many retired investors in decumulation, the simulation of a monthly paycheck or monthly pension check has a positive psychological effect. We’ve done it through annuities, and also through using VPW to set up automatic monthly withdrawals from IRA’s. The modality enhances the sense of retirement security (especially when accompanied by social security checks rolling in). The VPW methodology makes good common sense, and so provides reassurance. I set up the structure of withdrawals that way primarily to reassure spouse, but I like it too.

[longinvest]

There were a few forum threads, lately, seeking to define a "retirement number" (a specific amount of money target for a retirement portfolio). I think that targeting a fixed (inflation-indexed) retirement number is illogical. I briefly explained why in the post I've copied below, linking back to an earlier series of 4 consecutive posts of this threads starting here.

I also mentioned the approach I use to answer the question "How much should I save?". It's an approach that takes account of the investor's current situation (salary, age, etc.) and portfolio size, of future pensions, and of a desired retirement or financial independence age. It suggests a portfolio contribution amount for the current year (expressed as a monthly, semi-monthly, or biweekly contribution amount). It's the approach found in the Accumulation Worksheet of the VPW Accumulation And Retirement Worksheet.

It's similar to the Retirement Worksheet in that, every year, input data must be updated (age has increased, the portfolio balance has changed, etc.) and a new portfolio contribution amount, adapted to the investor's current circumstances and portfolio size, will be suggested.

Here's the post:

How is your "number" defined?

I have no "number".

A retirement number makes no sense, not even an inflation-adjusted one. Someone retiring with a $1,000,000 inflation-adjusted portfolio in 1982 had a much more valuable retirement portfolio than someone retiring with a $1,000,000 inflation-adjusted portfolio in 1966. On the other hand, it was easier to accumulate a bigger inflation-adjusted retirement portfolio for a 1966 retiree than for a 1982 retiree. (See the 4 posts starting here).

That's why, instead of targeting a specific retirement number, I use an adaptive retirement saving process. Every year, I adapt the amount I save for retirement based on my changing age, salary, portfolio balance and asset allocation, target retirement age (or financial independence age), annual pension contribution amounts, and future pension payment amounts.

I do this using the Accumulation Worksheet of our wiki's VPW Accumulation And Retirement Worksheet. It's easy to use. Here's a screenshot:



Future Social Security benefits can be estimated using the Social Security Administration Online Calculator.

[Sandi_k]

@longinvest, I had all my figures available due to end-of-year updates, so I finally got around to trying out the VPW worksheet.

Currently 54, planning on retirement at 60, with a generous pension (high 3 x years of service x 2.5% x .85 for survivor benefits).

When I enter current portfolio value, allocation, estimated SS for myself and DH, plus the pension calcs, the cell in green for monthly contributions is -0-.

I am confused, though. Even if we've saved "enough", the worksheet estimates an initial withdrawal rate of 5.15%, but the dollar amounts shown as "initial portfolio withdrawal" sums are not at 5.1% of the portfolio balance - either high or low estimate. What am I missing?

Thanks for all your efforts on this model; I have found the wiki entry on VPW very reassuring, as we definitely want to spend a bit more from age 60-70, and enjoy the more mobile years of our retirement.

[longinvest]

When I enter current portfolio value, allocation, estimated SS for myself and DH, plus the pension calcs, the cell in green for monthly contributions is -0-.

Congratulations!

I am confused, though. Even if we've saved "enough", the worksheet estimates an initial withdrawal rate of 5.15%, but the dollar amounts shown as "initial portfolio withdrawal" sums are not at 5.1% of the portfolio balance - either high or low estimate. What am I missing?

It's probably due to pension bridges. Anyway, the Accumulation sheet doesn't handle negative savings (e.g. making portfolio withdrawals while still working). I suggest to transition to the Retirement sheet when portfolio contributions drop to zero.

You would start simulating retirement using the Retirement sheet, calculating a hypothetical "retirement" gross (pre-tax) income based solely on your investments and future pensions (ignoring current work income). You would then estimate the amount of taxes (federal, state, anticipated medical premiums, etc.) you would have to pay on that hypothetical retirement income, and use the remaining hypothetical "net retirement income" amount as a guide for your after-tax budget for the current year. If the resulting net amount is smaller than your current work net income, you would invest the work income surplus into your portfolio. If your portfolio has grown so much that the resulting amount is bigger than your current work net income, you could safely extract the missing net income difference from your portfolio to increase your current after-tax spending budget.

[Sandi_k]

Thank you, longinvest! Very helpful, just wanted to make sure I wasn't missing something.

[Yukon]

Huge props for a fantastic spreadsheet. I agree it's worth the bookmark to regularly get updates.
One small laymen suggestion on the spreadsheet....
Insert notes in the A column to be able to hover over "Pension #1 Bridge Cost" or "Pension Bridge Deficit" to be given a written laymen definition of the term or how it's calculated. For example, "stock allocation" note is simple... "% of portfolio allocated towards stock" but I'm sort of guessing exact definition of some of the other terms. Maybe that'd help the "What does the initial portfolio income include?" or why it doesn't match initial withdrawal %, etc
Thanks for sharing!

[dpotter]

LongInvest,

Thank you for the wonderful VPW worksheets and for the continued support that you offer in this forum. This thread has contributed substantially to my education about retirement drawdown approaches.

I am attempting to use both the Accumulation/Retirement Worksheet and the Backtesting worksheet to assess my financial readiness for retirement - assessing whether the VPW drawdown plus my SS income will support my desired lifestyle.

A few questions about this:

• Firstly, are these the appropriate worksheets to use to make such assessments?

• Secondly, how would you recommend that I incorporate into my planning some future financial events that I anticipate will substantially alter my portfolio balance, but will not be repeated indefinitely like SS or a pension? (e.g. receipt of an inheritance, or payment of a grandchild's college tuition) - Can such events be plugged into the worksheet?

Thank you again!

[longinvest]

how would you recommend that I incorporate into my planning some future financial events [,,,]? (e.g. [...] payment of a grandchild's college tuition) - Can such events be plugged into the worksheet?

The goal of the Retirement Worksheet is to create retirement income. It isn't meant to replace normal budgeting techniques.

I'm still in the accumulation phase. My employer doesn't give me a bonus to pay for a new car when I need to replace my car. I must save for it in advance (or I could* use a low-interest loan).

* I don't like debt, so I save in advance.

When I'll retire, I'll continue budgeting and saving money for bigger irregular expenses. It's simple and it works.

I suggest doing the same or, if you already have the money for college tuition, you could put it aside in a dedicate account and exclude it from your portfolio when doing retirement income calculations.

[dpotter]

The goal of the Retirement Worksheet is to create retirement income. It isn't meant to replace normal budgeting techniques.

Great. Because assessing retirement income is exactly my use case.

Perhaps I should try rephrasing my question:

• The retirement worksheet already supports the concept of continued income in retirement, but does it support the concept of a future, one-time income that is not recurring (e.g. receipt of an inheritance)?

[longinvest]

The retirement worksheet already supports the concept of continued income in retirement, but does it support the concept of a future, one-time income that is not recurring (e.g. receipt of an inheritance)?

I don't know of guaranteed one-time future income that is not recurring. I would never spend more today based on the hope of receiving a future inheritance as a will can be changed at any time before death.

Once the inheritance is received and added to the portfolio VPW will naturally adapt withdrawal amounts to the bigger portfolio.

[dpotter]

Longinvest,

OK, thanks for your responses. Reading the subtext of your messages, it is apparent to me that the answers to my questions are:

Does (the retirement worksheet) support the concept of a future, one-time income that is not recurring (e.g. receipt of an inheritance)?

Inferred answer: No.

Are these the appropriate worksheets to use to make such assessments?

Inferred answer: Only for scenarios where there are no future contributions or reductions to your portfolio balance (other than portfolio income).

I don't know of guaranteed one-time future income that is not recurring.

I read this comment as your rationale for excluding the feature I was inquiring about. If I misunderstood your point, my apologies and please let me know. In response: No explanation required. If the feature is not present, that answers my question.

Although, if it is helpful to you, I disagree with your premises on this point, and in two dimensions:

• Firstly - whether or not these incomes and expenses are guaranteed doesn't really alter the value of exploring "what-if" scenarios that include them (and at various magnitudes). That exploration will inform decision-making. One may reasonably estimate the future value of an item or event for the purposes of assessing it's impact to one's income and lifestyle.

• Secondly - I believe that such events exist and are common. For example, my retirement planning currently includes several substantial 1-time income and expense events that I expect to substantially alter my portfolio size:
  
  As income (additions to my portfolio), I expect the sale of property (boat, artwork, real estate), the sale of my interest in some business partnerships, the balloon repayment of a loan, the completion of a contract, the settlement of a lawsuit, and the receipt of an inheritance to all trigger substantial additions to my portfolio at various points in the next fifteen years.
  
  As expenses (subtractions from my portfolio), I expect 1-time gifts to my family, charity, and some non-recurring tax events to cause future reductions to my portfolio.

you could put it (future expenses) aside in a dedicated account and exclude it from your portfolio when doing retirement income calculations

I don't find this compelling at all. This capital is currently generating income, and it will continue to do so until I give it away. I don't see any benefit gained by ignoring that income or "budgeting" such expenses out of monthly income for the convenience of the retirement worksheet.

There is no shame in saying "The retirement worksheet was not designed for this type of scenario analysis". Perhaps it's just not that type of tool.

Perhaps I need to build a tool like this for myself, or alter existing tools to add the features I am interested in. I suspect that the backtesting spreadsheet could be easily altered to incorporate future events that affect portfolio size, what do you think?

[KarenC]

I don't find this compelling at all. This capital is currently generating income, and it will continue to do so until I give it away. I don't see any benefit gained by ignoring that income or "budgeting" such expenses out of monthly income for the convenience of the retirement worksheet.

There is no shame in saying "The retirement worksheet was not designed for this type of scenario analysis". Perhaps it's just not that type of tool.

Perhaps I need to build a tool like this for myself, or alter existing tools to add the features I am interested in. I suspect that the backtesting spreadsheet could be easily altered to incorporate future events that affect portfolio size, what do you think?

You might want to check out the spreadsheet provided by Siamond in association with the article Early Retirement and Time Value of Money. (There's also a longish thread about this.)

[longinvest]

whether or not these incomes and expenses are guaranteed doesn't really alter the value of exploring "what-if" scenarios that include them (and at various magnitudes). That exploration will inform decision-making. One may reasonably estimate the future value of an item or event for the purposes of assessing it's impact to one's income and lifestyle.

I disagree. Studying "what-if" scenarios of winning the lottery doesn't require a complex spreadsheet. Worse, it would be foolish to change one's current spending because one has bought a lottery ticket.

The VPW Retirement Worksheet is a tool for creating retirement income; it's not a lottery marketing tool.

If you own a business and intend to sell it later, you can simply include its current value (with a discount for transaction costs and taxes) as part of the stock allocation of your portfolio, as its value is likely to fluctuate like the value of stocks. There's no need to modify the worksheet for this.

I believe that such events exist and are common. For example, my retirement planning currently includes several substantial 1-time income and expense events that I expect to substantially alter my portfolio size:

How did you succeed to set a spending budget until now, while still working? These future events were already in your future. As you were able to do it without the VPW Retirement Worksheet, I am suggesting is that you continue budgeting, in retirement, similarly to how you did it before.

Perhaps I need to build a tool like this for myself, or alter existing tools to add the features I am interested in. I suspect that the backtesting spreadsheet could be easily altered to incorporate future events that affect portfolio size, what do you think?

The two VPW spreadsheets are free and open. You can modify them to your liking.

There's value in the exercise of building a complex tool to analyse future scenarios in a world full of uncertainties, as it often leads to the discovery that a much simpler tool was sufficient all along. Simplicity is difficult to appreciate before discovering the futility of complexity. So, I encourage you to go ahead and build your own complex scenario analyzer and then to think about the false precision it provides. Things would be much simpler if we knew with certainty future asset returns and consumption prices until our exact day of death, but we don't.

"When there are multiple solutions to a problem, choose the simplest one." -- Jack Bogle, The Twelve Pillars of Wisdom

[retiringwhen]

The retirement worksheet already supports the concept of continued income in retirement, but does it support the concept of a future, one-time income that is not recurring (e.g. receipt of an inheritance)?

I have had a desire in the past to model potential future income streams (e.g., continuing to work at various income levels) and future potential windfalls. I have used the ERN Safe-Withdrawal Spreadsheet to do this modelling, mostly becasue I like the level of analysis it provides using a similar source dataset as FireCalc. It is the opposite of VPW sheet though, be forewarned. It is not a simple worksheet.

https://earlyretirementnow.com/2016/12/ ... t-1-intro/

My current retirement income modelling (we are in late stages considering when to retire) I use three different approaches to approximate future income potentials.

1.) I use longinvest's VPW sheet essentially exactly as he publishes it. I find the model simple, clear to understand and meaningful.
2.) I use a hacked VPW sheet that follows the TMV model advocated by willthrill1 as well. It gives me essentially a number somewhere in the middle between VPW's high and 50% off number. If find that a reality check and useful.
3.) I use ERN's SWR sheet to model a worst case number. One reason I use it is to model a legacy motive of assuming a minimum % of our portfolio as a legacy. That is hard to model in most of the other tools out there. SWR makes it an explicit planning assumption.

Together, I believe the three analyses provide a robust look at retirement planning with different emphasis. All three can be parameterized and what-if'd as desired as well to see what your potential outcomes could look like.

[dpotter]

You might want to check out the spreadsheet provided by Siamond in association with the article Early Retirement and Time Value of Money. (There's also a longish thread about this.)

Thank you very much! I'll review the spreadsheet and the thread you linked!

[dpotter]

I am suggesting is that you continue budgeting, in retirement, similarly to how you did it before.

I think that this understates the extents of your suggestion.

IIUC (and I think I do), a more accurate representation of your recommendation is to exclude future income and expenses from one's planning for retirement income (except for lifelong recurrent income, which should be included). Further, this exclusion should be respected regardless of the size of the income or expense.

For example, I expect to sell a farm sometime in the next 8-10 years, depending upon my energy level and other interests. That will certainly generate a few million dollars of capital gain. Your recommendation would have me exclude that capital from my calculations for retirement income because it is non-recurring.

I don't see any benefit to making such an exclusion. And to compound the perversity of this position, it appears that you are making this recommendation as a rationale to justify a feature limitation of the VPW retirement worksheet (by claiming it is not a feature limitation, but actually the proper way to plan for retirement income).

"When there are multiple solutions to a problem, choose the simplest one." -- Jack Bogle, The Twelve Pillars of Wisdom

"Everything should be made as simple as possible, but not simpler." -- Albert Einstein

[dpotter]

My current retirement income modelling (we are in late stages considering when to retire) I use three different approaches to approximate future income potentials.

Thank you very much for sharing the details of your approach and for making some tool recommendations. I sincerely appreciate the time you took in your response, and I will look over the links carefully.

[Rolyatroba]

Thank you very much for sharing the details of your approach and for making some tool recommendations. I sincerely appreciate the time you took in your response, and I will look over the links carefully.

dpotter...I also wanted VPW to be used as a planning tool, being a few years until DW retires. What I did was add some variables and then mirror the outcome 10,000 times to color with Monte Carlo analysis. The pic gives a glimpse of this, with various results, like % of years that you have to spend less than your plan. It is set up so that you enter various inputs (starting portfolio value, various incomes, initial spending plan, inflation, portfolio return assumptions, etc.) You can also see that this example spending plan has some decreases in it. I suppose this could be changed to have additions/subtractions to both spending and portfolio balance.

Pretty easy to create, but happy to share if anyone wants a copy of the file I use. Note, however, it is large and clunky; and I have set up for only one length of retirement--37 years (I suppose that is something that could change as well).

P.S. I also use i-orp.com for this kind of planning.

[jmk]

My current retirement income modelling (we are in late stages considering when to retire) I use three different approaches to approximate future income potentials.
1.) I use longinvest's VPW sheet essentially exactly as he publishes it. I find the model simple, clear to understand and meaningful.
2.) I use a hacked VPW sheet that follows the TMV model advocated by willthrill1 as well. It gives me essentially a number somewhere in the middle between VPW's high and 50% off number. If find that a reality check and useful.
3.) I use ERN's SWR sheet to model a worst case number.

Let me recommend a fourth spreadsheet to add to the mix: the one by Ken Steiner. He calls it an “actuarial approach” but It just uses simple TVM formulas like vpw.

It’s more generic however and includes options for legacy, one time expenses and income, recurring income, and Ltc. https://howmuchcaniaffordtospendinretir ... heets.html

[longinvest]

Maybe it's a good time to remind readers that one of the primary objectives of VPW is to spend the money without any risk of prematurely depleting the portfolio, instead of dying with a huge unspent portfolio!

Here's what I wrote in the first post of this thread, in July 2013, before the later improvements to the overall VPW accumulation and retirement approach to address various concerns like longevity:

I'm not satisfied with the methods I've seen :

• Constant-Dollar : [...] and (2) there is a very high probability that I will leave much money unspent (if the bad sequence doesn't show up). I might be too old to spend it, once I realize that I have too big a pile of money.
• Constant-Percentage : [...] The problem is that I am, again, quite likely to underspend and leave a lot of money unspent.
• Spend Only the Dividends :[...] Yet, I would still be very likely to leave a lot of money unspent.

Here's my idea. It is a Variable Percentage Withdrawal (VPW) method. [...]

[...]
And so on, until year 30, where I withdraw 100% of my remaining portfolio. [...]

So, even if the famous bad sequence of return was to happen, I wouldn't run out of money before the end of 30 years. As a bonus, I am sure to have spent it all; there's no risk of having a single dime left for year 31 (unless I died before year 30, of course).

I see no reason to wait until I'm dead to give money to loved ones and charities. I can do it while alive using part of the excess money VPW gives me. This way, I get to enjoy the process and also observe if they are responsible with it, making sure my money is really helping them instead of hurting them.

The reason I live below my means and invest the difference in a portfolio, today, is to delay consumption and, most likely, increase future consumption and giving. It isn't in the hope of looking at a huge number on a financial statement when I'll be on my deathbed.

[Tony-S]

Maybe it's a good time to remind readers that one of the primary objectives of VPW is to spend the money without any risk of prematurely depleting the portfolio, instead of dying with a huge unspent portfolio!

That's been perfectly clear since the beginning. Thanks for the effort you've (continued) to put into this.

[Rolyatroba]

dpotter...I also wanted VPW to be used as a planning tool, being a few years until DW retires. What I did was add some variables and then mirror the outcome 10,000 times to color with Monte Carlo analysis. The pic gives a glimpse of this, with various results, like % of years that you have to spend less than your plan. It is set up so that you enter various inputs (starting portfolio value, various incomes, initial spending plan, inflation, portfolio return assumptions, etc.) You can also see that this example spending plan has some decreases in it. I suppose this could be changed to have additions/subtractions to both spending and portfolio balance.

Pretty easy to create, but happy to share if anyone wants a copy of the file I use. Note, however, it is large and clunky; and I have set up for only one length of retirement--37 years (I suppose that is something that could change as well).

P.S. I also use i-orp.com for this kind of planning.

Several people PM'd me for my Monte Carlo adaptation to the VPW spreadsheet, so below is a link to it (after some cleanup/prettyifying). WARNING: it is a 115MB file (I/m no Excel expert and don't have any plan to optimize this).

In case this is an inappropriate thing to post in this thread, please let me know and I"ll delete.

Disclaimer: I kluged this from Longinvest's spreadsheet, so it is not a professional work and may not be suitable for your purposes. With that in mind I didn't delete anything that would be superfluous for my use...so go to the "VPW Monte Carlo" sheet if it doesn't open there.

https://www.dropbox.com/s/16qfcj7ix7xvs ... .xlsx?dl=0