Picking out of 2 Pension Options: Constant vs Leveled for 62

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
Post Reply
Topic Author
TetrisCollider
Posts: 69
Joined: Sun Nov 18, 2018 3:03 pm

Picking out of 2 Pension Options: Constant vs Leveled for 62

Post by TetrisCollider »

I am about to hit the Pension Button at work. Lot’s of number crunching over the last month to ensure I did not make any stupid mistakes, but at this point, I am down to two options. And while we’ll do just fine with either one, I still would like to pick the most prudent one. I do understand that it is extremely difficult for anyone to make a recommendation since it is such a personal situation for every single individual and family, but I figure I’ll throw it out to this fine board and see what wisdom I will get that I have not figured into my plans.

High-Level Info that should help, without getting into nitty-gritty details:

• Tax Filing Status – MFJ
• Current Age: 55 Him, 54 Her
• Current Taxable, Deferred and After-Tax accounts combined at about $2.5M (50/50 allocation)
• Only one person (55) with SS PIA (@67) of $3.3K/month. Not planning on taking it until 70.
• Estimated yearly expenses - $100K (inclusive of rent, taxes and Health Care Coverage – we no longer own any property)
• Additional income – Stock Options. Estimated potential income over 10 years - $1M
• Pensioner Life Insurance (Term) - $850K (ends @ 72)
• Life Expectancy for the Pensioner – and here is the rub for the two options (figure somewhere between 70 - 85)

Pension Options (both are 75% Survivor option, with exact same amounts for the spouse - $4.4K/month):

1. Constant Pension Amount ($6K/month) 55 until end of life
2. Pension leveled for 62 ($7.5K/month) from 55 – 62, then $5.3K/month until end of life

Break-even point between the two is at 75. After 75, Constant Pension wins (by about $122K at age of 90).

So, what Option would you recommend with this info?

Thanks in advance to all!
For some reason, people that know nothing, seem to know everything...
Carl53
Posts: 2119
Joined: Sun Mar 07, 2010 8:26 pm

Re: Picking out of 2 Pension Options: Constant vs Leveled for 62

Post by Carl53 »

A little younger than you about 13 years ago I faced a similar decision with pension numbers and resources somewhat smaller than yours. In my case the overriding factor was that I decided that the company would be bankrupt in five years and that in either case the pension/age factors were such that the pension would not be fully covered by PBGC. So my calculations were tilted as I factored in a reduced failed pension for the majority of my retirement. I took the early enhanced pension as opposed to the higher level permanent number. As it turned out the company lasted six years but in a surprising to me turnabout, the post bankrupt company continued the pensions for a number of old timers. Turns out there were a number of executives that stood heavily to benefit from the pension programs including some with less than a lot of longevity. (special rules for the execs) The bankruptcy judge allowed the plan to stand as well as ability for those execs to cash out their pensions (not available to me or already retired folk) even though they did not have a lot of longevity nor would they have been covered anywhere near in full by PBGC. I am now several years past the age at which I'd need be for PBGC to fully cover the pension but I'm glad I took the money as it easily could have been a lot different.

Just something to consider.
dailybagel
Posts: 523
Joined: Tue Feb 15, 2011 8:35 pm

Re: Picking out of 2 Pension Options: Constant vs Leveled for 62

Post by dailybagel »

You’re fortunate to be well-positioned with retirement assets and this pension, whichever you choose.

It looks to me like the constant pension and deferred social security would cover your fixed expenses at age 70, which might be a very nice feature. Assuming 15 years of inflation at an average rate of 2%, the real payout of the pension at that point will have decreased by 35%, and combined with the social security, it would be roughly matched to expenses. (This assumes that expenses and SS payments grow with inflation, and that the $100k of expenses includes income taxes, as mentioned in the OP.)

I think many people would be *very* happy to know that SS and a pension are covering their anticipated expenses. You could simply plan for withdrawals from your portfolio to cover $4k/month (or less depending on taxable status) until SS kicks in at age 70.

