Spread sheet testing

[Fred Payne]

Our business has decided to get more involved in cross-testing; previously, we have sub-contracted the actual cross-testing to a law firm. Consequently, we are not as familiar with the calcs as we would like.

I'm building some macro-driven spreadsheets to handle the cross-tested calcs. So far, I only have limited examples of plans that have been cross-tested for use in confirming my calculations. Once I'm confident of the calculations, I'll both expand the spreadhseet to accomodate a variety of situtions, i.e., 401ks, and complete the automation. I have attached the zipped Excel spreadsheet I am developing. I would appreciate anyone confirming my calcs by comparing its results against calcs you have undertaken. Alternatively, if you could send me your calcs, I'll run the numbers.

I consider myself a top-notch spreadhseet writer, and my finished product should be a productive tool. Anyone who helps me trouble-shoot my spreadhseet and/or can answer questions about cross-testing I will undoubtedly have, can have a copy of the spreadhseet when I have completed it.

[Tom Poje]

sounds like a good challenge Fred! ah, if I can get a moment free.

I am not top notched Excel, so only have a real crude version I use as a number check for E-Bars, just to make sure the software I am using is doing things correctly. Most of the times it works ok, but I haven't written it to consider age def. nearest/last. - maybe yours will do that!

Thanks!

[Fred Payne]

I've had no luck in finding a stand-alone program that does the cross-testing. Our Relius Administration software will only perform tests once the allocation has already been determined. It's no help in trying to determine what the allocation should be. Consequently, I'm forced to write my own.

I'm receptive to any input from experienced practitioners who can "spec out" what a spreadsheet should involve.

[Guest Richard Scheer]

Fred,

I downloaded your spreadsheet and will look is over for you.

A couple of quick comments:

(1) Why aren't you imputing disaprity into your calculations? This will raise the EBARs for all employees, but the NHCE's will be increased by a bigger percentage which will help the HCEs.

(2) I see that you have to manually input the contributions for the different rate groups. A better option would be if the spreadsheet can solve for minimum required NHCE allocation automatically.

A few years ago, I designed a spreadhseet which we used in our UNIX system which can do all of the calculations and solve for the NHCE contribution. I am in the process of converting it to Excel, but am not as fluent in Excel as you seem to be.

Maybe we can collaborate on the project together. I know all of the required formulas and the steps necessary to solve for the NHCE contribution.

Let me know what you think.

We are in the process of updating our email system. In the interim, I cam be emailed at fedpen@att.net.

[AndyH]

Just a caution. Remember, the regs aren't final yet. Didn't they initially say this month they'd be finalized? Something tells me they may impose greater restrictions in light of new law than a 1/3 or 5% requirement.

Any word on when the final regs will be issued?

I'll also try to find some time to look at the spreadsheet. My company has developed a proprietary program as well due to the apparent lack of a flexible commercial alternative.

[Guest RobertH]

I am also interested in alternatives which are available for cross-tested plan projections. The Relius system is labor intensive because the testing is only possible after running an allocation. FredH mentioned that his company has a proprietary product -- is this product being marketed? I think all of us are searching for an alternative and any help would be appreciated.

[Guest Tom Geer]

I have a spreadsheet that compares cross-testing and age weighting with other plan designs. It does not impute disparity because we sometimes don't know top-heavy status. I have the internal logic and some of the results pieces done, but it still requires some data manipulation. I'll post it when it's in better-looking shape. I'll also review yours, Fred.

[Fred Payne]

Several Users have been quite generous in their critique of my spreadhseet and have answered some technical questions for me. I also paid a local actuary to confirm many but not all of the calcs.

I'm confident that the spreadhseet calculates cross-testing accurately for straight PS plans, including disparity. (I run my results against the Relius program and I get identical numbers.) I believe the spreadsheet will calculates cross-testing with a 401k feature BUT WITHOUT DISPARITY. All bets are off when disparity is invloved and I could use some help here. (I've been told that if the Plan is a 401K Safe Harbor using the 3% non-elective, disparity hurts you so don't use it in that situation.)

The Zipped file you can download includes some instructions for the spreadsheet's use. It's real easy to use and includes quite a bit of automation. The output is intended for Client presentation.

If you detect an error, I'd apprecaite you bringing it to my attention. If you can help me out in areas where the spreadsheet is deficient by answering my technicakl questions, that would be wonderful.

[Tom Poje]

Fred:

I know you have done a bang up job on your spreadsheet, at least one or two versions i have seen and have played with. It has made some things easier along the way.

To clear things up:

you said

