Excel Custom Functions for PPA Annuity Factors

[Guest JoeActuary]

I have written custom functions in Excel to calculate Single Life (with or without period certain) and Joint & Survivor annuity factors using the 3-tiered interest rates required by PPA. I have limited access to other sources to test my functions and hope that some of you will be willing to test some factors and report back. If you're willing to do that, please go to http://www.woodley.ws/APRs.htm then fill out the form and I'll send a copy. I may offer this as shareware after it has been more rigorously tested.

[flosfur]

I have written custom functions in Excel to calculate Single Life (with or without period certain) and Joint & Survivor annuity factors using the 3-tiered interest rates required by PPA. I have limited access to other sources to test my functions and hope that some of you will be willing to test some factors and report back. If you're willing to do that, please go to http://www.woodley.ws/APRs.htm then fill out the form and I'll send a copy. I may offer this as shareware after it has been more rigorously tested.

I have done the same (I always had it for single interest rate) and would like to compare the results.

I have yet to add the additional feature for valuing the greater of S417 & plan assumptions lump sum instead of annuity from the date of event - in my case it would be NRA since for small plans I assume no pre-retirement decrements.

[Guest Colby]

Do you still have this file?

[Guest Doug Goelz]

Attached is an Excel add-in that I created that contains two annuity present value functions. One is called SLA for single-life annuities, and the other is JSLA for joint and contingent annuities. These functions calculate present values using the PPA segment interest rate approach. They also are based on using "first principles" and a UDD approach. Therefore, the resulting values will differ on average by approximately .03% to .04% from the present values that are determined using commutation functions and interpolation.

I know there are various programs and spreadsheets that have already been circulated, and they work just fine. However, I wanted to throw my approach out for consideration as well. The nice thing about having the functions in an Excel add-in is that they are available for use in any worksheet, and they give user the ability to create their own tools such as customized benefit calculation templates. In addition, this add-in is compiled in the C++ programming language, so the functions are much faster than they would be using Excel’s VBA language.

The name of the add-in is "PENPROG Functions.xll" - but in order to upload it to this site, I had to rename with an "xls" extension. Therefore, once you have downloaded it, rename it back to its original name (i.e., change the file extension from "xls" to "xll"). You will need to save it to a directory called C:\PENPROG.

The functions are fully operational and include 15 mortality tables. These tables are documented on the last page of the attached overview document. If anybody is interested in using the add-in going forward and would like the recently issued IRS tables required for years 2009-2013, please e-mail me directly at doug.goelz@phoenix-benefits.com. I plan to charge a one-time fee for release of the add-in that includes these updated tables. I am doing so since it took me a significant amount of time to create the tool, and I was hoping to receive a little bit of compensation in return for the time I neglected my family while doing so. However, this fee will be minimal when compared against the cost of someone developing a similar tool from scratch or the recurring subscription fee approach used by many software vendors.

Please note that there are four optional ways to incorporate pre-retirement mortality for the JSLA function. The default is option 2. In the near future, I will expand on the overview file to explain this parameter in detail.

Important Disclaimer: The add-in and sample application Excel file are being distributed “as is” and with no warranties of any kind, whether express or implied. The user assumes the entire risk of using these files and the validity of the values generated by them. In addition, in no event shall I or my employer be liable for any incidental, consequential, or punitive damages whatsoever relating to the use of the files. Your use or installation of the files indicates your acceptance of these terms. If you do not agree to these terms, then do not install or use the files.

Thanks and if anybody has any suggestions for improving the tool, please let me know.

PENPROG_Functions.xls

PENPROG_Functions_Overview.pdf

PENPROG_Functions___Sample_Applications.xls