With the “leveled”, non-constant pension, I think that your fixed income sources would not quite cover expenses at age 70, depending on the actual inflation trajectory. I am pretty sure you’d be fine based on your portfolio, but it would require a slightly more complicated decumulation plan that involves portfolio withdrawals throughout retirement.
Topic Author
TetrisCollider
Posts: 69
Joined: Sun Nov 18, 2018 3:03 pm

Re: Picking out of 2 Pension Options: Constant vs Leveled for 62

Post by TetrisCollider »

Carl53 wrote: Mon Oct 11, 2021 6:34 pm A little younger than you about 13 years ago I faced a similar decision with pension numbers and resources somewhat smaller than yours. In my case the overriding factor was that I decided that the company would be bankrupt in five years and that in either case the pension/age factors were such that the pension would not be fully covered by PBGC. So my calculations were tilted as I factored in a reduced failed pension for the majority of my retirement. I took the early enhanced pension as opposed to the higher level permanent number. As it turned out the company lasted six years but in a surprising to me turnabout, the post bankrupt company continued the pensions for a number of old timers. Turns out there were a number of executives that stood heavily to benefit from the pension programs including some with less than a lot of longevity. (special rules for the execs) The bankruptcy judge allowed the plan to stand as well as ability for those execs to cash out their pensions (not available to me or already retired folk) even though they did not have a lot of longevity nor would they have been covered anywhere near in full by PBGC. I am now several years past the age at which I'd need be for PBGC to fully cover the pension but I'm glad I took the money as it easily could have been a lot different.

Just something to consider.
Carl, appreciate your input. I am fully aware that nothing is guaranteed, inclusive of a company's Pension. PBGC benefits in my case would be less than 50% of my pension (as of 2021 published rates), so certainly something to consider. I just hope my company that has been around for much longer than I have been alive is able to meet their obligations - but obviously it's a risk that should be taken into consideration (for long term planning). As the saying goes, "A bird in the hand...", which is why I am considering the Leveling option.
For some reason, people that know nothing, seem to know everything...
joverby
Posts: 80
Joined: Sat Sep 18, 2021 7:14 pm

Re: Picking out of 2 Pension Options: Constant vs Leveled for 62

Post by joverby »

Great summary. Also really solid finances, as you said you'll do just fine with either one.

The Constant Pension Amount is really compelling to me because of the higher guaranteed income 70+. I think I'd just feel better knowing everything is covered. That's somewhat irrational, as with your NW everything is covered. YMMV.
Topic Author
TetrisCollider
Posts: 69
Joined: Sun Nov 18, 2018 3:03 pm

Re: Picking out of 2 Pension Options: Constant vs Leveled for 62

Post by TetrisCollider »

dailybagel wrote: Mon Oct 11, 2021 9:06 pm You’re fortunate to be well-positioned with retirement assets and this pension, whichever you choose.

It looks to me like the constant pension and deferred social security would cover your fixed expenses at age 70, which might be a very nice feature. Assuming 15 years of inflation at an average rate of 2%, the real payout of the pension at that point will have decreased by 35%, and combined with the social security, it would be roughly matched to expenses. (This assumes that expenses and SS payments grow with inflation, and that the $100k of expenses includes income taxes, as mentioned in the OP.)

I think many people would be *very* happy to know that SS and a pension are covering their anticipated expenses. You could simply plan for withdrawals from your portfolio to cover $4k/month (or less depending on taxable status) until SS kicks in at age 70.

With the “leveled”, non-constant pension, I think that your fixed income sources would not quite cover expenses at age 70, depending on the actual inflation trajectory. I am pretty sure you’d be fine based on your portfolio, but it would require a slightly more complicated decumulation plan that involves portfolio withdrawals throughout retirement.
Can you clarify what you mean by With the “leveled”, non-constant pension, I think that your fixed income sources would not quite cover expenses at age 70, depending on the actual inflation trajectory. What if there was an additional income of $60K/year, every year, from 56-66 (the potential outcome of exercising stock options) - would you still lean towards the Constant Pension choice? And also, isn't a "Dollar Today is worth more than a Dollar Tomorrow"?
For some reason, people that know nothing, seem to know everything...
Valuethinker
Posts: 43176
Joined: Fri May 11, 2007 11:07 am

