Salary Template - Excel

[djmousie]

I need to create a template that allows you to plug in someone’s annual salary and then it breaks it down monthly and applies the appropriate taxes…. But I am not sure this can be done. So you would plug in an annual salary somewhere and then the next 12 columns (Jan-Dec) would plug in the cost. For example, if the person made $120,000 per year… the first month would be $10,000*.03, the second month would need to calculate $6,000*.03 plus $4,000, and the rest of the year would charge nothing for that tax. But if the salary were $60,000 it would take 4 months for the $16,000 to be burned.

Anyone know an easy way using Excel?

 

[SkipVought]

djmousie,

Your example does not make sense to me.

Please remember, no one can understand whatever assumptions you have in your mind, unless they are abeolutely obvious or unless you explicitly state eachone that is relevant.

Fon instance, you state a $120,000 annyual salary, then 3 values that sum to $20,000 in addition to .03. How does that all relate?????

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[djmousie]

For example, if Unemployment tax is 3% up to $16,000 in earnings... then you would pay 3% on your first $16,000 earned, and then 0%. I'm sorry if i'm not being specific enough, I'm trying to help a friend with an excel formula, so Im doing my best to translate what he means.

Thanks

 

[SkipVought]

How can anyone help you if you do not explain the ESSENTIAL requirements of the problem???

So is the monthly allocation of salary base on a

weekly allotment (salary/52), paid on what day of the week

bi-weekly allotment (salary/26), paid with respect to what reference date

monthly alotment (salary/12)


Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[djmousie]

We pay weekly, but budget on a monthly basis... So I only need the calculation to be on a montlhly basis. I don't think the actual pay periods would make a difference.

Picture that the first column you enter a salary. The next 12 columns are the cost for that person per month. That cost is the salary/12 plus the tax (assume 3 percent on the first $16,000).... So the monthly cost would change once the person earned 16K

 

[SkipVought]

here the result table
[tt]
salary cum sal taxable tax
10000 10000 10000 300
10000 20000 16000 480
10000 30000 0 0
10000 40000 0 0
10000 50000 0 0
10000 60000 0 0
10000 70000 0 0
10000 80000 0 0
10000 90000 0 0
10000 100000 0 0
10000 110000 0 0
10000 120000 0 0
[/tt]
where
taxable: =IF(C2-UnemploymentTaxLimit<UnemploymentTaxLimit,IF((B2+D1)<=UnemploymentTaxLimit,B2+D1,IF(UnemploymentTaxLimit-D1>0,(UnemploymentTaxLimit-D1)+D1,0)),0)
tax: =D2*UnemploymentTax

I have named ranges for AnnualSalary, UnemploymentTax, UnemploymentTaxLimit

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[djmousie]

Isnt Column C the taxable and column D the Tax field? Just trying to understand since to me that is a circular reference, sorry not good with ranges and what not

 

[SkipVought]

Sorry, I did not include the entire table
[tt]
Month Salary Cum Sal 0 UTax
1 10000 10000 10000 300
2 10000 20000 16000 480
3 10000 30000 0 0
4 10000 40000 0 0
5 10000 50000 0 0
6 10000 60000 0 0
7 10000 70000 0 0
8 10000 80000 0 0
9 10000 90000 0 0
10 10000 100000 0 0
11 10000 110000 0 0
12 10000 120000 0 0
[tt]


Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[djmousie]

One last question, what is D1 pointing too? Im trying to figure that last piece out

 

[SkipVought]

D1 is ZERO

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[djmousie]

My results differ when replacing D1 as ZERO:

Month Annual Salary Cum Sal Taxable Tax
1 $10,000 $10,000 10000 $300
2 $10,000 $20,000 10000 $300
3 $10,000 $30,000 10000 $300
4 $10,000 $40,000 0 $0
5 $10,000 $50,000 0 $0
6 $10,000 $60,000 0 $0
7 $10,000 $70,000 0 $0
8 $10,000 $80,000 0 $0
9 $10,000 $90,000 0 $0
10 $10,000 $100,000 0 $0
11 $10,000 $110,000 0 $0
12 $10,000 $120,000 0 $0
30000

B17-Unemployed Tax Limit 16000
B18-Unemployment Tax 0.03
B-19Annual Salary 120000

This is how I have the code written in the TAXABLE column:
=IF(C2-$B$17<$B$17,IF((B2+0)<=$B$17,B2+0,IF($B$17-0>0,($B$17-0)+0,0)),0)

 

[SkipVought]

[tt]
E2: =IF(D2-D1>0,(D2-D1)*UnemploymentTax,0)
[/tt]


Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[djmousie]

I get the same results with the above, I just changed your formular to:

Column/Row (E2)=IF(D2-0>0,(D2-0)*$B$18,0)

 

[SkipVought]

[tt]
Month Salary Cum Sal 0 UTax
1 10000 10000 10000 300
2 10000 20000 16000 180
3 10000 30000 0 0
4 10000 40000 0 0
5 10000 50000 0 0
6 10000 60000 0 0
7 10000 70000 0 0
8 10000 80000 0 0
9 10000 90000 0 0
10 10000 100000 0 0
11 10000 110000 0 0
12 10000 120000 0 0
[/tt]
D is cum taxable
E is tax
480 is 3% of 16,000.

Works for me!

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[djmousie]

you are right it does work! i didnt realize D was pointing to the Cum taxable field.

Thanks a ton!

 

[djmousie]

Thanks buddy....could you walk me through the logic of this formula above, this was great, I just want a better understanding of whats happening in each IF statement,

=IF(C2<$B$17,IF((B2+D1)<=$B$17,B2+D1,IF($B$17-D1>0,($B$17-D1)+D1,0)),0)

thanks

 

[UniqueFD]

djmousie,
If you turn on Formula Auditing (View - Toolbars) and use the 'Evaluate Formula' facility, this will show you what's going on and in what order each step is taken.

Tony
___________________________________________________
Reckless words pierce like a sword,
but the tongue of the wise brings healing (Solomon)

 

[djmousie]

UniqueFD i am actually looking to spell out whats happening, for instance:

=IF(C2<$B$17,IF((B2+D1)<=$B$17,B2+D1,IF($B$17-D1>0,($B$17-D1)+D1,0)),0)

"IF the cumulative salary is less th an the unemployment tax limit..."

I need the logic in more leis terms so I can understand what's going on with the formula better. I get most of it, but I want to make sure in case i had to explain this someone.

 

[SkipVought]

It helps is you name all the ranges and Insert/Name/Apply to the expression
[tt]
=IF(Cum_Sal-UnemploymentTaxLimit<UnemploymentTaxLimit,IF((Salary+D1)<=UnemploymentTaxLimit,Salary+PreviousTaxableCum,IF(UnemploymentTaxLimit-D1>0,(UnemploymentTaxLimit-PreviousTaxableCum)+PreviousTaxableCum,0)),0)
[/tt]
so analyzing the expression...
[tt]
IF Cum_Sal-UnemploymentTaxLimit < UnemploymentTaxLimit Then
IF (Salary+D1) <= UnemploymentTaxLimit Then
Salary+PreviousTaxableCum
Else
IF UnemploymentTaxLimit-D1 > 0 Then
(UnemploymentTaxLimit-PreviousTaxableCum)+PreviousTaxableCum
Else
0
End If
End If
Else
0
End If
[/tt]


Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}