Excel Formula for UK tax system ??

Nomunnofun1

KidJo said:

I couldn't find a simple free online calculator that simulated a self assessment and tackled non-savings, savings interest and dividends as well as pension contributions due to the complexities. Couldn't get it with salarycalculator.co.uk or listentotaxman etc so I built this one https://taxgrid.co.uk 

Your last three posts have recommended this site. Don’t have a connection?

SnowMan

KidJo said:

I couldn't find a simple free online calculator that simulated a self assessment and tackled non-savings, savings interest and dividends as well as pension contributions due to the complexities. Couldn't get it with salarycalculator.co.uk or listentotaxman etc so I built this one https://taxgrid.co.uk 

That looks interesting.

Not intended as a criticism, you can't cover everything after all, but as constructive comment, but one thing that I noticed it doesn't allow for is the ability to choose to allocate different income sources against the personal allowance so as to minimise tax.

For example £8,000 gross salary, £6,000 savings interest and £4,570 in dividends should result in zero tax not £356. This is because you can allocate the £8,000 salary and £4,570 dividends to the personal allowance. And that then leaves the savings interest that is covered by the £5,000 starter savings rate and the remaining £1,000 which falls within the savings allowance 0% band.

Are you intending to update for that at some point?

KidJo

SnowMan said:

KidJo said:

I couldn't find a simple free online calculator that simulated a self assessment and tackled non-savings, savings interest and dividends as well as pension contributions due to the complexities. Couldn't get it with salarycalculator.co.uk or listentotaxman etc so I built this one https://taxgrid.co.uk 

That looks interesting.

Not intended as a criticism, you can't cover everything after all, but as constructive comment, but one thing that I noticed it doesn't allow for is the ability to choose to allocate different income sources against the personal allowance so as to minimise tax.

For example £8,000 gross salary, £6,000 savings interest and £4,570 in dividends should result in zero tax not £356. This is because you can allocate the £8,000 salary and £4,570 dividends to the personal allowance. And that then leaves the savings interest that is covered by the £5,000 starter savings rate and the remaining £1,000 which falls within the savings allowance 0% band.

Are you intending to update for that at some point?

Thank you for taking the time to test it - genuinely one of the most helpful bits of feedback I've had.
Yes, I hadn't factored in the reordering of the PA as I assumed non-savings, savings and dividends were a fixed order throughout- its subtle but makes a meaningful difference mainly for those on modest earnings. All updated and for clarity I've added an onscreen flag when its applied. Quick question - I have assumed that the blind person's allowance and the marriage allowance add to the PA and then can be reordered as a total - is that correct?

Dazed_and_C0nfused

KidJo said:

SnowMan said:

KidJo said:

I couldn't find a simple free online calculator that simulated a self assessment and tackled non-savings, savings interest and dividends as well as pension contributions due to the complexities. Couldn't get it with salarycalculator.co.uk or listentotaxman etc so I built this one https://taxgrid.co.uk 

That looks interesting.

Not intended as a criticism, you can't cover everything after all, but as constructive comment, but one thing that I noticed it doesn't allow for is the ability to choose to allocate different income sources against the personal allowance so as to minimise tax.

For example £8,000 gross salary, £6,000 savings interest and £4,570 in dividends should result in zero tax not £356. This is because you can allocate the £8,000 salary and £4,570 dividends to the personal allowance. And that then leaves the savings interest that is covered by the £5,000 starter savings rate and the remaining £1,000 which falls within the savings allowance 0% band.

Are you intending to update for that at some point?

Thank you for taking the time to test it - genuinely one of the most helpful bits of feedback I've had.
Yes, I hadn't factored in the reordering of the PA as I assumed non-savings, savings and dividends were a fixed order throughout- its subtle but makes a meaningful difference mainly for those on modest earnings. All updated and for clarity I've added an onscreen flag when its applied. Quick question - I have assumed that the blind person's allowance and the marriage allowance add to the PA and then can be reordered as a total - is that correct?

It's after the Personal Allowance has been allocated that the order applies (non savings non dividend income taxed first, then savings then dividends).

Blind Persons Allowance is an extra allowance in the normal sense.  

Marriage Allowance isn't.  Although the applicant has a reduced Personal Allowance (£11,310 at the moment) the recipient retains the standard Personal Allowance and gets a tax credit off their liability of £252.

