Circular reference? Help!

Hi, this is more of a mathematical question initially, but I also want to get the info on to a spreadsheet as well.

Below is the breakdown of an example salary payment paid through an umbrella company;

 

a)Gross Pay £500.00

b)Taxable expenses -£115.00

c)Employers NI -£27.53

d)Umbrella Co Margin -£22.00

e)Adjusted Gross £335.47

f)Tax -£38.20

g)NI -£23.58

h)Add back expenses £115.00

i)Net pay £388.70

 

My question is.... the employers NI (c) is calculated dependent on the adjusted gross (e), but the adjusted gross is arrived at using (a) - (b) - (c) - (d).  How is this worked out, I assume it is by a calculated sum? rather than trial and error to see which two numbers eventually fit?!

 

And if someone could be even kinder as to explain how I can get this info into excel so that when I change the gross pay, the rest just works itself out.

 

Thank you in advance.

Comments
sysmod's picture

I assume the tax is 8,21%

sysmod | | Permalink

I assume the tax is 8,21%

 c=e*8.21%
e=a-b-c-d
e=a-b-(e*8.21%)-d
e+e*8.21%=a-b-d
e=(a-b-d)/(1+0.0821)
 

Help, but quite late to arrive!

Tax24-7 | | Permalink

I used the 5 April 2012 tax rates as used in the original calculations and also solved algebraically.

 

Two equations                 

1              e = a + b + c + d

2              -c=  (e - 136) * .138

(using 5 April 2012 NI Rates.  £136 is NI free and thereafter the rate is 13.8%.  I've ignored the higher rates.)

                               

Solve algebraically:

-c =  (e - 136) * .138        

-c = .138e - 136 * .138    

c = -.138e + 136 * .138   

c = -.138e + 18.768          

e = a + b + (-.138e + 18.768) + d

1.138e = a + b + d + 18.768          

e = (a + b + d +18.768)/1.138      

 

 

jndavs's picture

Circular reference

jndavs | | Permalink

You are using Gross wages to calculate Employers NI

and Employers NI to calculate to Gross, hence the circular reference.

Out of interest why is the Employers NI being deducted from the Gross?

Add comment
Log in or register to post comments