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)
 

Add comment
Log in or register to post comments
Group: ExcelZone
A gathering place for the Excel community to explore new ideas and techniques and a forum to debate product features and best practices.