• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vacation Accrual Assistance

Hi David,

Thanks for your encouragement and vote of confidence, it is greatly appreciated.
Yes, you are absolutely correct in saying that I am "having some anxiety with some features" in the new report. Working with tables and advanced formulas are all new to me. In addition, I am anxious because I've been given yet another project that involves creating advance formulas and was hoping that your suggestions with the previous report would help me with that report as well.
If you could explain how tables actually work that would be helpful. Please respond at your earliest convenience. Again, thank you so very much for your assistance, I really appreciate it.
 
Hi,

Can someone tell me what formula I should be using to obtain the per pay cycle accrual? It appears the formula I am currently using is not working properly.
In addition, to calculating the monthly match accrual I need it to calculate if applicable, partial payments as well. Any assistance you can provide will be greatly appreciated.
 

Attachments

  • Sample #2.xlsx
    140.8 KB · Views: 4
Surrena said:
Hi,

Can someone tell me what formula I should be using to obtain the per pay cycle accrual? It appears the formula I am currently using is not working properly.
In addition, to calculating the monthly match accrual I need it to calculate if applicable, partial payments as well. Any assistance you can provide will be greatly appreciated.
Click to expand...


Hi Surrena -

Good to see you back wrestling with these calculations

I've had a shot at fixing your problem(s) - as usual I've used Excel's Table feature - it is worth learning to use it, as it clarifies formulas.

What are you trying to verify in this column?

You appear to have some duplication of data in cols Z and AA - you can probably remove AA at no loss. (There are other cols that are superfluous in this Table also - for clarity and simplicity you might want to think about removing them later when you;re comfortable with what each col achieves)



In Col AD, you are calculating the number of payroll periods remaining - the answer must be an integer; e.g. you can't have 7.2 pay periods remaining. It's easily fixed - however there's also another issue with this - shouldn't the number of Pay Periods remaining be the same for all? 401k contributions are based on Calendar years, so everyone has the same number of pay periods until the year end?
 
David Evans said:
Hi Surrena -

Good to see you back wrestling with these calculations

I've had a shot at fixing your problem(s) - as usual I've used Excel's Table feature - it is worth learning to use it, as it clarifies formulas.

What are you trying to verify in this column?

You appear to have some duplication of data in cols Z and AA - you can probably remove AA at no loss. (There are other cols that are superfluous in this Table also - for clarity and simplicity you might want to think about removing them later when you;re comfortable with what each col achieves)



In Col AD, you are calculating the number of payroll periods remaining - the answer must be an integer; e.g. you can't have 7.2 pay periods remaining. It's easily fixed - however there's also another issue with this - shouldn't the number of Pay Periods remaining be the same for all? 401k contributions are based on Calendar years, so everyone has the same number of pay periods until the year end?
Click to expand...


Hi David,

Thank you very much for your quick response!

Yes, columns Z and AA have duplications; however column AA is the focus column because it takes into consideration the annual 401k maximum contribution amount based on age. Let’s just keep both columns for now. J

In Col AD the concept is to calculate how many pay periods each employee has remaining for contributions prior to reaching the annual limits (This will vary depending on Col AC; Remaining Deferrals and Column V Deferrals Per Pay Cycle). To get to the remaining pay periods we divided the Remaining Deferrals by Deferrals Per Pay Cycle. If the number exceeded the actual number of Pay Cycles remaining we defaulted the number to 8 in Col AE. So, in some cases an employee will contribute partial payments. For example; the employee with 7.2 remaining Pay Periods for contributions will contribute $391.63 for 7 Pay Periods and a partial amount of $87.81 in the 8th pay cycle.


What we are trying to determine in Columns AJ – AQ is the estimated Per Pay ER Match; Col W that the company must pay (including partial amounts) for each pay cycle throughout the remaining pay cycles (there are 8 pay cycles remaining). Once this has been determined we can then subtract the total payments from Col AH which will provide any applicable True-Up payments. Does this make sense?

Again, thanks for your assistance it is greatly appreciated.
 
Sorry - I got confused with a similar calculation I've been undertaking. Some interesting things happen when people who are maximizing their contributions and are at a level that has them contributing the maximum before the year-end - but I won't bore you with those details, here.

