Tóm lược
Để tính tổng thuế thu nhập dựa trên nhiều khung thuế, bạn có thể sử dụng hàm VLOOKUP và bảng tỷ lệ có cấu trúc như trong ví dụ. Công thức trong G5 là:
=VLOOKUP(inc,rates,3,1)+(inc-VLOOKUP(inc,rates,1,1))*VLOOKUP(inc,rates,2,1)
trong đó "inc" (G4) và "rate" (B5: D11) là các phạm vi được đặt tên và cột D là cột trợ giúp tính tổng số thuế tích lũy ở mỗi khung.
Bối cảnh và bối cảnh
Hệ thống Thuế của Hoa Kỳ là "lũy tiến", có nghĩa là những người có thu nhập chịu thuế cao hơn sẽ phải trả mức thuế liên bang cao hơn. Tỷ giá được đánh giá trong ngoặc vuông được xác định bởi một ngưỡng trên và dưới. Phần thu nhập nằm trong một khung nhất định sẽ bị đánh thuế ở mức tương ứng cho khung đó. Khi thu nhập chịu thuế tăng lên, thu nhập bị đánh thuế theo nhiều khung thuế hơn. Do đó, nhiều người nộp thuế phải trả một số tỷ lệ khác nhau.
Trong ví dụ được hiển thị, khung thuế và thuế suất dành cho những người nộp đơn lẻ ở Hoa Kỳ cho năm tính thuế 2019. Bảng dưới đây trình bày các tính toán thủ công cho thu nhập chịu thuế 50.000 đô la:
| dấu ngoặc | Phép tính | Thuế |
|---|---|---|
| 10% | ($ 9,700 - $ 0) x 10% | $ 970,00 |
| 12% | ($ 39.475 - $ 9.700) x 12% | $ 3,573,00 |
| 22% | (50.000 USD- 39.475 USD) x 22% | $ 2.315,50 |
| 24% | NA | 0,00 đô la |
| 32% | NA | 0,00 đô la |
| 35% | NA | 0,00 đô la |
| 37% | NA | 0,00 đô la |
Do đó, tổng số thuế là $ 6,858,50. (hiển thị là 6,859 trong ví dụ được hiển thị).
Ghi chú thiết lập
1. Công thức này phụ thuộc vào hàm VLOOKUP trong "chế độ so khớp gần đúng". Khi ở chế độ đối sánh gần đúng, hàm VLOOKUP sẽ quét qua các giá trị tra cứu trong bảng (phải được sắp xếp theo thứ tự tăng dần) cho đến khi tìm thấy giá trị cao hơn. Sau đó, nó sẽ "lùi lại" và trả về một giá trị từ hàng trước đó. Trong trường hợp khớp chính xác, hàm VLOOKUP sẽ trả về kết quả từ hàng đã khớp.
2. Để VLOOKUP truy xuất số thuế tích lũy thực tế, chúng đã được thêm vào bảng dưới dạng cột trợ giúp trong cột D. Công thức trong D6, được sao chép xuống, là:
=((B6-B5)*C5)+D5
Tại mỗi hàng, công thức này áp dụng tỷ lệ từ hàng trên cho thu nhập trong khung đó.
3. Để dễ đọc, các phạm vi được đặt tên sau đây, được xác định: "inc" (G4) và "rate" (B5: D11).
Giải trình
Trong G5, hàm VLOOKUP đầu tiên được định cấu hình để truy xuất thuế tích lũy ở mức biên với các đầu vào sau:
- Giá trị tra cứu là "inc" (G4)
- Bảng tra cứu là "giá" (B5: D11)
- Số cột là 3, Thuế cộng dồn
- Loại đối sánh là 1 = đối sánh gần đúng
VLOOKUP(inc,rates,3,1) // returns 4,543
Với thu nhập chịu thuế 50.000 đô la, VLOOKUP, ở chế độ đối sánh gần đúng, khớp với 39.475 và trả về 4.543, tổng số thuế lên đến 39.475 đô la.
Hàm VLOOKUP thứ hai tính toán thu nhập còn lại phải chịu thuế:
(inc-VLOOKUP(inc,rates,1,1)) // returns 10,525
được tính như thế này:
(50.000-39.475) = 10.525
Cuối cùng, hàm VLOOKUP thứ ba nhận mức thuế cận biên (trên cùng):
VLOOKUP(inc,rates,2,1) // returns 22%
Số tiền này được nhân với thu nhập được tính ở bước trước. Công thức hoàn chỉnh được giải như sau:
=VLOOKUP(inc,rates,3,1)+(inc-VLOOKUP(inc,rates,1,1))*VLOOKUP(inc,rates,2,1) =4,543+(10525)*22% =6,859
Tỷ lệ biên và hiệu quả
Ô G6 chứa tỷ lệ cận biên cao nhất, được tính bằng hàm VLOOKUP:
=VLOOKUP(inc,rates,2,1) // returns 22%
Thuế suất hiệu dụng trong G7 là tổng số thuế chia cho thu nhập chịu thuế:
=G5/inc // returns 13.7%
Lưu ý: Tôi đã tìm thấy công thức này trên blog của Jeff Lenning tại Đại học Excel. Đó là một ví dụ tuyệt vời về cách hàm VLOOKUP có thể được sử dụng trong chế độ đối sánh gần đúng và cũng là cách hàm VLOOKUP có thể được sử dụng nhiều lần trong cùng một công thức.