Re: Picking out of 2 Pension Options: Constant vs Leveled for 62

Post by Valuethinker »

joverby wrote: Tue Oct 12, 2021 2:26 pm Great summary. Also really solid finances, as you said you'll do just fine with either one.

The Constant Pension Amount is really compelling to me because of the higher guaranteed income 70+. I think I'd just feel better knowing everything is covered. That's somewhat irrational, as with your NW everything is covered. YMMV.
Psychologically, I am all for deferred gratification.

Thus the Constant Pension Amount seems to be superior.

PBGC limit is tricky because it would tilt the decision the other way-- more money now. It's a judgement call on whether the company is in good financial shape (now) and likely to be so in 10 or 20 years time.
User avatar
Stinky
Posts: 8711
Joined: Mon Jun 12, 2017 11:38 am
Location: Sweet Home Alabama

Re: Picking out of 2 Pension Options: Constant vs Leveled for 62

Post by Stinky »

TetrisCollider wrote: Mon Oct 11, 2021 5:15 pm
• Only one person (55) with SS PIA (@67) of $3.3K/month. Not planning on taking it until 70.

[snip]

Pension Options (both are 75% Survivor option, with exact same amounts for the spouse - $4.4K/month):

1. Constant Pension Amount ($6K/month) 55 until end of life
2. Pension leveled for 62 ($7.5K/month) from 55 – 62, then $5.3K/month until end of life
The "pension leveled for 62" option is intended for a person who needs "extra cash income now", prior to early SS age (at 62).

Your post says that you plan to defer SS to age 70. (A wise BH move, in my opinion.) That tells me that you don't need the "extra cash income now".

Given that, I'd opt for the constant pension amount of $6k per month.
It's a GREAT day to be alive! - Travis Tritt
dcd72
Posts: 74
Joined: Fri Apr 10, 2015 7:45 am

Re: Picking out of 2 Pension Options: Constant vs Leveled for 62

Post by dcd72 »

The leveled for 62 option generates an additional $126,000 from 55-62. If you invest that, rather than spend it, it likely will generate significantly more that the $122k delta you mention. For example, if you invest it as a lump sum *at age 72* (so no investment gains from 55-62), at a 5% average return you'll make about $116k by the time you are 90.

That said, the $122k delta is about 3.5% of your current investments + option income, and that doesn't take into account social security. DOn't worry about optimizing, and enjoy yourself with the extra funds while your health permits.
Topic Author
TetrisCollider
Posts: 69
Joined: Sun Nov 18, 2018 3:03 pm

Re: Picking out of 2 Pension Options: Constant vs Leveled for 62

Post by TetrisCollider »

dcd72 wrote: Wed Oct 13, 2021 7:45 am The leveled for 62 option generates an additional $126,000 from 55-62. If you invest that, rather than spend it, it likely will generate significantly more that the $122k delta you mention. For example, if you invest it as a lump sum *at age 72* (so no investment gains from 55-62), at a 5% average return you'll make about $116k by the time you are 90.

That said, the $122k delta is about 3.5% of your current investments + option income, and that doesn't take into account social security. DOn't worry about optimizing, and enjoy yourself with the extra funds while your health permits.
DCD, thank you for your feedback. I am leaning towards the Leveled for 62 Option for similar reasons you have outlined. I am also expecting that we will not be as mobile @ 75 (when both options break-even) and should theoretically spend less. On the other hand, that's only a theory, because most likely we'll end up spending more on our health after 75. The other reason I am leaning towards the Leveled option is: if I croak by 75 (a good possibility), my wife will get exactly the same amount of pension regardless which option I go with, so why not pick the one that loads the $s upfront.
For some reason, people that know nothing, seem to know everything...
User avatar
#Cruncher
Posts: 3299
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: Picking out of 2 Pension Options: Constant vs Leveled for 62

Post by #Cruncher »

