Резюме
Щоб розрахувати загальний податок на прибуток на основі декількох податкових груп, ви можете використовувати 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% | (9700 - 0 дол. США) x 10% | $ 970,00 |
| 12% | ($ 39 475 - $ 9700) x 12% | 3573,00 дол |
| 22% | ($ 50 000- $ 39 475) x 22% | 2315,50 дол |
| 24% | НС | 0,00 дол |
| 32% | НС | 0,00 дол |
| 35% | НС | 0,00 дол |
| 37% | НС | 0,00 дол |
Таким чином, загальний податок становить 6858,50 доларів США. (відображається як 6859 у наведеному прикладі).
Примітки щодо встановлення
1. Ця формула залежить від функції VLOOKUP у "режимі приблизного збігу". У режимі приблизного збігу VLOOKUP буде сканувати значення пошуку в таблиці (які потрібно сортувати за зростанням), поки не буде знайдено більш високе значення. Потім він "відступить" і поверне значення з попереднього рядка. У разі точного збігу, VLOOKUP поверне результати з відповідного рядка.
2. Для того, щоб VLOOKUP отримав фактичні сукупні суми податку, вони були додані до таблиці як допоміжний стовпець у стовпці D. Формула в D6, скопійована нижче, має вигляд:
=((B6-B5)*C5)+D5
У кожному рядку ця формула застосовує ставку з наведеного вище рядка до доходу в цій дужці.
3. Для читабельності визначені такі названі діапазони: "inc" (G4) та "rate" (B5: D11).
Пояснення
У G5 перший VLOOKUP налаштований на отримання сукупного податку за граничною ставкою з такими входами:
- Значення пошуку "inc" (G4)
- Таблиця пошуку - "ставки" (B5: D11)
- Номер стовпця 3, сукупний податок
- Тип відповідності - 1 = приблизний збіг
VLOOKUP(inc,rates,3,1) // returns 4,543
З оподатковуваним доходом 50 000 доларів США, VLOOKUP, в режимі приблизного збігу, відповідає 39 475 та повертає 4543, загальний податок до 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 можна використовувати кілька разів в одній і тій же формулі.
