Podsumowanie
Aby obliczyć całkowity podatek dochodowy na podstawie wielu przedziałów podatkowych, możesz użyć funkcji WYSZUKAJ.PIONOWO i tabeli stawek o strukturze pokazanej w przykładzie. Wzór w G5 to:
=VLOOKUP(inc,rates,3,1)+(inc-VLOOKUP(inc,rates,1,1))*VLOOKUP(inc,rates,2,1)
gdzie „inc” (G4) i „rate” (B5: D11) to nazwane zakresy, a kolumna D to kolumna pomocnicza, która oblicza łączny skumulowany podatek w każdym przedziale.
Tło i kontekst
Amerykański system podatkowy jest „progresywny”, co oznacza, że osoby o wyższym dochodzie podlegającym opodatkowaniu płacą wyższą stawkę podatku federalnego. Stawki są oceniane w nawiasach określonych przez górny i dolny próg. Kwota dochodu mieszcząca się w danym przedziale jest opodatkowana według odpowiedniej stawki dla tego przedziału. W miarę wzrostu dochodu do opodatkowania dochód jest opodatkowany w większej liczbie przedziałów podatkowych. Dlatego wielu podatników płaci kilka różnych stawek.
W przedstawionym przykładzie przedziały podatkowe i stawki dotyczą osób składających jedno zgłoszenie w Stanach Zjednoczonych za rok podatkowy 2019. Poniższa tabela przedstawia ręczne obliczenia dochodu podlegającego opodatkowaniu w wysokości 50000 USD:
| Wspornik | Obliczenie | Podatek |
|---|---|---|
| 10% | (9 700 USD - 0 USD) x 10% | 970,00 $ |
| 12% | (39 475 USD - 9700 USD) x 12% | 3573,00 $ |
| 22% | (50 000-39 475 USD) x 22% | 2315,50 $ |
| 24% | NA | 0,00 USD |
| 32% | NA | 0,00 USD |
| 35% | NA | 0,00 USD |
| 37% | NA | 0,00 USD |
Całkowity podatek wynosi zatem 6858,50 USD. (wyświetlany jako 6,859 w pokazanym przykładzie).
Uwagi dotyczące konfiguracji
1. Ta formuła zależy od funkcji WYSZUKAJ.PIONOWO w „trybie przybliżonego dopasowania”. W trybie przybliżonego dopasowania funkcja WYSZUKAJ.PIONOWO przeskanuje wartości wyszukiwania w tabeli (które należy posortować w kolejności rosnącej), aż zostanie znaleziona wyższa wartość. Następnie „cofnie się” i zwróci wartość z poprzedniego wiersza. W przypadku dokładnego dopasowania funkcja WYSZUKAJ.PIONOWO zwróci wyniki z dopasowanego wiersza.
2. Aby funkcja WYSZUKAJ.PIONOWO mogła pobrać rzeczywiste skumulowane kwoty podatków, zostały one dodane do tabeli jako kolumna pomocnicza w kolumnie D. Formuła w D6, skopiowana w dół, to:
=((B6-B5)*C5)+D5
W każdym wierszu ta formuła stosuje stawkę z wiersza powyżej do dochodu w tym przedziale.
3. Dla czytelności zdefiniowano następujące nazwane zakresy: „inc” (G4) i „rate” (B5: D11).
Wyjaśnienie
W G5 pierwsze WYSZUKAJ.PIONOWO jest skonfigurowane do pobierania skumulowanego podatku według stawki krańcowej z następującymi danymi wejściowymi:
- Wartość wyszukiwania to „inc” (G4)
- Tabela przeglądowa to „stawki” (B5: D11)
- Numer kolumny to 3, Skumulowany podatek
- Typ dopasowania to 1 = dopasowanie przybliżone
VLOOKUP(inc,rates,3,1) // returns 4,543
Z dochodem podlegającym opodatkowaniu w wysokości 50 000 USD, funkcja WYSZUKAJ.PIONOWO w trybie przybliżonego dopasowania dopasowuje 39475 i zwraca 4543, czyli łączny podatek do 39475 USD.
Druga funkcja WYSZUKAJ.PIONOWO oblicza pozostały dochód do opodatkowania:
(inc-VLOOKUP(inc,rates,1,1)) // returns 10,525
obliczone w ten sposób:
(50 000–39 475) = 10 525
Wreszcie trzecie WYSZUKAJ.PIONOWO uzyskuje (najwyższą) krańcową stawkę podatku:
VLOOKUP(inc,rates,2,1) // returns 22%
Jest to mnożone przez dochód obliczony w poprzednim kroku. Pełna formuła jest rozwiązana w następujący sposób:
=VLOOKUP(inc,rates,3,1)+(inc-VLOOKUP(inc,rates,1,1))*VLOOKUP(inc,rates,2,1) =4,543+(10525)*22% =6,859
Stawki krańcowe i efektywne
Komórka G6 zawiera najwyższą stawkę krańcową obliczoną za pomocą funkcji WYSZUKAJ.PIONOWO:
=VLOOKUP(inc,rates,2,1) // returns 22%
Efektywna stawka podatkowa w G7 to całkowity podatek podzielony przez dochód do opodatkowania:
=G5/inc // returns 13.7%
Uwaga: wpadłem na tę formułę na blogu Jeffa Lenninga na Uniwersytecie Excel. To świetny przykład tego, jak funkcja WYSZUKAJ.PIONOWO może być używana w trybie przybliżonego dopasowania, a także jak funkcja WYSZUKAJ.PIONOWO może być używana wiele razy w tej samej formule.