TetrisCollider wrote: Mon Oct 11, 2021 5:15 pmPension Options (both are 75% Survivor option, with exact same amounts for the spouse - $4.4K/month):
  1. Constant Pension Amount ($6K/month) 55 until end of life
  2. Pension leveled for 62 ($7.5K/month) from 55 – 62, then $5.3K/month until end of life
Break-even point between the two is at 75. ... what Option would you recommend with this info?
Actually the breakeven age is 77, not 75. [62 + 7 * (7.5 - 6) / (6 - 5.3)] Using the SSA 1970 Cohort Life Table the first option produces about $45,000 more in survival-weighted benefits. But when discounted at 3% it produces a present value about $14,000 less. So there's not too much difference between the options, and the second one (with a higher amount the first seven years) is fine.

Code: Select all

             ------- Present Value --------
  4    Rate    Case 1     Case 2     Diff
       ----  ---------  ---------  --------
  5      0%  2,375,492  2,330,367   45,125 
  6      1%  2,000,226  1,980,432   19,795 
  7      2%  1,707,488  1,707,051      437 
  8      3%  1,476,209  1,490,626  (14,417) [1]
  9      4%  1,291,229  1,317,076  (25,847)
 10      5%  1,141,523  1,176,175  (34,652)
Following is a table showing the survival-weighted benefits each year.

Code: Select all

Row ColA  Col B   Col C   Col D    Col E   Col F    Col G   Col H
 12                                                Case 1  Case 2
 13                               Change age -->       62      62
 14                      Man $ before change -->    6,000   7,500
 15                       Man $ after change -->    6,000   5,300
 16                             Only Woman $ -->    4,400   4,400
     -- Age ---  --- Alive ----  --- % Alive ---   --- Wtd $ ----
 18  Man  Woman     Man   Woman    Man  Only Wom   Case 1  Case 2

