Excel Help , formula

[bilbonvidia]

I am trying to work out a way to get payroll net to gross figures given only net and a taxcode. I have created a spreadsheet that will give gross to net and deductions. Please see the linked spreadsheet. I have put a some results in a table underneath. Is it possible to get these figures to change automatically when the tax code above changes?

If so is it then possible to some how be able to type into a cell a net figure and have the gross figure returned using the table? Or can this be done via a formula using the figures in the table? Any help is greatly appreciated.

http://www.mediafire.com/?drftjpnyetu"]Spreadsheet Clicky[/URL

 

[SkipVought]

Hi,

Sure. Just have to work backwards with the math. It's not rocket science.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[bilbonvidia]

Thanks for that, but I am talking about only having a net amount and a tax code. I don't know what any of the deductions were so I cant simply add the deductions to the net to get the gross. If you can reverse the math to get the gross from only the net and tax code would you mind giving me a helpful hand?
Cheers

 

[Gavona]

Would Tools, Goal Seek help?



Gavin

 

[bilbonvidia]

Gavin, thank you very much. That is absolutely marvellous and has solved my problem simply and completely.

 

[SkipVought]

If you knew the gross, how would you calculate the net?

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[bilbonvidia]

I see where you are coming from, I guess it can be done in reverse somehow.

 

[SkipVought]

Simple algabra, for instance...
[tt]
net = gross * (1 - TaxRate)

gross = net / (1 - TaxRate)
[/tt]

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[bilbonvidia]

Yes but the net amount has also had national insurance deducted and there are two tax rates so it not as straight forward as that, are there not too any unknowns?

 

[SkipVought]

Put it in a formula. What's the formula???

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[Gavona]

The Goal-Seeking solution is fine for one-off. A more general solution would indeed be to devise a formula. However I see where you are coming from with this Bilbonvidia. It could well be a horrendous task. Never the less, acedemically interesting!
(I might be more tempted to automate the goal seeking approach with vba if I needed to do the calculation repeatedly).

Regards,

Gavin

 

[bilbonvidia]

I was just wondering how I could automate it, so maybe that when you put an amount in net cell the gross automatically appears, or create a button that if pressed prompts for the net and returns the gross changing figures in the spread sheet accordingly. Mmm, I think I have some reading up on VBA to do.

 

[randy700]

I have created a spreadsheet that will give gross to net and deductions

Why not post the formula you use for this, so someone can help you derive at the formula you need?


Randy

 

[SkipVought]

Do I hear an echo?????????


post the formula ....

post the formula ....

post the formula ....

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[bilbonvidia]

here's the current version of the spreadsheet.

http://www.mediafire.com/?cjin2d20kzz

 

[yogia]

here's the current version of the spreadsheet.

http://www.mediafire.com/?cjin2d20kzz

I have looked at the spreassheet -- the formulas show TAX computations for HR personnel.

I am having difficulty understanding the formulas. How about describing in narrative ...

1) how the various taxes are to be computed
2) what are the TAX bands
3) are we limiting computations to HR personnel only --
4) if not are there just two groups a) HR and b) others
5) any additional information so that you can provide complete and clear picture

By the way there are no notes and/or comments on your uploaded spreadsheet, there is no background information on what this is about. Make sure you provide the complete specification ... in words only (not in terms of formulas)


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[bilbonvidia]

The tax 3 digit code represents the amount that can be earned tax free (allowance). So 885 this is multiplied by ten to get the amount £8850 per year for example. HR means the entire amount is taxed at the higher rate of 18%. So if the weekly gross is less than 8850/52 there is no tax payable.
Taxable pay is worked out, if tax code is hr the full gross amount is taxable at higher rate so = entire gross amount. If it’s a code and the gross is more than the allowance , the allowance is deducted from the gross to establish taxable pay.
There are two rates of tax 10% and 18%. The band 10500 means that any amount earned after the tax free pay upto 10500 per year is taxed at 10%, anything after this is taxed at 18%. For weekly figures I am dividing by 52 and for monthly 12.
To work out 10%, again if tax code is hr value is 0. Otherwise if taxable pay is less than or equal to the 10% band then multiply it by 10% to get the value if it is higher then its 10% of the band. So for montly 10500/12 * 10%.
For 18%, if tax code is HR then the full gross amount is taxed at 18%. If the gross is less than the 10% 10500/12(monthly 10500/52 (weekly) band this is 0. If the gross is more than the 10% band then subtract 10% 10500/12(monthly 10500/52 (weekly) band limit from the gross and multiply by 18%.

National insurance is another government payment. Weekly employees start paying NI after they earn 100. to get the amount it is gross wage minus 100 x 10% up to a maximum of 57 weekly. On sheet2 for montly figures it is gross – 435 x 10% up to 246.9.
Employers NI is 12.8% instead of 10% and there is no maximum

 

[bilbonvidia]

Is it possible to change the below so that it prompts for a goal value where it currently says 123456?

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/07/2007 by WAAM
'

'
Range("F16").Select
Range("F16").GoalSeek Goal:=123456, ChangingCell:=Range("B10")

End Sub

Regards

 

[Gavona]

Dim MyNetPay as Integer
MyNetPay=Range("A1").value
Range("F16").GoalSeek Goal:=MyNetPay, ChangingCell:=Range("B10")

Gavin

 

[Gavona]

Sorry I assumed you would input the net into cell A1. Have a look at InputBox in VBA help and post vba related queries to Forum707.

(I am sure that a formula approach is possible. Might be easier to approach if you used Max() and Min() rather than IF().)

Gavin