NB.  Any unused Marriage Allowance credit is not repayable.

SnowMan

KidJo said:

SnowMan said:

KidJo said:

I couldn't find a simple free online calculator that simulated a self assessment and tackled non-savings, savings interest and dividends as well as pension contributions due to the complexities. Couldn't get it with salarycalculator.co.uk or listentotaxman etc so I built this one https://taxgrid.co.uk 

That looks interesting.

Not intended as a criticism, you can't cover everything after all, but as constructive comment, but one thing that I noticed it doesn't allow for is the ability to choose to allocate different income sources against the personal allowance so as to minimise tax.

For example £8,000 gross salary, £6,000 savings interest and £4,570 in dividends should result in zero tax not £356. This is because you can allocate the £8,000 salary and £4,570 dividends to the personal allowance. And that then leaves the savings interest that is covered by the £5,000 starter savings rate and the remaining £1,000 which falls within the savings allowance 0% band.

Are you intending to update for that at some point?

Thank you for taking the time to test it - genuinely one of the most helpful bits of feedback I've had.
Yes, I hadn't factored in the reordering of the PA as I assumed non-savings, savings and dividends were a fixed order throughout- its subtle but makes a meaningful difference mainly for those on modest earnings. All updated and for clarity I've added an onscreen flag when its applied. Quick question - I have assumed that the blind person's allowance and the marriage allowance add to the PA and then can be reordered as a total - is that correct?

You're very welcome.

It can also make a difference where earnings on their own are just below 40% tax and savings and dividends takes someone into higher rate tax.

There is an example on the LITRG website (see the Finlay example)

https://www.litrg.org.uk/savings-property/tax-savings-and-investments/tax-dividends#11

In that example earnings are £49,150, savings are £800 and dividends are £6,000. By deducting £11,450 of his employment income, £300 of his savings income and £820 of his dividend income against the personal allowance he saves £112.75 in tax relative to deducting £12,570 of his employment income against the personal allowance. It's because by pushing his employment income (on its own) just up to the higher rate band he saves himself some 33.75% tax on dividends (while avoiding tax on the savings through the £300 and £500 0% savings allowance band) and this more than compensates for the extra 20% tax on earnings. The saving is (0.3375 - 0.2) x 820.

KidJo

SnowMan said:

KidJo said:

SnowMan said:

KidJo said:

I couldn't find a simple free online calculator that simulated a self assessment and tackled non-savings, savings interest and dividends as well as pension contributions due to the complexities. Couldn't get it with salarycalculator.co.uk or listentotaxman etc so I built this one https://taxgrid.co.uk 

That looks interesting.

Not intended as a criticism, you can't cover everything after all, but as constructive comment, but one thing that I noticed it doesn't allow for is the ability to choose to allocate different income sources against the personal allowance so as to minimise tax.

For example £8,000 gross salary, £6,000 savings interest and £4,570 in dividends should result in zero tax not £356. This is because you can allocate the £8,000 salary and £4,570 dividends to the personal allowance. And that then leaves the savings interest that is covered by the £5,000 starter savings rate and the remaining £1,000 which falls within the savings allowance 0% band.

Are you intending to update for that at some point?

Thank you for taking the time to test it - genuinely one of the most helpful bits of feedback I've had.
Yes, I hadn't factored in the reordering of the PA as I assumed non-savings, savings and dividends were a fixed order throughout- its subtle but makes a meaningful difference mainly for those on modest earnings. All updated and for clarity I've added an onscreen flag when its applied. Quick question - I have assumed that the blind person's allowance and the marriage allowance add to the PA and then can be reordered as a total - is that correct?

You're very welcome.

It can also make a difference where earnings on their own are just below 40% tax and savings and dividends takes someone into higher rate tax.

There is an example on the LITRG website (see the Finlay example)

https://www.litrg.org.uk/savings-property/tax-savings-and-investments/tax-dividends#11

In that example earnings are £49,150, savings are £800 and dividends are £6,000. By deducting £11,450 of his employment income, £300 of his savings income and £820 of his dividend income against the personal allowance he saves £112.75 in tax relative to deducting £12,570 of his employment income against the personal allowance. It's because by pushing his employment income (on its own) just up to the higher rate band he saves himself some 33.75% tax on dividends (while avoiding tax on the savings through the £300 and £500 0% savings allowance band) and this more than compensates for the extra 20% tax on earnings. The saving is (0.3375 - 0.2) x 820.

