Actuarial Outpost Accrued benefit calculation in excel doesn't match software
 User Name Remember Me? Password
 Register Blogs Wiki FAQ Calendar Search Today's Posts Mark Forums Read
 FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

Search Actuarial Jobs by State @ DWSimpson.com:
AL AK AR AZ CA CO CT DE FL GA HI ID IL IN IA KS KY LA
ME MD MA MI MN MS MO MT NE NH NJ NM NY NV NC ND
OH OK OR PA RI SC SD TN TX UT VT VA WA WV WI WY

 Thread Tools Search this Thread Display Modes
#1
02-26-2016, 12:34 PM
 JAC SOA Join Date: Feb 2016 College: Columbia University Posts: 2
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?
#2
02-26-2016, 03:15 PM
 Helen Sass 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

Quote:
 Originally Posted by JAC 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 Whew!!~ I can finally stop this continuous drawer-shitting that I've been doing for the past week or so.
#3
02-29-2016, 01:42 PM
 JAC SOA Join Date: Feb 2016 College: Columbia University Posts: 2

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.

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 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 07:17 PM.

 -- Default Style - Fluid Width ---- Default Style - Fixed Width ---- Old Default Style ---- Easy on the eyes ---- Smooth Darkness ---- Chestnut ---- Apple-ish Style ---- If Apples were blue ---- If Apples were green ---- If Apples were purple ---- Halloween 2007 ---- B&W ---- Halloween ---- AO Christmas Theme ---- Turkey Day Theme ---- AO 2007 beta ---- 4th Of July Contact Us - Actuarial Outpost - Archive - Privacy Statement - Top

Powered by vBulletin®
Copyright ©2000 - 2019, 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.16119 seconds with 11 queries