Quick, clean, and to the point

This video is part of our video training library.

Complex formula example - 401k Match

In this video, we build two formulas to calculate the company match for a 401k contribution. This is a great example of building a complex formula with several nested IFs in small steps.
Video Transcript 

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 the deferral is <= 6%, subtract 4% and multiply by B5. If greater than 6%, just use 2%, since that's the limit.

Then, because the match is 50% in tier 2, we multiply by 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.

Dave Bruns
I’ve actually been gorging on your articles and videos every night this week. Thank you so very much for making the site!! - Brenda
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course