Code: Select all

 19   55     54  88,211  93,163  100.00%   0.00%   71,943  89,889
 20   56     55  87,689  92,830   99.41%   0.59%   71,822  89,658
 21   57     56  87,125  92,466   98.77%   1.22%   71,689  89,406
 22   58     57  86,518  92,066   98.08%   1.90%   71,543  89,131
 23   59     58  85,864  91,629   97.34%   2.62%   71,381  88,831
 24   60     59  85,161  91,154   96.54%   3.38%   71,203  88,504
 25   61     60  84,404  90,638   95.68%   4.20%   71,006  88,145 [2]
 26   62     61  83,586  90,077   94.76%   5.07%   70,786  62,868
 27   63     62  82,701  89,466   93.75%   6.00%   70,540  62,710
 28   64     63  81,739  88,801   92.66%   6.99%   70,264  62,530
 29   65     64  80,694  88,075   91.48%   8.06%   69,954  62,324
 30   66     65  79,557  87,282   90.19%   9.19%   69,605  62,087
 31   67     66  78,326  86,416   88.79%  10.39%   69,213  61,817
 32   68     67  77,000  85,472   87.29%  11.66%   68,775  61,510
 33   69     68  75,581  84,451   85.68%  12.98%   68,287  61,162
 34   70     69  74,071  83,352   83.97%  14.34%   67,745  60,768
 35   71     70  72,459  82,176   82.14%  15.75%   67,141  60,323
 36   72     71  70,740  80,913   80.19%  17.20%   66,469  59,819
 37   73     72  68,921  79,556   78.13%  18.67%   65,725  59,253
 38   74     73  67,011  78,108   75.97%  20.15%   64,907  58,620
 39   75     74  65,014  76,572   73.70%  21.61%   64,005  57,914
 40   76     75  62,912  74,947   71.32%  23.07%   63,008  57,123
 41   77     76  60,694  73,215   68.81%  24.52%   61,903  56,234
 42   78     77  58,362  71,362   66.16%  25.92%   60,682  55,240
 43   79     78  55,926  69,392   63.40%  27.26%   59,339  54,135
 44   80     79  53,386  67,316   60.52%  28.53%   57,867  52,909
 45   81     80  50,743  65,131   57.52%  29.69%   56,250  51,549
 46   82     81  47,981  62,828   54.39%  30.76%   54,464  50,033
 47   83     82  45,073  60,380   51.10%  31.69%   52,475  48,329
 48   84     83  41,992  57,753   47.60%  32.48%   50,250  46,406
 49   85     84  38,740  54,913   43.92%  33.06%   47,769  44,241
 50   86     85  35,353  51,844   40.08%  33.35%   45,030  41,829
 51   87     86  31,888  48,554   36.15%  33.28%   42,053  39,182
 52   88     87  28,414  45,072   32.21%  32.80%   38,872  36,329
 53   89     88  24,998  41,442   28.34%  31.88%   35,533  33,309
 54   90     89  21,702  37,716   24.60%  30.52%   32,091  30,173
 55   91     90  18,577  33,949   21.06%  28.77%   28,605  26,975
 56   92     91  15,663  30,195   17.76%  26.66%   25,138  23,773
 57   93     92  12,993  26,509   14.73%  24.26%   21,751  20,628
 58   94     93  10,589  22,944   12.00%  21.67%   18,506  17,599
 59   95     94   8,464  19,552    9.60%  18.97%   15,463  14,744
 60   96     95   6,635  16,378    7.52%  16.26%   12,682  12,123
 61   97     96   5,104  13,482    5.79%  13.63%   10,211   9,784
 62   98     97   3,854  10,906    4.37%  11.19%    8,074   7,754
 63   99     98   2,861   8,673    3.24%   9.01%    6,276   6,040
 64  100     99   2,090   6,785    2.37%   7.11%    4,800   4,629
 65  101    100   1,503   5,228    1.70%   5.52%    3,612   3,490
 66  102    101   1,061   3,963    1.20%   4.20%    2,672   2,587
 67  103    102     736   2,951    0.83%   3.14%    1,941   1,883
 68  104    103     500   2,157    0.57%   2.30%    1,383   1,344
 69  105    104     332   1,545    0.38%   1.65%      966     940
 70  106    105     216   1,083    0.24%   1.16%      660     643
 71  107    106     137     741    0.16%   0.79%      440     430
 72  108    107      85     495    0.10%   0.53%      287     280
 73  109    108      51     321    0.06%   0.34%      181     177
 74  110    109      30     202    0.03%   0.22%      111     109
 75  111    110      17     123    0.02%   0.13%       66      65
 76  112    111       9      73    0.01%   0.08%       38      37
 77  113    112       5      41    0.01%   0.04%       21      20
 78  114    113       2      22    0.00%   0.02%       11      11
 79  115    114       1      12    0.00%   0.01%        6       6
 80  116    115       1       6    0.00%   0.01%        3       3
 81  117    116       0       3    0.00%   0.00%        1       1
 82  118    117       0       1    0.00%   0.00%        0       0
  1. The present values are computed using the Excel NPV function. Here it is with a 3% discount rate. (The "* 1.03 ^ 0.5" is to account for NPV assuming cash flows occur at the end of each year, while actual pension benefits occur mid-year on average.)
    1,476,209 = NPV(3%, G$19:G$82) * 1.03 ^ 0.5
  2. Example calculations for age 61 on row 25:

    Code: Select all

    E25: 95.68% =  84404 / 88211
    F25:  4.20% = (90638 / 93163) * (1 - 0.9568)
    G25: 71,006 = 6 * ($E25 + $E26) * IF($A25 < G$13, G$14, G$15) + 6 * ($F25 + $F26) * G$16
    H25: 88,145 = 6 * ($E25 + $E26) * IF($A25 < H$13, H$14, H$15) + 6 * ($F25 + $F26 )* H$16
Topic Author
TetrisCollider
Posts: 69
Joined: Sun Nov 18, 2018 3:03 pm

Re: Picking out of 2 Pension Options: Constant vs Leveled for 62

Post by TetrisCollider »

