Calculating UK tax and NI contributions

[RebeccaV]

Hello!

I have searched for an answer, but as yet am unable to find one. Here is my query:

I have a formula which calculates the year-end total for tax and national insurance (class 4) contributions on my spreadsheet. However, I would like to do this month by month, bearing in mind you have to reach a threshold before you begin paying tax and national insurance (class 4) contributions.

Is there a way where I can detract my expenses from my income for each month, and then calculate my tax/NI accordingly (obviously for a few months I won't pay anything) but then I would like to have an accumulative formula which recognises each month previously in that months calculation to ensure that I set aside the correct amount for tax/NI accordingly for that month.

For example, April = 406GBP (after expenses) so I won't pay tax or NI (class 4) contributions, then in May = 1,943GBP (after expenses) - still I haven't earned enough to pay Tax or NI (class 4), but I want to add the monthly payments together to then use the greater than formula, that once the threshold has been reached for Tax and NI accordingly, so I can see what I need to set aside for that month.

As mentioned previously, I have a master front sheet which shows the year-end totals, but I'd like to have a month by month analysis to see what I need to set aside for Tax and NI as I go through the year.

I hope this makes sense, and that there is a way to do this. Thank you for any help you can offer.

Kind regards,
RebeccaV

 

[Dave Patton]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1										Bracket/ Hurdle	Rate	Rate Differential
2	Monthly progressive tax (sliding scale) calculated on cumulative sales.									0	10%	10%
3										20,000	15%	5%
4										40,000	20%	5%
5										60,000	25%	5%
6
7		Jan	Feb	Mar	Apr	May	Jun	Jul
8	Input amounts: Net income	10,000.00	20,000.00	30,000.00	18,000.00	10,000.00	12,000.00
9
10	Tax	1,000.00	2,500.00	5,500.00	4,500.00	2,500.00	3,000.00	0.00
11
12	Total	100,000.00	19,000.00
1ee

Excel 2003

Cell Formulas
Range	Formula
L2	=K2-N(K1)
B10	=SUMPRODUCT(--(SUM($B$8:B8)>rB),SUM($B$8:B8)-rB,rRate)-SUM($A$10:A10)
B12	=SUM(B8:H8)
C12	=SUMPRODUCT(--(B12>rB),B12-rB,rRate)

Excel Workbook
Name	Refers To
rB	='1ee'!$J$2:$J$5
rRate	='1ee'!$L$2:$L$5
Workbook Defined Names

I do not know your tax rates or tax brackets.
Hopefully, you can adapt the above for your challenge.

 

[oldbrewer]

It is better to FORECAST annual earnings based on monthly figures - the forecast will get more and more accurate as the year progresses - and put aside 1/12 of the forecast NI class 4 contribution EACH MONTH, adjusting for under or over estimates

 

[oldbrewer]

						tax rates
						10k-15k	10%
						15k-25k	20%
						25k plus	25%
		earnings after expenses
		jan	feb	mar	apr	may	jun	jul	aug	sep	oct	nov	dec
	forecast	1800	1900	1900	2300	2700	2400	2400	2200	2300	2000	2000	2000
	cum forecast	1800	3700	5600	7900	10600	13000	15400	17600	19900	21900	23900	25900
	actual	1900	1800	2100	2700	2800	2400	2200	2300	2200
	cum actual	1900	3700	5800	8500	11300	13700	15900	18200	20400
	end year forecast based on actuals	22800	22200	23200	25500	27120	27400	27257.14	27300	27200
	end year forecast based on forecasts	21600	22200	22400	23700	25440	26000	26400	26400	26533.33	26280	26072.73	25900
tax on actuals	15000	500	500	500	500	500	500	500	500	500
	25000	1560	1440	1640	2000	2000	2000	2000	2000	2000
	>25000	0	0	0	125	530	600	564.2857	575	550
	total tax	2060	1940	2140	2625	3030	3100	3064.286	3075	3050
tax on forecasts	15000	500	500	500	500	500	500	500	500	500	500	500	500
	25000	1320	1440	1480	1740	2000	2000	2000	2000	2000	2000	2000	2000
	>25000	0	0	0	0	110	250	350	350	383.3333	320	268.1818	225
	total tax	1820	1940	1980	2240	2610	2750	2850	2850	2883.333	2820	2768.182	2725
	variation (for-act)	-240	0	-160	-385	-420	-350	-214.286	-225	-166.667	2820	2768.182	2725

<colgroup><col><col><col span="13"></colgroup><tbody>
</tbody>