In diesem Video sehen wir uns an, wie Sie eine Formel erstellen, die eine 401k-Übereinstimmung mithilfe mehrerer verschachtelter IF-Anweisungen berechnet.
In den USA entsprechen viele Unternehmen einer Stundung des Ruhestands eines Mitarbeiters bis zu einem bestimmten Prozentsatz. In diesem Beispiel hat das Match zwei Ebenen.
In Tier 1 entspricht das Unternehmen 100% bis zu 4% der Vergütung eines Mitarbeiters.
In Tier 2 entspricht das Unternehmen 50% bei Stundungen zwischen 4% und 6%.
Wenn ein Mitarbeiter 10% beiträgt, entspricht das Unternehmen 100% bis 4% und 50% 4 bis 6%. Danach gibt es keine Übereinstimmung mehr.
Schauen wir uns an, wie wir die Übereinstimmung für diese beiden Ebenen mit IF-Anweisungen berechnen können.
Dann schauen wir uns im nächsten Video an, wie wir einfach die Formeln erstellen können.
Um die Übereinstimmung für Tier 1 zu berechnen, können wir wie folgt beginnen:
= IF (C5 <= 4%, C5 * B5)
Dies funktioniert gut für Verzögerungen von 4% oder weniger, aber wir erhalten FALSCH für alles über 4%.
Daher müssen wir die IF-Funktion erweitern, um dies zu handhaben, indem wir einen Wert hinzufügen, wenn false. Da Tier 1 auf 4% begrenzt ist und wir wissen, dass der Aufschub mindestens 4% beträgt, verwenden wir einfach 4%.
= IF (C5 <= 4%, C5 * B5,4% * B5)
Wenn ich das aufschreibe, haben wir die richtigen Beträge für Tier 1.
Für Tier 2 können wir auf die gleiche Weise beginnen:
= IF (C5 <= 4%,
In diesem Fall geben wir jedoch bei einer Verschiebung von 4% oder weniger Null zurück, da dies bereits durch Tier 1 abgedeckt ist.
= IF (C5 <= 4%, 0
Wenn der Wert falsch ist, ist er etwas kniffliger.
Wenn wir es bis hierher geschafft haben, wissen wir, dass der Aufschub größer als 4% ist, und wir wissen, dass das Match für Tier 2 auf 6% begrenzt ist. Also brauchen wir eine weitere IF:
= IF (C5 <= 4%, 0, IF (C5 <= 6%, (C5-4%) * B5,2% * B5))
Wenn die Verschiebung <= 6% ist, subtrahieren Sie 4% und multiplizieren Sie mit B5. Wenn mehr als 6%, verwenden Sie einfach 2%, da dies die Grenze ist.
Da die Übereinstimmung in Stufe 2 50% beträgt, multiplizieren wir sie mit 50%:
* 50%
Wenn ich die Formel nach unten kopiere, haben wir vollständige Tier 2-Beträge.
Also, um es noch einmal zusammenzufassen …
Wie Sie sehen, kann diese Art von Berechnungen in Excel sehr komplex werden, wenn wir weitere IF-Anweisungen hinzufügen, um die Logik zu verwalten.
Im nächsten Video werde ich Ihnen zeigen, wie Sie diese Formeln vereinfachen, indem Sie die IF-Anweisungen durch die MIN-Funktion und ein bisschen boolesche Logik ersetzen.
