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]

Hi Kirk

I love the single formula solution, but when i tested that against the "test numbers" it threw errors too. I'm going to post the errors to follow - you will see different numbers in the yellow (single formula) to the dividend calculator cells. The correct answer should be £2,662.50 for dividend tax - but there are errors in several cells.

Kind regards

Mike

 

[MikeXYZ7]

200417 MR EXCEL SUPPORT.xlsx
	B	C	D	E	F	G	H	I	J	K	L
8	WAGES INCOME PART A
9	Total Income (Excluding Dividends)	£ 12,500.00				Band start	Band end
10	Personal Allowance	£ -		Personal Allowance	0%	£ -	£ 12,500
11	Basic Rate	£ -		Basic Rate	20%	£ 12,501	£ 50,000
12	Higher Rate	£ -		Higher Rate	40%	£ 50,001	£ 150,000
13	Top Rate	£ -		Top Rate	45%	£ 150,001	£ 1,000,000
14	TAX TO PAY IN THIS SLICE	£ -
15
16	DIVIDENDS INCOME PART B										Remainder
17	DIVIDEND PAYMENTS	£ 37,500.00				Band start	Band end	Adj start	Adj end	Allocation	£ 37,500
18	Dividend Allowance	£ -		Dividend Allowance	0.0%	£ -	£ 2,000	£ -	£ 2,000	£ 2,000	£ 35,500
19	Basic Rate	£ 2,662.50		Basic Rate	7.5%	£ 2,001	£ 50,000	£ 2,000	£ 37,500	£ 35,500	£ -
20	Higher Rate			Higher Rate	32.5%	£ 50,001	£ 150,000	£ 37,500	£ 137,500
21	Top Rate	£ 323,850.00		Top Rate	38.1%	£ 150,001	£ 1,000,000	£ 137,500	£ 987,500	£ 850,000	#VALUE!
22	TAX TO PAY IN THIS SLICE	£ 326,512.50	£ 7,037.50	<-- single formula
KIRK

Cell Formulas
Range	Formula
C10	C10	=(MAX(MIN($H10-$G10,C$9-$H10),0))*$F10
C11	C11	=(MAX(MIN($H11-$G11, C$9-$G11), 0))*$F11
C12:C13	C12	=(MAX(MIN($H12-$G12,C$9-$G12),0))*$F12
C14,C22	C14	=SUM(C10:C13)
L17	L17	=C17
I18:J18	I18	=G18
K18:K21	K18	=IF(L17>0,IF(L17>J18,J18-I18,L17),"")
L18:L21	L18	=IF(L17>0,L17-K18,"")
I19:I21	I19	=J18
J19:J21	J19	=H19-$C$9
C18:C21	C18	=IFERROR(K18*F18,"")
D22	D22	=SUMPRODUCT(--(C17>={0;2000;20000;120000}),(C17-{0;2000;20000;120000}),{0;0.075;0.25;0.056})

 

[KRice]

Mike and Dave, thank you both for the feedback. I've been looking into the U.K. tax rules, as like Dave, I am not familiar with them. Perhaps I was not looking in the right place, but that information seems to be surprisingly difficult to find. I can find descriptions of bands limits and rates, but the methodology descriptions are lacking. I did find a pretty good illustration of the method on YouTube (

), and I see why my approach, or any approach that applies a conventional marginal tax algorithm will produce errors. The conventional approach that I am familiar with apportions the amount from the bottom up, but that is not quite the case here. My single formula offering suffers from this issue.

The issue concerns how the U.K. tax rules determine how to tax dividends. If my understanding is correct (this is a dubious), it appears that the U.K. uses the same tax band limits for income and dividends, but applies different rates to each band. Focusing now on determining the tax on dividends, I believe total income is used to establish where in the tax bands to begin applying tax on the dividends...so the income amount is matched to the dividend band starts to determine which band's starting threshold is exceeded. The income amount establishes a new floor within than band, and the band starting threshold is then ignored. Said differently, the amount of income could place someone partway through a tax band, and the dividend tax would not be assessed on the lower part of that initial placement band (from the band start threshold up to the new floor). To summarize, income determines the dividend starting band and it establishes a new floor within that band.

Then one looks at the headroom within that band and begins to make the tax calculation. For dividends, the amount of taxable headroom is determined in two steps:
1) one compares income to the income personal allowance, and if income exceeds the personal allowance there is no adjustment based on this component, but if income is less than the personal allowance, then the difference between those two is carried over into the dividend calculation and the taxable headroom is reduced by that amount;
2) the dividend allowance also subtracted from the taxable headroom.

And then the dividend tax is determined in the next two steps:
3) now the dividend amount is apportioned based on the new floor established...that is, the new floor is (income + (income - personal allowance if greater than 0) + dividend allowance). The dividends are then apportioned from this new floor to the ending threshold of that band, and then anything that spills over is apportioned to the next band, and so on.
4) band rates are applied to each of their respective apportioned amounts and then summed.

I haven't converted this into Excel just yet, but wanted to share so that others can correct my understanding, or otherwise might benefit from it so that the starting efforts can be improved further.

 

