Executive Summary
Many financial planners are faced with the client question: "Which pension option should I choose?" Comparing various lump sum and annual pension payout choices based on client life expectancies has often been mathematically intensive and quite difficult - at least, up until now.
The basics of selecting pension options are often fairly straightforward. A pension inherently has some internal rate of return until life expectancy. If you think you can outearn the internal rate of return (and are ready to bear the risk that you do not), a lump sum should be more appealing than the pension option. When choosing amongst pension payout options, it is also typically prudent to minimize pension payments to an unhealthy person to the extent possible - so if the non-pensioner spouse is healthy, you might lean towards a reduced survivor payout or simply go straight life on the primary pensioner. Alternatively, if the primary pensioner is unhealthy but the spouse is healthy, maximize the survivor option to the extent possible (or again, consider a lump sum).
Of course, this is somewhat off-the-cuff guidance. What if you want to dig into the analysis further? How much shorter does someone's life expectancy have to be to make you change pension options? What IS an appropriate assumption for life expectancy, to determine the internal rate of return on the pension?
These questions are largely answered thanks to some spreadsheet tools recently shared with my by David Hultstrom of Financial Architects, LLC, in Woodstock, GA. David has designed three spreadsheets to assist with this very type of analysis, and has generously consented to allow me to make them available to readers here.
The first is a Joint Life Mortality Calculator. By entering the client ages and genders into the yellow boxes, you now have a quick reference spreadsheet for each on the probability that one, the other, both, or neither live for a specified number of years. The material is based on the Period Life Table 2004 published by the government.
The second spreadsheet is a Pension Payout Probability Analysis, which incorporates the preceding life expectancy tables to determine the actuarially adjusted internal rate of return of various pension options. The sheet also allows you to include a "buy term for the difference" strategy, and again provides a great deal of flexibility in comparing various options.
The third spreadsheet is a Pension Payout Scenario Analysis. The difference between this sheet and the preceding one is that in this case, life expectancies are not used. Instead, you can analyze the results over certain specified scenarios - e.g., what happens if the husband actually dies in 5 years, but the wife lives for another 22 years.
For those who have been seeking a more rigorous way to analyze pension options, these tools should prove to be a tremendous resource in your work. As David Hultstrom points out, you can also further adjust your analysis in any of these sheets to account for health problems by adjusting the client's age - for instance, an unhealthy 65 year old might be analyzed as though he/she were 75 years old for projection purposes. Likewise, an extremely healthy 65 year old might be entered as a 60 year old to reflect their above-average life expectancy.
On a minor technical note, David Hultstrom also points out that Excel's IRR function does occasionally have problems. Because it only tries 30 iterations before yielding a solution, it occasionally gives an error (if the solution wasn't found in 30 trials in the software's solver engine). If this IRR error occurs, you can add an entry after the comma in the IRR formula to force Excel to using a starting "guess" closer to the anticipated actual result. I haven't had this error occur yet with Hultstrom's software, but have certainly seen it occur in my own work, so you may have it occur from time to time.
I hope that you find these tools as useful as I do in seeking to determine the best client solution to a challenging analytical problem. Thanks again to David Hultstrom for his generosity in sharing this with all of us!
Michael and David, thanks for sharing these!
These calculators are great, very useful. Their value can’t be overstated. There is one way they could be even more useful. If they also included a variable for a lump sum distribution from the pension plan that may or may not be coupled with an annuity benefit. Numerous of the companies in my town allow for annuity+LS distribution scenarios. Clients love those LS distributions but often don’t understand the investment challenge they are signing up for!
I’ve been exercising these tools on real client situations.
I noticed that in the Scenario Analysis sheet, the Discount Rate
is set to 5% by default. I’m wondering what guidelines one
should follow to set this number. Setting the number appropriately is critically important to finding the age-of-death scenarios where option 1 and the Alternative option “break even”.
Kay
so if my client has recieved a lump sum projection from
his employer as well as 2 survivor option to choose from
his employer as well. Which caculator cuould help me
figure out rates of return in order to compare the options…
I am trying to value a pension. Are the mortality tables the latest RP2014 with the 2017 update? Is there a way to modify the interest rates? I am looking for one that would do 1.39% for the first 5 years and then 3.27% for the next 15 years. Does anyone know of an official “gold standard for assumed interest rates on annuities? Not looking for the qualified ERISA data.
The mortality table spreadsheet, which I updated with the latest 2015 inputs from Social Security, was very useful. (particularly in its both alive, either alive, neither alive columns – which meant I didn’t need to program those columns). It was interesting seeing the measurable improvement expected over the 2004 tables…. an extra year or two of longevity. Hoorah!