Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Pension - Social Security
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions


Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 02-26-2016, 01:34 PM
JAC JAC is offline
SOA
 
Join Date: Feb 2016
College: Columbia University
Posts: 2
Default Accrued benefit calculation in excel doesn't match software

I'm testing cash balance plans for compliance with 401(a)26. To pass the test, at least 40% of the participants have to be accruing benefits at at least .5% of their salary. The company I work for has software (Relius) that calculates this, but when I cross check in excel I don't get the same result. Here's what I'm doing. Let's say in the CB plan, we're giving each employee a 2% pay credit. So if his salary is 128,375, he is getting 2657.5. Say he is 25 years old and accumulate it to retirement age (65) at 5% interest:

2567.5*1.05^40= 18075

Buy him an annuity at age 65 at the APR of 145.8 (this number is from the table the software uses).

18075/145.8 = 123.97

So his accrued benefit is 123.97.

Yet the Relius system, using the same assumptions, gives me an accrued benefit of only 58.90 for this same employee. As a result, plans that pass the test according to my excel sheet don't pass according to this software. Does anyone know what's going on here?
Reply With Quote
  #2  
Old 02-26-2016, 04:15 PM
Helen Sass Helen Sass is offline
Member
SOA AAA
 
Join Date: Oct 2015
Location: Nearby
Studying for Colorectal Exam
College: SUNY Alumni
Favorite beer: #1 - Free; #2 - Cold
Posts: 2,715
Default

Quote:
Originally Posted by JAC View Post
I'm testing cash balance plans for compliance with 401(a)26. To pass the test, at least 40% of the participants have to be accruing benefits at at least .5% of their salary. The company I work for has software (Relius) that calculates this, but when I cross check in excel I don't get the same result. Here's what I'm doing. Let's say in the CB plan, we're giving each employee a 2% pay credit. So if his salary is 128,375, he is getting 2657.5. Say he is 25 years old and accumulate it to retirement age (65) at 5% interest:

2567.5*1.05^40= 18075

Buy him an annuity at age 65 at the APR of 145.8 (this number is from the table the software uses).

18075/145.8 = 123.97

So his accrued benefit is 123.97.

Yet the Relius system, using the same assumptions, gives me an accrued benefit of only 58.90 for this same employee. As a result, plans that pass the test according to my excel sheet don't pass according to this software. Does anyone know what's going on here?
Does the plan document say to use 5% as the interest crediting rate? Or does it say something like "the Applicable Interest Rate that would have been used for purposes of Code Section 417(e) prior to the enactment of the Pension Protection Act of 2006 " (a.k.a. the 30-year Treasury rate or the GATT rate)? If so, look at the definition of the Applicable Interest Rate in the first part of the document to see which month to use. If it is something like the second or third month prior to the beginning of the current plan year it will be more like a little over 3% https://www.irs.gov/Retirement-Plans...est-Rate-Table which gets you durn close.
__________________
Spoiler:
Quote:
Originally Posted by pete5383 View Post
Whew!!~ I can finally stop this continuous drawer-shitting that I've been doing for the past week or so.
Reply With Quote
  #3  
Old 02-29-2016, 02:42 PM
JAC JAC is offline
SOA
 
Join Date: Feb 2016
College: Columbia University
Posts: 2
Default

Thanks, this was very helpful. It turned out I had forgotten to fill in the 5% crediting rate in one of the pages of plan specs. Apparently when you leave it blank the software falls back on the 30 year treasury rate as you said.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 06:28 PM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.31983 seconds with 11 queries