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

 

[KRice]

Have you reviewed the approaches for computing tiered rates here:

Help needed :) - Total cost by tier

I'm looking to create a formula that would calculate costs based on 'Report Used'. For example, if reports used is 2000 * $7.50, If 6000 then 5000 * 7.5 plus 6000-5000*$5.00, If 11000 then 5000 * 7.5 plus 5 *$5.00 plus 11000- 10000 *$2.5 Can someone help?

www.mrexcel.com

I think any of those shown at that link, or any of embedded links should help.

 

[Dave Patton]

If you require additional help, please post an extract of your sheet with XL2BB.
The information below, shows a SumProduct formula that calculates the tax.
E1 is the same formula but it does not require the Table of Brackets and Rates.

T202004a.xlsm
	A	B	C	D	E
1	Brackets	Rates	50,000.00	7,500.00	7,500.00
2
3	0.00	0%
4	12,500.00	20%
5	50,000.00	40%
6	150,000.00	45%
7
2bb

Cell Formulas
Range	Formula
D1	D1	=SUMPRODUCT(--(C1>A3:A6),C1-A3:A6,B3:B6-B2:B5)
E1	E1	=SUMPRODUCT(--(C1>{0;12500;50000;150000}),C1-{0;12500;50000;150000},{0;0.2;0.2;0.05})

 

[MikeXYZ7]

Have you reviewed the approaches for computing tiered rates here:

Help needed :) - Total cost by tier

I'm looking to create a formula that would calculate costs based on 'Report Used'. For example, if reports used is 2000 * $7.50, If 6000 then 5000 * 7.5 plus 6000-5000*$5.00, If 11000 then 5000 * 7.5 plus 5 *$5.00 plus 11000- 10000 *$2.5 Can someone help?

www.mrexcel.com

I think any of those shown at that link, or any of embedded links should help.

Thank you so much for getting back to me and recommending the link but that doesnt work. I dont think i made my query clear enough. I'm going to post better info in a moment. Best wishes. Mike

 

[MikeXYZ7]

Attached is a better explanation of the problem

I'm also going to try and upload the original SS

 

[MikeXYZ7]

MT TAX CALCULATOR v1.xlsx
	Q
16
Sheet1

 

[MikeXYZ7]

It doesnt look like the XL2BB has worked? I have MrExcel in my excel and did "select all", then paste but the paste doesn't look right to me???

 

[Fluff]

@MikeXYZ7
You need to select the cells you want to show, before using the XL2BB add-in.
And you can use the testboard to practice, rather then the main board.

 

[KRice]

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.

 

[Dave Patton]

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.