Stepped Tax!

let
  S1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  S2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content], 
  Tax = Table.AddColumn(
    S2, 
    "Tax", 
    each List.Accumulate(
      Table.ToRows(S1), 
      0, 
      (a, b) => a + List.Max({0, (List.Min({[Income], b{1}}) - b{0}) * b{2}})
    )
  )
in
  Tax

let
  Source = each Excel.CurrentWorkbook(){[Name = _]}[Content], 
  S = Table.AddColumn(
    Source("Question"), 
    "Tax", 
    each List.Sum(
      List.Transform(
        Table.ToRows(Source("TaxRate")), 
        (r) => (
          List.Max({r{0}, List.Min({{r{1}, [Income]}{Number.From(r{1} = "Over")}, [Income]})})
            - r{0}
        )
          * r{2}
      )
    )
  )
in
  S

let
  Source = Table.RemoveColumns(Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], "Column1"), 
  AddCJ = Table.AddColumn(Source, "CJ", each Source), 
  AddAnswer = Table.AddColumn(
    AddCJ, 
    "Answer", 
    each [
      a = [Income], 
      b = [CJ], 
      c = Table.ReplaceValue(
        b, 
        "Over", 
        if a >= List.Max(b[From]) then a else List.Max(b[From]) - 1, 
        Replacer.ReplaceValue, 
        {"To"}
      ), 
      d = Table.AddColumn(c, "Marg", each [To] - [From] + 1), 
      e = Table.AddColumn(d, "InPlay", each if a >= [From] - 1 then 1 else 0), 
      f = Table.SelectRows(e, each [InPlay] = 1), 
      g = Table.AddColumn(
        f, 
        "Taxable", 
        each if [From] = List.Max(f[From]) then a - [From] else [Marg]
      ), 
      h = Table.AddColumn(g, "Tax", each [Tax Rate] * [Taxable]), 
      i = Number.Round(List.Sum(h[Tax]), 0)
    ][i]
  ), 
  Clean = Table.SelectColumns(AddAnswer, {"Person ID", "Income", "Answer"})
in
  Clean

let
  Tax = Excel.CurrentWorkbook(){[Name = "Tax"]}[Content], 
  Data = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Generate = List.Generate(
    () => [n = - 1, e = 0, a = 0], 
    each [n] < Table.RowCount(Tax) - 1, 
    each [n = [n] + 1, b = Tax{n}, e = b[To], t = b[Tax Rate], a = [a] + (e - [e]) * t], 
    each [To = [e], Rate = Tax{[n] + 1}[Tax Rate], Amt = [a]]
  ), 
  TaxTable = Table.FromRecords(List.Skip(Generate)), 
  Return = Table.AddColumn(
    Data, 
    "Tax", 
    each [
      F = Table.SelectRows(TaxTable, (f) => f[To] <= [Income]), 
      L = Table.Last(F), 
      R = Int64.From(([Income] - L[To]) * L[Rate] + L[Amt])
    ][R]
  )
in
  Return

let
 Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
 Sol = Table.AddColumn(Source, "Answer", (x)=> 
let
a = Table1,
b = Table.ToRows(a),
c = List.Select(b, each try List.Contains({_{0}.._{1}}, x[Income]) otherwise 
 List.Contains({_{0}..List.Max(Source[Income])}, x[Income])){0}{2},
d = List.PositionOf(Table1[Tax Rate], c),
e = List.RemoveLastN(List.Transform({1..List.Count(a[To])}, 
 each (({0}&a[To]){_}-({0}&a[To]){_-1})*a[Tax Rate]{_-1})),
f = List.Sum(List.FirstN(e, d))+(x[Income]-a[To]{d-1})*a[Tax Rate]{d}
in f)
in
 Sol

let
  Table1 = Table.Buffer(
    Table.AddColumn(
      Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
      "Incr", 
      each try ([To] - [From] + 1) * [Tax Rate] otherwise 0
    )
  ), 
  Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content], 
  Result = Table.AddColumn(
    Source, 
    "Tax", 
    each [
      Subtable     = Table.SelectRows(Table1, (r) => [Income] >= r[From]), 
      MarginalRate = List.Max(Subtable[Tax Rate]), 
      BracketMin   = List.Max(Subtable[From]) - 1, 
      MarginalTax  = ([Income] - BracketMin) * MarginalRate, 
      IncrTax      = List.Sum(List.RemoveLastN(Subtable[Incr], 1)), 
      Tax          = MarginalTax + IncrTax
    ][Tax]
  )
