Resumé
For at beregne den samlede indkomstskat baseret på flere skatteklasser kan du bruge VLOOKUP og en rentetabel struktureret som vist i eksemplet. Formlen i G5 er:
=VLOOKUP(inc,rates,3,1)+(inc-VLOOKUP(inc,rates,1,1))*VLOOKUP(inc,rates,2,1)
hvor "inc" (G4) og "satser" (B5: D11) er navngivet områder, og kolonne D er en hjælpekolonne, der beregner den samlede akkumulerede skat ved hver parentes.
Baggrund og kontekst
Det amerikanske skattesystem er "progressivt", hvilket betyder, at folk med højere skattepligtig indkomst betaler en højere føderal skattesats. Satser vurderes i parentes defineret af en øvre og nedre tærskel. Indkomstbeløbet, der falder inden for en given parentes, beskattes med den tilsvarende sats for denne parentes. Når den skattepligtige indkomst stiger, beskattes indkomsten over flere skatteklasser. Mange skatteydere betaler derfor flere forskellige satser.
I det viste eksempel gælder skatteklasser og satser for enkeltpersoner i USA for skatteåret 2019. Tabellen nedenfor viser de manuelle beregninger for en skattepligtig indkomst på $ 50.000:
| Beslag | Beregning | Skat |
|---|---|---|
| 10% | ($ 9.700 - $ 0) x 10% | 970,00 $ |
| 12% | ($ 39.475 - $ 9.700) x 12% | 3.573,00 $ |
| 22% | ($ 50.000 - $ 39.475) x 22% | 2.315,50 $ |
| 24% | NA | $ 0,00 |
| 32% | NA | $ 0,00 |
| 35% | NA | $ 0,00 |
| 37% | NA | $ 0,00 |
Den samlede skat er derfor $ 6.858,50. (vises som 6.859 i det viste eksempel).
Opsætningsnoter
1. Denne formel afhænger af VLOOKUP-funktionen i "approximate match mode". I omtrentlig matchtilstand scanner VLOOKUP gennem opslagsværdier i en tabel (som skal sorteres i stigende rækkefølge), indtil en højere værdi findes. Derefter "træder tilbage" og returnerer en værdi fra den foregående række. I tilfælde af et nøjagtigt match returnerer VLOOKUP resultater fra den matchede række.
2. For at VLOOKUP skal hente de faktiske kumulative skattebeløb, er disse blevet føjet til tabellen som en hjælpekolonne i kolonne D. Formlen i D6, kopieret ned, er:
=((B6-B5)*C5)+D5
Ved hver række anvender denne formel satsen fra ovenstående række til indkomsten i denne parentes.
3. For læsbarhed er følgende navngivne områder defineret: "inc" (G4) og "rate" (B5: D11).
Forklaring
I G5 er den første VLOOKUP konfigureret til at hente den kumulative skat med den marginale sats med disse input:
- Opslagsværdi er "inc" (G4)
- Opslagstabellen er "rater" (B5: D11)
- Kolonne nummer er 3, Kumulativ skat
- Kamptype er 1 = omtrentlig kamp
VLOOKUP(inc,rates,3,1) // returns 4,543
Med en skattepligtig indkomst på $ 50.000 matcher VLOOKUP i omtrentlig kamptilstand 39.475 og returnerer 4.543, den samlede skat op til $ 39.475.
Den anden VLOOKUP beregner den resterende indkomst, der skal beskattes:
(inc-VLOOKUP(inc,rates,1,1)) // returns 10,525
beregnet således:
(50.000-39.475) = 10.525
Endelig får den tredje VLOOKUP den (øverste) marginale skatteprocent:
VLOOKUP(inc,rates,2,1) // returns 22%
Dette ganges med indkomsten beregnet i det foregående trin. Den komplette formel løses således:
=VLOOKUP(inc,rates,3,1)+(inc-VLOOKUP(inc,rates,1,1))*VLOOKUP(inc,rates,2,1) =4,543+(10525)*22% =6,859
Marginal og effektiv sats
Celle G6 indeholder den øverste marginale sats, beregnet med VLOOKUP:
=VLOOKUP(inc,rates,2,1) // returns 22%
Den effektive skattesats i G7 er samlet skat divideret med skattepligtig indkomst:
=G5/inc // returns 13.7%
Bemærk: Jeg løb ind i denne formel på Jeff Lennings blog over på Excel University. Det er et godt eksempel på, hvordan VLOOKUP kan bruges i omtrentlig matchtilstand, og også hvordan VLOOKUP kan bruges flere gange i samme formel.
