Abstract
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 employee's 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 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%, then 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:
=IF(C5<=4%,C5*B5)
This works fine for deferrals of 4% or less, but we'll get FALSE for anything over 4%.
So we'll 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%.
=IF(C5<=4%,C5*B5,4%*B5)
When I copy this down, we'll get the correct amounts for Tier 1.
For Tier 2, we can start off in the same way:
=IF(C5<=4%,
In this case, though, if the deferral is 4% or less, we return 0, since that's already been covered by Tier 1.
=IF(C5<=4%,0
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 to use another IF:
=IF(C5<=4%,0,IF(C5<=6%,(C5-4%)*B5,2%*B5))
If the deferral is <= 6%, subtract 4% since that was already handled in Tier 1, and then multiply by B5. If greater than 6%, just use 2%, since 2% is the maximum percent for Tier 2.
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 you can simplify these formulas by replacing the IF statements with the MIN function and a bit of Boolean logic.
