Forum Discussion
Solved
Calculating taxes backwards
Dear community, here's one for you math lovers!
Basically, what I want to do is calculate backwards how much tax I have to pay to acquire a set amount after tax. See attached file for table.
The input in this case is usually the output when using these tax tables. Column A and B are the set interval, so a salary of 22700 kr would yield a 4634 kr tax cut, ending up in roughly 18000 kr after taxes.
In this case, I want to use the same table to calculate it backwards, so the same example would be phrased as, how much do I have to earn to receive (at least) 18000 kr after taxes. The output needed is in column A, so the answer would be 22601.
I'm at a loss how to program this calculation in Excel, so I would very much appreciate any input!
Regards
/Q
See the attached version; it uses XLOOKUP.
11 Replies
QWeelon I’m not a fan of messing around with taxes, so accounting practice management software always helps me! An excellent assistant, you don’t have to count or worry that you made a mistake somewhere!Although I knew mathematics well at school 😃
See the attached version; it uses XLOOKUP.
Very nice! Thank you!
Is there a way to further augment this formula and make it even more general and safe for future expansion of the table? As you might have figured out, I will have to add another column for next year, and the next year and so on.
The input formula could be assumed to be the first day of the current month, using the formula below:
=TODAY()-DAY(TODAY())+1
Thank you again!
See the attached version.
QWeelon This can be done using a VLOOKUP() or INDEX(MATCH()) after subtracting the tax off the range (which could have been a helper column but I put into the formula). I converted the data into a table to make the data range reference easier/better but you could use regular cell ranges too. I also noticed you have multiple tax years on the sheet so I added the ability to select which year and show both the low and high of the range.
=INDEX(Table1[[Low]:[High]],MATCH(P8,Table1[Low]-INDEX(Table1,,MATCH(TEXT(P7,"0"),Table1[#Headers],0)),1),{1;2})If you have an older version of excel you might need this version and you could also add an identical one but with [High] if you wanted both values:
=INDEX(Table1[Low],MATCH(P8,Table1[Low]-INDEX(Table1,,MATCH(TEXT(P7,"0"),Table1[#Headers],0)),1))
