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!
