Jump to content
Sign in to follow this  
Mike Preston

PPA Lump Sum Calculator

Recommended Posts

Hi everybody,

As I promised last week, I herewith post a free copy of my program which will calculate certain PPA lump sums. IMPORTANT NOTE: I guess my magic moderator permissions are a bit lacking at the moment, because the message board wouldn't let me upload the file unless I changed the extension from .exe to something it thinks is benign. Hence, I changed the extenstion to .pdf. But, I promise that it really is an executable file that you must rename from ".pdf" to ".exe" in order to allow it to run on your computer. Windows only, please, as it isn't set up to run on Unix.

The program is in the form of a regular windows installation routine. The program does not write to your computer's registry. Hence, it has an uninstall that comes with it that will completely eradicate any traces of the program from your computer as long as you haven't stored any additional files in the program's directories. If you have stored information, then the uninstall will not delete that additonal information.

I will be honest with everybody and state up front that the purpose of posting this copy of my program is to encourage you to purchase a version of my program that has more capability than the posted version.

NOTE TO THE MODERATORS: I have already discussed this with Dave Baker and he has encouraged me to post this version here and to announce the availability of the other versions.

The purpose of the attached program, other than the obvious attempt to have everybody and their mother's uncle write me checks or otherwise funnel money to me so that they can have their very own copy of one of the other versions, is to provide everybody in the industry with a completely free calculator that can be used as they see fit; including the ability to:

1) check the results produced by my program against their own spreadsheets or programs they have developed

2) check the results produced by my program against the results that their valuation system produces

3) compare the relative value of present values based on alternative assumptions such as the funding segment rates versus the lump sum segment rates

With that said, the attached version, which I have labeled the "trial" version, has some limitations that can be removed by purchasing either the "lite" or the "pro" version. The trial version can only calculate present values for ages between 62 and 65 (either current age or retirement age). It does calculations at non-integral ages. In addition, it can only use certain periods of 0 through 12, 60 and 120 months. That should be enough to check against whatever system you want to check against.

Obviously, the "lite" version eliminates the restriction on ages and certain periods and is otherwise fully functional, calculation-wise. The lite version can be used in a production environment, but it isn't intended for heavy lifting, so to speak. It is intended more for one-off type calculations where you want to have a quick and handy way to calculate present values.

The "pro" version includes a number of options that are intended to be used in a production environment. From the help file:

1) the ability to enter dates (valuation, birth and retirement) rather than ages (current and retirement). The system will then calculate the resulting ages. The system provides for six separate methods of determining ages based on the difference between two dates. You can select which method to use separately for current age and retirement age. You can use dates for one age (current or retirement) and not for the other, if you choose.

2) the ability to save a specific calculation in a separate data file.

3) the ability to call up the information from a specific data file so that it is redisplayed on the screen

4) the system maintains a history file which records the full path and file name each time a data file is saved. This history information is accessible by accessing the FILE menu option and selecting the HISTORY option which appears on the drop-down menu. Use of this option allows one to recall a prior calculation easily. The number of files maintained in the history file is configurable.

5) the ability to create your own output file (like a mail merge file) and therefore format the output as you see fit. [Actually, I have left this capability in the trial version.]

6) Range Print Option - you can easily print a series of calculations so that you can publish what the present value will be in the future. This option is intended to allow the user to generate output for a client indicating how much a plan should pay to a participant based on the exact date in the future that a payment is made, but it is not limited to merely that.

7) the information that is recorded allows for participant name and plan name.

A program like this requires that interest rates and mortality tables be updated in order to remain usable. I currently intend to provide 12 months of such updated information to purchasers of the lite version without an additional charge and 24 months to purchasers of the pro version. I expect any additional charge for updates beyond those dates to be nominal.

The cost for a single user license of the lite version is $225 and for the pro version it is $425. Additional licenses for the same site are 25% of the original price, with a maximum payment of twice the original price. That is, once you have purchased 5 licenses (the original and four additional licenses) you have a site license and can use it on all computers in the office. Contact me about pricing for multiple offices or for those, like me, who have businesses where employees work from home in multiple locations.