(I've been told that if the Plan is a 401K Safe Harbor using the 3% non-elective, disparity hurts you so don't use it in that situation.)

this is maybe, maybe not.

example: safe harbor 401(k) using 3% SHNEC (Safe harbor nonelective) owner is only one who gets additional contribution.

lets say 12% extra.

If you impute you would have:

rank and file 3% SHNEC - no impute allowed.

owner additonal 12% - impute and his E Bar increases while no one leses does.

in 2002, the minimum gateway would require the NHCEs receive 5%.

Now you have

rank and file 3% SHNEC - no impute

2% additional - impute

owner - 3% shnec - no impute

12% additional - impute

but imputing disparity will increase an E-Bar at most .65 (or up to .75 depending on the SSRA). in the case of an individual will large comp, this increase is even smaller. so, if there is an additional contribution to ALL parties involved, imputing disparity may help.

[Fred Payne]

I'm a student when it comes to cross-testing in general and imputing disparity in particular. I need someone to teach me the rules for making the calculations and then I can incorporate them into the spreadsheet.

My understanding is that one cannot impute disparity on salary deferrals or match contributions, but on all other contributions. Thus, disparity comes into play when performing the Ratio Percentage Test and the Non-Discrimination Class Test. However when undertaking the Average Benefits Test, disparity is not used.

Is this right?

[Tom Poje]

close, but no cigar.

I will use allocation method rather than accrual method as an explanation, but the concept is the same.

Maximum disparity is lesser of 5.7 or the ps amount

EE defers 5%, match 2%, ps 4%, shnec 3%

avg ben % is 5 + 2 + 4 + 4(imputed disparity) + 3 = 18

for the nondiscrim test (401(a)(4)) you would have

4 + 4(imputed) + 3 = 11

in other words, you get to impute for the avg ben % test as well.

or, if given a spreadsheet with values entered and cute little columns, you only impute on the purely nonelective column (this can include forfeitures) but not on deferral, match, QNEC, QMAC, SHNEC, ESOP.

If it helps, you would not integrate any of those items, so you impute on them either. the only piece you could integrate is the purely nonelective, no strings attached piece.

[Guest Roman]

You did a good job, Fred, and generous enough to share your talents. My suggestions: 1). Can you add in the option of using banding? 2). Maybe put all data in one sheet and so you save only that sheet instead of saving the whole file everytime you use it thus saving space.

I am quite good in spreadsheets too but my forte is Lotus. My wife and I are actuaries and we can share talents together. She has an Excel spreadsheet which is not quite user friendly but you can decipher what it does with your familiarity with Excel. Write me at rtu@pdq.net and we can share ideas. Regards and keep up the good work.

[Guest rickw]

Fred: Love the spreadsheet.

It is my understanding that EACH rate group does not have to pass the Avg. Ben. Pct. Test--just the overall plan.

[Fred Payne]

When one or all Rate Groups fail the Ratio Percentage Test, passage of the Average Benefits Test is mandatory.

The Average Benefits test has two parts. In Part 1, each Rate Group must pass the Nondiscriminatory Classification Test. A Rate Group does so if its coverage ratio is greater than or equal to the Midpoint Safe Harbor Percentage--the same percentage for each Rate Group. All Rate Groups MUST pass the Nondiscriminatory Classification Test BEFORE you can perform Part 2 of the Average Benefits Test. For Part 2, you are correct that passage is based on the entire plan and not any specific Rate Group. But you need to evaluate the coverage ratios of each Rate Group first.

I am working on a third version of the spreadsheet that places the various component parts of the tests on separate pages in the worksheet to allow the user to "tab" to each test section, rather than scroll left-to-right.

[Guest rrausser]

Great spreadsheet. Have there been any more recent updates?

[Fred Payne]

Here's my third version of the spreadsheet. It should allow the user to more easily follow the calculation of the EBR and the mechanics of the cross-test. Many thanks to those who downloaded the program and critiqued it. Any incorrect calculation that has been brought to my attention has been fixed.

The cell references are quite complex. DO NOT ADJUST YOUR DATA BY DELETING A ROW! DO NOT SORT YOUR CENSUS DATA ONCE IT IS ENTERED INTO THE DATA PAGE OF SPREADSHEET! A page is included in the spreadsheet on which census data can be modifed and sorted, then manually copied to the data page.

Do not hesitate to email me directly if you have any questions as to its use or suggestions for improvement at fredp@RetirementAssets.com.

I am working on a version that will allow for the earned-income calculation when one or more of the HCEs are self-employed.

[Guest crosseyedtester]

