Late Deposit Lost Earnings Calculation

[Archimage]

Anyone ever created a spreadsheet to mimic the DOL calculator that one could just copy/paste the information into the spreadsheet?  Sure would be easier than doing them one by one on the DOL page. 

[BG5150]

You could, but you would need to get all the underpayment rates and for which months they applied.  Someone in my office did one, but I cannot find it at the moment.

[401king]

Upon realizing that the DOL VFCP will require you to use a print of their webpage we got rid of that project. 

[RatherBeGolfing]

I agree with the above.  That said, I have been able to get the DOL to accept a combination of their calculator and a spreadsheet.  It has probably been 5 years since I did it that way so I'm not sure if they would still accept it, but it may be worth a try.

For example, lets say you have 100 participants who deferred and you have one late payroll.  Rather than doing 100 individual calculations,you do one earnings calculation on the entire amount and use a spreadsheet to distribute the lost earnings based on each participants "share" of the late deposit.  The result can be a little different from one by one calculations due to rounding, but it is minimal and to be on the safe side you can always round up so that a participant will get a fraction of a cent more rather than less.

 

[ESOP Guy]

47 minutes ago, RatherBeGolfing said:

I agree with the above.  That said, I have been able to get the DOL to accept a combination of their calculator and a spreadsheet.  It has probably been 5 years since I did it that way so I'm not sure if they would still accept it, but it may be worth a try.

For example, lets say you have 100 participants who deferred and you have one late payroll.  Rather than doing 100 individual calculations,you do one earnings calculation on the entire amount and use a spreadsheet to distribute the lost earnings based on each participants "share" of the late deposit.  The result can be a little different from one by one calculations due to rounding, but it is minimal and to be on the safe side you can always round up so that a participant will get a fraction of a cent more rather than less.

 

Another version of this that I never had to show my work to the DOL or IRS but how I computed a large group of people's lost earns was I put $100 in the DOL calculator.  It came back with a result.  For example, $117.29 was the result I got.  I took any given person's late deferral times by 1.11729 in a spreadsheet.   I checked my work on a few people via the DOL calculator and got within a penny for results.  We used those results to compute the deposit and the excise tax due.  Once you know the compounded interest rate factor if everyone has the same situation it ought to work. 

I haven't worked on 4k plans now for 5 years and like I said I never had to show my work to a DOL auditor or IRS auditor as they never asked but I don't see how that math doesn't work every time. 

[Belgarath]

I've done it exactly as ESOP guy did, and used the same methodology for VCP filings with the IRS as well. Always has been accepted without question. "Past performance is no guarantee of future results!"