6 ms·
Because I like a challenge: =SUMPRODUCT( (bracket_min<income)* ( ((income<=bracket_max)*(income-bracket_min)) + ((income>bracket_ma
by basseq 5y ago
Because I like a challenge:
=SUMPRODUCT(
(bracket_min<income)*
(
((income<=bracket_max)*(income-bracket_min))
+
((income>bracket_max)*(bracket_max-bracket_min))
)
*bracket_rate
)
Where income is your income, bracket_min is the range of bracket minimums, bracket_max is the range of bracket maximums, and bracket_rate is the range of bracket tax rates.
Demo on Google Sheets:
https://docs.google.com/spreadsheets/d/1z0vx8TJeWr-hbJ3q6E7rFL_TDMWJZpaPxmJD84AD_gg/edit?usp=sharing https://docs.google.com/spreadsheets/d/1z0vx8TJeWr-hbJ3q6E7r...
- bouk 5y agoReally cool! Unfortunately Numbers doesn't support more complicated SUMPRODUCT formulas...
- basseq 5y agoYou could do it with a bunch of INDEX-MATCH formulas and pre-calculating base tax per bracket: =(income-INDEX(bracket_min,match(income,bracket_min,1)))* INDEX(bracket_tax,match(income,bracket_min,1))+ INDEX(bracket_base_tax,match(income,bracket_min,1))
- taviso 5y agoClever, but I'm curious why you don't use VLOOKUP? It's simpler, and Lotus 1-2-3 doesn't have MATCH! :-) I think something like this would work... (income-@VLOOKUP(income,table,1))*@VLOOKUP(income,table,3)+@VLOOKUP(income,table,5)
- basseq 5y agoHabit, mostly: I tend to use VLOOKUP for exact matches and MATCH for relative ones.
- onlyrealcuzzo 5y agoThe biggest problem with this is trying to model tax payments in the future. In ten years, you have no idea what tax rates will be. But you can be pretty confident the Fed will have devalued money by 30%+. Even if you just want to have tax brackets adjust to inflation - this function gets to be really complicated.