Sažetak
Da biste izračunali ukupni porez na dobit na temelju više poreznih zagrada, možete koristiti VLOOKUP i tablicu stopa strukturiranu kao što je prikazano u primjeru. Formula u G5 je:
=VLOOKUP(inc,rates,3,1)+(inc-VLOOKUP(inc,rates,1,1))*VLOOKUP(inc,rates,2,1)
gdje su "inc" (G4) i "rate" (B5: D11) imenovani rasponi, a stupac D je pomoćni stupac koji izračunava ukupni akumulirani porez u svakoj zagradi.
Pozadina i kontekst
Američki porezni sustav je "progresivan", što znači da ljudi s većim oporezivim dohotkom plaćaju višu saveznu poreznu stopu. Stope se procjenjuju u zagradama definiranim gornjim i donjim pragom. Iznos dohotka koji spada u određeni stupanj oporezuje se odgovarajućom stopom za taj razred. Kako se oporezivi dohodak povećava, dohodak se oporezuje u više poreznih razreda. Mnogi porezni obveznici stoga plaćaju nekoliko različitih stopa.
U prikazanom primjeru, porezne zagrade i stope odnose se na pojedinačne datoteke u Sjedinjenim Državama za poreznu 2019. godinu. Tablica u nastavku prikazuje ručne izračune za oporezivi prihod od 50 000 USD:
| Zagrada | Proračun | Porez |
|---|---|---|
| 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 - 39.475 USD) x 22% | 2315,50 USD |
| 24% | NA | 0,00 USD |
| 32% | NA | 0,00 USD |
| 35% | NA | 0,00 USD |
| 37% | NA | 0,00 USD |
Ukupni porez stoga iznosi 6.858,50 američkih dolara. (prikazano kao 6.859 u prikazanom primjeru).
Bilješke o postavljanju
1. Ova formula ovisi o funkciji VLOOKUP u "načinu približnog podudaranja". Kada je u načinu približnog podudaranja, VLOOKUP će skenirati vrijednosti pretraživanja u tablici (koje se moraju sortirati u rastućem redoslijedu) dok se ne pronađe veća vrijednost. Tada će se "odmaknuti" i vratiti vrijednost iz prethodnog retka. U slučaju točnog podudaranja, VLOOKUP će vratiti rezultate iz odgovarajućeg retka.
2. Da bi VLOOKUP dohvatio stvarni kumulativni iznos poreza, oni su dodani u tablicu kao pomoćni stupac u stupcu D. Formula u D6, kopirana, je:
=((B6-B5)*C5)+D5
U svakom retku ova formula primjenjuje stopu iz gornjeg retka na dohodak u toj skupini.
3. Radi čitljivosti definirani su sljedeći imenovani rasponi: "uklj." (G4) i "stope" (B5: D11).
Obrazloženje
U G5, prvi VLOOKUP konfiguriran je za prikupljanje kumulativnog poreza po najnižoj stopi s ovim ulazima:
- Vrijednost pretraživanja je "inc" (G4)
- Tablica za pretraživanje je "stope" (B5: D11)
- Broj stupca je 3, kumulativni porez
- Vrsta podudaranja je 1 = približno podudaranje
VLOOKUP(inc,rates,3,1) // returns 4,543
S oporezivim prihodom od 50.000 USD, VLOOKUP, u približnom načinu podudaranja, podudara se s 39.475 i vraća 4.543, ukupni porez do 39.475 USD.
Drugi VLOOKUP izračunava preostali prihod koji se oporezuje:
(inc-VLOOKUP(inc,rates,1,1)) // returns 10,525
izračunato ovako:
(50.000-39.475) = 10.525
Konačno, treći VLOOKUP dobiva (gornju) graničnu poreznu stopu:
VLOOKUP(inc,rates,2,1) // returns 22%
To se množi s prihodom izračunatim u prethodnom koraku. Kompletna formula riješena je ovako:
=VLOOKUP(inc,rates,3,1)+(inc-VLOOKUP(inc,rates,1,1))*VLOOKUP(inc,rates,2,1) =4,543+(10525)*22% =6,859
Granične i efektivne stope
Stanica G6 sadrži najvišu graničnu stopu, izračunatu pomoću VLOOKUP-a:
=VLOOKUP(inc,rates,2,1) // returns 22%
Efektivna porezna stopa u G7 ukupni je porez podijeljen s oporezivim prihodom:
=G5/inc // returns 13.7%
Napomena: Naletjela sam na ovu formulu na blogu Jeffa Lenninga na Sveučilištu Excel. To je sjajan primjer kako se VLOOKUP može koristiti u načinu približnog podudaranja, kao i kako se VLOOKUP može koristiti više puta u istoj formuli.