Hi Fred,

Thanks for your spreadsheet!! I was able to follow the links like you said and managed to get the numbers to match my own for confirmation.

Two things I'm still unclear about....

1. Mortality - Is there a set mortality table and rate to use? The one I was using was not one of the two options in your spreadsheet. Furthermore, the plan documentation for the plan being tested does not state what mortality to use as I believe it has not yet been updated.

2. Rate group testing - I'm still unclear what this safe harbor / unsafe harbor 50% testing is about. But it needed to be done according to your earlier posting since the individual rate groups failed the coverage testing. Do you have a hint on where to read more about it?

Thank you very much for your help.

[Tom Poje]

Fred:

I have not looked at the new sheet yet, but I noticed the other day on the earlier version of the spreadsheet that the 1983 table is not correct (or at least doesn't match Relius). could be we have a bad table on our system or the table on your spreadsheet is not correct. (Or at least as far as I can tell)

[Fred Payne]

First a response to Tom's question. The mortality tables I currently use were furnished me by an actuary. And I assumed they were correct. And with these tables I get the same answers to every example of cross-testing I have seen published by PPD or the ERISA Outline Book.

(Unfortunately, there is very, very little published information on cross-testing that I have found. One reason I make my spreadsheet available to everyone as a way to verify my calcs are correct!)

I can get the same answers from Relius from my spreadsheet--most of the time. For example, within a census occasionally one or two of 50 EBARs don't agree. Truthfully, I don't know why such anomolies occur. But they haven't caused a failure to the cross-test.

That all being said, I always pay attention to comments from Tom Poje and now I'm nervous. Where is the "Source" for annuity rates so I can verify the tables? Anyone, please?

Regarding Crosseyedtester's questions, my understanding is that if the document does not specify the Table and Rate, it's at the discretion of the tester so long as it is allowable by statute. The PPD document specifies the Tables and Rates I incorporated into the spreadsheet. The Spreadsheet would allow a practitioner to incpororate any other table.

Concerning the Safe Harbor for the NHC Concentration Ratio, I pulled this part of the calculation from the ERISA Outline Book, the section on Nondiscrimination Testing. If one fails the Ratio Percentage Test and must resort to passing with the Average Benefits Test, EACH Rate Group must pass the Nondiscriminatory Classification Test. To pass the NCT, "the coverage ratio must be equal to the midpoint between the applicable safe harbor percentage and unsafe harbor percentage in Trea. Reg.1.401(B)-4."

[Tom Poje]

Fred (and all):

On your spreadsheet, one page is 'Tables'

for 1984 UP, the value for age 65 at 8.5% is 7.948576

if you multiply this by 12 you get 95.383. if you look at the APR for nondiscrim report you see 95.38

for 1983 IAM the value is 9.201652. multiplying by 12 I get 110.42, but my nondiscrim report shows 105.93. So, I either have a very bad table on my system, or the table in the spreadsheet is bad. (Or I don't understand how the table works!) On the old pentabs system the value for the APR is also 105.93.

[AndyH]

The 105.93 (I'd use 105.9347) matches our systems for 1983 IAM (Male) at 8.50%.

[Mike Preston]

Fred, the reason you probably match everything published is that the actual APR at retirement doesn't matter in the typical case. It could be 50 for all we care. It just has to be the same for all participants at the same testing age and then if somebody is at a different testing age, the ratio between the APR's must be accurate. That might happen even if the APR's are taken from the wrong source. I've seen stranger things happen.

[Tom Poje]

I will disagree with Mike, slightly. I would agree with his statement only if you do not impute disparity. While the APR generally acts like a constant (since everyone has the same APR) I have seen cases where changing the Moratality table makes a difference.

now, I certainly wouldn't disagree with his picks for the basketball. C'mon Mike, help the rest of us out!

[Tom Poje]

Andy:

I believe the value that prints on the report is only printing to two places due to space restraints. I think the system actually uses the value you indicated. sorry I did not make myself clearer.

[Guest crosseyedtester]

Originally posted by Fred Payne

Regarding Crosseyedtester's questions, my understanding is that if the document does not specify the Table and Rate, it's at the discretion of the tester so long as it is allowable by statute.  The PPD document specifies the Tables and Rates I incorporated into the spreadsheet.  The Spreadsheet would allow a practitioner to incpororate any other table.

Regarding the 'PPD document', without having access to this document, or really knowing what it is, do you happen to have a quick pointer to it or else any chance to list what tables and rates they specify?

The table i'm questioning about is GAM 83 Male at 8.5%.

Thank you.:confused: