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, consequently, misleading analysis. Let’s fix this using 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 $3,000 $75,784 $135,784 1.000000000 $135,784
66 $1,507,322 $3,060 $78,566 $138,566 1.020000000 $135,849
67 $1,512,127 $3,121 $81,424 $141,424 1.040400000 $135,932
68 $1,514,187 $3,184 $84,362 $144,362 1.061208000 $136,036
69 $1,513,258 $3,247 $87,371 $147,371 1.082432160 $136,148
70 $1,509,090 $3,312 $50,731 $150,475 1.104080803 $136,290
71 $1,543,458 $3,378 $53,130 $153,666 1.126162419 $136,451
72 $1,577,291 $3,446 $55,599 $156,951 1.148685668 $136,635
73 $1,610,486 $3,515 $58,141 $160,321 1.171659381 $136,832
74 $1,642,928 $3,585 $60,781 $163,801 1.195092569 $137,061
75 $1,674,468 $3,657 $74,469 $167,385 1.218994420 $137,314
76 $1,693,362 $3,730 $77,504 $171,080 1.243374308 $137,593
77 $1,710,147 $3,805 $80,641 $174,889 1.268241795 $137,899
78 $1,724,591 $3,881 $83,888 $178,820 1.293606630 $138,234
79 $1,736,441 $3,958 $87,251 $182,879 1.319478763 $138,599
80 $1,745,423 $4,038 $105,737 $187,073 1.345868338 $138,998
81 $1,750,365 $4,118 $110,413 $192,481 1.372785705 $140,212
82 $1,750,647 $4,201 $115,268 $198,080 1.400241419 $141,461
83 $1,745,806 $4,285 $120,317 $203,885 1.428246248 $142,752
84 $1,720,340 $4,370 $124,358 $208,694 1.456811173 $143,254
85 $1,689,111 $4,458 $128,545 $213,661 1.485947396 $143,788
86 $1,651,628 $4,547 $132,890 $218,810 1.515666344 $144,366
87 $1,607,359 $4,638 $137,409 $224,145 1.545979671 $144,986
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.
From age 65 to age 79, the model is identical to the one presented above. Here’s the results table:
Code: Select all
Portfolio Social Portfolio Retirement Price Real
Age Balance Security Withdrawal Income Index Income
65 $1,500,000 $3,000 $75,784 $135,784 1.000000000 $135,784
66 $1,507,322 $3,060 $78,566 $138,566 1.020000000 $135,849
67 $1,512,127 $3,121 $81,424 $141,424 1.040400000 $135,932
68 $1,514,187 $3,184 $84,362 $144,362 1.061208000 $136,036
69 $1,513,258 $3,247 $87,371 $147,371 1.082432160 $136,148
70 $1,509,090 $3,312 $50,731 $150,475 1.104080803 $136,290
71 $1,543,458 $3,378 $53,130 $153,666 1.126162419 $136,451
72 $1,577,291 $3,446 $55,599 $156,951 1.148685668 $136,635
73 $1,610,486 $3,515 $58,141 $160,321 1.171659381 $136,832
74 $1,642,928 $3,585 $60,781 $163,801 1.195092569 $137,061
75 $1,674,468 $3,657 $74,469 $167,385 1.218994420 $137,314
76 $1,693,362 $3,730 $77,504 $171,080 1.243374308 $137,593
77 $1,710,147 $3,805 $80,641 $174,889 1.268241795 $137,899
78 $1,724,591 $3,881 $83,888 $178,820 1.293606630 $138,234
79 $1,736,441 $3,958 $87,251 $182,879 1.319478763 $138,599
80 $1,745,423 $4,038 $100,596 $196,932 1.345868338 $146,323
81 $1,740,806 $4,118 $104,584 $201,652 1.372785705 $146,893
82 $1,731,699 $4,201 $108,712 $206,524 1.400241419 $147,492
83 $1,717,692 $4,285 $112,990 $211,558 1.428246248 $148,124
84 $1,698,339 $4,370 $117,429 $216,765 1.456811173 $148,794
85 $1,673,159 $4,458 $122,044 $222,160 1.485947396 $149,507
86 $1,641,626 $4,547 $126,851 $227,771 1.515666344 $150,278
87 $1,603,165 $4,638 $131,869 $233,869 1.545979671 $151,276
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% work pension starting at age 83.
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%). At age 83, mark the Work Pension (-25%) as started.
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 $3,000 $74,567 $134,567 1.000000000 $134,567
66 $1,508,610 $3,060 $77,572 $137,572 1.020000000 $134,875
67 $1,514,542 $3,121 $80,655 $140,655 1.040400000 $135,193
68 $1,517,557 $3,184 $83,821 $143,821 1.061208000 $135,526
69 $1,517,397 $3,247 $87,057 $147,057 1.082432160 $135,858
70 $1,513,804 $3,312 $50,647 $150,391 1.104080803 $136,214
71 $1,548,535 $3,378 $53,276 $153,812 1.126162419 $136,581
72 $1,582,510 $3,446 $55,976 $157,328 1.148685668 $136,963
73 $1,615,610 $3,515 $58,749 $160,929 1.171659381 $137,351
74 $1,647,707 $3,585 $61,620 $164,640 1.195092569 $137,763
75 $1,678,639 $3,657 $75,540 $168,456 1.218994420 $138,193
76 $1,696,643 $3,730 $78,804 $172,380 1.243374308 $138,639
77 $1,712,243 $3,805 $82,170 $176,418 1.268241795 $139,104
78 $1,725,191 $3,881 $85,643 $180,575 1.293606630 $139,590
79 $1,735,219 $3,958 $89,230 $184,858 1.319478763 $140,099
80 $1,742,035 $4,038 $92,936 $189,272 1.345868338 $140,632
81 $1,745,328 $4,118 $96,771 $193,839 1.372785705 $141,201
82 $1,744,753 $4,201 $100,743 $198,555 1.400241419 $141,801
83 $1,739,941 $4,285 $119,861 $203,429 1.428246248 $142,433
84 $1,714,615 $4,370 $123,893 $208,229 1.456811173 $142,935
85 $1,683,544 $4,458 $128,071 $213,187 1.485947396 $143,469
86 $1,646,238 $4,547 $132,406 $218,326 1.515666344 $144,046
87 $1,602,167 $4,638 $136,915 $223,651 1.545979671 $144,666
Here’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 ($134,567 instead of $135,784). From age 65 to 87, the retiree gets practically the same amount of money ($3,197,699 instead of $3,192,474 in inflation-adjusted dollars, a difference of $19/month on a $11,567/month income, on average). The portfolio balance at age 87 is also practically identical, yet slightly lower ($1,036,344 instead of $1,039,703 in inflation-adjusted dollars).
I fail to see the attractiveness of breaking the robustness of the VPW worksheet to support negative pensions, especially when the whole thing amounts to gross 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,197,699 / $3,192,474) – 1) = 0.16%!
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.
