This Excel workbook computes and displays retirement plan contributions. It uses three types of worksheets: Setup-Enter monthly data and plan requirements; Monthly: Enter employee salary and contributions and display employee information and employer matches; Summary: Accumulation of salaries, contributions and matches.
The Retirement Workbook - Basic is an Excel workbook dedicated to calculating employee matches and producing monthly contribution reports. Descriptions and illustrations are included below.
The transmittal sheets can also include fund breakdowns if needed.
The workbook is a collection of three types of worksheets:
- The "Setup" worksheet provides:
- A help file to provide detailed instructions.
- Areas to enter informative information to be presented on each of the reporting worksheets.
- An area to define how to determine the employer match.
- A feature to display from 10 to 50 employees on the reporting worksheets.
- A table to enter employee data to be displayed on each of the reporting pages.
- Each employee's name, social security number and account number. This is automatically copied from the Setup sheet.
- The employee salary and contribution which is entered each month into the worksheet.
- The employer match which is calculated from the employee salary and contribution and the rules established in the Setup worksheet.
- The total contribution.
The "Summary" worksheet accumulates the data from each of the Monthly worksheets.Some of the features of each sheet are explained below.
The Calculation section sets the data used in determining the employer match.
The calculation algorithm accommodates two levels, one on top of the other, of matching employee contributions. The first matching region covers the employee's contribution from zero to "Cap, Region #1" as a percentage of salary. The second matching region covers the employee's contribution from "Cap, Region #1" to "Cap, Region #2" as a percentage of salary. The chart below illustrates the employer's match as a function of the employee's contribution and for the following settings:
With these settings, the employer would match the employee contribution dollar-for-dollar for a contribution ranging from zero to 3% of the employee's salary. Furthermore, the employer would match the employee contribution at the rate of half-dollar for each dollar for contributions ranging from 3% to 5% of the employee's salary.
Each month after January allows a "catch up" in the employer match. This is accomplished with the selection in the yellow box. If "N" is selected, all
computations are based on the employee's salary and contribution for the month only. If "Y", then the match is determined on the year-to-date salary and contribution. Additionally, the previous month's matches are subtracted from the total to compute the match due for the current month.
Retirement Workbook $29.95 Secure Payment Options
Special ! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $70.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
GIVE YOURSELF OR YOUR COMPANY 24/7 MICROSOFT EXCEL SUPPORT & QUESTIONS