Some rigorous testing there and I have to concede you beat the calculator (albeit only a diff of £100).... ok, I get the correct numbers in all those examples except the one above where there is a partial allocation of the PA across the income types. In order to get that sort of accuracy, I'm assuming you need tax software to run all the possible outcomes in £1 increments which is outside my coding skillset. I think I'll aim for (and settle for) accuracy in the 95% region over time but its important to be aware of these limitations... much appreciated. 

KidJo

Dazed_and_C0nfused said:

KidJo said:

SnowMan said:

KidJo said:

I couldn't find a simple free online calculator that simulated a self assessment and tackled non-savings, savings interest and dividends as well as pension contributions due to the complexities. Couldn't get it with salarycalculator.co.uk or listentotaxman etc so I built this one https://taxgrid.co.uk 

That looks interesting.

Not intended as a criticism, you can't cover everything after all, but as constructive comment, but one thing that I noticed it doesn't allow for is the ability to choose to allocate different income sources against the personal allowance so as to minimise tax.

For example £8,000 gross salary, £6,000 savings interest and £4,570 in dividends should result in zero tax not £356. This is because you can allocate the £8,000 salary and £4,570 dividends to the personal allowance. And that then leaves the savings interest that is covered by the £5,000 starter savings rate and the remaining £1,000 which falls within the savings allowance 0% band.

Are you intending to update for that at some point?

Thank you for taking the time to test it - genuinely one of the most helpful bits of feedback I've had.
Yes, I hadn't factored in the reordering of the PA as I assumed non-savings, savings and dividends were a fixed order throughout- its subtle but makes a meaningful difference mainly for those on modest earnings. All updated and for clarity I've added an onscreen flag when its applied. Quick question - I have assumed that the blind person's allowance and the marriage allowance add to the PA and then can be reordered as a total - is that correct?

It's after the Personal Allowance has been allocated that the order applies (non savings non dividend income taxed first, then savings then dividends).

Blind Persons Allowance is an extra allowance in the normal sense.  

Marriage Allowance isn't.  Although the applicant has a reduced Personal Allowance (£11,310 at the moment) the recipient retains the standard Personal Allowance and gets a tax credit off their liability of £252.

NB.  Any unused Marriage Allowance credit is not repayable.

For marriage allowance the MA recipient keeps a standard PA and gets a tax credit and the MA donor's PA get reduces - thats some pretty specific knowledge - I've had to double-check that but its bang on - you know your stuff - I cant see many people knowing that detail. thats caused me some work, but I now have 3 buttons: blind persons allowance adds to PA, MA received applies a tax credit and MA donor reduces PA - cheers for sharing...

Dazed_and_C0nfused

KidJo said:

Dazed_and_C0nfused said:

KidJo said:

SnowMan said:

KidJo said:

I couldn't find a simple free online calculator that simulated a self assessment and tackled non-savings, savings interest and dividends as well as pension contributions due to the complexities. Couldn't get it with salarycalculator.co.uk or listentotaxman etc so I built this one https://taxgrid.co.uk 

That looks interesting.

Not intended as a criticism, you can't cover everything after all, but as constructive comment, but one thing that I noticed it doesn't allow for is the ability to choose to allocate different income sources against the personal allowance so as to minimise tax.

For example £8,000 gross salary, £6,000 savings interest and £4,570 in dividends should result in zero tax not £356. This is because you can allocate the £8,000 salary and £4,570 dividends to the personal allowance. And that then leaves the savings interest that is covered by the £5,000 starter savings rate and the remaining £1,000 which falls within the savings allowance 0% band.

Are you intending to update for that at some point?

Thank you for taking the time to test it - genuinely one of the most helpful bits of feedback I've had.
Yes, I hadn't factored in the reordering of the PA as I assumed non-savings, savings and dividends were a fixed order throughout- its subtle but makes a meaningful difference mainly for those on modest earnings. All updated and for clarity I've added an onscreen flag when its applied. Quick question - I have assumed that the blind person's allowance and the marriage allowance add to the PA and then can be reordered as a total - is that correct?

It's after the Personal Allowance has been allocated that the order applies (non savings non dividend income taxed first, then savings then dividends).

