Program Calculating Tax Rate Per Line Item Equaling Into ExclVAT

Imagine you have the following df: d = {'line amount#1': [5.95], 'line amount#2': [5.95], 'line amount#3': [15.75],'line amount#4': [15.75], 'line amount#5': [3.9] ,'line amoun

Solution 1:

There is a bit of a problem here with rounding, no combination of taxrates actually gives us exactly the right answer.

I've updated the code to find the combination that gives us the most accurate number that can be achieved:

from itertools import product

# get all possible tax rate combinations
x = [0.00, 0.09, 0.21]
combinations = np.array(list(product(*[x]*10)))

# get amount columns
amounts = dfcalc.filter(like='line amount')

# calculate amounts excluding VAT for each row for each tax rate combination
exclvat = amounts.fillna(0).dot((1 + combinations.T)**-1)

# for each row find the combination that gives amounts excluding VAT
# that is equal to the value in ExclVAT column for that row
ix = np.abs(exclvat.sub(dfcalc['ExclVAT'].squeeze(), 0)).idxmin(1)
taxrates = np.where(amounts.notna(), combinations[ix], np.nan)

# subtract tax from line amounts
dfcalc[amounts.columns] /= (1 + taxrates)
dfcalc['line amount sum'] = dfcalc.filter(like='line amount').sum(1)
dfcalc.T

Output:

                         0
line amount#1     4.917355
line amount#2     4.917355
line amount#3    14.449541
line amount#4    14.449541
line amount#5     3.223140
line amount#6     2.396694
line amount#7          NaN
line amount#8          NaN
line amount#9          NaN
line amount#10         NaN
BTW               5.850000
ExclVAT          44.350000
Totaal           50.200000
line amount sum  44.353628