Donate to Remove ads

Got a credit card? use our Credit Card & Finance Calculators

Thanks to eyeball08,Wondergirly,bofh,johnstevens77,Bhoddhisatva, for Donating to support the site

Excel Tax calculator for 16-17

Practical Issues
air04
2 Lemon pips
Posts: 135
Joined: November 10th, 2016, 11:19 am
Has thanked: 45 times
Been thanked: 24 times

Re: Excel Tax calculator for 16-17

#29611

Postby air04 » February 7th, 2017, 2:07 pm

GPhelan wrote:but the manual is a maze of cross references and is written for insider not public use.

Thanks GPhelen. I feel that it is for public use, for people with a bit more complicated finances who want to do it themselves. I was made aware of this page by the people in this board years ago, and it has been very helpful in some cases like "tax on traded options etc..." which are treated a bit differently than normal shares. There is also the website http://www.legislation.gov.uk/ukpga/200 ... ce#match-1 which I felt is far more complex to understand... just in case you are interested.

Unfortunately the manual has not been updated for 2016-17
https://www.gov.uk/hmrc-internal-manual ... l/saim1000
SAIM1110 Tax on savings and investment income: example for tax year 2014-15
SAIM1112 Tax on savings and investment income: tax year 2015-16


Thanks and Good luck,
ap

mearnsfool
2 Lemon pips
Posts: 186
Joined: November 7th, 2016, 5:29 pm
Has thanked: 7 times
Been thanked: 52 times

Re: Excel Tax calculator for 16-17

#30042

Postby mearnsfool » February 8th, 2017, 11:54 pm

Withholding tax on foreign shares.

In 2015 2016 it is handled as below.

Your foreign dividends are input as gross including this Foreign Withholding Tax and you enter your Foreign Withholding tax in a separate box.

You can also claim Foreign Tax Credit Relief which I did.

After all your tax is calculated during the self assessment a box on the calc sheet says

Total income on which tax has been charged with a figure against it.

Then you get a box with Income Tax charged with a figure against it.

Then a box with the tax already taken off by your bank on bank interest with a figure against it.

Next box minus Foreign Tax Credit Relief with a figure against it

Next box minus Withholding Tax again with the figure you entered in the return.

The next box is "Income Tax due after allowances and reliefs" which removes the already paid tax on bank interest, Foreign Tax Credit Relief and the Withholding Tax from the "Income Tax Charged" box above.

Hope that helps.

mearnsfool
2 Lemon pips
Posts: 186
Joined: November 7th, 2016, 5:29 pm
Has thanked: 7 times
Been thanked: 52 times

Re: Excel Tax calculator for 16-17

#30046

Postby mearnsfool » February 9th, 2017, 12:15 am

Now another little wrinkle for those of us in Scotland to calculate our tax bill.

The UK BR band for 2017 2018 will be £33,500.

The Scottish BR band will be circa £31,500 for 2017 2018 but it may change before April.

Therefore for earned income those living in Scotland will pay tax on their earn income at 20% on circa £31,500 and 40% etc. above that.

The wrinkle is that when calculating dividend, savings and CGT tax those in Scotland have to use the UK BR band of £33,500 in those calculations.

Happy days!!!!!!!!

mearnsfool
2 Lemon pips
Posts: 186
Joined: November 7th, 2016, 5:29 pm
Has thanked: 7 times
Been thanked: 52 times

Re: Excel Tax calculator for 16-17

#30048

Postby mearnsfool » February 9th, 2017, 12:33 am

hiriskpaul was asking a poster to check they were declaring enough earned income to get an annual state pension contribution.

In 2016 2017 the minimum earnings to get that is 52 x Lower Earnings Limit which is £5824 and £5876 in 2017 2018.

The figures for the maximum earned income payable without paying national insurance is 52 X The Primary Threshold which is £8060 in 2016 2017 and £8164 in 2017 2018.

The poster was correctly declaring £8,060 in 2016 2017 therefore that is sorted.

hiriskpaul
Lemon Quarter
Posts: 3893
Joined: November 4th, 2016, 1:04 pm
Has thanked: 698 times
Been thanked: 1524 times

Re: Excel Tax calculator for 16-17

#30090

Postby hiriskpaul » February 9th, 2017, 11:29 am

mearnsfool wrote:Withholding tax on foreign shares.

In 2015 2016 it is handled as below.

Your foreign dividends are input as gross including this Foreign Withholding Tax and you enter your Foreign Withholding tax in a separate box.

You can also claim Foreign Tax Credit Relief which I did.

After all your tax is calculated during the self assessment a box on the calc sheet says

Total income on which tax has been charged with a figure against it.

Then you get a box with Income Tax charged with a figure against it.

Then a box with the tax already taken off by your bank on bank interest with a figure against it.