I want to thank Dave Baker for generously agreeing to let me post this. I intend to use a separate site for support discussions as I don't really think it is appropriate for me to use the regular forums within the BenefitsLink message boards on a continual basis.

If anybody notices any calculation errors, please let me know about them.

I can be reached via email at mike.preston@prestonactuarial.com

I intend to set up a download site that will enable purchase and downloading online.

Thanks

mike

NOTE: The attachment was deleted and re-uploaded at 9:45am Eastern time on Friday, February 8. If you downloaded the attachment before then, you may want to uninstall that version (or not install it at all) and install this version. Then again, you may not. The only changes were: 1) fixed a mis-spelling on the main screen (you would think I would know how to spell preretirement) 2) enhanced the pathing so that the context sensitive help will hopefully work no matter how one has their explorer settings "set".

PPAPresentValuesInstallTrial.pdf

Share this post


Link to post
Share on other sites
Guest Jefik

Mike, We downloaded the program - it is great - a significant timesaver! We checked it out - is perfect! We will order a full version.

Please consider coming up with a network version.

Thanks,

Jefik

Share this post


Link to post
Share on other sites

The Pro version will within a week. The big issue appears to be pre-retirement mortality. I have found, through informally surveying a number of people who have J&S normal forms in their plans, that there are two basic ways that people have been implementing pre-retirement mortality. I'm not sure whether 417 is clear as to which way is appropriate so I need to ensure that the program will be able to do it either way.

The two methods in common use appear to be:

1) The "simple" way: decrement between current age and retirement

age is based solely on the primary annuitant. If the primary

annuitant is considered to have died, there is a forfeiture. If not, the

annuity payable at retirement is a J&S at the elected percentage to a contingent annuitant whose age is the same as the current contingent annuitant's would be.

2) The "complicated" way: decrement between current age and

retirement age is based on both primary annuitant and contingent

annuitant. There are four states to value:

a) both live to retirement age of participant: commence J&S

b) primary annuitant only lives to retirement age of the participant:

commence SLA to primary annuitant

c) contingent annuitant only lives to retirement age of the

participant: commence SLA of greater of J&S percentage or QPSA

percentage to contingent annuitant

d) both die: forfeiture

The "complicated" version appears to provide for an increase of roughly 2% at 100% J&S over the "simple" version.

I'm finished with the "simple" version and am working on finalizing

the "complicated" version.

If anybody implements pre-retirement mortality differently, I'd love to hear the specifics.

Of course, the really simple way to deal with this for 417 purposes is to write your plans so that there is no pre-retirement mortality, but that won't help existing plans! :unsure:

Those who have already ordered the program will receive these sorts of minor updates to the program's capability without additional charge.

mike

Share this post


Link to post
Share on other sites

Mike... GREAT program. I was interested to know your status for providing J&S annuity forms for the program. Thanks...

Share this post


Link to post
Share on other sites

Glad you like it. It has been a long week! :angry:

I actually have the J&S done, but I'm stress testing it to make sure there aren't any holes in the logic. I guess that means it isn't really done. I KNOW that means it isn't ready to go out the door. Those who have the program will get an update with the J&S feature as soon as I can up my confidence level as to its accuracy! I'm contemplating sending it out as a beta with respect to this feature just to get it out a bit earlier. If somebody wants to be a beta tester on the J&S capabilities, please contact me off list.

The next version also has an excel-like feature built into the monthly annuity field. Hence, if you have a formula for the benefit, you can use that instead. Something like: $185,000/12*.1 will give you the value based on an annuity of $1541.67.

