Zusammenfassung
Um die Gesamteinkommenssteuer anhand mehrerer Steuerklassen zu berechnen, können Sie VLOOKUP und eine Steuertabelle verwenden, die wie im Beispiel dargestellt aufgebaut ist. Die Formel in G5 lautet:
=VLOOKUP(inc,rates,3,1)+(inc-VLOOKUP(inc,rates,1,1))*VLOOKUP(inc,rates,2,1)
Dabei werden "inc" (G4) und "rate" (B5: D11) als Bereiche bezeichnet, und Spalte D ist eine Hilfsspalte, die die kumulierte Gesamtsteuer in jeder Klammer berechnet.
Hintergrund und Kontext
Das US-Steuersystem ist "progressiv", was bedeutet, dass Personen mit höherem steuerpflichtigem Einkommen einen höheren Bundessteuersatz zahlen. Die Raten werden in Klammern angegeben, die durch einen oberen und einen unteren Schwellenwert definiert sind. Die Höhe des Einkommens, das in eine bestimmte Klammer fällt, wird mit dem entsprechenden Satz für diese Klammer besteuert. Wenn das zu versteuernde Einkommen steigt, wird das Einkommen über mehr Steuerklassen besteuert. Viele Steuerzahler zahlen daher mehrere unterschiedliche Sätze.
In dem gezeigten Beispiel gelten die Steuerklassen und Steuersätze für Einzelanmelder in den USA für das Steuerjahr 2019. Die folgende Tabelle zeigt die manuellen Berechnungen für ein zu versteuerndes Einkommen von 50.000 USD:
| Halterung | Berechnung | MwSt |
|---|---|---|
| 10% | (9.700 USD - 0 USD) x 10% | 970,00 USD |
| 12% | (39.475 USD - 9.700 USD) x 12% | 3.573,00 USD |
| 22% | (50.000 bis 39.475 USD) x 22% | 2.315,50 USD |
| 24% | N / A | 0,00 $ |
| 32% | N / A | 0,00 $ |
| 35% | N / A | 0,00 $ |
| 37% | N / A | 0,00 $ |
Die Gesamtsteuer beträgt daher 6.858,50 USD. (im gezeigten Beispiel als 6.859 angezeigt).
Setup-Hinweise
1. Diese Formel hängt von der VLOOKUP-Funktion im "ungefähren Übereinstimmungsmodus" ab. Im ungefähren Übereinstimmungsmodus durchsucht VLOOKUP die Suchwerte in einer Tabelle (die in aufsteigender Reihenfolge sortiert werden muss), bis ein höherer Wert gefunden wird. Dann wird "zurücktreten" und ein Wert aus der vorherigen Zeile zurückgegeben. Im Falle einer genauen Übereinstimmung gibt VLOOKUP Ergebnisse aus der übereinstimmenden Zeile zurück.
2. Damit VLOOKUP die tatsächlichen kumulierten Steuerbeträge abrufen kann, wurden diese als Hilfsspalte in Spalte D zur Tabelle hinzugefügt. Die Formel in D6 lautet:
=((B6-B5)*C5)+D5
Bei jeder Zeile wendet diese Formel den Satz aus der obigen Zeile auf das Einkommen in dieser Klammer an.
3. Zur besseren Lesbarkeit sind die folgenden benannten Bereiche definiert: "inc" (G4) und "Raten" (B5: D11).
Erläuterung
In G5 ist der erste VLOOKUP so konfiguriert, dass die kumulierte Steuer mit diesen Eingaben zum Grenzsatz abgerufen wird:
- Der Suchwert ist "inc" (G4)
- Nachschlagetabelle ist "Preise" (B5: D11)
- Die Spaltennummer lautet 3, kumulative Steuer
- Der Übereinstimmungstyp ist 1 = ungefähre Übereinstimmung
VLOOKUP(inc,rates,3,1) // returns 4,543
Mit einem steuerpflichtigen Einkommen von 50.000 US-Dollar entspricht VLOOKUP im ungefähren Match-Modus 39.475 US-Dollar und gibt 4.543 US-Dollar zurück, was einer Gesamtsteuer von 39.475 US-Dollar entspricht.
Der zweite VLOOKUP berechnet das verbleibende zu besteuernde Einkommen:
(inc-VLOOKUP(inc,rates,1,1)) // returns 10,525
so berechnet:
(50.000-39.475) = 10.525
Schließlich erhält der dritte VLOOKUP den (obersten) Grenzsteuersatz:
VLOOKUP(inc,rates,2,1) // returns 22%
Dies wird mit dem im vorherigen Schritt berechneten Einkommen multipliziert. Die komplette Formel wird folgendermaßen gelöst:
=VLOOKUP(inc,rates,3,1)+(inc-VLOOKUP(inc,rates,1,1))*VLOOKUP(inc,rates,2,1) =4,543+(10525)*22% =6,859
Grenz- und effektive Raten
Zelle G6 enthält die mit VLOOKUP berechnete obere Grenzrate:
=VLOOKUP(inc,rates,2,1) // returns 22%
Der effektive Steuersatz in G7 ist die Gesamtsteuer geteilt durch das zu versteuernde Einkommen:
=G5/inc // returns 13.7%
Hinweis: Ich bin auf Jeff Lennings Blog an der Excel University auf diese Formel gestoßen. Es ist ein großartiges Beispiel dafür, wie VLOOKUP im ungefähren Übereinstimmungsmodus verwendet werden kann und wie VLOOKUP in derselben Formel mehrmals verwendet werden kann.