OK - I understand what you're trying to achieve ... Will look at it after lunch and get back to you ...
 
David Evans said:
Sorry - I got confused with a similar calculation I've been undertaking. Some interesting things happen when people who are maximizing their contributions and are at a level that has them contributing the maximum before the year-end - but I won't bore you with those details, here.

OK - I understand what you're trying to achieve ... Will look at it after lunch and get back to you ...
Click to expand...

Trust me you are not boring me. I do understand.
Thanks, again for looking into this for me. I really appreciate it.
 
Surrena -

There's something awry in this workbook - specifically look at rows 17, 62 91 and 298 in the attached. The estimated YTD deferrals and the YTD deferrals as of 08/31/2016 seem odd .... why would the estimated be greater than the limits? And the YTD don't jive with the amounts in deferrals per Pay Cycle ...

Where are these plug numbers coming from - are they a data dump, C&P or manually entered?
 
David Evans said:
Surrena -

There's something awry in this workbook - specifically look at rows 17, 62 91 and 298 in the attached. The estimated YTD deferrals and the YTD deferrals as of 08/31/2016 seem odd .... why would the estimated be greater than the limits? And the YTD don't jive with the amounts in deferrals per Pay Cycle ...

Where are these plug numbers coming from - are they a data dump, C&P or manually entered?
Click to expand...

Hi David,

The numbers don’t jive because employees’ deferral rates vary (not a fix rate) from pay cycle to pay cycle (these percentages are current rates in payroll). Col AA “YTD Estimated Deferral Limits” takes into account the annual contribution limits (18K below age 50 & 24K age 50 & above), this is the column of focus.

What we are trying to accomplish with this report is to determine how much ER match the organization will pay throughout the remaining 8 pay cycles and set up an accrual for it each pay cycle. In addition, we are trying to determine the total True-up payment for employees who front-loaded their 401k at Bonus time (Beginning of the year) to be funded at year end (Col AF “Total Estimated YTD ER match” minus Col AG YTD ER Match as of 8/31/2016 = Anticipated True-Up payment at year end; Col AG depicts the total match that has already been funded up until 8/31/2016). I hope this clarifies things for you. Again, thank you so very much for assisting me with this, I really appreciate it.
 
OK - Take a look at this - Cols AI:AP show the Employer Match amount for each remaining payroll period, if I'm understanding your problem correctly.

I don't think it's the most elegant solution, but let's see if it makes sense to you. I apologize the formula is a little involved, but that's the best I could without totally rebuilding the model ...

I've assumed that the Employer matches are only paid when the Employee makes a deferral in a Pay Period. Thus, for example, if the remianing Pay periods for Contribution is 3.729 - the employer matches 3 periods and 0.729 of the normal amount in the final one ... (it's highlighted in green)
 

Attachments

  • Sample #2 = DME.xlsx
    172.1 KB · Views: 8
David Evans said:
OK - Take a look at this - Cols AI:AP show the Employer Match amount for each remaining payroll period, if I'm understanding your problem correctly.

I don't think it's the most elegant solution, but let's see if it makes sense to you. I apologize the formula is a little involved, but that's the best I could without totally rebuilding the model ...

I've assumed that the Employer matches are only paid when the Employee makes a deferral in a Pay Period. Thus, for example, if the remianing Pay periods for Contribution is 3.729 - the employer matches 3 periods and 0.729 of the normal amount in the final one ... (it's highlighted in green)
Click to expand...

Hi David,

This looks awesome!
Thanks for taking the time to assist me with this.

As I am still learning how tables work can you please explain the following:
- The formula for col AI - AP row 1 (What is it actually computing?)

- The formula that was used to arrive at the per pay cycle accrual in row 3,col AI - AP. In short, what is it actually computing?

Again, thank you so very much for your assistance with this. It is very likely that I will have additional questions pertaining to tables as I continue to learn more about them. I will post them here for your response.

Please note there were several columns hidden in the report of which I revealed.
 
Hi David,