Blind Persons Allowance is an extra allowance in the normal sense.  

Marriage Allowance isn't.  Although the applicant has a reduced Personal Allowance (£11,310 at the moment) the recipient retains the standard Personal Allowance and gets a tax credit off their liability of £252.

NB.  Any unused Marriage Allowance credit is not repayable.

For marriage allowance the MA recipient keeps a standard PA and gets a tax credit and the MA donor's PA get reduces - thats some pretty specific knowledge - I've had to double-check that but its bang on - you know your stuff - I cant see many people knowing that detail. thats caused me some work, but I now have 3 buttons: blind persons allowance adds to PA, MA received applies a tax credit and MA donor reduces PA - cheers for sharing...

There are some odd quirks of the tax system!

What does your calculator do if you have someone receiving Marriage Allowance who also had £1 of dividend income taxed at the dividend nil rate but with that falling into the higher rate band 😳

LITRG have a really good graphic for Marriage Allowance - see "mechanics" here.

https://www.litrg.org.uk/tax-nic/income-tax/tax-allowances/marriage-allowance-transferable-tax-allowance

SnowMan

KidJo said:

SnowMan said:

KidJo said:

SnowMan said:

KidJo said:

I couldn't find a simple free online calculator that simulated a self assessment and tackled non-savings, savings interest and dividends as well as pension contributions due to the complexities. Couldn't get it with salarycalculator.co.uk or listentotaxman etc so I built this one https://taxgrid.co.uk 

That looks interesting.

Not intended as a criticism, you can't cover everything after all, but as constructive comment, but one thing that I noticed it doesn't allow for is the ability to choose to allocate different income sources against the personal allowance so as to minimise tax.

For example £8,000 gross salary, £6,000 savings interest and £4,570 in dividends should result in zero tax not £356. This is because you can allocate the £8,000 salary and £4,570 dividends to the personal allowance. And that then leaves the savings interest that is covered by the £5,000 starter savings rate and the remaining £1,000 which falls within the savings allowance 0% band.

Are you intending to update for that at some point?

Thank you for taking the time to test it - genuinely one of the most helpful bits of feedback I've had.
Yes, I hadn't factored in the reordering of the PA as I assumed non-savings, savings and dividends were a fixed order throughout- its subtle but makes a meaningful difference mainly for those on modest earnings. All updated and for clarity I've added an onscreen flag when its applied. Quick question - I have assumed that the blind person's allowance and the marriage allowance add to the PA and then can be reordered as a total - is that correct?

You're very welcome.

It can also make a difference where earnings on their own are just below 40% tax and savings and dividends takes someone into higher rate tax.

There is an example on the LITRG website (see the Finlay example)

https://www.litrg.org.uk/savings-property/tax-savings-and-investments/tax-dividends#11

In that example earnings are £49,150, savings are £800 and dividends are £6,000. By deducting £11,450 of his employment income, £300 of his savings income and £820 of his dividend income against the personal allowance he saves £112.75 in tax relative to deducting £12,570 of his employment income against the personal allowance. It's because by pushing his employment income (on its own) just up to the higher rate band he saves himself some 33.75% tax on dividends (while avoiding tax on the savings through the £300 and £500 0% savings allowance band) and this more than compensates for the extra 20% tax on earnings. The saving is (0.3375 - 0.2) x 820.

Some rigorous testing there and I have to concede you beat the calculator (albeit only a diff of £100).... ok, I get the correct numbers in all those examples except the one above where there is a partial allocation of the PA across the income types. In order to get that sort of accuracy, I'm assuming you need tax software to run all the possible outcomes in £1 increments which is outside my coding skillset. I think I'll aim for (and settle for) accuracy in the 95% region over time but its important to be aware of these limitations... much appreciated. 

There is some potential for errors using the all or nothing approach.

For example £1,570 non savings non dividend income, £12,000 savings and £12,000 dividends gives £1,006 tax compared with the optimised £568.74 (optimised with £1,570, 6K and 5K of the personal allowance allocated to non savings non dividends, savings and dividends respectively).

Of course I am being mean in choosing those figures but it does show there is a potential for significant errors.