Next box minus Foreign Tax Credit Relief with a figure against it

Next box minus Withholding Tax again with the figure you entered in the return.

The next box is "Income Tax due after allowances and reliefs" which removes the already paid tax on bank interest, Foreign Tax Credit Relief and the Withholding Tax from the "Income Tax Charged" box above.

Hope that helps.


I am familiar with the old way of handling foreign dividends thanks. I have not seen anything written on the subject with respect to the new rules, so assume it is unchanged and WHT acts in a similar way as a credit to reduce income tax. I have not added it to my spreadsheet yet as I have enough complexity already with the PSA! I am intending to add it in at some point though.

benfool
Posts: 4
Joined: February 21st, 2017, 7:19 am

Re: Excel Tax calculator for 16-17

#33301

Postby benfool » February 21st, 2017, 7:41 am

hiriskpaul wrote:New version of my spreadsheet available here:

<link>

I have taken down the older ones.


Excellent spreadsheet, Paul, thanks for making it!
One query, in another site/thread (see below) shown below, they consider an example of income comprising 8,500 earned income, 6,500 savings income and 6,000 dividend income. They first concluded that the tax should be 75, and this agrees with your spreadsheet. But then in the subsequent replies in that thread, its agreed that the tax should be nil because you can allocate the personal allowance in the way most favourable to the taxpayer. Is your spreadsheet still correct or does it need amending? Cheers.
[I don't have permission to post links, so please Google "For example, if person A has £8500 earned income, £6500 interest income and £6000 dividend income." and the top hit will be the thread.]

air04
2 Lemon pips
Posts: 135
Joined: November 10th, 2016, 11:19 am
Has thanked: 45 times
Been thanked: 24 times

Re: Excel Tax calculator for 16-17

#33328

Postby air04 » February 21st, 2017, 9:32 am

benfool wrote:[I don't have permission to post links, so please Google "For example, if person A has £8500 earned income, £6500 interest income and £6000 dividend income." and the top hit will be the thread.]

https://ion.icaew.com/taxfaculty/f/tax- ... -allowance is the link. I just pasted the link without clicking any button on the top.

It is very interesting.. Thanks for pointing it out. Always learning, the below is what I did not know!!!
You can indeed allocate the personal allowance in the manner most beneficial to the taxpayer.

hiriskpaul
Lemon Quarter
Posts: 3893
Joined: November 4th, 2016, 1:04 pm
Has thanked: 698 times
Been thanked: 1524 times

Re: Excel Tax calculator for 16-17

#33388

Postby hiriskpaul » February 21st, 2017, 12:13 pm

benfool wrote:One query, in another site/thread (see below) shown below, they consider an example of income comprising 8,500 earned income, 6,500 savings income and 6,000 dividend income. They first concluded that the tax should be 75, and this agrees with your spreadsheet. But then in the subsequent replies in that thread, its agreed that the tax should be nil because you can allocate the personal allowance in the way most favourable to the taxpayer. Is your spreadsheet still correct or does it need amending?


Great find Benfool and thanks for posting this. I had no idea about this:

However, as the legislation states that the taxpayer may deduct the reliefs and allowances in the way which will result in the greatest reduction in the taxpayer's income tax liability, it is now beneficial to ensure the PA is not wasted on income that would otherwise be taxed at 0% under the new allowances.


That means in the paulnumbers example, he should be able to move the £2000 interest out of his personal allowance and £2000 of dividend income in. The starting rate band can then cover the interest, making a saving of £2000 * 7.5% = £150.

This means that if someone had £6,000 in interest, £16,000 in dividends they would pay no tax as they could allocate £11,000 of the dividends to the PA, £6,000 interest to the starting rate band and personal savings allowance, and £5,000 dividends to the dividends allowance.

I will take a look at my spreadsheet to see if I can incorporate this. I fear it might not be easy though without adding a lot of complexity.

air04
2 Lemon pips
Posts: 135
Joined: November 10th, 2016, 11:19 am
Has thanked: 45 times
Been thanked: 24 times

Re: Excel Tax calculator for 16-17

#33416

Postby air04 » February 21st, 2017, 1:12 pm

However, as the legislation states that the taxpayer may deduct the reliefs and allowances in the way which will result in the greatest reduction in the taxpayer's income tax liability, it is now beneficial to ensure the PA is not wasted on income that would otherwise be taxed at 0% under the new allowances.

Probably "Dividend allowance" is not a relief or allowance but a 0% band and so can not be allocated to the higher rate part of the dividend. Example 6 of the below https://www.gov.uk/government/publicati ... -factsheet.

hiriskpaul
Lemon Quarter
Posts: 3893
Joined: November 4th, 2016, 1:04 pm
Has thanked: 698 times
Been thanked: 1524 times

Re: Excel Tax calculator for 16-17

#33438

Postby hiriskpaul » February 21st, 2017, 2:33 pm

air04 wrote:
However, as the legislation states that the taxpayer may deduct the reliefs and allowances in the way which will result in the greatest reduction in the taxpayer's income tax liability, it is now beneficial to ensure the PA is not wasted on income that would otherwise be taxed at 0% under the new allowances.

Probably "Dividend allowance" is not a relief or allowance but a 0% band and so can not be allocated to the higher rate part of the dividend. Example 6 of the below https://www.gov.uk/government/publicati ... -factsheet.


Yes, this is very interesting as it looks as though the Example 6 is wrong! It shows what happens to a higher rate taxpayer with salary £40k and dividends £9k. In the example, they allocate to tax bands like this:

PA Earnings at 0% £11,000.00
BR Tax on earnings £29,000.00 tax @20% = £5,800.00
Dividends at 0% Dividend Allowance £5,000.00
HR Tax on dividends £4,000.00 tax@32.50% = £1,300.00

Total Tax £7,100

But if you moved the £4,000 dividends that attract higher rate tax into the PA, it would look like this:

PA Earnings at 0% £7,000.00
PA Dividends at 0% £4,000.00
BR Tax on earnings £32,000.00 tax @20% = £6,400.00
HR Tax on Earnings £1,000.00 tax@40% = £400.00
Dividends at 0% Dividend Allowance £5,000.00

Total tax £6,800.

So if the statement "the legislation states that the taxpayer may deduct the reliefs and allowances in the way which will result in the greatest reduction in the taxpayer's income tax liability" by what appears to be a tax expert is correct, then Example 6 in the factsheet is incorrect. Alternatively the tax experts on the forum are incorrect, or their interpretation may have been superseded by legislation.

I have posted a question on the Tax Forum concerning this.

It will be interesting to see what the SA tax calculation does in April and whether it will be correct.

benfool
Posts: 4
Joined: February 21st, 2017, 7:19 am

Re: Excel Tax calculator for 16-17

#33462

Postby benfool » February 21st, 2017, 3:23 pm

hiriskpaul wrote:I will take a look at my spreadsheet to see if I can incorporate this. I fear it might not be easy though without adding a lot of complexity.

Thanks for looking in to this, Paul.
I foresee many more "IF" functions in your near future. ;)

air04
2 Lemon pips
Posts: 135
Joined: November 10th, 2016, 11:19 am
Has thanked: 45 times
Been thanked: 24 times

Re: Excel Tax calculator for 16-17

#33534

Postby air04 » February 21st, 2017, 6:37 pm

Paul,
My feeling is that if the "SR band" is allocated before "PA Used" for "Savings allocation to tax bands", that will do the trick.

I tried the below, and it seems to work. You know the spreadsheet better, the HMRC tax calculation is so complex, one change can break something else!!!

C53=MIN(D51,B29)
E53=MIN(B45,B29-C53)
to
C53=MIN(D51,B29-E53)
E53=MIN(B45,B29)

Thanks a lot,
ap

hiriskpaul
Lemon Quarter
Posts: 3893
Joined: November 4th, 2016, 1:04 pm
Has thanked: 698 times
Been thanked: 1524 times

Re: Excel Tax calculator for 16-17

#34229

Postby hiriskpaul » February 24th, 2017, 1:17 pm

air04 wrote:Paul,
My feeling is that if the "SR band" is allocated before "PA Used" for "Savings allocation to tax bands", that will do the trick.

I tried the below, and it seems to work. You know the spreadsheet better, the HMRC tax calculation is so complex, one change can break something else!!!

C53=MIN(D51,B29)
E53=MIN(B45,B29-C53)
to
C53=MIN(D51,B29-E53)
E53=MIN(B45,B29)

Thanks a lot,
ap


Thanks for that, I will try it out. I am still not entirely happy about this idea of shuffling income around. Just about everything I have read indicates that dividends come last in the taxation layer, above earnings and savings income. I might create a separate tab for those with earnings below the PA, as this is required in order to take advantage of this taxation tweak.

air04
2 Lemon pips
Posts: 135
Joined: November 10th, 2016, 11:19 am
Has thanked: 45 times
Been thanked: 24 times

Re: Excel Tax calculator for 16-17

#39848

Postby air04 » March 20th, 2017, 2:48 pm

air04 wrote:I have sent a email to support@sa2000.co.uk, they prepare software for SelfAssessment; and I have been using theirs for a few years now. I do not have much hopes from them, but will wait and see.

They sent me a copy of theirs today. They call "starting rate" band for the £5000 interest payment, and they call "nil-rate" band for the £1000/£500 PSA. PSA uses band allowance, so PSA is another 0% band and not a allowance(just like Dividend allowance is not an allowance).

The next test is on Apr 6th, if HMRC online works to test.

Good luc,
ap

DiamondEcho
Lemon Quarter
Posts: 3131
Joined: November 4th, 2016, 3:39 pm
Has thanked: 3060 times
Been thanked: 554 times

Re: Excel Tax calculator for 16-17

#41495

Postby DiamondEcho » March 26th, 2017, 4:31 pm

hiriskpaul wrote:
helfordpirate wrote:
hiriskpaul wrote:Does anyone know of a way to resolve this?

I will wait till TaxCalc come out with the 2016/2017 version or HMRC publish the SA110 Working Sheets.

Any idea when that will be? Do we need to wait until next financial year in order to resolve questions and make decisions about this one?


I like and use Taxcalc too, esp their reasonable pricing [added to which it's tax deductible for me] and 'does what it says on the tin' product.
To answer the above question, this seems to apply:-

'What If? Planner£10.99
This add-on calculates a future tax liability for the 2017-18 tax year and enables the creation of tax planning scenarios. Applies to Individual returns only. The add-on is released in June each year to include changes announced for the next tax year.'


See further at https://www.taxcalc.com/individual#Versions

genou
Lemon Quarter
Posts: 1081
Joined: November 4th, 2016, 1:12 pm
Has thanked: 178 times
Been thanked: 373 times

Re: Excel Tax calculator for 16-17

#41607

Postby genou » March 27th, 2017, 10:33 am

air04 wrote:
The next test is on Apr 6th, if HMRC online works to test.

Good luc,
ap



You might be interested to read this

http://www.accountingweb.co.uk/tax/hmrc ... per-filing

XFool
The full Lemon
Posts: 12636
Joined: November 8th, 2016, 7:21 pm
Been thanked: 2608 times

Re: Excel Tax calculator for 16-17

#41832

Postby XFool » March 28th, 2017, 10:34 am

I may be behind the curve here, but I've just noticed this information in my online Personal Tax Account:

"Personal Savings Allowance
From 6 April 2017 Personal Savings Allowance (PSA) will be a zero percent band. The PSA introduced from 6 April 2017 will still apply but PSA will not be listed as a tax-free allowance.
"

air04
2 Lemon pips
Posts: 135
Joined: November 10th, 2016, 11:19 am
Has thanked: 45 times
Been thanked: 24 times

Re: Excel Tax calculator for 16-17

#41855

Postby air04 » March 28th, 2017, 12:05 pm

XFool wrote:I may be behind the curve here, but I've just noticed this information in my online Personal Tax Account:

"Personal Savings Allowance
From 6 April 2017 Personal Savings Allowance (PSA) will be a zero percent band. The PSA introduced from 6 April 2017 will still apply but PSA will not be listed as a tax-free allowance.
"



Thanks XFool. So that is definite. That matches the sa2000 tax calculator. I was initially not sure, and thought it may be glitch in the software.

Cheers,
ap

air04
2 Lemon pips
Posts: 135
Joined: November 10th, 2016, 11:19 am
Has thanked: 45 times
Been thanked: 24 times

Re: Excel Tax calculator for 16-17

#41858

Postby air04 » March 28th, 2017, 12:08 pm

genou wrote:
air04 wrote:
The next test is on Apr 6th, if HMRC online works to test.

Good luc,
ap



You might be interested to read this

http://www.accountingweb.co.uk/tax/hmrc ... per-filing

I had a glitch in 15-16. I used SA2000 to calculate and file returns, and it was right. Then HMRC sent me at tax calculation and gave me a small amount of tax refund!!! I raised it with them and gave up..
viewtopic.php?f=49&t=3420

benfool
Posts: 4
Joined: February 21st, 2017, 7:19 am

Re: Excel Tax calculator for 16-17

#41939

Postby benfool » March 28th, 2017, 5:21 pm

air04 wrote:
XFool wrote:I may be behind the curve here, but I've just noticed this information in my online Personal Tax Account:

"Personal Savings Allowance
From 6 April 2017 Personal Savings Allowance (PSA) will be a zero percent band. The PSA introduced from 6 April 2017 will still apply but PSA will not be listed as a tax-free allowance.
"



Thanks XFool. So that is definite. That matches the sa2000 tax calculator. I was initially not sure, and thought it may be glitch in the software.

Cheers,
ap

air04, sorry to be dense but can you dumb this down for me? (I mean dumb it down a lot!) Is highriskpaul's spreadsheet correct or does it need to be modified as per your suggestion on 21 Feb? Is this issue related to the 'glitch' highlighted by genou on 27 March?


Return to “Taxes (Practical)”

Who is online

Users browsing this forum: No registered users and 36 guests