Help with Calculating Stacked payments using banding eg tax

[MikeXYZ7]

Hi there

I am hoping someone can take pity on me and put me out of my misery? I have been trying to figure this out for hours! I am trying to get on top of my finances and want to make a "calculator" that can automatically work out out TAX DUE for PAYE & DIVIDENDS (both use different tax %). Hopefully this is obvious on the screen shot attached.

I have done this successfully for the first part eg assuming a £50k PAYE start, i have successfully worked out how to automatically work out the tax (please note that as i can't attach the excel file, i have shown the formula i have used in the attachment).

What i need to be able to do (and need help with) is to have a start point of £50k going into the second part (DIVIDEND section) and then allocate tax against the new income of £50k (for example) using the new tax % as shown in the figures. And it is here i just cannot work out what to do? I have tried lots of attempts but cannot get my head round how to approach this automatically (it is easy to manually do it - i have shown the answer ii am trying to achieve).

Please can someone put me out of my misery?

Best wishes

Mike

 

[MikeXYZ7]

MT TAX CALCULATOR v1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2		STACKED INCOME CALCULATIONS
3
4		In the example below, Dave receives £90,000 wages (part A) and dividends (part B) from his employer. PART A : Wages are always taxed first, and wages use tax bands 0%, 20%, 40% & 45%. The INCOME PART A example below has correctly calculated the tax due for part A. PART B : Dividends : The way the dividends are calculated DEPEND on the Wages INCOME. So if Dave has already received £30,000 wages, the DIVIDENDS payments should start from £30,001 to £90,000. The DIVIDEND of £60,000 needs to be assigned the correct tax band eg £ 30,001 - £32,000 should incur 0% TAX = £ 0 £ 2,000 £ 32,001 - £50,000 should incur 7.5% tax = £ 1,350.00 £18,000 £ 50,001 - £150,000 should incur 32.5% tax = £13,000 £40,000 £150,001 - £1,000,000 should incur 38.1% tax = £0 THE PROBLEM I HAVE IS I CANNOT GET THE DIVIDEND SECTION TO WORK OUT THE TAX DUE (WHEN YOU ALSO TAKE INTO ACCOUNT THE £30,000 PAYE START POINT) (If the dividend start point was zero, this would be easy, but the dividend start point is the wages figure.
5
6
7
8		WAGES INCOME PART A
9		Total Income (Excluding Dividends)	£ 30,000				Band start	Band end
10		Personal Allowance	£ -		Personal Allowance	0%	£ -	£ 12,500		=(MAX(MIN($H6-$G6, C$5-$H6), 0))*$F6
11		Basic Rate	£ 3,500		Basic Rate	20%	£ 12,501	£ 50,000		=(MAX(MIN($H7-$G7, C$5-$G7), 0))*$F7
12		Higher Rate	£ -		Higher Rate	40%	£ 50,001	£ 150,000		=(MAX(MIN($H8-$G8, C$5-$G8), 0))*$F8
13		Top Rate	£ -		Top Rate	45%	£ 150,001	£ 1,000,000		=(MAX(MIN($H9-$G9, C$5-$G9), 0))*$F9
14		TAX TO PAY IN THIS SLICE	£ 3,500
15
16		DIVIDENDS INCOME PART B		This section should allocate the dividend payments "stacked" as income but using the tax rates (%) across the bands (not stacked)
17		DIVIDEND PAYMENTS	£ 60,000				Band start	Band end
18		Dividend Allowance	£ -		Dividend Allowance	0.0%	0	2000
19		Basic Rate	£ 1,350		Basic Rate	7.5%	2001	50000
20		Higher Rate	£ 3,250		Higher Rate	32.5%	50001	150000
21		Top Rate	£ -		Top Rate	38.1%	150001	1000000
22		TAX TO PAY IN THIS SLICE	£ 4,600
23
24		Income	£ 90,000
25		Tax Part A	£ 3,500
26		Tax Part B	£ 4,600
27		Take Home	£ 81,900
28
29
Sheet1

Cell Formulas
Range	Formula
C10	C10	=(MAX(MIN($H10-$G10, C$9-$H10), 0))*$F10
C11:C13	C11	=(MAX(MIN($H11-$G11, C$9-$G11), 0))*$F11
C14,C22	C14	=SUM(C10:C13)
C18	C18	=(MAX(MIN($H18-$G18, C$17-$H18), 0))*$F18
C21	C21	=(MAX(MIN($H21-$G21, C$17-$G21), 0))*$F21
C24	C24	=C9+C17
C25	C25	=C14
C26	C26	=C22
C27	C27	=C24-C25-C26

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C17	Cell Value	<0	text	NO
C22	Cell Value	<0	text	NO
B22	Cell Value	<0	text	NO
B17	Cell Value	<0	text	NO
E11:E13	Cell Value	<0	text	NO
C9	Cell Value	<0	text	NO
C14	Cell Value	<0	text	NO
B14	Cell Value	<0	text	NO
B9	Cell Value	<0	text	NO

 

[MikeXYZ7]

Apologies - this screenshot shows the correct tax for PART B

 

[MikeXYZ7]

MT TAX CALCULATOR v1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2		STACKED INCOME CALCULATIONS
3
4		In the example below, Dave receives £90,000 wages (part A) and dividends (part B) from his employer. PART A : Wages are always taxed first, and wages use tax bands 0%, 20%, 40% & 45%. The INCOME PART A example below has correctly calculated the tax due for part A. PART B : Dividends : The way the dividends are calculated DEPEND on the Wages INCOME. So if Dave has already received £30,000 wages, the DIVIDENDS payments should start from £30,001 to £90,000. The DIVIDEND of £60,000 needs to be assigned the correct tax band eg £ 30,001 - £32,000 should incur 0% TAX = £ 0 £ 2,000 £ 32,001 - £50,000 should incur 7.5% tax = £ 1,350 £18,000 £ 50,001 - £150,000 should incur 32.5% tax = £13,000 £40,000 £150,001 - £1,000,000 should incur 38.1% tax = £0 THE PROBLEM I HAVE IS I CANNOT GET THE DIVIDEND SECTION TO WORK OUT THE TAX DUE (WHEN YOU ALSO TAKE INTO ACCOUNT THE £30,000 PAYE START POINT) (If the dividend start point was zero, this would be easy, but the dividend start point is the wages figure.
5
6
7
8		WAGES INCOME PART A
9		Total Income (Excluding Dividends)	£ 30,000				Band start	Band end
10		Personal Allowance	£ -		Personal Allowance	0%	£ -	£ 12,500		=(MAX(MIN($H6-$G6, C$5-$H6), 0))*$F6
11		Basic Rate	£ 3,500		Basic Rate	20%	£ 12,501	£ 50,000		=(MAX(MIN($H7-$G7, C$5-$G7), 0))*$F7
12		Higher Rate	£ -		Higher Rate	40%	£ 50,001	£ 150,000		=(MAX(MIN($H8-$G8, C$5-$G8), 0))*$F8
13		Top Rate	£ -		Top Rate	45%	£ 150,001	£ 1,000,000		=(MAX(MIN($H9-$G9, C$5-$G9), 0))*$F9
14		TAX TO PAY IN THIS SLICE	£ 3,500
15
16		DIVIDENDS INCOME PART B		This section should allocate the dividend payments "stacked" as income but using the tax rates (%) across the bands (not stacked)
17		DIVIDEND PAYMENTS	£ 60,000				Band start	Band end
18		Dividend Allowance	£ -		Dividend Allowance	0.0%	0	2000
19		Basic Rate	£ 1,350		Basic Rate	7.5%	2001	50000
20		Higher Rate	£ 13,000		Higher Rate	32.5%	50001	150000
21		Top Rate	£ -		Top Rate	38.1%	150001	1000000
22		TAX TO PAY IN THIS SLICE	£ 14,350
23
24		Income	£ 90,000
25		Tax Part A	£ 3,500
26		Tax Part B	£ 14,350
27		Take Home	£ 72,150
28
Sheet1

Cell Formulas
Range	Formula
C10	C10	=(MAX(MIN($H10-$G10, C$9-$H10), 0))*$F10
C11:C13	C11	=(MAX(MIN($H11-$G11, C$9-$G11), 0))*$F11
C14,C22	C14	=SUM(C10:C13)
C18	C18	=(MAX(MIN($H18-$G18, C$17-$H18), 0))*$F18
C21	C21	=(MAX(MIN($H21-$G21, C$17-$G21), 0))*$F21
C24	C24	=C9+C17
C25	C25	=C14
C26	C26	=C22
C27	C27	=C24-C25-C26

 

[MikeXYZ7]

It may help if you show how you calculated the 4600 for the Dividends.
If we can determine what you mean by stacking the income, we can probably adjust the formula.
The suggestion in post #3 is relevant.

Hi Dave
Thanks for the tip...you are right. I made a mistake with £4,600 - should have said £14,350

 

[MikeXYZ7]

I don't see anything in your XL2BB posts. Select the range you'd like to extract and then click on MrExcel in the toolbar. At that point, the toolbar ribbon will provide an option to "Capture Range"...click that and then a new window will open where you can choose specific content to include, such as "Cell formulas". Confirm and the content is in your clipboard for convenient pasting into the post.

Thanks for the tip KRice

 

[Dave Patton]

What country are you in?
Do the tax authorities provide an explanation and example of the tax calculation?
Please provide an extract of the tax calculation and an example with the expected result.

Review post 3 and adapt the brackets as necessary.

 

[MikeXYZ7]

What country are you in?
Do the tax authorities provide an explanation and example of the tax calculation?
Please provide an extract of the tax calculation and an example with the expected result.

Review post 3 and adapt the brackets as necessary.

Hi Dave
The tax calculation is in the screen shot where i explain the problem much more clearly. I'll re-post the problem as a post in a moment. Thanks for the input Kind regards. Mike (Ps i am in the UK)

 

[MikeXYZ7]

In the example below, Dave receives £90,000 wages (part A) and dividends (part B) from his employer.

PART A : Wages are always taxed first, and wages use tax bands 0%, 20%, 40% & 45%. The INCOME PART A example below has correctly calculated the tax due for part A.

PART B : Dividends : The way the dividends are calculated DEPEND on the Wages INCOME. So if Dave has already received £30,000 wages, the DIVIDENDS payments should start from £30,001 to £90,000. The DIVIDEND of £60,000 needs to be assigned the correct tax band eg
£ 30,001 - £32,000 should incur 0% TAX = £ 0 £ 2,000
£ 32,001 - £50,000 should incur 7.5% tax = £ 1,350 £18,000
£ 50,001 - £150,000 should incur 32.5% tax = £13,000 £40,000
£150,001 - £1,000,000 should incur 38.1% tax = £0

THE PROBLEM I HAVE IS I CANNOT GET THE DIVIDEND SECTION TO WORK OUT THE TAX DUE (WHEN YOU ALSO TAKE INTO ACCOUNT THE £30,000 PAYE START POINT)
(If the dividend start point was zero, this would be easy, but the dividend start point is the wages figure.

 

[Dave Patton]

Do you know the correct amount per the Tax Authorities for 30,000 +60,000?
Review the following and check to see if it provides the correct result.
N.B. I have no information or knowledge of the U.K. tax system.
You can use Excel's Formula Evaluate to step through the formula.

T202004a.xlsm
	A	B	C	D	E	F	G
1	Income
2	Regular	30,000.00	3,500.00
3	Dividends	60,000.00
4		90,000.00	18,000.00
5
6	Regular Income				Dividend Income
7	Brackets	Rates	Rate Diff		Brackets	Rates	Rate Diff
8	0.00	0%	0.00%		0.00	0.0%	0.00%
9	12,500.00	20%	20.00%		2,000.00	7.5%	7.50%
10	50,000.00	40%	20.00%		50,000.00	32.5%	25.00%
11	150,000.00	45%	25.00%		150,000.00	38.1%	5.60%
12
2bbbb

Cell Formulas
Range	Formula
B4	B4	=B2+B3
C4	C4	=SUMPRODUCT(--(B4>rB),B4-rB,rDiff)-SUMPRODUCT(--(B2>rB),B2-rB,rDiff)+C2
C8:C11	C8	=B8-N(C7)
G8:G11	G8	=F8-N(F7)

 

[MikeXYZ7]

Hi Dave

The correct amount as per tax authorities is as already shown - the % in the band shows the correct calculation eg :

£ 30,001 - £32,000 should incur 0% TAX = £ 0 £ 2,000
£ 32,001 - £50,000 should incur 7.5% tax = £ 1,350 £18,000
£ 50,001 - £150,000 should incur 32.5% tax = £13,000 £40,000
£150,001 - £1,000,000 should incur 38.1% tax = £0

The issue is the start point for the dividends is £30,000.
Then, £2,000 is tax free...takes the figure to £32,000
Because the start point is higher, the remaining £18,000 income sits in the next band (£2,001-£50,000) = a tax liability of £1,350
So TOTAL INCOME is now over £50,000, then the next bank kicks in eg £40,000 income taxed at 32.5% = £13,000

The total tax liability for the dividend section should be £14,350

The problem i cannot solve is how to calculate the tax due when the income starts at £30,000 (rather than zero)

Does that make sense?

Kind regards

Mike