Forum Discussion
Calculating taxes backwards
- Sep 21, 2021
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.
- QWeelonSep 22, 2021Brass ContributorBeautiful! Thank you kindly!
Have a great day!
/Q - QWeelonSep 22, 2021Brass Contributor
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!
- HansVogelaarSep 22, 2021MVP
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.
- QWeelonSep 24, 2021Brass Contributor
Hello.
HansVogelaar 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