Резюме
Чтобы рассчитать общий подоходный налог на основе нескольких налоговых категорий, вы можете использовать ВПР и таблицу ставок, структурированную, как показано в примере. Формула в 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% | (9700–0 долларов) x 10% | 970 долларов США |
| 12% | (39 475–9700 долларов) x 12% | $ 3 573,00 |
| 22% | (50 000–39 475 долларов США) x 22% | $ 2 315,50 |
| 24% | NA | 0,00 руб. |
| 32% | NA | 0,00 руб. |
| 35% | NA | 0,00 руб. |
| 37% | NA | 0,00 руб. |
Таким образом, общий налог составляет 6 858,50 долларов. (отображается как 6 859 в показанном примере).
Примечания по настройке
1. Эта формула зависит от функции ВПР в «режиме приблизительного совпадения». В режиме приблизительного соответствия ВПР будет сканировать значения поиска в таблице (которые должны быть отсортированы в порядке возрастания), пока не будет найдено более высокое значение. Затем он «отступит» и вернет значение из предыдущей строки. В случае точного совпадения ВПР вернет результаты из совпавшей строки.
2. Чтобы функция ВПР могла получить фактические совокупные суммы налогов, они были добавлены в таблицу в качестве вспомогательного столбца в столбце D. Формула в D6, скопированная ниже, выглядит так:
=((B6-B5)*C5)+D5
В каждой строке эта формула применяет ставку из строки выше к доходу в этой группе.
3. Для удобства чтения определены следующие именованные диапазоны: «inc» (G4) и «rate» (B5: D11).
Объяснение
В G5 первая функция ВПР настроена на получение совокупного налога по предельной ставке со следующими входными данными:
- Значение поиска: inc (G4)
- Таблица поиска - "ставки" (B5: D11)
- Номер столбца 3, Накопленный налог.
- Тип соответствия: 1 = приблизительное соответствие
VLOOKUP(inc,rates,3,1) // returns 4,543
При налогооблагаемом доходе в 50 000 долларов ВПР в режиме приблизительного соответствия соответствует 39 475 и возвращает 4543, то есть общий налог составляет до 39 475 долларов.
Вторая функция VLOOKUP рассчитывает оставшуюся прибыль, подлежащую налогообложению:
(inc-VLOOKUP(inc,rates,1,1)) // returns 10,525
рассчитывается так:
(50 000–39 475) = 10 525
Наконец, третья ВПР получает (верхнюю) предельную ставку налога:
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(inc,rates,2,1) // returns 22%
Эффективная ставка налога в G7 представляет собой сумму налога, деленную на налогооблагаемую прибыль:
=G5/inc // returns 13.7%
Примечание: я встретил эту формулу в блоге Джеффа Леннинга в Университете Excel. Это отличный пример того, как ВПР можно использовать в режиме приблизительного соответствия, а также как ВПР можно использовать несколько раз в одной формуле.