[MikeXYZ7]

Hi Kirk

Sounds like you've got a great handle on this

In the UK we use a "stack" approach to work out TAX. We ALWAYS start with Wages, and tax that at the appropriate rates (provided).

Then we look at dividends and ADD this income on top of the WAGES. The DIVIDEND income will start where the wages finished eg if i earned £50,000 wages, then £30,000 dividends, the dividends would be taxed from £50,001 to £80,000 etc etc using the banding provided (see my manual calcs as examples in either post #29 or #30 i think?)

We would use the appropriate DIVIDEND bands to tax the DIVIDENDS.

First £2000 is tax free regardless of the start point / band.
In this example, we have a £50,000 start point so we add £2,000 = £52,000 (accounted for so far)
The remaining £52,000 - £80,000 is taxed at a rate of 32.5% - and as £28,000 of the remaining DIVIDEND income sits in this band, it works out as £28,000 * 32.5%

You are correct in that the DIVIDEND tax rate depends on the start point (from Wages) - you nailed the idea in your solution but as you say, this puzzle requires certain adjustments to make the income "stack" work in excel to do it automatically. I've been playing around with it this morning but haven't been successful

Really appreciate your help with this ))

Kind regards

Mike

 

[KRice]

Here is a version that does the apportionment of the dividends according to my understanding. I think this is correct for the dividend calculation, but it doesn't include any computations for the income tax. I understand there are some rules where the personal allowance is phased out as a function of income level, so that would need to be added. But please let me know if this checks out okay for the dividends calculations.

mrexcel_20200416.xlsm
	B	C	D	E	F	G	H	I	J
8	WAGES INCOME PART A
9	Total Income (Excluding Dividends)	12500				Band start	Band end
10	Personal Allowance	0		Personal Allowance	0.000	0	12500
11	Basic Rate	0		Basic Rate	0.200	12500	50000
12	Higher Rate	0		Higher Rate	0.400	50000	150000
13	Top Rate	0		Top Rate	0.450	150000	1000000
14	TAX TO PAY IN THIS SLICE	0
15
16	DIVIDENDS INCOME PART B						Taxable Dividends ->	35500
17	DIVIDEND PAYMENTS	37500				Band start	Band end	Allocation	Remainder
18	Personal Allowance Carryover	0.00
19	Dividend Allowance	2000.00		Dividend Allowance	0.000	0	2000	0	35500
20	Basic Rate	2662.50		Basic Rate	0.075	2000	50000	35500	0
21	Higher Rate	0.00		Higher Rate	0.325	50000	150000	0	0
22	Top Rate	0.00		Top Rate	0.381	150000	1000000	0	0
23	TAX TO PAY IN THIS SLICE	2662.50
Sheet18 (2)

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	C14	=SUM(C10:C13)
I16	I16	=IF(C17-C18-C19>0,C17-C18-C19,0)
I19:I22	I19	=CHOOSE(IF(ROWS($G$19:$G19)<MATCH($C$9,$G$19:$G$22,1),1,IF(ROWS($G$19:$G19)=MATCH($C$9,$G$19:$G$22,1),2,IF(ROWS($G$19:$G19)>MATCH($C$9,$G$19:$G$22,1),3))),0,MIN(INDEX($H$19:$H$22,MATCH($C$9,$G$19:$G$22,1))-($C$9+$C$18+$C$19),J18),MIN(J18,H19-G19) )
J19	J19	=C17-C18-C19
J20:J22	J20	=J19-I20
C18	C18	=IF(C9<H10,H10-C9,0)
C19	C19	=IF(C17>H19,H19,C17)
C20:C22	C20	=IFERROR(I20*F20,"")
C23	C23	=SUM(C20:C22)

 

[Dave Patton]

Mike
Did you review the post #26.
With complete information, you or someone may work out a formula to adjust the table for your requirements.
You can consider the various increments and adjustments the brackets and rates or add additional tables.
The example below shows a formula for the modest salary and for high salary. Adjust the formula to your actual data and taxation requirements. I included just one set of named ranges.
As an alternative, you could build your own UDF.

 

[MikeXYZ7]

Mike
Did you review the post #26.
With complete information, you or someone may work out a formula to adjust the table for your requirements.
You can consider the various increments and adjustments the brackets and rates or add additional tables.
The example below shows a formula for the modest salary and for high salary. Adjust the formula to your actual data and taxation requirements. I included just one set of named ranges.
As an alternative, you could build your own UDF.

Hi Dave

Thank you so much for all your help with this. I did review it and it looks good. Kirk's latest version works brilliantly without the need for modest & high salaries. So i am going to use that one.

You've been awesome Dave!

Best wishes

Mike

 

[MikeXYZ7]

Here is a version that does the apportionment of the dividends according to my understanding. I think this is correct for the dividend calculation, but it doesn't include any computations for the income tax. I understand there are some rules where the personal allowance is phased out as a function of income level, so that would need to be added. But please let me know if this checks out okay for the dividends calculations.

Hi Kirk

I think you've cracked it! It solves all the test data and even worked out the correct answers versus the wrong ones i manually worked out.

