Actually don't worry, it's basic substitution!
Now, I just need to work out how to get that into excel!
I've ended up here whilst looking for some help with a calculation, I genuinely can't work out what formulae would be required! As this is basically algebra, I felt this was the most appropriate sub-forum.
The premise for this is calculating employee's gross pay and employers national insurance contributions given that I have a defined amount of money, X, to cover both. Essentially I bill monthly for Y per day, expenses come off the top line and what's left is to cover both my salary and employers NI contributions from the business. Employers NI is not a straight percentage, it's 13.8% of employee's earnings over £589 per month (roughly).
The math/algebra looks as follows;
X = A + B
B = 0.138*(A-589) = X - A
A= (B/0.138)+589 = X - B
How can you work out A and B when you input X (i.e A and B are unknowns). I assume this would be perhaps some iterative process?
Any input appreciated. I can work these out by trial and error with online tax calculators, or by using a third party umbrella company website, but I want to be able to build my own spreadsheet that tells me everything I need to know based on inputting just the number of days I'm billing for.
I'm not understanding the difficulty.
You have all the equations. If you know X (input Y and the expenses, then Y-expenses = X), you can find both A and B. You have three equations, and three unknowns.
X=A+B. Knowing X, find A, you can find B and vice versa.
B = X-A= 0.138*(A-589) Solve this for A in terms of X.
X-A = .138A - 589(.138) Add A to both sides, and add 589(.138) to both sides.
X + 589(.138) = 1.138A Divide both sides by 1.138.
(X+589(.138))/1.138=A Now you have A in terms of X. Once you find A, you know B = X-A.
In excel, whatever cell X (call it X1) is in the formula is ...
=(X1+(589*.138))/1.138 That's the A. Say that's in Cell A2
If Y were 10,000 and expenses were 5,000, then X = 5000.
(5000+589(.138))/1.138 = 4465.0941827768...
That makes B = 534.901581
I don't know if you have to round the tax payment to the hundredth, thousandth, or what decimal (talk to a tax attorney), but by general rounding rules that's 4465.10 paid to employees with 534.90 as the NI.