Thread: Math/algebra help required - practical application (tax)

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.

Actually don't worry, it's basic substitution!

Now, I just need to work out how to get that into excel!

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

B is

=X1-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.

Thanks for that!

The difficulty was, high school algebra was quite some time ago! Once I remembered substitution I was good to go! Some rearranging later and it's in excel nicely too.