Help with Calculating Stacked payments using banding eg tax

MikeXYZ7

New Member
Joined
Aug 11, 2017
Messages
35
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
 

Attachments

  • MrExcel Support Request.png
    MrExcel Support Request.png
    88.7 KB · Views: 41
Mike,

I wanted to check back in with an update. Your post #41 provided an important clue that something was not being handled properly with the dividend tax calculations. You noted that the issue appeared when wages were within £2000 of an end-of-band threshold. That piqued my curiosity, and further investigation revealed some nuances in how the tax authorities perform these calculations. I am accustomed to conventional marginal (or graduated) tax calculations, including those that have some type of allowance that reduces the taxable amount of income. And indeed, the calculations for taxable wages use that scheme, with the first £12500 of wages income being exempt from taxation. Allocations of taxable wages to the various rate bands follow conventional rules: allocations are determined by the portion of taxable wages within the starting and ending thresholds of each band. I think all proposed solutions employ some method for performing this calculation.

However, there are some exceptions: when the total of wages and dividends exceed £100000, the amount of personal allowance is reduced at the rate of £1 for every £2 earned. So a combined income (wages + dividends) of £125000 is the threshold where the personal allowance would be reduced to 0. I will refer to this evaluation step---where the maximum personal allowance for the particular combination of wages and dividends---as yielding an "adjusted personal allowance". The adjusted personal allowance could be the full amount of £12500 or some lesser amount down to 0, depending on the sum of wages and dividends. This step is important for two reasons: 1) it affects the amount of taxes on wages for higher earners, and 2) it can affect the amount of taxes on dividends if personal allowance "carryover" is involved (more on this further below).

So the first step involves determining the adjusted personal allowance, and then applying as much of it against wage income to reduce the amount of taxable wages. Any remaining balance of the adjusted personal allowance (that cannot be applied to the wages because wages are less than the adjusted personal allowance) is applied (if possible) as "personal allowance carryover" against dividend wages, which also have up to an additional £2000 dividend allowance (if possible) that can be applied against them to reduce the taxable amount of dividends.

The other issue I noted is how taxable dividends are allocated. Despite how the dividend tax band thresholds are often presented, it is convenient to adjust the limits of the first two bands (basic rate and higher rate) to facilitate correct allocation of taxable dividends among the bands. I found one website where these adjusted limits can be seen:
(UK PAYE Tax Rates and Allowances 2019/20 - Tax Rates & Tables)
The helper cells in L8:M22 of my worksheet establish adjusted allocation tables for both wages and dividends.

The exceptions mentioned above can cause some issues with computations that follow traditional marginal rate strategies. I investigated several dozen test cases to develop a clearer understanding. Those test cases are summarized on the worksheet named "TestCases" (from row 6 down). Many of the test cases were designed to walk along the thresholds where different rules apply, and in some of those cases, I selected a wage or dividend income £1 on either side of the threshold to "see" something...to show that a different rate band takes effect, or a different rule now applies. To facilitate running the test cases against the proposed approaches offered earlier by me and by Dave, I constructed a selection section in B1:E4. Either enter a test case number in C3 to copy the wages and dividends from the test case in the list below, or enter an "X" in C4 together with inputs in D4 and E4 to run that case. The numbers to be run on the other worksheets are then copied to a common spot in D2:E2 and results returned to F2:I2.

On the "MikeXYZ7" worksheet is my revised approach, and on the "UK2" worksheet is Dave's latest approach. The inputs on both of those sheets are linked to 'TestCases'!D2:E2. As the test cases were run, I watched the MikeXYZ7 sheet to confirm that allowances, allowance phase-out, personal allowance carryover, and allocations were working correctly. It was during this review that I saw an issue with J18 and J19, which reported negative values in some cases. Those values occurred when the personal allowance carryover exceeded the dividends. Other formulas prevented the negative values from affecting the calculations, but these cells were changed to clean up the display:
  • Changed J18 from =$C$17-$C$18 to =max($C$17-$C$18,0) to prevent a negative amount for the taxable dividends to allocate.
  • Changed J19 from =J18-$C$19 to =max(J18-$C$19,0) to prevent a negative amount for the taxable dividends to allocate.
Results are summarized on the TestCases sheet along with results from two or three on-line calculators (links to those calculators are in the worksheet...only one of those calculators reports both wage and dividend taxes, the others report back only dividend tax). The results from the MikeXYZ7 sheet consistently agree with the online calculators for both wages and dividends tax. Any difference is less than £0.01 and attributed to how the online calculators round their results. One calculator (I refer to as "OLC1") rounds, in most cases, to the whole £1, which isn't enough precision to confirm what I wanted to see...so I stopped using it after 13 cases. That calculator is useful though for understanding how the band thresholds are readjusted, because it offers details on the allocation of dividends among the various bands.

With these last tweaks to the J18 and J19 formulas, I haven't encountered any more surprises, but please let me know if you discover any.

I'm providing a link to this file since it's too complex to handle through XL2BB.
 
Upvote 0

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top