Net to Gross Calculation, payroll

[bilbonvidia]

Hello.

I have created a spreadsheet to work out deductions from gross weekly pay to give a net amount. I would like to be able to get from a net amount back to a gross amount but am unsure of how to do this.

Deductions are tax and national insurance. A person has a tax code for example 885 meaning they can earn up to 8850 a year before being taxed of 8850/52 a week so anything after 8850 is taxable. There are two tax rates (10% )and (18%). The band for 10% is 10500 per year or 10500/52=201.92 a week. This means any taxable pay above 201.92 is taxable at 18%.

To work out National insurance deductions for an employee, weekly gross amount- 100 x 10% giving the National insurance to be deducted. So Gross - Tax - National Insurance = Net amount.

Attached is a spreadsheet I created to work it out. Firstly does this look okay? Secondly is it possible to get back to the gross amount given ONLY a net amount and a tax code?

Thanks for any help

[bilbonvidia]

I have been thinking about another way to do this, not using a forumla. I have plotted a sample of net and gross figures out put by the original formula into a table so I should be able to find the net to gross using this table. My new question is however now this, how can I get the figures in the table to automatically change when A different tax code is used? Please have a look at this revised table.

B

[ExcelSolutions]

Bilbonvidia,

There is no real quick easy way I know, however, I've written a small macro to populate the new figures for you.

Just click the button on sheet1 (ensure Tools> Macro > Security > Medium is selected first), and the table will be updated.

Please note, the code refers to the explicit location of the table. If you move the table you'll have to change the code.

Hope this was your goal.

[fishead]

Hi Bilbonvidia,

I also need to create a spreadsheet to do the same, but can not open the attachment in this forum. Would you be kind enough to e mail me a cop salmond@live.co.uk

Thanks

Sue

[jeffreybrown]

Hi Sue & Welcome to the Forum,

The last activity from Bilbonvidia was 11-16-2011, so not sure you will get a reply anytime soon.

If you should happen to have a question though, please start a new thread.