Niwashikun wrote: Fri Sep 12, 2025 4:13 pm
longinvest wrote: Thu Sep 11, 2025 6:42 pmSSpies, removing constraints on negative values in the worksheet breaks fundamental robustness features of the VPW accumulation and retirement approach.
Longinvest, I’m not sure what “breaks fundamental robustness features” means. I did try several tests and found no issues. Can you give an example?
SSpies wrote: Thu Sep 11, 2025 6:15 pm
From what you explained it does seem like a really good compromise for my use case and I plan to implement this approach myself. It looks to me you have been very thorough in your analysis. Hopefully if there is anything overlooked in this approach that would cause detrimental output from the spreadsheet I am hopeful someone would notice and post a reply. Thanks again!
SSpies, You probably noticed that longinvest and I have different philosophies. Longinvest generally believes unless there is a very large difference change the expectations/behavior of the person not the tool. I believe unless there is only a very small difference change the tool not the expectations/behavior of the person. Both views have pros and cons.
Also, longinvest generally gives limited options (recommendations) based on his/her preferences, commonly called guided decision making. I prefer to give all the options (not recommendations), generally called informed decision making. Again both ways have pros and cons.
(I realize my use of the phrase “It seems like a good compromise” might be taken as a recommendation, but I simply meant, based on your seeming interest in accounting for the lack of a COLA, that that option might be a good compromise.)
I ran two more scenarios:
0 – Not entering the pension at all and just spending the payments as they come.
5.5 – Entering the full pension w/o COLA in the DBP cells and reducing the amount at 85, when the insolvency happens. (Mentioned by longinvest in the last post)
Here’s a new chart with those options:
The pairs of red cells under “Total Monthly Income” show the jumps and drops and when they occur. A market change could offset these bumps, but of course, that would happen in all the scenarios, so relative to one another they would remain.
I’m a big fan of smoothing income as much as possible (within the context of a changing market), I’m less concerned with the COLA issue. So if it were me I would probably have chosen Method 6 before, but now that I see Method 5.5, which produces similar results but is easier, I would probably use that one. However, I’m not recommending those to you because I’m not you and these aren’t your numbers, so you may get different results when using your inputs. But this should give you enough info to decide.
Also note that, when using a no-COLA pension, the amount in cell “Annual Income Floor After Age 100”, which is shown in today’s dollars, won’t adjust once the pension starts. It will show @65% of the nominal amount every year from age 80 to 120.
Forum member Niwashikun has presented a broken and misleading analysis.
Let’s pick a reasonable example. The retiree is 65 years old, has a $1,500,000 with a portfolio 60/40 stocks/bonds allocation, has a fixed $5,000/month work pension, and is delaying Social Security to age 70 to get $3,000/month payments. The retiree anticipates Social Security payments to drop by 25% at age 75 and the work pension to also drop by 25% at age 83.
An easy approach to handle such a scenario by break pensions in two parts. the lifelong part and the temporary part. In particular, the temporary 25% work pension is marked as starting at age 65 and ending at age 80 (the upper limit allowed by the worksheet). During the additional 3 years (ages 80, 81, and 82), the extra 25% is “silently” added into the portfolio at the end of the year, increasing the initial portfolio balance by (12 X 25% X $5,000) = $15,000 at ages 81, 82 and 83.
Here’s the theoretical model* using annual withdrawals. Calculations are made in nominal dollars. Results are then adjusted to 2% annual inflation.
* That’s the theoretical outcome before applying market returns.
THEORETICAL MODEL
It’s easy to build the model. Simply fill the worksheet with the initial configuration at age 65, subtract the suggested withdrawal amount from the portfolio balance, and apply the 3.76% real (5.8352% nominal) growth trend to it. Then, repeat for ages 66, 67, …, adjusting the Social Security payment up by 2% every year. At age 70, mark Social Security (75%) as started. At age 75, eliminate the temporary Social Security (25%). At age 80, eliminate the temporary work pension (25%). At ages 81, 82, and 83, increase the initial portfolio balance (before calculations) by $15,000.
Here are the results from age 65 to 87. The first column contains the age. The second column contains the portfolio balance before withdrawal (including the extra $15,000 at ages 81 to 83) in nominal dollars. The third column contains the 100% Social Security monthly amount in nominal dollars. The fourth column contains the suggested portfolio withdrawal amount. The fifth column contains the calculated annual retirement income (including withdrawal and pensions) in nominal dollars. The sixth column contains a price index (starting at 1 at age 65) which increases by 2% every year. Finally, the seventh column contains the inflation-adjusted retirement income (including withdrawal and pensions).
Code: Select all
Portfolio Social Portfolio Retirement Price Real
Age Balance Security Withdrawal Income Index Income
65 $1,500,000 $5,000 $90,760 $150,760 1.000000000 $150,760
66 $1,491,472 $5,100 $93,842 $153,842 1.020000000 $150,825
67 $1,479,184 $5,202 $97,009 $157,009 1.040400000 $150,912
68 $1,462,828 $5,306 $100,256 $160,256 1.061208000 $151,013
69 $1,442,081 $5,412 $103,585 $163,585 1.082432160 $151,127
70 $1,416,600 $5,520 $40,768 $167,008 1.104080803 $151,264
71 $1,456,114 $5,631 $42,959 $170,531 1.126162419 $151,427
72 $1,495,616 $5,743 $45,238 $174,154 1.148685668 $151,612
73 $1,535,010 $5,858 $47,577 $177,873 1.171659381 $151,813
74 $1,574,228 $5,975 $50,002 $181,702 1.195092569 $152,040
75 $1,613,168 $6,095 $70,783 $185,635 1.218994420 $152,285
76 $1,632,386 $6,217 $73,744 $189,700 1.243374308 $152,569
77 $1,649,592 $6,341 $76,806 $193,878 1.268241795 $152,871
78 $1,664,561 $6,468 $79,976 $198,188 1.293606630 $153,206
79 $1,677,049 $6,597 $83,261 $202,637 1.319478763 $153,574
80 $1,686,788 $6,729 $101,667 $207,231 1.345868338 $153,976
81 $1,692,616 $6,864 $106,261 $213,037 1.372785705 $155,186
82 $1,693,922 $7,001 $111,033 $219,045 1.400241419 $156,434
83 $1,690,254 $7,141 $115,998 $225,270 1.428246248 $157,725
84 $1,666,117 $7,284 $119,952 $230,508 1.456811173 $158,228
85 $1,636,387 $7,430 $124,051 $235,915 1.485947396 $158,764
86 $1,600,584 $7,578 $128,307 $241,515 1.515666344 $159,346
87 $1,558,187 $7,730 $132,733 $247,297 1.545979671 $159,961
Here’s the model in nominal dollars, constructed using these results (with minor adjustments to make the work pension drop appear at age 83). Retirement income (sum of withdrawal and pension) isn’t affected by visual adjustments:
Here’s the model after adjustment to inflation:
One has to look very closely at the chart to see a small increase at ages 81, 82, and 82, on top of a slow increase due to the worksheet’s conservative approach to handling fixed pensions (without cost of living adjustments).
THEORETICAL MODEL – WORK PENSION REDUCTION AVOIDED
Here’s what happens when, at age 80, the retiree learns that the work pension plan has sufficiently resorbed its deficit to avoid reducing payments.
I leave the detailed calculations as an exercise. Note that, from age 65 to age 79, the model is identical to the one presented above.
Here’s the nominal model:
Here’s the inflation-adjusted model:
ANALYSIS
I think that it’s pretty obvious that the real problem, here, is the uncertainty of whether the pension will be reduced or not at age 83, which cannot be known at age 65. No amount of false precision added to the VPW worksheet will eliminate this uncertainty.
The suggestion to use a “work pension (25%)” temporary income from age 65 to 80 is good enough in face of this much bigger uncertainty.
THEORETICAL (BROKEN) MODEL – NEGATIVE PENSION
Now, let’s get back to forum member Niwashikun’s use of a pension with negative payments. In real life, that’s the kind of “negative pension” that those who only pay the minimum on their credit card statement have. Obviously, the VPW worksheet doesn’t support this. I had to modify a copy of the worksheet (breaking its robustness) for the exercise.
This time, the work pension is separated in two parts, a fixed 100% work pension starting at age 65, and a fixed -25% pension starting at age 83. No special handling is necessary at ages 80 to 82.
The model is easy to build. Fill the modified worksheet with the initial configuration at age 65, subtract the suggested withdrawal amount from the portfolio balance, and apply the 3.76% real (5.8352% nominal) growth trend to it. Then, repeat for ages 66, 67, …, adjusting the Social Security payment up by 2% every year. At age 70, mark Social Security (75%) as started. At age 75, eliminate the temporary Social Security (25%).
Here are the results from age 65 to 87. The first column contains the age. The second column contains the portfolio balance before withdrawal in nominal dollars. The third column contains the 100% Social Security monthly amount in nominal dollars. The fourth column contains the suggested portfolio withdrawal amount. The fifth column contains the calculated annual retirement income (including withdrawal and pensions) in nominal dollars. The sixth column contains a price index (starting at 1 at age 65) which increases by 2% every year. Finally, the seventh column contains the inflation-adjusted retirement income (including withdrawal and pensions).
Code: Select all
Portfolio Social Portfolio Retirement Price Real
Age Balance Security Withdrawal Income Index Income
65 $1,500,000 $5,000 $89,543 $149,543 1.000000000 $149,543
66 $1,492,760 $5,100 $92,849 $152,849 1.020000000 $149,852
67 $1,481,599 $5,202 $96,241 $156,241 1.040400000 $150,174
68 $1,466,196 $5,306 $99,714 $159,714 1.061208000 $150,502
69 $1,446,219 $5,412 $103,272 $163,272 1.082432160 $150,838
70 $1,421,311 $5,520 $40,684 $166,924 1.104080803 $151,188
71 $1,461,189 $5,631 $43,105 $170,677 1.126162419 $151,556
72 $1,500,832 $5,743 $45,615 $174,531 1.148685668 $151,940
73 $1,540,132 $5,858 $48,185 $178,481 1.171659381 $152,332
74 $1,579,005 $5,975 $50,842 $182,542 1.195092569 $152,743
75 $1,617,334 $6,095 $71,853 $186,705 1.218994420 $153,163
76 $1,635,663 $6,217 $75,044 $191,000 1.243374308 $153,614
77 $1,651,684 $6,341 $78,334 $195,406 1.268241795 $154,076
78 $1,665,158 $6,468 $81,731 $199,943 1.293606630 $154,562
79 $1,675,824 $6,597 $85,239 $204,615 1.319478763 $155,073
80 $1,683,398 $6,729 $88,866 $209,430 1.345868338 $155,610
81 $1,687,577 $6,864 $92,619 $214,395 1.372785705 $156,175
82 $1,688,027 $7,001 $96,508 $219,520 1.400241419 $156,773
83 $1,684,387 $7,141 $115,542 $224,814 1.428246248 $157,406
84 $1,660,390 $7,284 $119,487 $230,043 1.456811173 $157,909
85 $1,630,818 $7,430 $123,577 $235,441 1.485947396 $158,445
86 $1,595,191 $7,578 $127,822 $241,030 1.515666344 $159,026
87 $1,552,993 $7,730 $132,240 $246,804 1.545979671 $159,642Here’s the model in nominal dollars, constructed using these results:
Here’s the model after adjustment to inflation:
Visually, it’s very difficult to see any difference between this broken model (nominal & inflation adjusted) and the theoretical model I first presented. One has to look at the results table to see the tiny differences. The broken model starts with a slightly lower retirement income ($149,543 instead of $150,760). From age 65 to 87, the retiree gets practically the same amount of money ($3,542,142 instead of $3,536,917 in inflation-adjusted dollars, a difference of $19/month on a $12,815/month income, on average). The final portfolio balance is also practically identical, yet slightly lower ($1,004,537 instead of $1,007,896 in inflation-adjusted dollars).
I fail to see the attractiveness of breaking the robustness of the VPW wokrsheet to support negative pensions, especially when the whole thing amounts to grossly false precision. In real life, market returns are applied on top the model to determine the suggested withdrawal amount, making fun of any attempt to increase retirement income by (($3,542,142 / $3,536,917) – 1) = 0.15%!
The VPW worksheet is very simple to use, but this is the result of a careful design to support a complex underlying model while presenting easily-verifiable results in the detailed calculations section. I suggest to avoid playing Sorcerer’s Apprentice with the VPW worksheet.
