Sammendrag
For å beregne total inntektsskatt basert på flere skatteparametre, kan du bruke VLOOKUP og en rentetabell strukturert som vist i eksemplet. Formelen i G5 er:
=VLOOKUP(inc,rates,3,1)+(inc-VLOOKUP(inc,rates,1,1))*VLOOKUP(inc,rates,2,1)
der "inc" (G4) og "rate" (B5: D11) er kalt områder, og kolonne D er en hjelpekolonne som beregner total akkumulert skatt ved hver brakett.
Bakgrunn og kontekst
Det amerikanske skattesystemet er "progressivt", noe som betyr at folk med høyere skattepliktig inntekt betaler en høyere føderal skattesats. Priser vurderes i parentes definert av en øvre og nedre terskel. Inntektene som faller inn i en gitt brakett skattlegges med tilsvarende sats for braketten. Når skattepliktig inntekt øker, beskattes inntekt over flere skatteparametre. Mange skattebetalere betaler derfor flere forskjellige satser.
I eksemplet som er vist gjelder skatteparentesene og satsene for enkeltpersoner i USA for skatteåret 2019. Tabellen nedenfor viser de manuelle beregningene for en skattepliktig inntekt på $ 50000:
| Brakett | Beregning | Avgift |
|---|---|---|
| 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% | $ 2315,50 |
| 24% | NA | $ 0,00 |
| 32% | NA | $ 0,00 |
| 35% | NA | $ 0,00 |
| 37% | NA | $ 0,00 |
Den totale avgiften er derfor $ 6,858.50. (vises som 6 859 i eksemplet som vises).
Oppsettmerknader
1. Denne formelen avhenger av VLOOKUP-funksjonen i "omtrentlig kampmodus". I omtrentlig kampmodus vil VLOOKUP skanne gjennom oppslagsverdier i en tabell (som må sorteres i stigende rekkefølge) til en høyere verdi blir funnet. Deretter vil den "gå tilbake" og returnere en verdi fra forrige rad. I tilfelle nøyaktig samsvar vil VLOOKUP returnere resultater fra den matchede raden.
2. For at VLOOKUP skal hente de faktiske kumulative skattebeløpene, er disse lagt til tabellen som en hjelpekolonne i kolonne D. Formelen i D6, kopiert ned, er:
=((B6-B5)*C5)+D5
På hver rad bruker denne formelen satsen fra raden ovenfor til inntekten i den parentesen.
3. For lesbarhet er følgende navngitte områder definert: "inc" (G4) og "rates" (B5: D11).
Forklaring
I G5 er den første VLOOKUP konfigurert til å hente den kumulative avgiften til marginalsatsen med disse inngangene:
- Oppslagsverdi er "inc" (G4)
- Oppslagstabellen er "priser" (B5: D11)
- Kolonne nummer er 3, Kumulativ skatt
- Kamptype er 1 = omtrentlig kamp
VLOOKUP(inc,rates,3,1) // returns 4,543
Med en skattepliktig inntekt på $ 50 000, samsvarer VLOOKUP, i omtrentlig kampmodus, 39 475 og returnerer 4543, den totale skatten opp til $ 39 475.
Den andre VLOOKUP beregner gjenværende inntekt som skal beskattes:
(inc-VLOOKUP(inc,rates,1,1)) // returns 10,525
beregnet slik:
(50.000-39.475) = 10.525
Endelig får den tredje VLOOKUP den (øverste) marginale skattesatsen:
VLOOKUP(inc,rates,2,1) // returns 22%
Dette multipliseres med inntekten beregnet i forrige trinn. Den komplette formelen løses slik:
=VLOOKUP(inc,rates,3,1)+(inc-VLOOKUP(inc,rates,1,1))*VLOOKUP(inc,rates,2,1) =4,543+(10525)*22% =6,859
Marginale og effektive priser
Cell G6 inneholder den øverste marginalsatsen, beregnet med VLOOKUP:
=VLOOKUP(inc,rates,2,1) // returns 22%
Den effektive skattesatsen i G7 er total skatt delt på skattepliktig inntekt:
=G5/inc // returns 13.7%
Merk: Jeg kjørte inn i denne formelen på Jeff Lennings blogg på Excel University. Det er et godt eksempel på hvordan VLOOKUP kan brukes i omtrentlig kampmodus, og også hvordan VLOOKUP kan brukes flere ganger i samme formel.