While I'm adding a J&S normal form feature to the program, there are a number of people who have asked whether the program can calculate alternate forms. I don't think that is in the cards, for the near future, anyway, unless somebody can describe for me why doing so would be useful based solely on the segment rates. It seems to me that what folks are looking for is the ability to produce the information necessary to generate distribution paperwork, like relative value disclosures. That is a great extension of the program and I may implement that at some point. But it isn't trivial, because to do so requires that the program either have lots and lots of mortality tables or, at the least, allow users to enter in their own mortality tables. That is, the program needs to be capable of doing actuarial equivalence based on PLAN FACTORS. Right now, the program only has actuarial factors based on segment rates/regulatory mortality tables. Am I wrong on this? Is it useful to calculate alternate forms based on segment rates/regulatory mortality tables?

Share this post


Link to post
Share on other sites

I tried and failed. All I got was an immediate "File does not begin with '%PDF-' message and wouldn't let me go anywhere from there. I ain't no techie.

Share this post


Link to post
Share on other sites

If you have a browser that attempts to open the file based on its name, it will attempt to invoke the software you have for pdf reading. As you noted, it ain't a pdf file so the software appropriately complains.

What you need to do is DOWNLOAD the file. Try right-clicking on it and see if your browser gives you an option to do something like "Open link in IE tab" (if you are using Firefox). If so, when you do that you will then have an option to save the file (and then you can rename it). If you are using Internet Explorer, try right clicking and using the "Save target as..." option.

Share this post


Link to post
Share on other sites

Nice program Mike. I am one of those who would love to see optional forms added to the program, with all results able to be output to a data file to be used to generate customizable distribution forms.

Share this post


Link to post
Share on other sites
Nice program Mike. I am one of those who would love to see optional forms added to the program, with all results able to be output to a data file to be used to generate customizable distribution forms.
Thanks for the kind words, David.

The program already has the ability to output its results to a data file (in any format you choose). But it doesn't do alternate forms, yet. I have had one request to make it do alternate forms using the PPA 417(e) mortality table/segment rates as the basis for alternate forms calculations. I think that can be added fairly easily (famous last words), but I'm wondering if there are enough people who have plans with that sort of provision to make going through the effort worthwhile?

