Обобщение
За да изчислите общия данък върху дохода въз основа на множество данъчни скоби, можете да използвате VLOOKUP и таблица с тарифи, структурирана, както е показано в примера. Формулата в G5 е:
=VLOOKUP(inc,rates,3,1)+(inc-VLOOKUP(inc,rates,1,1))*VLOOKUP(inc,rates,2,1)
където "inc" (G4) и "ставки" (B5: D11) са наречени диапазони, а колона D е помощна колона, която изчислява общия натрупан данък за всяка скоба.
Предистория и контекст
Данъчната система на САЩ е „прогресивна“, което означава, че хората с по-висок облагаем доход плащат по-висока федерална данъчна ставка. Цените се оценяват в скоби, определени от горен и долен праг. Размерът на дохода, който попада в дадена категория, се облага със съответната ставка за тази група. Тъй като облагаемият доход се увеличава, доходът се облага с повече данъчни скоби. Поради това много данъкоплатци плащат няколко различни ставки.
В показания пример данъчните скоби и ставки са за единични архиватори в САЩ за данъчната 2019 година. Таблицата по-долу показва ръчните изчисления за облагаем доход от $ 50 000:
| Скоба | Изчисляване | Данък |
|---|---|---|
| 10% | ($ 9 700 - $ 0) x 10% | $ 970,00 |
| 12% | ($ 39 475 - $ 9 700) x 12% | 3573,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 |
Следователно общият данък е $ 6858,50. (показва се като 6,859 в показания пример).
Бележки по настройката
1. Тази формула зависи от функцията VLOOKUP в "режим на приблизително съвпадение". Когато е в режим на приблизително съвпадение, VLOOKUP ще сканира чрез справочни стойности в таблица (които трябва да бъдат сортирани във възходящ ред), докато се намери по-висока стойност. След това ще "отстъпи" и ще върне стойност от предишния ред. В случай на точно съвпадение, VLOOKUP ще върне резултати от съответния ред.
2. За да може VLOOKUP да извлече действителните кумулативни данъчни суми, те са добавени към таблицата като помощна колона в колона D. Формулата в D6, копирана надолу, е:
=((B6-B5)*C5)+D5
На всеки ред тази формула прилага процента от горния ред към дохода в тази група.
3. За четливост са дефинирани следните наименовани диапазони: "вкл." (G4) и "скорости" (B5: D11).
Обяснение
В G5 първият VLOOKUP е конфигуриран да извлича кумулативния данък по пределната ставка с тези входове:
- Справочната стойност е "вкл." (G4)
- Справочната таблица е "тарифи" (B5: D11)
- Номер на колона е 3, Кумулативен данък
- Тип на съвпадението е 1 = приблизително съвпадение
VLOOKUP(inc,rates,3,1) // returns 4,543
С облагаем доход от $ 50 000, VLOOKUP, в режим на приблизително съвпадение, съответства на 39 475 и връща 4,543, общият данък до $ 39 475.
Вторият VLOOKUP изчислява оставащия доход за облагане:
(inc-VLOOKUP(inc,rates,1,1)) // returns 10,525
изчислено по следния начин:
(50 000-39 475) = 10525
И накрая, третият VLOOKUP получава (горната) пределна данъчна ставка:
VLOOKUP(inc,rates,2,1) // returns 22%
Това се умножава по дохода, изчислен в предишната стъпка. Пълната формула е решена по следния начин:
=VLOOKUP(inc,rates,3,1)+(inc-VLOOKUP(inc,rates,1,1))*VLOOKUP(inc,rates,2,1) =4,543+(10525)*22% =6,859
Пределни и ефективни ставки
Клетка G6 съдържа най-високата пределна ставка, изчислена с VLOOKUP:
=VLOOKUP(inc,rates,2,1) // returns 22%
Ефективната данъчна ставка в G7 е общият данък, разделен на облагаемия доход:
=G5/inc // returns 13.7%
Забележка: Срещнах тази формула в блога на Джеф Ленинг в Университета на Excel. Това е чудесен пример за това как VLOOKUP може да се използва в режим на приблизително съвпадение, както и как VLOOKUP може да се използва няколко пъти в една и съща формула.
