Spreadsheet - self referencing/recursive formula for tax
Discussion
I can't get my head around this so hoping somebody might be able to give me some pointers.
Trying to put together a sheet to calculate tax for an 'umbrella' contracting company and the 'employer' liability.
To calculate gross salary you need to know employer costs. But the problem is that employer costs calculations are all based on gross salary!
If I guess the gross salary (E44) then you can see the calculated salary in E42 doesn't match. I think I can brute force this with a recursive script to take the value from E42 and use it as the 'input' value for E44, check if it matches and then repeat. You can actually do this by hand surprisingly quickly just copy/pasting.
But is there a way I can do this with a formula? Thanks in advance!
Trying to put together a sheet to calculate tax for an 'umbrella' contracting company and the 'employer' liability.
To calculate gross salary you need to know employer costs. But the problem is that employer costs calculations are all based on gross salary!
If I guess the gross salary (E44) then you can see the calculated salary in E42 doesn't match. I think I can brute force this with a recursive script to take the value from E42 and use it as the 'input' value for E44, check if it matches and then repeat. You can actually do this by hand surprisingly quickly just copy/pasting.
But is there a way I can do this with a formula? Thanks in advance!
Edited by walamai on Tuesday 23 November 15:33
walamai said:
I can't get my head around this so hoping somebody might be able to give me some pointers.
Trying to put together a sheet to calculate tax for an 'umbrella' contracting company and the 'employer' liability.
To calculate gross salary you need to know employer costs. But the problem is that employer costs calculations are all based on gross salary!
If I guess the gross salary (E44) then you can see the calculated salary in E42 doesn't match. I think I can brute force this with a recursive script to take the value from E42 and use it as the 'input' value for E44, check if it matches and then repeat. You can actually do this by hand surprisingly quickly just copy/pasting.
But is there a way I can do this with a formula? Thanks in advance!
You can go in Excel to file > options > formulas and tick the box to enable iterative calculation (which does what you are doing manually, but faster).Trying to put together a sheet to calculate tax for an 'umbrella' contracting company and the 'employer' liability.
To calculate gross salary you need to know employer costs. But the problem is that employer costs calculations are all based on gross salary!
If I guess the gross salary (E44) then you can see the calculated salary in E42 doesn't match. I think I can brute force this with a recursive script to take the value from E42 and use it as the 'input' value for E44, check if it matches and then repeat. You can actually do this by hand surprisingly quickly just copy/pasting.
But is there a way I can do this with a formula? Thanks in advance!
Edited by walamai on Tuesday 23 November 15:33
Or, better, you can work out how to make the calculations in reverse / or separate so that the formulae aren't circular.
The second option is "better" but the first option is much easier and will probably work.
David_M said:
You can go in Excel to file > options > formulas and tick the box to enable iterative calculation (which does what you are doing manually, but faster).
Thanks. I think I can do similar in Google Sheets which I'm using, that was the 'brute force' option I am trying to avoid.David_M said:
Or, better, you can work out how to make the calculations in reverse / or separate so that the formulae aren't circular.
Indeed, but that's what I can't work out! 
Ok - sorry for being unhelpful. 
Why not simply use the calculation that you have at the bottom, create a total (ie E44 plus E49) and then use goalseek to find the gross figure that you want?
I am more familiar with Excel than Google Sheets, but had a quick look and it seems that you can add this functionality?
Why not simply use the calculation that you have at the bottom, create a total (ie E44 plus E49) and then use goalseek to find the gross figure that you want?
I am more familiar with Excel than Google Sheets, but had a quick look and it seems that you can add this functionality?
Mine is a flat £ 'company margin' so don't have to worry about differing amounts every week/month, but are you sure that your gross salary is not AFTER the umbrella companies deductions, and these should be taken off the total invoice amount first?
My gross pay is my assignment income minus Company Margin, minus Employer NI, minus Apprenticeship Levy, so mine is more like:
£1250 Assignment Income
- £21 Company Margin
- £128 Employer NI
- £6 Apprenticeship Levy
= £1095 Gross Pay
- £196 Employee PAYE
- £97 Employee NI
- £100 Pension
= £702 Net Pay
My gross pay is my assignment income minus Company Margin, minus Employer NI, minus Apprenticeship Levy, so mine is more like:
£1250 Assignment Income
- £21 Company Margin
- £128 Employer NI
- £6 Apprenticeship Levy
= £1095 Gross Pay
- £196 Employee PAYE
- £97 Employee NI
- £100 Pension
= £702 Net Pay
Edited by mmm-five on Tuesday 23 November 16:48
David_M said:
Ok - sorry for being unhelpful.
Why not simply use the calculation that you have at the bottom, create a total (ie E44 plus E49) and then use goalseek to find the gross figure that you want?
I am more familiar with Excel than Google Sheets, but had a quick look and it seems that you can add this functionality?
Oooh, I'd not come across that, it looks like witchcraft! Why not simply use the calculation that you have at the bottom, create a total (ie E44 plus E49) and then use goalseek to find the gross figure that you want?
I am more familiar with Excel than Google Sheets, but had a quick look and it seems that you can add this functionality?
Seems to be exactly what I'm after though. I don't have time now to try it, but I'm definitely going to have a go at that and will report back.mmm-five said:
Mine is a flat £ 'company margin' so don't have to worry about differing amounts every week/month, but are you sure that your gross salary is not AFTER the umbrella companies deductions, and these should be taken off the total invoice amount first?
I might be misunderstanding what you are saying, but the gross salary is after umbrella/employer deductions. So I am trying to take the umbrella deductions off the invoice figure. The problem is that calculating those deductions is self-referencing.It works!!
I just created a new cell as the 'target' which is simply a formula with E44-E42. And then set up a 'target' for that to be 0. ie; for the Gross Salary figure in each section to be equal.
Then hit the 'Solve' button. You actually see it iterating through the numbers, exactly like I was doing manually with copy/paste.
And out pops the answer....
After 30 seconds.
So this is the 'brute force' approach, which saved me writing any script. I like that it works, but I'm not sure that I like how it does it!
If nothing else I've learnt something new, so thanks for the pointer.
I just created a new cell as the 'target' which is simply a formula with E44-E42. And then set up a 'target' for that to be 0. ie; for the Gross Salary figure in each section to be equal.
Then hit the 'Solve' button. You actually see it iterating through the numbers, exactly like I was doing manually with copy/paste.
And out pops the answer....
After 30 seconds.
So this is the 'brute force' approach, which saved me writing any script. I like that it works, but I'm not sure that I like how it does it!
If nothing else I've learnt something new, so thanks for the pointer.
xeny said:
Two iterations/second - I don't typically do any kind of computation in Excel, but is that typical?
I think that Excel is much faster than this. Not sure what rate per second, put essentially immediate set at the default of 100 iterations.I don't use google sheets as much, the speed this is running at suggests a client-side process in the browser?
David_M said:
I think that Excel is much faster than this. Not sure what rate per second, put essentially immediate set at the default of 100 iterations.
I don't use google sheets as much, the speed this is running at suggests a client-side process in the browser?
I tried running it a couple of times again with Resource Monitor open (so you can see individual CPU cores) and it seems to be a hybrid of local and 'cloud' processing. The CPU ticks up to about 5-10% utilisation, but none of the CPU cores are pegged at 100%. You can literally see the numbers in the cell iterating at each change, so I think it must send the value to the 'cloud' and then back to the Google Sheet.I don't use google sheets as much, the speed this is running at suggests a client-side process in the browser?
When it took 35 seconds my first thought was that I'm pretty sure I could write a simple recursive script which would be much more efficient. But this is pretty impressive for a general purpose 'magic' tool that takes 30 seconds to set up.
walamai said:
This is beautiful! Thank you!
Now that I know there is an answer, I kind of want to try and derive it myself. But if you feel like explaining how you worked it out then I won't be upset
No probsNow that I know there is an answer, I kind of want to try and derive it myself. But if you feel like explaining how you worked it out then I won't be upset
Employer costs = (Salary-8160)*0.138 + Salary*0.005
Invoiced amount - Salary = (Salary-8160)*0.138 + Salary*0.005
Invoiced amount - Salary = 0.138Salary - 1126.08 + 0.005Salary
Invoiced amount = 1.143Salary - 1126
Salary = (Invoiced amount+1126) / 1.143
Seventy-Eight said:
No probs
Employer costs = (Salary-8160)*0.138 + Salary*0.005
Invoiced amount - Salary = (Salary-8160)*0.138 + Salary*0.005
Invoiced amount - Salary = 0.138Salary - 1126.08 + 0.005Salary
Invoiced amount = 1.143Salary - 1126
Salary = (Invoiced amount+1126) / 1.143
So simple when I can now see it written out! Thanks for that. Employer costs = (Salary-8160)*0.138 + Salary*0.005
Invoiced amount - Salary = (Salary-8160)*0.138 + Salary*0.005
Invoiced amount - Salary = 0.138Salary - 1126.08 + 0.005Salary
Invoiced amount = 1.143Salary - 1126
Salary = (Invoiced amount+1126) / 1.143
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff