4 ms·

Okay, now do one for taxes: given a taxable income, and a table representing the tax brackets, calculate the taxes owed in a single formula.

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 ago
  Really cool! Unfortunately Numbers doesn't support more complicated SUMPRODUCT formulas...
  - basseq 5y ago
    You 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 ago
      Clever, 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 ago
        Habit, mostly: I tend to use VLOOKUP for exact matches and MATCH for relative ones.
- onlyrealcuzzo 5y ago
  The 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.
bouk 5y ago
I think you could probably do this in a single formula with SUMPRODUCT, but right now I just have a table that uses MIN(0, X) and MAX(0, Y) tricks to calculate it.
- jldugger 5y ago
  AFAICT, SUMPRODUCT is insufficient, you need to provide different numbers to each row. I need a fold_left version of sumproduct.
NamTaf 5y ago
Some form of the golden duo of INDEX and MATCH should do it in Excel. I suspect you could do it with SUMPRODUCT too if the tax table contains sufficient data (e.g. for each band a lump-sum + progressive rate may be necessary) but it may still be an array equation (ctrl-shift-enter when entered, with curly braces displayed around it). I’m not in front of a PC so I can’t try to confirm.
whateveracct 5y ago
Not in spreadsheets, but there's fun to be had here in Haskell-land: https://hackage.haskell.org/package/tax-0.2.0.0/docs/Data-Tax.html https://hackage.haskell.org/package/tax-0.2.0.0/docs/Data-Ta... I still use a spreadsheet, but I'm always tempted to manage my financial planning with Haskell and org-mode heh

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 ago
Really cool! Unfortunately Numbers doesn't support more complicated SUMPRODUCT formulas...
- basseq 5y ago
  You 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 ago
    Clever, 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 ago
      Habit, mostly: I tend to use VLOOKUP for exact matches and MATCH for relative ones.
onlyrealcuzzo 5y ago
The 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.

bouk 5y ago

I think you could probably do this in a single formula with SUMPRODUCT, but right now I just have a table that uses MIN(0, X) and MAX(0, Y) tricks to calculate it.

jldugger 5y ago
AFAICT, SUMPRODUCT is insufficient, you need to provide different numbers to each row. I need a fold_left version of sumproduct.

NamTaf 5y ago

Some form of the golden duo of INDEX and MATCH should do it in Excel. I suspect you could do it with SUMPRODUCT too if the tax table contains sufficient data (e.g. for each band a lump-sum + progressive rate may be necessary) but it may still be an array equation (ctrl-shift-enter when entered, with curly braces displayed around it). I’m not in front of a PC so I can’t try to confirm.

whateveracct 5y ago

Not in spreadsheets, but there's fun to be had here in Haskell-land: https://hackage.haskell.org/package/tax-0.2.0.0/docs/Data-Tax.html https://hackage.haskell.org/package/tax-0.2.0.0/docs/Data-Ta... I still use a spreadsheet, but I'm always tempted to manage my financial planning with Haskell and org-mode heh