"Quick" challenge (allocation of personal allow)

"Quick" challenge (allocation of personal allow)

Didn't find your answer?

I call it "quick" because someone will already have worked this out :)  Otherwise it will not be quick, methinks.

Tax year = 2016-17 or later

In an Excel sheet I have the following items (I *think* that it is all that is required, at least for 99% of cases)

Cell A1 = Basic rate band, adjusted for pension contributions, gift aid payments etc
Cell A2 = Personal allowance (abated as necessary for income over £100K)
Cell A3 = total "general" income net of deductions (ie trade, employment, property, less losses, RAP etc)
Cell A4 = total savings income
Cell A5 = limit on savings income chargeable at 0% (ie ether 0, 500 or 1000)
Cell A6 = total dividend income

Your mission, Jim, should you choose to accept it, is to give a formula that shows how much of the personal allowance should be allocated to dividend income.  If you wish to express it as the ADDITIONAL personal allowance above that which would be allocated under a simple "stacking" algorithm (general income then savings then dividend) then that would be OK.

With kind regards

Clint Westwood

PS I am not being lazy - have spent the best part of a day trying to do it for myself but keep running into errors.  If I get to an answer before all y'all respond and which I think is reliable I shall upload

Replies (3)

Please login or register to join the discussion.

By SteveHa
11th Apr 2017 10:32

Not fully tested (or barely even slightly), but:

=IF(A3+A4>A1,IF(A3+A4-A1<5000,A3-A4-A1,5000),0)

EDIT: Assumes that you know there will be HR liability.

Thanks (1)
avatar
By nogammonsinanundoubledgame
11th Apr 2017 11:51

Still thinking about this. Perhaps a worked example.
Simplify it by excluding savings income.
Salary £36338, dividends £41236, nothing else. 2016-17.
If you allocate all £11K PA to salary, then you have spare BR band £6662 before considering divis. So, £5K divis at 0%, £1662 at 7.5%, 34572 @ 32.5%. Total tax bill £16428.80 as base line "Case 1").

If you allocate £1662 PA to divis then the overall tax bill goes up to £16636.55 (Case 2), ie 12.5% of £1662, the 12.5% being BR rate on salary 20% less BR rate on divis 7.5%

So at first sight that seems to be a bad move.

BUT if you allocate £6662 PA to divis, then the total bill goes down to £16011.55 (case 3) being the best case. The difference between case 2 and case 3 is 12.5% of the excess of £6662 over £1662. Not the same 12.5% as the comparison above, as it happens, this time 32.5% HR tax on divis less 20% BR tax on salary.

So in *this case* whether to allocate PA to divis at all seems to depend on whether £1662 is less than or greater than half of the £11K PA available to be transferred ("half" being relevant only by the coincidence that 20% - 7.5% happens to be the same as 32.5% - 20%). In this case it is less, so transfer is beneficial provided that it exceeds double £1662.

Now, how to put that into a general case formula ...

Thanks (0)
avatar
By nogammonsinanundoubledgame
11th Apr 2017 13:03

My closest attempt so far ...

To arrive at a figure for the ADDITIONAL personal allowance to be allocated to dividends over and above any already allocated by the default stack:

=MAX(0,MIN(A2,A1+A2-A3,MAX(0,A6-5000)-MIN(MAX(0,A6-5000),MAX(0,A1+A2-A3-MAX(0,A4-A5)+MAX(5000,A6)+MAX(A4,A5)))))*((A2-A3-MAX(0,A4-A5))<0)

Not an elegant solution.

Pretty sure that there will be fringe cases where this fails. In particular I am expecting the presence of savings income to throw up an anomaly.

Thanks (0)