Hacker News Reader

Comment by jldugger | Hacker News Reader

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.

View on HN

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-hbJ3q6E7r...

Really cool! Unfortunately Numbers doesn't support more complicated SUMPRODUCT formulas...

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))

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)

Habit, mostly: I tend to use VLOOKUP for exact matches and MATCH for relative ones.

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.

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.

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

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-Ta...

I still use a spreadsheet, but I'm always tempted to manage my financial planning with Haskell and org-mode heh

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.

Reply on news.ycombinator.com