Pension Calculator
 Share
The version of the browser you are using is no longer supported. Please upgrade to a supported browser.Dismiss

View only
 
 
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQ
1
What's Your Pension Worth?
2


For those on the cusp of retirement:
You may have a lump-sum option. If you don’t take it, you probably will have several choices for the monthly benefit: a high payout with no survivorship rights, or smaller payouts with different percentages for your surviving spouse.
Fill in ages, genders, mortality adjustments (-5% for nonsmokers) and the pension amounts for one of the options you are considering. For example, you get $3,000 but your surviving spouse gets nothing. Now repeat the exercise with a different choice; perhaps you can take a reduced $2,500 annuity with a full survivor benefit (put $2,500 under your pension and $2,500 under spouse's pension). Which payout option is more valuable? In the illustrated case, the reduced pension with full survivorship is the better deal.
For those in mid-career:
Use your ages today. Change “years until start” to the right number. If you haven’t signed up for a specified survivorship option, leave the spouse’s pension at $0.
If you just left your job or your employer shut down the pension plan, you probably will get a lump-sum offer. It will be a low-ball number. Why? Because employers are permitted to use ridiculously high interest rates as discount factors.
Probably a lot more than you thought. This calculator will help you make choices, whether your monthly pension is due to start soon or it’s years in the future.

For those on the cusp of retirement:
You may have a lump-sum option. If you don’t take it, you probably will have several choices for the monthly benefit: a high payout with no survivorship rights, or smaller payouts with different percentages for your surviving spouse.
Fill in ages, genders, mortality adjustments (-5% for nonsmokers) and the pension amounts for one of the options you are considering. For example, you get $3,000 but your surviving spouse gets nothing. Now repeat the exercise with a different choice; perhaps you can take a reduced $2,500 annuity with a full survivor benefit (put $2,500 under your pension and $2,500 under spouse's pension). Which payout option is more valuable? In the illustrated case, the reduced pension with full survivorship is the better deal.
For those in mid-career:
Use your ages today. Change “years until start” to the right number. If you haven’t signed up for a specified survivorship option, leave the spouse’s pension at $0.
If you just left your job or your employer shut down the pension plan, you probably will get a lump-sum offer. It will be a low-ball number. Why? Because employers are permitted to use ridiculously high interest rates as discount factors.
3
Probably a lot more than you thought. This calculator will help you make choices, whether your monthly pension is due to start soon or it’s years in the future. To use this template, go to FILE and select MAKE A COPY. Then type in new ages, monthly pension and other inputs on your COPY.
4
5
6
7
8
Boxes where you input data are blue.
9
Boxes where you get data are yellow.
10
11
Your Spouse
12
YouInstructions
13
14
Ages today5853Use whole numbers between 40 and 84.
15
Years until pension starts
2A 45-year-old expecting a pension at 65 would put 20 here.
16
GenderMFUse the single letter M for male.
17
Mortality adjustment-5%-5%Use -5% for average nonsmokers and +75% for smokers.
18
Monthly pension$3,440$0Put primary pension in cell B18 and survivorship pension, if any, in C18.
19
Base interest rate (10-year Treasury or substitute)1.75%The 10-year Treasury was yielding 1.75% on 10/17/19. You can update this number.
20
Life expectancy, years85.687.0
21
Fair value of pension using realistic assumptions
$761,800
22
Value using assumptions in a pension regulation
$545,500
23
Probability at least one of you makes it to age 90
68%
24
Expected widow(er)hood, years*
13.6
25
*average time between first and second deaths
26
27
For those on the cusp of retirement:
28
You may have a lump-sum option. If you don’t take it, you probably will have several choices for the monthly benefit: a high payout with no survivorship rights, or smaller payouts with different percentages for your surviving spouse.
29
30
Fill in ages, genders, mortality adjustments (-5% for nonsmokers) and the pension amounts for one of the options you are considering. For example, you get $3,000 but your surviving spouse gets nothing. Now repeat the exercise with a different choice; perhaps you can take a reduced $2,500 annuity with a full survivor benefit (put $2,500 under your pension and $2,500 under spouse's pension). Which payout option is more valuable? In the illustrated case, the reduced pension with full survivorship is the better deal.
31
32
33
34
35
36
For those in mid-career:
37
Use your ages today. Change “years until start” to the right number. If you haven’t signed up for a specified survivorship option, leave the spouse’s pension at $0.
38
39
If you just left your job or your employer shut down the pension plan, you probably will get a lump-sum offer. It will be a low-ball number. Why? Because employers are permitted to use ridiculously high interest rates as discount factors.
40
41
42
Mortality. This calculator uses mortality rates from the Society of Actuaries, coupled with projections for declines in mortality over the period 2016-2032. It allows you to adjust these numbers to suit your health. If you are a nonsmoker, you can leave the adjustment at -5%. You could go to -25% if you jog and had long-lived parents. Smoker mortality rates are about double the rates for nonsmokers. (That doesn't mean smokers are twice as likely to die someday. It means that if a nonsmoker of a certain age has a 1% probability of dying within 12 months, a smoker of the same age faces a probability more like 2%.)
43
44
45
46
Interest rates. The formulas here build in a yield curve for Treasury zero-coupon bonds. You can update the spreadsheet by inserting a single number in cell E18, the yield on the 10-year coupon Treasury (available at Yahoo Finance). In the unlikely event that your pension is insecure (see story Maximize Your Pension http://www.forbes.com/sites/baldwin/2016/01/10/maximize-your-pension-with-this-calculator for details), you should use a slightly higher rate than this.
47
48
49
Lump sums. IRS rules set a floor on lump-sum offers, and they deliberately make life easy for employers by assuming high interest and mortality rates. By using more-realistic rates, you can get a better comparison of your options. If you are healthy, you are likely to conclude that a lump sum that might be offered to you is a raw deal.
50
51
52
53
Value using regulatory assumptions. This is an approximation to the lump sum that your employer will come up with. It is only approximate because the spreadsheet does not update the rates used in the IRS regulations. Your plan’s IRS rates, moreover, may vary somewhat because of lags of up to a year in its rate updating rule.
54
55
Death before retirement. If you die before you start collecting—and before you have chosen a survivorship percentage—your spouse, per federal law, has to get something. How much? The law gives employers some maneuvering room. Here, we’re assuming that your spouse will collect, beginning at age 65, half the single-life annuity you would have had. But variations on the survivorship rights don’t have a big impact on a pension’s value because not that many workers perish before 65.
56
57
58
59
60
Marital status. The calculator works fine for married gay couples. It doesn’t like singles. If you are unmarried and contemplating a mid-career pension buyout, give yourself an 84-year-old spouse in order to get an accurate value.
61
62
Cola. If your pension has a cost-of-living adjustment, you are (a) very fortunate and (b) probably employed by the government. In that case the base interest rate that goes in cell E18 is the rate on 10-year Tips. In August 2019 the rate was in thye vicinity of 0%. You can get an update by going to https://www.treasury.gov/resource-center/data-chart-center/interest-rates/Pages/TextView.aspx?data=realyield. If that link doesn't work, search your browser for “Daily Treasury real yield curve rates.”
63
64
65
66
67
68
RP-2014IRS unisex mortality*
69
Base mortalityMortality decline rate
70
AgeMFMF
71
72
400.000630.000401.2%1.1%0.00064
73
410.000670.000431.3%1.1%0.00067
74
420.000730.000481.3%1.1%0.00071
75
430.000790.000531.3%1.1%0.00076
76
440.000880.000591.3%1.1%0.00081
77
450.000970.000661.3%1.2%0.00085
78
460.001090.000731.3%1.2%0.00089
79
470.001220.000821.3%1.2%0.00094
80
480.001360.000911.3%1.2%0.00100
81
490.001520.001001.3%1.2%0.00108
82
500.004060.002771.3%1.1%0.00117
83
510.004380.002911.3%1.1%0.00126
84
520.004710.003061.3%1.1%0.00139
85
530.005040.003231.3%1.1%0.00157
86
540.005380.003411.2%1.0%0.00179
87
550.005740.003621.2%1.0%0.00213
88
560.006100.003861.1%1.0%0.00259
89
570.006480.004131.1%1.0%0.00300
90
580.006880.004441.1%1.0%0.00344
91
590.007310.004791.1%1.0%0.00390
92
600.007770.005191.1%1.1%0.00446
93
610.008280.005651.1%1.1%0.00519
94
620.008850.006161.1%1.1%0.00596
95
630.009490.006721.1%1.1%0.00695
96
640.010210.007351.1%1.1%0.00786
97
650.011010.008051.1%1.1%0.00888
98
660.011920.008821.1%1.2%0.01018
99
670.012930.009681.1%1.2%0.01135
100
680.014070.010631.1%1.2%0.01243
Loading...