#Cruncher wrote: Fri Oct 15, 2021 7:04 am
TetrisCollider wrote: Mon Oct 11, 2021 5:15 pmPension Options (both are 75% Survivor option, with exact same amounts for the spouse - $4.4K/month):
  1. Constant Pension Amount ($6K/month) 55 until end of life
  2. Pension leveled for 62 ($7.5K/month) from 55 – 62, then $5.3K/month until end of life
Break-even point between the two is at 75. ... what Option would you recommend with this info?
Actually the breakeven age is 77, not 75. [62 + 7 * (7.5 - 6) / (6 - 5.3)] Using the SSA 1970 Cohort Life Table the first option produces about $45,000 more in survival-weighted benefits. But when discounted at 3% it produces a present value about $14,000 less. So there's not too much difference between the options, and the second one (with a higher amount the first seven years) is fine.

Code: Select all

             ------- Present Value --------
  4    Rate    Case 1     Case 2     Diff
       ----  ---------  ---------  --------
  5      0%  2,375,492  2,330,367   45,125 
  6      1%  2,000,226  1,980,432   19,795 
  7      2%  1,707,488  1,707,051      437 
  8      3%  1,476,209  1,490,626  (14,417) [1]
  9      4%  1,291,229  1,317,076  (25,847)
 10      5%  1,141,523  1,176,175  (34,652)
Following is a table showing the survival-weighted benefits each year.

Code: Select all

Row ColA  Col B   Col C   Col D    Col E   Col F    Col G   Col H
 12                                                Case 1  Case 2
 13                               Change age -->       62      62
 14                      Man $ before change -->    6,000   7,500
 15                       Man $ after change -->    6,000   5,300
 16                             Only Woman $ -->    4,400   4,400
     -- Age ---  --- Alive ----  --- % Alive ---   --- Wtd $ ----
 18  Man  Woman     Man   Woman    Man  Only Wom   Case 1  Case 2

