we dream, we create, we change, we love

An Actuary without Excel Is Like…

leave a comment »

He’s Got the General Nondiscrimination Test in His Hand

Back in the early 90s, interviewing a candidate for the pension actuarial research unit I once led in Washington, DC, I steered the conversation toward the general nondiscrimination test. The candidate had led actuarial software development efforts for a major nationwide competitor, so I was interested in getting an idea of his computer skills. Oh, he protested, that can’t be done except on a mainframe computer. I calmly informed him that I had just finished performing such a calculation for a group with more than 50,000 plan participants on my laptop. Forgetting who he was speaking with and what his goal was, he vehemently argued the point with me, claiming that at best one could only perform a crude estimate of the calculation on a laptop.

No. It can be done quite easily. And now, on an iPhone in the palm of my hand. For even larger groups. And with projections of future tests or alternatives that look at things like potential plan amendments.

Using Excel.

Not only did that job candidate not know his software and programming and systems and actuarial math well enough, but he really did not know with whom he was talking. One of the key regulators who developed the final 1991 regulations has often credited me personally as the creator of the general nondiscrimination test. What even that regulator probably doesn’t know is how instrumental Excel was in that creation.

So, to this day I’m one of maybe a handful of people who knows without looking into a manual how to do a general nondiscrimination test in Excel? There’s a very good reason why I’m so good at it. When the regulations that were proposed in 1989 were found to be too clunky with what then was called rate segment restructuring, I rolled up my sleeves and did a lot of creative problem solving with Excel, searching for a better way to arrive at a reasonable result. Rather than credit me with coming up with the idea for the general nondiscrimination test, we maybe should credit Excel. And so the reason I can do it so easily in Excel is because I used Excel to design the test in the first place.

And the thing is, I have since found no other competing software that can do the same.

Even if an actuarial software firm does use APL or C+ or any other programming language to jerry-rig a system to do the general nondiscrimination test – perhaps even with some clunky app version that could run on an iPhone – could it possibly promise me the flexibility of multiple general nondiscrimination tests, so as to compare alternative options or to predict future transactions or help out with a potential merger? Excel can give me that, without locking me into how I might need to do it for some unique situation.

And yes, I know, for the general nondiscrimination test even Microsoft itself has its own competitor to Excel in the form of its database giant, Access. All I’ll say there is that I’ve tried. Excel gets me there quicker and easier.

And although I doubt anyone but me will ever read this, so I know I’m mainly telling myself all of this: for the record, no pension actuary currently practicing in the U.S. would get very far without the need to perform the general nondiscrimination test. So when I claim that I know of absolutely no way to do that test with as much power and flexibility as Excel gives us, I’m pretty much echoing this morning’s Wall Street Journal article — without Excel, we cannot do what we do.

P.S. — Thank you always, Tim, for your contribution on this one. I had been using Excel for the general nondiscrimination test for several years before Tim resolved an issue I’d struggled with: getting the central formula into a format that facilitates Excel’s powerful cell-copying feature.

And P.P.S. — I continue to pose this as an intellectual challenge. At that company back in the 90s where I was partner and head of the pension actuarial research unit, the final Excel formula that we developed was so elegant as to have become one of the few specific items I might be inclined to consider to be trade secret quality. Indeed, I myself developed a separate expression of the same concept after leaving that company, so seriously did I believe in the secret nature of the formula as we had developed it. And yet as I once challenged a colleague at an enrolled actuaries meeting and have repeatedly demonstrated at numerous professional meetings since, the concept and practice is really rather simple and direct. So for anyone who wants a good Excel exercise, here it is: turn to Reg.1.401(a)(4)-3(c) for the description of the general nondiscrimination test, and don’t read it as a sentence in English; rather, read it as the English language translation of an Excel formula. Do that well, and you will know why a pension actuary finds it difficult if not impossible to live without Excel.

bumper sticker [] - excel hero

Pages: 1 2 3 4

Written by macheide

1 December 2017 at 6:52 am

Posted in excel hero

Tagged with , ,


Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: