Complex formula example – 401k Match – Best article

In this video, we’ll look at how to build a formula that calculates a 401k match using several nested IF statements.

In the US, many companies match an employees retirement deferral up to a certain percent. In this example, the match has two tiers.

In tier 1 the company matches 100% up to 4% of the an employee’s compensation.

In tier 2, the company matches 50% on deferrals between 4% and 6%.

So, if an employee contributes 10%, the company matches 100% up to 4%, and 50% from 4 to 6%. After that, there’s no match.

Let’s look at how we can calculate the match for these two Tiers with IF statements.

Then in the next video, we’ll look at how we can simply the formulas.

To calculate the match for Tier 1, we can start off like this:


This works fine for deferrals of 4% or less, but we’ll get FALSE for anything over 4%.

So we need to extend the IF function to handle this by adding a value if false. Since tier 1 is capped at 4%, and we know the deferral is at least 4%, we simply use 4%.


When I copy this down, we have the correct amounts for Tier 1.

For Tier 2, we can start off the same way:


In this case though, if the deferral 4% or less, we return zero, since that’s already covered by Tier 1.


For the value if false, it’s a little more tricky. 

If we’ve made it this far, we know the deferral is greater than 4%, and we know the match is capped at 6% for tier 2. So, we’ll need another IF:

=IF(C5<=4%,0,IF(C5<=6%,(C5-4%)*B5,2%*B5))

If the deferral is

Then, because the match is 50% in tier 2, we multiply by 50%:

*50%

When I copy the formula down, we have complete tier 2 amounts.

So, to recap…

As you can see, these kind of calculations can become quite complex in Excel as we add more IF statements to manage the logic.

In the next video, I’ll show you how to simplify these formulas by replacing the IF statements with the MIN function and a bit of boolean logic.

 

Leave a Reply