souhrn
Chcete-li vypočítat celkovou daň z příjmu na základě několika daňových pásem, můžete použít VLOOKUP a sazební tabulku strukturovanou podle příkladu. Vzorec v G5 je:
=VLOOKUP(inc,rates,3,1)+(inc-VLOOKUP(inc,rates,1,1))*VLOOKUP(inc,rates,2,1)
kde „inc“ (G4) a „sazby“ (B5: D11) jsou pojmenovány rozsahy a sloupec D je pomocný sloupec, který vypočítává celkovou akumulovanou daň v každé závorce.
Pozadí a kontext
Americký daňový systém je „progresivní“, což znamená, že lidé s vyšším zdanitelným příjmem platí vyšší federální daňovou sazbu. Sazby jsou hodnoceny v závorkách definovaných horní a dolní hranicí. Výše příjmu, který spadá do dané skupiny, je zdaněna odpovídající sazbou pro tuto skupinu. Jak se zvyšuje zdanitelný příjem, příjem se zdaňuje ve více daňových pásmech. Mnoho daňových poplatníků proto platí několik různých sazeb.
V uvedeném příkladu jsou daňové pásy a sazby pro jednotlivé filers ve Spojených státech za daňový rok 2019. V následující tabulce jsou uvedeny ruční výpočty pro zdanitelný příjem 50 000 USD:
| Závorka | Výpočet | Daň |
|---|---|---|
| 10% | (9 700 $ - 0 USD) x 10% | 970,00 $ |
| 12% | (39 475 USD - 9 700 USD) x 12% | 3 573,00 $ |
| 22% | (50 000–39 475 USD) x 22% | 2 315,50 $ |
| 24% | NA | 0,00 $ |
| 32% | NA | 0,00 $ |
| 35% | NA | 0,00 $ |
| 37% | NA | 0,00 $ |
Celková daň je tedy 6 858,50 USD. (v zobrazeném příkladu zobrazeno jako 6 859).
Poznámky k nastavení
1. Tento vzorec závisí na funkci VLOOKUP v „režimu přibližné shody“. V režimu přibližné shody bude VLOOKUP procházet vyhledávacími hodnotami v tabulce (které musí být seřazeny vzestupně), dokud nebude nalezena vyšší hodnota. Pak to bude „krok zpět“ a vrátí hodnotu z předchozího řádku. V případě přesné shody vrátí VLOOKUP výsledky z uzavřeného řádku.
2. Aby VLOOKUP načetl skutečné kumulativní částky daně, byly do tabulky přidány jako pomocný sloupec ve sloupci D. Vzorec v D6, zkopírovaný dolů, je:
=((B6-B5)*C5)+D5
V každém řádku tento vzorec použije sazbu z výše uvedeného řádku na příjem v této závorce.
3. Pro čitelnost jsou definovány následující pojmenované rozsahy: „inc“ (G4) a „rates“ (B5: D11).
Vysvětlení
V G5 je první VLOOKUP nakonfigurován tak, aby načetl kumulativní daň s mezní sazbou s těmito vstupy:
- Hodnota vyhledávání je „inc“ (G4)
- Vyhledávací tabulka je „sazby“ (B5: D11)
- Číslo sloupce je 3, Kumulativní daň
- Typ shody je 1 = přibližná shoda
VLOOKUP(inc,rates,3,1) // returns 4,543
S zdanitelným příjmem 50 000 USD se VLOOKUP v režimu přibližné shody shoduje s 39 475 a vrací 4543, celková daň až 39 475 USD.
Druhý VLOOKUP vypočítává zbývající příjem, který se má zdanit:
(inc-VLOOKUP(inc,rates,1,1)) // returns 10,525
vypočteno takto:
(50 000–39 475) = 10 525
A konečně třetí VLOOKUP získá (horní) mezní sazbu daně:
VLOOKUP(inc,rates,2,1) // returns 22%
To se vynásobí příjmem vypočítaným v předchozím kroku. Celý vzorec je vyřešen takto:
=VLOOKUP(inc,rates,3,1)+(inc-VLOOKUP(inc,rates,1,1))*VLOOKUP(inc,rates,2,1) =4,543+(10525)*22% =6,859
Mezní a efektivní sazby
Buňka G6 obsahuje nejvyšší mezní sazbu vypočítanou pomocí VLOOKUP:
=VLOOKUP(inc,rates,2,1) // returns 22%
Efektivní sazba daně v G7 je celková daň dělená zdanitelným příjmem:
=G5/inc // returns 13.7%
Poznámka: Na tento vzorec jsem narazil na blogu Jeffa Lenninga na Excel University. Je to skvělý příklad toho, jak lze VLOOKUP použít v režimu přibližné shody a také, jak lze VLOOKUP použít vícekrát ve stejném vzorci.
