Timesheets with tax, upper limit tax-free help

[JakkeJakobsen]

Hi! I got a timesheet, one sheet for each month, and a totals sheet where I also set hourly wage, extras for evenings and weekends, as well as tax %-age and a tax-free limit. Now, if I set the tax-free limit to 50000NOK, £5000, it takes everything within that day as tax-free, but the hard limit is 50k, so the second you hit exactly 50k, everything above goes on %-age. I am unsure how I can fix that, and I get two new employees who are school kids most likely and will use the tax-free limit to the max.

Anyone got an idea?

 

[JakkeJakobsen]

Timesheet Mr Excel - Google Sheets

Here is a full sheet for 2018 I made, anonymous ofc, rates are changed for wages and extras.

What I wan't to get, is that if earned 30k in January, 18k in February, I would be at 48k. In march, I would tip over 50k, and on the pound (or Norwegian krone) it would start with the tax %-age, not the following day or month. Currently it seems to take only up to the current day, then let the next day be taxed.

And I cba to change the name of the weekday part, because you fully understand the Norwegian abbreviations there.

 

[Dave Patton]

a formula can probably provide the amount that you require

Please clarify "would tip over 50k, and on the pound (or Norwegian krone) it would start with the tax %-age, not the following day or month."

- many of us do not know
your tax rules
your tax rates
your currencies

Please provide a few examples and the expected results

 

[JakkeJakobsen]

Rate is at $1 for 8NOK, let's simplify it to 1 for 10. So the tax-free limit is $5000 a year. If a school kid works every other weekend here, he would earn around $700 a month before taxes. Now, those who earn low amounts per year can use the tax-free limit, as the tax over the $5000 is not too high.

So, after July is over, the school kid has earned $4900. Now, in my sheet, the day he earns over the $5000 limit all gets taxed. I need it to split it exactly at the 5k mark, so it is exactly 5000 tax-free, not 4967 or 5032.

Since it all is translated to English, you can see where I set the tax-free limit and on each month in the bottom where it shows gross and net.

 

[Dave Patton]

You may want to read information on using the forum including
Guidelines for Forum Use

- how to post a sample of your data see the suggested addins

What version of Excel do you use?

 

[JakkeJakobsen]

I'm posting this from phone, I am using Google Sheets as I need to stay cheap (free) and cloud based. The link I gave is the full version of the sheet I am using, except names and wage levels.

I am not sure I can directly copy it here without downloading it, manually changing a fair bit of formulas to work in Excel, as Google Sheets got some formulas that might not exist in Excel, which aren't necessarily needed for you guys, but can be.

The best solution is to just click the link and have a look around. This sheet is fully working except for when using the tax-free part, as I have no idea how to make it start using tax % at 5000 exactly, not for the whole day if that day tips it over 5000.

Thats the only way I can explain it. 700 a month times 7 months makes July 31st the day you hit 4900, which means the next day you hit 5000. The sheet currently uses the %-age tax rate on that whole day, making the tax-free stop at 4900. If the total salary was 4999 on July 31st, August 1st would tax the single dollar according to the tax rate in %, but it should not.

 

[JakkeJakobsen]

I can try to clarify:

If you work every other weekend, you earn 350 per weekend, 700 per month. That is 8400 a year. Now, 5000 is tax-free, and anything above is taxed to, say 20%. Now, currently, unless you end a day on exactly 5000, the whole day that goes from below 5000 to above 5000 will be taxed at 20%. What I'd want is to tax only what is above that 5000 limit.

 

[Dave Patton]

One or more of the following alternatives may be of help.
Copy formulas to the right.

Excel 2010
	A	B	C	D	E
1	Tax free	5,000.00
2	Rate	20%	Tax Free * Rate	1,000.00
3
4			June	July	August
5	Earnings	4,500.00	200.00	580.00	1,000.00
6	Cumulative	4,500.00	4,700.00	5,280.00	6,280.00
7
8	CumulativeTax	0.00	0.00	56.00	256.00
9	Current tax	0.00	0.00	56.00	200.00
10
11	Current Tax	0.00	0.00	56.00	200.00
12
13	CumulativeTax	0.00	0.00	56.00	256.00
14
5d

Cell Formulas
Range	Formula
D2	=B1*B2
B6	=B5
B8	=MAX(0,B6*TaxRate-$D$2)
B9	=B8-SUM($A$8:A8)
B11	=(SUM($A$5:B5)>$B$1)*(SUM($A$5:B5)-$B$1)*$B$2-SUM($A$11:A11)
B13	=IF(B6<$B$1,0,(B6-$B$1)*$B$2)

Named Ranges
Name	Refers To	Cells
TaxRate	='5d'!$B$2