Calculating taxes backwards

[QWeelon]

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

[#2]

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

[#3 · Accepted solution]

@QWeelon 

See the attached version; it uses XLOOKUP.

[#4]

@Hans Vogelaar 

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!

[#5]

@QWeelon 

See the attached version.

[#6]

Beautiful! Thank you kindly!

Have a great day!
/Q

[#7]

@Hans Vogelaar 

Hi again,

Double checked the results and and got an error of 1 cell for certain values. See attached file for test algorithm.

This formula is lightyears from my Excel comprehension so I'd never be able to figure out how and if it can be tweaked.

Thanks again! 

[#8]

@QWeelon 

That's a matter of interpretation, I think,

For an amount before tax of 18001 kr, the tax is 3384, so the amount after tax is 14617 kr.

You want 14618 kr. That is more than 14617 kr, so the formula rounds up to the next higher amount before tax.

[#9]

@Hans Vogelaar 

Hello.

---

@Hans Vogelaar wrote:

For an amount before tax of 18001 kr, the tax is 3384, so the amount after tax is 14617 kr.

---

Correct. I'm with you so far - and as far as I can see the your formula works fine for the exakt value of 18001. However, if the value increased with 1kr to 18002 (up to 18100), we are still in the same tax interval (18001-18100) and the value in your formula goes to the next interval.

I hear what you are saying, but I'm not able to intuitively see the logic in it. In my head the values when counting forwards and backwards should match - but I've been wrong before when it comes to math..

/Q

[#10]

@QWeelon 

OK, try this (confirmed with Ctrl+Shift+Enter:

=U162+INDEX(C3:R488,MATCH(INDEX(A3:A488,MATCH(U162,A3:A488-INDEX(C3:R488,0,MATCH(U159,C1:R1,0)))),A3:A488,0),MATCH(U159,C1:R1,0))

[#11]

Thank you, the formula is close but not flawless. In this case both of your formulas are good enough for my intentions. I'm very grateful for your time and effort!

Thank you!
Have a good week-end!

[#12]

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