It seems that since the program already can output the information to a merge file, the program can already do alternate forms in a merge sort of way. For example, run the program and set the parameters up to do a lump sum based on Single Life Annuity rates. That information is now output to a text file. Change the information on the screen to an alternate form (say, 100% J&S based on a specific beneficiary's birth date) and output that to another text file.

It would then be a simple matter to add the two text files together and use them as input to an excel or word mail merge. I know that Word *can* (although it isn't trivial) do merges based on information on multiple lines. It *is* trivial to do so if the merge is being done in Excel.

So, other than putting together a little tutorial on how to output the information necessary and then how to use that in Word or Excel I'm left wondering if it makes sense to try and have my program do any of these things automatically?

I need to ponder it a bit, I suppose.

Share this post


Link to post
Share on other sites

Mike... we've been using your program with great results, but are now trying to calculate J&S annuities. We're trying to do it "manually", but would like to use your program to make life easier, and less prone to errors. Can you give us a status update for J&S annuities? Thanks again...

Share this post


Link to post
Share on other sites

Be prepared, this might be a relatively long response. I'll try to make it as brief as I can, though, for both of our sakes. :rolleyes:

First, thanks for the kind words and the follow up.

In response though, and in a nutshell, client responsibilities have precluded me from finishing the J&S up right now. First, I'll tell you what *IS* working and then I'll tell you what I need to *GET* working.

J&S calculations using the same commutation functions ARE working when all ages are integral. I could probably send out a version with the requirement to have all ages integral in a fairly short period of time (like a couple of days after the 3/17 deadline).

J&S calculations where any age is something other than integral have some "technical difficulties" in that the approach I have used for years (4-way interpolation) has a fundamental flaw when applied to segment rates: by definition an interpolation involves a high-order number and a low-order number. If all periods in between don't belong to the same segment rate period, the interpolation appears to fall apart, on a theoretical level because, by definition, there will be a payment (or a portion of a payment) that is valued using an interest rate which is inconsistent with the segment rate that is called for.

So I've reconciled myself to the fact that I can retain the interpolation methodology only for non-integral periods when dealing with single life and certain period annuities. And when I get back to this (after March 17th) I'll implement a "first principles" approach to J&S annuities.

However, I can already hear the chorus of people saying that *if* they are going to use J&S calculations based on first principles, they will want their single life and certain calculations to be done on a first principles basis. Actuaries are like that. Some of my best friends are actuaries. Since I know that will come soon after I publish a J&S routine that uses first principles, I'm resigned to the fact that I should include the option of doing *ALL* calculations on the basis of first principles.

So, the apple I have to bite into seems a bit larger than anticipated.

Now, there is a difference between practice and theory. It will probably be difficult to convince me to put out a program which calculates numbers that are fundamentally flawed at the theoretical level (doing interpolation between factors where some payments should be at a different segment rate, for example). However, if the error rate is low (less than 1/100th of 1% for example), I'm sure few people (other than me) would care. The problem is that I need to implement it before I can test it and if I've implemented it, I don't need to test the error factor!

Remember, the dates routine that is already in place allows age calculations to be done to the nearest month or to the percentage of a year based on days. So it is possible to come up with a present value based on the plan participant being 43.2935 years old and retirement date at 62.0233, leaving a discount period of 18.7298 years. Of course, each age can be rounded to the nearest month, in which case the current age would be 43.3333 and the retirement age would be 62.0000, with a discount period of 18.6667 years.

If somebody can convince me there is a fundamentally sound way to interpolate non-integral ages using commutation columns and segment rates, I'm all ears.

Barring that, do you think that a version which forces ages to be integral for J&S has any usefulness?

Again, thanks for the followup.

Share this post


Link to post
Share on other sites

Two actuaries -- three opions.

My vote is for reasonable and consistent, whatever the aproximation technique used, especially when using a one-size-fits-all mortality table and there are variations in stability period and look-back month.

Share this post


Link to post
Share on other sites
Two actuaries -- three opions.

My vote is for reasonable and consistent, whatever the aproximation technique used, especially when using a one-size-fits-all mortality table and there are variations in stability period and look-back month.

Thanks, Andy.

I understand what you are saying and on one level, I agree. However, when it comes to this segment rate concept, I'm just having a hard time convincing myself that an approximation which involves a reference to a payment discounted at the "wrong" segment rate will necessarily be accepted as reasonable.

Share this post


Link to post
Share on other sites

Mike,

My two cents worth is that a big allure of your program is there is little else out there commercially available. I think being "first" with reasonable approximations, even if not totally perfect, is more important than delaying until it's perfect and satisifies everyone. Sooner or later the larger actuarial software vendors will eventually get to creating their own mortality table utilities programs and presumably they have more dollars and man power to throw at these challenges and may eventually perfect the process. My firm subscribes to 2 of the major small plan actuarial software systems and yet we would still purchase yours if the J&S process is worked out even on a reasonable but imperfect basis. At some point I know our software providers will get it done on there end, but in the meantime we need to be administering plans and doing distributions so someone who can develop a reasonable approximation for J&S (many of our plans normal form are J&S) is still worth the purchase vs. waiting on our normal software vendors. That's just where our firm is at.

Share this post


Link to post
Share on other sites

Thanks, Jay, appreciate the input.

Share this post


Link to post
Share on other sites
J&S calculations using the same commutation functions ARE working when all ages are integral. I could probably send out a version with the requirement to have all ages integral in a fairly short period of time (like a couple of days after the 3/17 deadline).

*****

Barring that, do you think that a version which forces ages to be integral for J&S has any usefulness?

FWIW... looking at single life anniuties, between ages 44 and 45, comparing your actual calculations with interpolation to integer months, the largest difference (age 44 and 6 months) was less than 0.07% with mortality, and less than 0.03% without mortality. Choice of different interest rates (look-back months) produces much larger differences. By using our "manual" calculations for J&S annuities, we had intended to interpolate integer ages, assuming that the small differences for single life annuities will be essentially true for J&S annuities. So, yes, if your program does J&S annuities for integer ages, it will be very useful. Will hopefully be looking for your updated program next week> :rolleyes: Thanks again...

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×
×
  • Create New...