In determining the YTD True-Up match I notice a discrepancy.
When subtracting the total Match amount for Col AI - AP from Col AH; Remaining Match 9/1 -12/31, I noticed that several employees are showing negative amounts. This is because they are higher than the amount in Col AH; Remaining Match 9/1 - 12/31. Can you please explain why this is happening? The grand total match for the 8 remaining pay cycles should not exceed Col AH; Remaining Match 9/1 -12/31. Again, thank you for your assistance.
 
Sorry I have not been able to get to this sooner - yesterday was a very busy day for me.

As I've noted earlier, this model has some inherent flaws. It derives some values from an external source, which we don't know much about. It also includes many columns that I would characterize as clutter, which need to be removed for clarity. I'd start to look for the underlying issue you identified by checking the source data for column AG - they are "plugged" or derived from an external source. You might also filter the table to show only those that are returning negative amounts to see what clues they will offer you.

Other than those suggestions, that's all I can do as I don't have access to the external data that drives so much of this model.

My intuition also tells me that this model is not following the terms of the 401k plan exactly - without knowing those terms I can only speculate, but there's something nagging at me ... :eek::confused:
 
David Evans said:
Sorry I have not been able to get to this sooner - yesterday was a very busy day for me.

As I've noted earlier, this model has some inherent flaws. It derives some values from an external source, which we don't know much about. It also includes many columns that I would characterize as clutter, which need to be removed for clarity. I'd start to look for the underlying issue you identified by checking the source data for column AG - they are "plugged" or derived from an external source. You might also filter the table to show only those that are returning negative amounts to see what clues they will offer you.

Other than those suggestions, that's all I can do as I don't have access to the external data that drives so much of this model.

My intuition also tells me that this model is not following the terms of the 401k plan exactly - without knowing those terms I can only speculate, but there's something nagging at me ... :eek::confused:
Click to expand...

Hi David,

Thank you for your feedback!
Can you tell me what it is about this report that is nagging at you?
Would you be able to assist me further if I provided a sample of the raw data? Although, the report at first glance appears to not be in compliance with standard 401k policy, I assure you our plan is in compliance. Outside of the annual contribution limits ($18k under 50 and $24K 50 & above), there are a number of other variables that must be considered such as the annual IRS Wage limits (2016 $265K), the plan's match limit; 4.80%/$12,720. Our plan matches 80% up to the first 6 percent. The total EE YTD contributions (as of 8/31/16), etc.

What I am trying to accomplish here is to ascertain an estimate of how much the organization needs to accrue as ER Match over the next 8 pay cycles. In addition, I want to ascertain an estimate for the total True-Up payment that must be funded at year-end.

I am so out of my element with analyzing and creating such reports and because of the sensitivity of the information I'm afraid obtaining assistance within the organization is extremely limited. Again, any assistance you can provide will be greatly appreciated.
 
Surrena -

It's my intuition - it's a gut thing. I've worked on quite a few 401k models, and some quirky things happen, especially when you approach maximum values and the deferrals are early loaded in the year. Participants maximize their matches by having the deferrals arrive at the maximum at the final PR period of the year. Also, ER matches don't typically apply to Catch up Contributions (the $6000 for over 50s) however, your plan might be different.

I don't know how your model is handling deferrals as it occurs outside of it. The inconsistency you're seeing with the negative numbers is because you're comparing something derived in your model with a number form the external source - I don't think it's to do with the formula that lays out the match amounts over the remaining periods, as that is easy to verify

I'll see if I can rustle up a dummy model for you to illustrate the above - as I noted you won't see it in most participants unless they're at the max deferral.
 
David Evans said:
Surrena -

It's my intuition - it's a gut thing. I've worked on quite a few 401k models, and some quirky things happen, especially when you approach maximum values and the deferrals are early loaded in the year. Participants maximize their matches by having the deferrals arrive at the maximum at the final PR period of the year. Also, ER matches don't typically apply to Catch up Contributions (the $6000 for over 50s) however, your plan might be different.

I don't know how your model is handling deferrals as it occurs outside of it. The inconsistency you're seeing with the negative numbers is because you're comparing something derived in your model with a number form the external source - I don't think it's to do with the formula that lays out the match amounts over the remaining periods, as that is easy to verify

I'll see if I can rustle up a dummy model for you to illustrate the above - as I noted you won't see it in most participants unless they're at the max deferral.
Click to expand...

