Sammanfattning
För att beräkna den totala inkomstskatten baserat på flera skatteklasser kan du använda VLOOKUP och en skattetabell strukturerad som visas i exemplet. Formeln i G5 är:
=VLOOKUP(inc,rates,3,1)+(inc-VLOOKUP(inc,rates,1,1))*VLOOKUP(inc,rates,2,1)
där "inc" (G4) och "rater" (B5: D11) heter områden, och kolumn D är en hjälpkolumn som beräknar total ackumulerad skatt vid varje parentes.
Bakgrund och sammanhang
Det amerikanska skattesystemet är "progressivt", vilket innebär att personer med högre skattepliktig inkomst betalar en högre federal skattesats. Priser bedöms inom parentes definierade av en övre och nedre tröskel. Inkomstbeloppet som faller inom en viss nivå beskattas med motsvarande skattesats för den gruppen. När beskattningsbar inkomst ökar beskattas inkomst över fler skatteklasser. Många skattebetalare betalar därför flera olika skattesatser.
I exemplet som visas är skatteklasserna och skattesatserna för enstaka arkiverare i USA för skatteåret 2019. Tabellen nedan visar de manuella beräkningarna för en skattepliktig inkomst på 50 000 USD:
| Konsol | Beräkning | Beskatta |
|---|---|---|
| 10% | (9 700 $ - 0 $) x 10% | 970,00 US $ |
| 12% | (39 475 $ - 9 700 $) x 12% | 3.573,00 US $ |
| 22% | (50 000 $ - 39 475 $) x 22% | 2315,50 $ |
| 24% | NA | 0,00 USD |
| 32% | NA | 0,00 USD |
| 35% | NA | 0,00 USD |
| 37% | NA | 0,00 USD |
Den totala skatten är därför 6 858,50 dollar. (visas som 6 859 i exemplet som visas).
Inställningar för installation
1. Denna formel beror på VLOOKUP-funktionen i "ungefärligt matchningsläge". I ungefärligt matchningsläge skannar VLOOKUP igenom uppslagsvärden i en tabell (som måste sorteras i stigande ordning) tills ett högre värde hittas. Då kommer det att "gå tillbaka" och returnera ett värde från föregående rad. I händelse av en exakt matchning returnerar VLOOKUP resultat från den matchade raden.
2. För att VLOOKUP ska hämta de faktiska kumulativa skattebeloppen har dessa lagts till i tabellen som en hjälpkolumn i kolumn D. Formeln i D6, kopierad, är:
=((B6-B5)*C5)+D5
Vid varje rad tillämpar denna formel räntan från raden ovan på inkomsterna inom parentes.
3. För läsbarhet definieras följande namngivna intervall: "inc" (G4) och "rate" (B5: D11).
Förklaring
I G5 är den första VLOOKUP konfigurerad för att hämta den kumulativa skatten till marginalsatsen med dessa ingångar:
- Uppslagsvärde är "inc" (G4)
- Uppslagstabellen är "räntor" (B5: D11)
- Kolumnnummer är 3, Kumulativ skatt
- Matchningstyp är 1 = ungefärlig matchning
VLOOKUP(inc,rates,3,1) // returns 4,543
Med en skattepliktig inkomst på $ 50 000 matchar VLOOKUP i ungefärligt matchningsläge 39 475 och returnerar 4543, den totala skatten upp till $ 39 475.
Den andra VLOOKUP beräknar återstående inkomst som ska beskattas:
(inc-VLOOKUP(inc,rates,1,1)) // returns 10,525
beräknat så här:
(50 000-39 475) = 10 525
Slutligen får den tredje VLOOKUP den (översta) marginella skattesatsen:
VLOOKUP(inc,rates,2,1) // returns 22%
Detta multipliceras med inkomsten beräknad i föregående steg. Hela formeln löses så här:
=VLOOKUP(inc,rates,3,1)+(inc-VLOOKUP(inc,rates,1,1))*VLOOKUP(inc,rates,2,1) =4,543+(10525)*22% =6,859
Marginal och effektiva priser
Cell G6 innehåller den högsta marginalräntan, beräknad med VLOOKUP:
=VLOOKUP(inc,rates,2,1) // returns 22%
Den effektiva skattesatsen i G7 är total skatt dividerat med skattepliktig inkomst:
=G5/inc // returns 13.7%
Obs: Jag stötte på den här formeln på Jeff Lennings blogg på Excel University. Det är ett utmärkt exempel på hur VLOOKUP kan användas i ungefärligt matchningsläge, och också hur VLOOKUP kan användas flera gånger i samma formel.