If I was doing it then I would always be testing, in scenarios where non savings dividend income was below the personal allowance, the minimisation attempt where (after the allocation to non savings non dividend income) the balance of the personal allowance allocated to savings exactly used up the starter savings 5K 0% band and the £1,000/£500 0% savings allowance band, but wasn't wasted on pushing savings below this level. This would have eliminated the error in the example above.

That leaves you with the cases such as the LITRG case I mentioned before where there is an error but typically it is just a small one to do with relative tax rates and quirks around the higher rate level. A similar type of error can happen even if non savings non dividend income is below the personal allowance but savings takes you to close to higher rate and dividends pushes you over. But again I think it should be a small error usually. Again this can probably be eliminated by testing an intelligent automated choice of personal allowance allocation. So no you wouldn't need to run things in £1 increments at all. 

Should say unlike Dazed and Confused I'm no tax expert at all just fairly good with numbers

KidJo

Dazed_and_C0nfused said:

KidJo said:

Dazed_and_C0nfused said:

KidJo said:

SnowMan said:

KidJo said:

I couldn't find a simple free online calculator that simulated a self assessment and tackled non-savings, savings interest and dividends as well as pension contributions due to the complexities. Couldn't get it with salarycalculator.co.uk or listentotaxman etc so I built this one https://taxgrid.co.uk 

That looks interesting.

Not intended as a criticism, you can't cover everything after all, but as constructive comment, but one thing that I noticed it doesn't allow for is the ability to choose to allocate different income sources against the personal allowance so as to minimise tax.

For example £8,000 gross salary, £6,000 savings interest and £4,570 in dividends should result in zero tax not £356. This is because you can allocate the £8,000 salary and £4,570 dividends to the personal allowance. And that then leaves the savings interest that is covered by the £5,000 starter savings rate and the remaining £1,000 which falls within the savings allowance 0% band.

Are you intending to update for that at some point?

Thank you for taking the time to test it - genuinely one of the most helpful bits of feedback I've had.
Yes, I hadn't factored in the reordering of the PA as I assumed non-savings, savings and dividends were a fixed order throughout- its subtle but makes a meaningful difference mainly for those on modest earnings. All updated and for clarity I've added an onscreen flag when its applied. Quick question - I have assumed that the blind person's allowance and the marriage allowance add to the PA and then can be reordered as a total - is that correct?

It's after the Personal Allowance has been allocated that the order applies (non savings non dividend income taxed first, then savings then dividends).

Blind Persons Allowance is an extra allowance in the normal sense.  

Marriage Allowance isn't.  Although the applicant has a reduced Personal Allowance (£11,310 at the moment) the recipient retains the standard Personal Allowance and gets a tax credit off their liability of £252.

NB.  Any unused Marriage Allowance credit is not repayable.

For marriage allowance the MA recipient keeps a standard PA and gets a tax credit and the MA donor's PA get reduces - thats some pretty specific knowledge - I've had to double-check that but its bang on - you know your stuff - I cant see many people knowing that detail. thats caused me some work, but I now have 3 buttons: blind persons allowance adds to PA, MA received applies a tax credit and MA donor reduces PA - cheers for sharing...

There are some odd quirks of the tax system!

What does your calculator do if you have someone receiving Marriage Allowance who also had £1 of dividend income taxed at the dividend nil rate but with that falling into the higher rate band 😳

LITRG have a really good graphic for Marriage Allowance - see "mechanics" here.

https://www.litrg.org.uk/tax-nic/income-tax/tax-allowances/marriage-allowance-transferable-tax-allowance

Ok, just so I understand... Mrs Smith applies to MA to her husband. This all gets approved by HMRC. Mr Smith has a salary of £50,270 - no problem. He goes to the calculator and ticks the MA Rec'd box and gets a credit of £252 - happy days. Then Mr Smith gets informed that he has received a surprise dividend of £1. Even though, the £1 is covered by the dividend allowance it still forms part of his higher rate band but simply taxed a 0%. Now he's a higher rate tax payer and in theory doesn't qualify to receive MA. I can't believe in reality HMRC would clawback the £252 based on a small infringement but I don't know. I think the best way to handle this is to apply the £252 tax credit but give a warning pop-up to say how much over the threshold he is and to tell him to consider unchecking the tickbox. I think as its an approved tax credit its probably best not to reduce it to nil, but create a warning and leave it to the user to control. What do you think? That's how it currently calculates. Also, there's a similar warning for the donor.