Hi David,

Yes, with our plan several employees front-load their 401k at the beginning of the year (during bonus time) and our plan does match on catch up deferrals up to the maximum (80% on the first 6%). For example if an employee contributes 3% to regular 401k and is eligible to contribute 3% as catch up contributions the match will be calculated on both the 3% regular 401k contributions and the 3% catch up contributions for the maximum total match of 6%.

The inconsistencies that I'm seeing with the negative numbers may be a result of the deferral rates; however these totals are a data dump from payroll and often change from pay cycle to pay cycle which has a huge impact on determining accruals for the remaining 8 pay cycles. I'll await your dummy model to see if that may help. Again, thank you so very, very much for your assistance. I truly appreciate it.
 
Hi,

Can you tell me how I can create one formula for multiple criterion?

First, I want to calculate (multiply) semi-monthly rate by 16
then if the outcome is greater than 12k, return 12k, if the outcome is less than 12K return the lessor outcome. Is it possible to combine the 2 IF criterion with the multiplication for one formula?
 
=IF("Semi-Montly Rate"*16 > =12000,12000,"Semi-Montly Rate"*16)

I'd do it in the Worksheet for you, but I'm not sure which column the "Semi_Monthly Rate" resides in .... so as you build the formula, just point to that column in the first row and It's reference will be included.

if you want it done for you, send back the model you're using with the Semi Monthly Rate column highlighted.

The basic syntax of the IF statements is as follows
IF ("this equals that", Value if TRUE, Value if FALSE)
 
David Evans said:
=IF("Semi-Montly Rate"*16 > =12000,12000,"Semi-Montly Rate"*16)

I'd do it in the Worksheet for you, but I'm not sure which column the "Semi_Monthly Rate" resides in .... so as you build the formula, just point to that column in the first row and It's reference will be included.

if you want it done for you, send back the model you're using with the Semi Monthly Rate column highlighted.

The basic syntax of the IF statements is as follows
IF ("this equals that", Value if TRUE, Value if FALSE)
Click to expand...

Hi David,

Attached is the sample file. I've highlighted the columns to be included in the formula and included comments. Please let me know if you have any questions. Thank you for your assistance, I really appreciate it.
 

Attachments

  • Sample 3.xlsx
    89.2 KB · Views: 3
Surrena -

Here's an example of the matching differences when you front load your 401k contributions - it is significant! The whole idea of 401k contributions is to maximize the "free" money you get from company matching, and it's relatively easy to create a scenario where you actually short change yourself by being overly diligent. The optimal scenario is to reach your Maximum contribution in the final paycheck of a calendar year.

This model is not perfect in that the formula doesn't handle the match where the limit is reached *perfectly*, but it's fine for illustrative purposes. I've been distracted by family illness and the arrival of a house guest ....
I'm going to look at your model this morning
 

Attachments

  • Surrena 401k example.xlsx
    16.7 KB · Views: 3
David Evans said:
Surrena -

Here's an example of the matching differences when you front load your 401k contributions - it is significant! The whole idea of 401k contributions is to maximize the "free" money you get from company matching, and it's relatively easy to create a scenario where you actually short change yourself by being overly diligent. The optimal scenario is to reach your Maximum contribution in the final paycheck of a calendar year.

This model is not perfect in that the formula doesn't handle the match where the limit is reached *perfectly*, but it's fine for illustrative purposes. I've been distracted by family illness and the arrival of a house guest ....
I'm going to look at your model this morning
Click to expand...

Hi David,

I am really sorry to hear about your family illness. I truly hope things get better for your family and for you. Again, I truly appreciate all your assistance. I await your response.
 
OK - take a look at the attached.

I think we're closing in on a robust file, but you will see that there are some questionable values in some of the cells. Many of the Employees have maxed out their contributions too early thus limiting the amount of the match they receive. Also some have exceeded the Maximum deferral amounts for their respective ages.

If this is not computing according to your plan's rules, you'll need to illustrate where it is not reflecting the correct amounts and what you believe that amount should be, based on your understanding of the rules.
 

Attachments

  • Sample 3 - DME.xlsx
    113.7 KB · Views: 7
You must log in or register to reply here.
Back
Top