Dividend formula

[natsand]

Hi, I am trying to enter a formula that automatically works out dividend tax but it doesn't always work.

For example, when taxable income is £210,000 and dividends are £1,490.34 this is what shows in the cells:

The formulas are:
M25 =+IF(P9<2000,P9,2000)
M26 =+IF(P16<37700,P9-M25,IF(M19=37700,0,P9-(37000-M25)))
M27 =+IF(P16<37700,0,IF(P16<150000,P9-M25-M26,IF(M20=150000,0,P9-(150000-M26-M25)))
M28 =+IF(P16<150000,0,P9-M27-M26-M25)

I want to be able to change the taxable income and it change the dividend tax rate automatically but not sure what I am doing wrong?

The first £2,000 of dividends are not taxable
If there is any basic rate band left from taxable income, the next amount of dividends up to the top of that band (up to £37,700) are taxed at 7.5%
If the dividends go over the basic rate band (or taxable income is between £37700 and £150k), the next (up to) £112,300 is taxed at 32.5%
If dividends take this over £150k, the balance is taxed at 38.1%

Thanks
Natalie

 

[DRSteele]

Natalie, please post that sample data using the forum's tool, XL2BB - Excel Range to BBCode

 

[natsand]

excel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	2021/22
2	Limited company
3
4		Corporate P&L, tax computation								Personal tax computation
5		Turnover from Amazon/Audible						£100,000.00	5	Other employment income as reported on P60						£100,000.00
6		Less expenses:							6	Taxable benefits as reported on P11d						£0.00
7		Software				-£1,000.00			7	Employment income						£10,000.00
8		Advertising				-£10,000.00
9		Book production				-£10,000.00			9	Dividends						£51,710.34
10		Other expenses				-£5,000.00
11		Salary				-£10,000.00
12		Employer class 1 NIC				-£160.08			12	Total Income						£161,710.34
13
14		Total expenses						-£36,160.08	14	Personal allowance						£0.00
15
16		Profits before tax						£63,839.92	16	Taxable Income						£161,710.34
17
18		Tax due								Tax due on income
19		Corporation tax (19%)						£12,129.58	19	Tax (20%)			£37,700.00			£7,540.00
20									20	Tax (40%)			£72,300.00			£28,920.00
21		Pay out as dividends						£51,710.34	21	Tax (45%)			£0.00			£0.00
22																£36,460.00
23		Money left in the business						£0.00
24										Tax due on dividends
25									25	Tax (0%)			£2,000.00			£0.00
26									26	Tax (7.5%)			£0.00			£0.00
27									27	Tax (32.5%)			-£96,289.66			-£31,294.14
28									28	Tax (38.1%)			146000			£55,626.00
29
30										NIC due - employment
31										Class 1 NIC (0%)			£9,568.00			£0.00
32										Class 1 NIC (12%)			£40,702.00			£4,884.24
33										Class 1 NIC (2%)			£59,730.00			£1,194.60
Sheet1

Cell Formulas
Range	Formula
P7	P7	=-F11
P9	P9	=H21
F12	F12	=-IF(-F11>8840,(-F11-8840)*0.138,0)
P12	P12	=SUM(P5:P11)
H14	H14	=SUM(F7:F12)
P14	P14	=-IF(P12=0,0,IF(P12<100000,12570,IF(P12>125140,0,P12-100000)))
H16	H16	=SUM(H5:H14)
P16	P16	=SUM(P12:P14)
H19	H19	=+IF(H16<0,0,+H16*0.19)
H21	H21	=IF(H16<0,0,H16-H19)
M19	M19	=+IF(P16<-P14,0,+IF((P16-P9)<=37700,(P16-P9),37700))
M20	M20	=+IF((P16-P9)<37700,0,+IF((P16-P9)<=150000,((P16-P9)+-37700),(150000-37700)))
M21	M21	=+IF((P16-P9)<150000,0,(P16-P9-150000))
P19	P19	=M19*0.2
P20	P20	=M20*0.4
P21	P21	=M21*0.45
P22	P22	=SUM(P19:P21)
H23	H23	=H16-H19-H21
M25	M25	=+IF(P9<2000,P9,2000)
M26	M26	=+IF(P16<37700,P9-M25,IF(M19=37700,0,P9-(37700-M25)))
M27	M27	=+IF(P16<37700,0,IF(P16<150000,P9-M25-M26,IF(M20=150000,0,P9-(150000-M26-M25))))
M28	M28	=+IF(P16<150000,0,P9-M27-M26-M25)
P25,P31	P25	=M25*0
P26	P26	=M26*0.075
P27	P27	=M27*0.325
P28	P28	=M28*0.381
M31	M31	=IF(P5+P7=0,0,+IF((P5+P7)<9568,P5+P7,9568))
M32	M32	=+IF((P5+P7)<9568,0,+IF((P5+P7)<=50270,((P5+P7)-9568),(50270-9568)))
M33	M33	=+IF((P5+P7)<50270,0,(P5+P7)-50270)
P32	P32	=M32*0.12
P33	P33	=M33*0.02

 

[natsand]

excel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	2021/22
2	Limited company
3
4		Corporate P&L, tax computation								Personal tax computation
5		Turnover						£100,000.00		Other employment income as reported on P60						£100,000.00
6		Less expenses:								Taxable benefits as reported on P11d						£0.00
7		Software				-£1,000.00				Employment income						£10,000.00
8		Advertising				-£10,000.00
9		Production				-£10,000.00				Dividends						£51,710.34
10		Other expenses				-£5,000.00
11		Salary				-£10,000.00
12		Employer class 1 NIC				-£160.08				Total Income						£161,710.34
13
14		Total expenses						-£36,160.08		Personal allowance						£0.00
15
16		Profits before tax						£63,839.92		Taxable Income						£161,710.34
17
18		Tax due								Tax due on income
19		Corporation tax (19%)						£12,129.58		Tax (20%)			£37,700.00			£7,540.00
20										Tax (40%)			£72,300.00			£28,920.00
21		Pay out as dividends						£51,710.34		Tax (45%)			£0.00			£0.00
22																£36,460.00
23		Money left in the business						£0.00
24										Tax due on dividends
25										Tax (0%)			£2,000.00			£0.00
26										Tax (7.5%)			£0.00			£0.00
27										Tax (32.5%)			-£96,289.66			-£31,294.14
28										Tax (38.1%)			146000			£55,626.00
29
30										NIC due - employment
31										Class 1 NIC (0%)			£9,568.00			£0.00
32										Class 1 NIC (12%)			£40,702.00			£4,884.24
33										Class 1 NIC (2%)			£59,730.00			£1,194.60
Sheet1

Cell Formulas
Range	Formula
P7	P7	=-F11
P9	P9	=H21
F12	F12	=-IF(-F11>8840,(-F11-8840)*0.138,0)
P12	P12	=SUM(P5:P11)
H14	H14	=SUM(F7:F12)
P14	P14	=-IF(P12=0,0,IF(P12<100000,12570,IF(P12>125140,0,P12-100000)))
H16	H16	=SUM(H5:H14)
P16	P16	=SUM(P12:P14)
H19	H19	=+IF(H16<0,0,+H16*0.19)
H21	H21	=IF(H16<0,0,H16-H19)
M19	M19	=+IF(P16<-P14,0,+IF((P16-P9)<=37700,(P16-P9),37700))
M20	M20	=+IF((P16-P9)<37700,0,+IF((P16-P9)<=150000,((P16-P9)+-37700),(150000-37700)))
M21	M21	=+IF((P16-P9)<150000,0,(P16-P9-150000))
P19	P19	=M19*0.2
P20	P20	=M20*0.4
P21	P21	=M21*0.45
P22	P22	=SUM(P19:P21)
H23	H23	=H16-H19-H21
M25	M25	=+IF(P9<2000,P9,2000)
M26	M26	=+IF(P16<37700,P9-M25,IF(M19=37700,0,P9-(37700-M25)))
M27	M27	=+IF(P16<37700,0,IF(P16<150000,P9-M25-M26,IF(M20=150000,0,P9-(150000-M26-M25))))
M28	M28	=+IF(P16<150000,0,P9-M27-M26-M25)
P25,P31	P25	=M25*0
P26	P26	=M26*0.075
P27	P27	=M27*0.325
P28	P28	=M28*0.381
M31	M31	=IF(P5+P7=0,0,+IF((P5+P7)<9568,P5+P7,9568))
M32	M32	=+IF((P5+P7)<9568,0,+IF((P5+P7)<=50270,((P5+P7)-9568),(50270-9568)))
M33	M33	=+IF((P5+P7)<50270,0,(P5+P7)-50270)
P32	P32	=M32*0.12
P33	P33	=M33*0.02