Code: Select all

 19   55     54  88,211  93,163  100.00%   0.00%   71,943  89,889
 20   56     55  87,689  92,830   99.41%   0.59%   71,822  89,658
 21   57     56  87,125  92,466   98.77%   1.22%   71,689  89,406
 22   58     57  86,518  92,066   98.08%   1.90%   71,543  89,131
 23   59     58  85,864  91,629   97.34%   2.62%   71,381  88,831
 24   60     59  85,161  91,154   96.54%   3.38%   71,203  88,504
 25   61     60  84,404  90,638   95.68%   4.20%   71,006  88,145 [2]
 26   62     61  83,586  90,077   94.76%   5.07%   70,786  62,868
 27   63     62  82,701  89,466   93.75%   6.00%   70,540  62,710
 28   64     63  81,739  88,801   92.66%   6.99%   70,264  62,530
 29   65     64  80,694  88,075   91.48%   8.06%   69,954  62,324
 30   66     65  79,557  87,282   90.19%   9.19%   69,605  62,087
 31   67     66  78,326  86,416   88.79%  10.39%   69,213  61,817
 32   68     67  77,000  85,472   87.29%  11.66%   68,775  61,510
 33   69     68  75,581  84,451   85.68%  12.98%   68,287  61,162
 34   70     69  74,071  83,352   83.97%  14.34%   67,745  60,768
 35   71     70  72,459  82,176   82.14%  15.75%   67,141  60,323
 36   72     71  70,740  80,913   80.19%  17.20%   66,469  59,819
 37   73     72  68,921  79,556   78.13%  18.67%   65,725  59,253
 38   74     73  67,011  78,108   75.97%  20.15%   64,907  58,620
 39   75     74  65,014  76,572   73.70%  21.61%   64,005  57,914
 40   76     75  62,912  74,947   71.32%  23.07%   63,008  57,123
 41   77     76  60,694  73,215   68.81%  24.52%   61,903  56,234
 42   78     77  58,362  71,362   66.16%  25.92%   60,682  55,240
 43   79     78  55,926  69,392   63.40%  27.26%   59,339  54,135
 44   80     79  53,386  67,316   60.52%  28.53%   57,867  52,909
 45   81     80  50,743  65,131   57.52%  29.69%   56,250  51,549
 46   82     81  47,981  62,828   54.39%  30.76%   54,464  50,033
 47   83     82  45,073  60,380   51.10%  31.69%   52,475  48,329
 48   84     83  41,992  57,753   47.60%  32.48%   50,250  46,406
 49   85     84  38,740  54,913   43.92%  33.06%   47,769  44,241
 50   86     85  35,353  51,844   40.08%  33.35%   45,030  41,829
 51   87     86  31,888  48,554   36.15%  33.28%   42,053  39,182
 52   88     87  28,414  45,072   32.21%  32.80%   38,872  36,329
 53   89     88  24,998  41,442   28.34%  31.88%   35,533  33,309
 54   90     89  21,702  37,716   24.60%  30.52%   32,091  30,173
 55   91     90  18,577  33,949   21.06%  28.77%   28,605  26,975
 56   92     91  15,663  30,195   17.76%  26.66%   25,138  23,773
 57   93     92  12,993  26,509   14.73%  24.26%   21,751  20,628
 58   94     93  10,589  22,944   12.00%  21.67%   18,506  17,599
 59   95     94   8,464  19,552    9.60%  18.97%   15,463  14,744
 60   96     95   6,635  16,378    7.52%  16.26%   12,682  12,123
 61   97     96   5,104  13,482    5.79%  13.63%   10,211   9,784
 62   98     97   3,854  10,906    4.37%  11.19%    8,074   7,754
 63   99     98   2,861   8,673    3.24%   9.01%    6,276   6,040
 64  100     99   2,090   6,785    2.37%   7.11%    4,800   4,629
 65  101    100   1,503   5,228    1.70%   5.52%    3,612   3,490
 66  102    101   1,061   3,963    1.20%   4.20%    2,672   2,587
 67  103    102     736   2,951    0.83%   3.14%    1,941   1,883
 68  104    103     500   2,157    0.57%   2.30%    1,383   1,344
 69  105    104     332   1,545    0.38%   1.65%      966     940
 70  106    105     216   1,083    0.24%   1.16%      660     643
 71  107    106     137     741    0.16%   0.79%      440     430
 72  108    107      85     495    0.10%   0.53%      287     280
 73  109    108      51     321    0.06%   0.34%      181     177
 74  110    109      30     202    0.03%   0.22%      111     109
 75  111    110      17     123    0.02%   0.13%       66      65
 76  112    111       9      73    0.01%   0.08%       38      37
 77  113    112       5      41    0.01%   0.04%       21      20
 78  114    113       2      22    0.00%   0.02%       11      11
 79  115    114       1      12    0.00%   0.01%        6       6
 80  116    115       1       6    0.00%   0.01%        3       3
 81  117    116       0       3    0.00%   0.00%        1       1
 82  118    117       0       1    0.00%   0.00%        0       0
  1. The present values are computed using the Excel NPV function. Here it is with a 3% discount rate. (The "* 1.03 ^ 0.5" is to account for NPV assuming cash flows occur at the end of each year, while actual pension benefits occur mid-year on average.)
    1,476,209 = NPV(3%, G$19:G$82) * 1.03 ^ 0.5
  2. Example calculations for age 61 on row 25:

    Code: Select all

    E25: 95.68% =  84404 / 88211
    F25:  4.20% = (90638 / 93163) * (1 - 0.9568)
    G25: 71,006 = 6 * ($E25 + $E26) * IF($A25 < G$13, G$14, G$15) + 6 * ($F25 + $F26) * G$16
    H25: 88,145 = 6 * ($E25 + $E26) * IF($A25 < H$13, H$14, H$15) + 6 * ($F25 + $F26 )* H$16
Thanks, #Cruncher - really appreciate you crunching the numbers :D for me. Of course it gets slightly a bit more difficult with calculations because ultimately I have to deal with EXACT dates (of birthdays, the month when I declare retirement and start taking pension), but this is close enough. At this point, we decided to go with a Leveled option. Now only time will tell if we made the correct choice. :beer
For some reason, people that know nothing, seem to know everything...
Post Reply