You've even worked out the allowance carry over (if applicable) from wages to dividends!! I don't need to worry about the diminishing returns at the high end of the spectrum though - thanks for mentioning that though.

One thing i did notice is that if you enter £8,500 into wages, then this causes a negative in the dividends and this in turns creates spurious figures in the tax table.

I was going to try adding an IF statement to say IF calculation <= zero, then set cell to zero (rather than go negative) - does that sound sensible to you?

Thanks once again for such awesome help with ths!

Mike

 

[Dave Patton]

Very good.
Thanks for the feedback. Dave

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

Cell Formulas
Range	Formula
C2	C2	=SUMPRODUCT(--(B2>A8:A11),B2-A8:A11,C8:C11)
C3	C3	=SUMPRODUCT(--(B3>rB),B3-rB,rDiff)
B4:C4	B4	=B2+B3
G8:G11,C8:C11	C8	=B8-N(B7)
E10	E10	=50000-MIN(B2,50000)
E11	E11	=150000-MIN(150000,B2)

Named Ranges
Name	Refers To	Cells
rB	=UK!$E$8:$E$11	C3
rDiff	=UK!$G$8:$G$11	C3

 

[KRice]

Here is a somewhat cleaned up version. I noticed the same thing about low wage scenarios causing an error. In the end, I simply eliminated the formula in the Allocation part of the table for the Dividend Allowance. There is no taxable dividend associated with the allowance, so this shouldn't be problem. On this point, I made some changes, handling the allowances (both personal and dividend) in the left-side table, and then computing what is actually taxable in some new fields above the right-side tables. And then only those taxable amounts are apportioned in the various tax bands below. I've also expanded the upper income table to show the allocation...that's not really necessary as your formulas already do the trick, but if you want to see the allocations for error checking, then that might be handy. A single formula approach for the income table is also shown if you want to use that instead.

mrexcel_20200416.xlsm
	A	B	C	D	E	F	G	H	I	J
7
8		WAGES INCOME PART A							50000	<- Taxable Inc
9		Total Income (Excluding Dividends)	62500				Band start	Band end	TI Allocation	Remainder
10		Personal Allowance	12500.00		Personal Allowance	0.000	0	12500	~~~~~	50000.00
11		Basic Rate	7500.00	7500	Basic Rate	0.200	12500	50000	37500.00	12500
12		Higher Rate	5000.00	5000	Higher Rate	0.400	50000	150000	12500.00	0
13		Top Rate	0.00	0	Top Rate	0.450	150000	1000000	0.00	0
14		TAX TO PAY IN THIS SLICE	12500.00	12500.00	<-- single formula
15				12500.00	<-- using table @right
16		DIVIDENDS INCOME PART B
17		DIVIDEND PAYMENTS	18400						16400	<- Taxable Div
18		Personal Allowance Carryover	0.00				Band start	Band end	TD Allocation	Remainder
19		Dividend Allowance	2000.00		Dividend Allowance	0.000	0	2000	~~~~~	16400
20		Basic Rate	0.00		Basic Rate	0.075	12500	50000	0	16400
21		Higher Rate	5330.00		Higher Rate	0.325	50000	150000	16400	0
22		Top Rate	0.00		Top Rate	0.381	150000	1000000	0	0
23		TAX TO PAY IN THIS SLICE	5330.00
24
25
26		Income	80900.00
27		Tax Part A	12500.00
28		Tax Part B	5330.00
29		Take Home	63070.00
Sheet18 (2)

Cell Formulas
Range	Formula
I8	I8	=C9-C10
J10	J10	=C9-C10
I11:I13	I11	=MIN(H11-G11,J10)
J20:J22,J11:J13	J11	=J10-I11
C10	C10	=IF(C9<H10,C9,H10)
C11:C13	C11	=(MAX(MIN($H11-$G11, C$9-$G11), 0))*$F11
C14,C23	C14	=SUM(C11:C13)
D11:D13	D11	=I11*F11
D14	D14	=SUMPRODUCT(--(C9>={0;12500;50000;150000}),(C9-{0;12500;50000;150000}),{0;0.2;0.2;0.05})
D15	D15	=SUM(D11:D13)
I17	I17	=IF(C17-C18-C19>0,C17-C18-C19,0)
J19	J19	=C17-C18-C19
I20:I22	I20	=CHOOSE(IF(ROWS($G$19:$G20)<MATCH($C$9,$G$19:$G$22,1),1,IF(ROWS($G$19:$G20)=MATCH($C$9,$G$19:$G$22,1),2,IF(ROWS($G$19:$G20)>MATCH($C$9,$G$19:$G$22,1),3))),0,MIN(INDEX($H$19:$H$22,MATCH($C$9,$G$19:$G$22,1))-($C$9+$C$18+$C$19),J19),MIN(J19,H20-G20) )
C18	C18	=IF(C9<H10,H10-C9,0)
C19	C19	=IF(C17>H19,H19,C17)
C20:C22	C20	=IFERROR(I20*F20,"")
C26	C26	=C9+C17
C27	C27	=C14
C28	C28	=C23
C29	C29	=C26-C27-C28