in
  Result

let
  S = Excel.CurrentWorkbook(){[Name = "Tax"]}[Content], 
  A = Table.TransformColumnTypes(
    S, 
    {{"From", Int64.Type}, {"To", type any}, {"Tax Rate", type number}}
  ), 
  B = Table.ReplaceValue(A, "Over", 10000000000, Replacer.ReplaceValue, {"To"}), 
  C = Table.AddColumn(B, "Diff", each [To] - [From], type number), 
  In = Excel.CurrentWorkbook(){[Name = "Income"]}[Content], 
  D = Table.AddColumn(In, "TaxTbl", each Table.ReverseRows(C)), 
  E = Table.ExpandTableColumn(
    D, 
    "TaxTbl", 
    {"From", "Tax Rate", "Diff"}, 
    {"From", "Tax Rate", "Diff"}
  ), 
  F = Table.AddColumn(
    E, 
    "Rem", 
    each 
      if [Income] - [From] >= [Diff] then
        [Diff] + 1
      else if [Income] - [From] >= 0 then
        [Income] - [From]
      else
        null
  ), 
  G = Table.SelectRows(F, each ([Rem] <> null)), 
  H = Table.AddColumn(G, "Tax", each [Tax Rate] * [Rem], type number), 
  I = Table.Group(
    H, 
    {"Person ID", "Income"}, 
    {{"Tax", each Number.Round(List.Sum([Tax])), type number}}
  )
in
  I

=LET(e,
    G3:G7-TOROW(
        B3:B7
    ),
    MMULT(e*(e>0),
    D3:D7-N(
        +D2:D6
    )))

=MAP(G3:G7,
    LAMBDA(i,
    LET(e,
    i-B3:B7,
    SUM(e*(e>0)*(D3:D7-N(
        +D2:D6
    ))))))

This one should work but the Annoying bug causing it returns only 1 cell

Quick fix for now is IFS

=MAP(G3:G7,
    LAMBDA(i,
    LET(e,
    i-B3:B7,
    SUM(e*(e>0)*IFS(
        1,
        D3:D7-N(
        +D2:D6
    )
    )))))

= Question[Income]
 RETURN
 SUMX(ADDCOLUMNS(
     TaxRate,
      "Until",      VAR T = CONVERT(
          IF(
              TaxRate[To] = "Over",
               I,
               TaxRate[To]
          ),
           INTEGER
      )
      RETURN 
      MAX(
          MIN(
              I,
               T
          ),
           TaxRate[From]
      ) ),
     ([Until] - TaxRate[From]) * TaxRate[Tax Rate]
 )
)

=MAP(G3:G7,
    LAMBDA(x,
    SUM(TAKE((SORT(
        IFERROR(
            --C3:C7,
            x
        )
    )-B3:B7)*D3:D7,
    MATCH(
        x,
        B3:B7
    )))))

=MAP(G3:G7,
    LAMBDA(a,
    LET(c,
    C3:C7,
    d,
    D3:D7,i,
    IF(
        N(
            +c
        ),
        c,
        2^19
    ),
    x,
    XMATCH(
        a,
        i,
        -1
    ),
    (a-INDEX(
        i,
        x
    ))*
INDEX(
    d,
    1+x
)+SUM(TAKE((i-B3:B7+1)*d,
    x)))))

=MAP(G3:G7,
     LAMBDA(i,
     SUM((SORT(
         VSTACK(
             C3:C6,
              i
         )
     )-VSTACK(
         0,
          C3:C6
     ))*D3:D7)))

=MAP(G3:G7,
    LAMBDA(I,
    SUM(LET(R,
    (I-(B3:B7-1))*IFERROR(
        D3:D7-OFFSET(
            D3:D7,
            -1,
            0
        ),
        0
    ),
    IF(
        R>0,
        R,
        0
    )))))

=LET(a,B3:B7,b,C3:C7,c,D3:D7,d,G3:G7,e,SCAN(,IFERROR((b-a)*c,),SUM),f,XLOOKUP(d,a,c,,-1),g,XLOOKUP(d,a,a,,-1),((d-g)*f)+XLOOKUP(g-1,b,e))

=MAP(G3:G7,
    LAMBDA(x,
    LET(a,
    D3:D7*((SORT(
        IFERROR(
            --C3:C7,
            x
        )
    ))-B3:B7),
    SUM((a>0)*a))))

=SUMPRODUCT(
(G3>$C$3:$C$6)*(G3-$C$3:$C$6)*($D$4:$D$7-$D$3:$D$6))

Dynamic arrays:
=BYROW(G3:G7,
    LAMBDA(r,
    SUM(
(r>C3:C6)*(r-C3:C6)*(D4:D7-D3:D6))))

=SUM(
    IF(
        G3>$C$3:$C$7,
        $C$3:$C$7-$B$3:$B$7,
        IF(
            G3<$B$3:$B$7,
            0,
            G3-B$3:B$7
        )
    )*D$3:D$7
)

=MAP(
 G3:G7,
 LAMBDA(x,
 LET(
 v, ((1 + C3:C7) - B3:B7) * D3:D7,
 r, XMATCH(x, B3:B7, -1),
 _s1, SUM(TAKE(v, r - 1)),
 _s2, (x - INDEX(B3:B7, r) + 1) * INDEX(D3:D7, r),
 _s1 + _s2
 )
 )
)

=MAP(G3:G7,LAMBDA(t,
     SUM((t>B4:B7)*(t-B4:B7)*(D4:D7-D3:D6))))

import pandas as pd
import numpy as np
input1 = pd.read_excel("CH-058 Stepped Tax.xlsx", usecols="B:D", skiprows=1, nrows = 6)
input2 = pd.read_excel("CH-058 Stepped Tax.xlsx", usecols="F:G", skiprows=1, nrows = 6)
test   = pd.read_excel("CH-058 Stepped Tax.xlsx", usecols="H", skiprows=1, nrows = 6)

input1.loc[4, 'To'] = float('inf')
input1['key'] = 1
input2['key'] = 1
output = pd.merge(input1, input2, on='key')
output['income_over_threshold'] = output["Income"] - output["From"]
output['income_in_threshold'] = np.where((output["Income"] >= output["From"]) & (output["Income"] <= output["To"]), True, False)
output = output[output['income_over_threshold'] > 0].sort_values(by = ["Person ID"]).reset_index(drop = True)
output['tax'] = np.where(output['income_in_threshold'], 
 output['income_over_threshold'] * output['Tax Rate'], 
 (output['To'] - output['From']) * output['Tax Rate'])
output = output.groupby('Person ID').agg({'tax': 'sum'}).astype("float64").reset_index()

output['tax'] = output['tax'].round(2)
test['tax'] = test['Tax'].round(2)

print(all(output['tax'] == test['tax'])) # True

library(tidyverse)
library(readxl)

input1 = read_excel("files/CH-058 Stepped Tax.xlsx", range = "B2:D7")
input2 = read_excel("files/CH-058 Stepped Tax.xlsx", range = "F2:G7")
test  = read_excel("files/CH-058 Stepped Tax.xlsx", range = "H2:H7")

input1$To = ifelse(input1$To == "Over", Inf, input1$To) %>% as.numeric()

result = input1 %>% 
 mutate(key = 1) %>%
 full_join(input2 %>% mutate(key = 1), by = "key") %>%
 select(-key) %>%
 filter(From <= To) %>%
 mutate(income_over_threshold = Income - From,
 income_in_threshold = ifelse(Income >= From & Income <= To , T, F)) %>%
 filter(income_over_threshold >= 0) %>%
 arrange(`Person ID`) %>%
 mutate(tax = ifelse(income_in_threshold, income_over_threshold * `Tax Rate`, (To - From) * `Tax Rate`)) %>%
 summarise(Tax = sum(tax), .by = c(`Person ID`, Income)) %>%
 select(Tax)

all(round(result$Tax, 1) == round(test$Tax, 1))
# TRUE

y approach in 
hashtag
#rstats using 
hashtag
#dplyr 
hashtag
#NonEquiJoins. Correct up to 1 decimal point (see screenshot) To me it may be the shortest, but I am open to see shorter solutions. 

tax_rates <- read_excel("Others/CH-058 Stepped Tax.xlsx", 
 range = "B2:D7") |> 
 mutate(across(everything(), ~suppressWarnings(as.numeric(.))))

income <- read_excel("Others/CH-058 Stepped Tax.xlsx", 
 range = "F2:H7")

income |> 
 left_join(tax_rates |> 
 mutate(tax_prev = cumsum((To - From)*`Tax Rate`),
 tax_prev = c(0, head(tax_prev, -1))),
 by = join_by(closest(Income >= From))) |> 
 mutate(tax_calc = (Income - From)*`Tax Rate` + tax_prev)