Excel Tax calculator for 16-17

[air04]

Hi,

I created a excel "planning" my income/dividend/gains/marriageAllowance for this tax year.I hope it does not have too many errors. The only bit I am not sure if I got it right is the adjusted income and PSA amount linkage. The age UK calculator(http://www.ageuk.org.uk/money-matters/i ... alculator/) does not seem to treat PSA as a true allowance.

I checked using one example https://web.archive.org/web/20170127120 ... e#13412106

Please download the excel using the below link
https://drive.google.com/file/d/0B9JWNi ... NKYkU/view

Feedback is appreciated.

Cheers,
ap

[hiriskpaul]

I am working on something similar for myself, so I have downloaded yours to compare. I am also confused about the PSA - see my other post.

A couple of things I have spotted immediately is that you do not reduce the personal allowance for earnings over £100k and ignore additional rate tax. Fair enough if no use to you at present.

[hiriskpaul]

You have not allowed for the starting rate band giving extra relief on savings income.

For example:
Salary income £12,000.00
Interest Income UK(untaxed) £10,000.00
Dividend UK £12,000.00

The salary income lies between the personal allowance and personal allowance + £5000 (starting rate allowance).
In this case you have £11000 + £5000 - 12000 = £4000 that you can use against savings at 0%. You then get another £1000 on top due to the personal saving allowance, so altogether £5000 of the interest income is taxed at 0%. At least that is how my spreadsheet has worked it out and it agrees with the ageuk calculator. Thanks for the link, I have been looking for something like this.

[hiriskpaul]

If it helps, the way I have thought about the PSA and Starting Rate Allowance (SRA) is that they are allowances that you apply in parallel with the normal personal allowance, basic rate, higher rate and additional rate bands. So if you have salary of £100k, but no savings or dividends, you allocate like this:

PA band: 11,000
BR Band: 32,000
HR Band: 57,000

Savings income gets allocated after salary, etc. and this is where you bring in the special allowances for savings. So if salary is 12k, savings 88K, you get a starting rate band of £4000 and a personal savings allowance of £500, like this:

PA Band: 11,000 Salary
BR Band: 1,000 Salary
SR Band: 4,000 Starting rate savings
PS Band: 500 Personal savings allowance
BR Band: 26,500 Remaining basic rate band, filled with savings income
HR Band: 57,000 Higher rate tax band for remaining savings income

Note that the SR Band and PS band are allowances that are included within, or overlap with, the Basic rate band. They set the tax rate to 0% instead of 20%.

If the Salary is above 16,000, the sarting rate band shrinks to zero. The personal savings allowance is set according to the Adjusted Net Income.

I think the ageuk site may be wrong in their calc with this example by the way. For some reason they have not included the £500 personal savings allowance that higher rate taxpayers receive. I could also be wrong though.

[hiriskpaul]

Here is my attempt:

https://drive.google.com/file/d/0Bwpa1C ... sp=sharing

Seems to work ok in Google Sheets.

First section is just a copy and paste of the tax tables. Section in grey is for input. Section headed "Allocation of income to tax bands:" is just for calculations, the various tax bands are filled in sequence. The main, output is at the end.

Not pretty, but it seems to work. Happy to hear of any errors.

[supremetwo]

Here is my attempt:

https://drive.google.com/file/d/0Bwpa1C ... sp=sharing

Seems to work ok in Google Sheets.

First section is just a copy and paste of the tax tables. Section in grey is for input. Section headed "Allocation of income to tax bands:" is just for calculations, the various tax bands are filled in sequence. The main, output is at the end.

Not pretty, but it seems to work. Happy to hear of any errors.

Line 35:- Employment, Pension etc. earnings

Tax deductions from pensions is usually PAYE at source.

[hiriskpaul]

Line 35:- Employment, Pension etc. earnings

Tax deductions from pensions is usually PAYE at source.

Yes. The figure must be gross earnings and gross pension payments received, so add back in anything deducted through PAYE. The same is true of savings, if any tax has been deducted this needs to be added in.

Foreign income deductions are not handled properly, although Irish ETF dividends/interest should be fine as there is no withholding tax on them.

I don't yet know how foreign dividends with withholding tax will be handled. My working assumption is that you have to enter the dividend+withholding tax and then calculate tax on that basis. Withholding taxes will then come in at the end and be set against any UK dividend tax arising on those particular dividends, potentially reducing the tax to zero. Tricky to handle in a simple spreadsheet, so for the moment I am just going to add up dividend+WHT on foreign dividends and include in the dividends box. This should at least make the tax bracket calculation correct, so personal savings allowance and personal allowance tapering can be calculated, even if the final tax amount is wrong. Perhaps I should add a box for foreign dividends and withholding tax?

[air04]

You have not allowed for the starting rate band giving extra relief on savings income.

For example:
Salary income £12,000.00
Interest Income UK(untaxed) £10,000.00
Dividend UK £12,000.00

The salary income lies between the personal allowance and personal allowance + £5000 (starting rate allowance).
In this case you have £11000 + £5000 - 12000 = £4000 that you can use against savings at 0%. You then get another £1000 on top due to the personal saving allowance, so altogether £5000 of the interest income is taxed at 0%. At least that is how my spreadsheet has worked it out and it agrees with the ageuk calculator. Thanks for the link, I have been looking for something like this.

Dear Paul,

Thanks a lot for your feedback.
No I have not handled the £100+ income. There are far more complications of reduction in pers allowance, pension contributions and probably much more that I am not aware of. I will never need it I would say. I am not working, and my wife is on 3-day week.

As the £1000 if PSA is not in any band, I removed it from the bands. So "Total non-div income" in B18 is £9000. The tax should be as expected. The same is with the £12K salary and £88K interest. The reason I came to believe that the PSA is outside the bands is Gengulphus's post. It took me quite a while to understand the detailed justification given by Gengulphus in the below post thread(particularly post 42586 and 42556). For 13K salary, 22K savings and 15K dividends, the calculation differs in your excel. I am not very sure what is right, but I went with Gengulphus.
https://web.archive.org/web/20170127120 ... e#13412106

If you have any source of information, that says that it is a 0% band, let me know. I was always under the same assumption, until I read(and re-read and re-read) Gengulphus's post.

Thanks,
ap

[hiriskpaul]

I have read that post by Gengulphus and a few before it and now my head hurts! I don't know whether the PSA is technically a band or not a band. I am not familiar enough with our tax system to answer that, but I ave followed the rules as far as I have read them and cannot get Gengulphus's results in his posting.

This is hi example:
Employment: £13,000.00
Savings: £22,000.00
Dividends: £15,000.00

I think they should be divided into bands like this (but accept I could be wrong):

Pesonal allowance: £11,000 of earnings
Basic rate band for remaining earnings: £2,000, leaving £30,000 in basic rate band, tax to pay £400
Basic rate band/Starting rate band: £3,000 of savings, taking £3,000 from basic rate band, leaving £27,000, no tax to pay
Basic rate band/Starting rate allowance: £500 of savings, taking £500 from basic rate band, leaving £26,500, no tax to pay
Basic rate band: £18,500 of savings, leaving £8,000 in basic rate band, £3,700 tax
Basic rate band/Dividend allowance band £5,000, leaving £3,000 in basic rate band, no tax to pay
Basic Rate band: £3,000 dividends, £225 tax
Higher rate band: £7,000 dividends, £2,275 tax

That is what my spreadsheet throws out and I think (thought) was how it should work.

The difference between the way I have done it and Gengulphus has is that I think the £500 from the SRA should also be deducted from the basic rate band. Gengulphus does not. I don't know which is right, but will try to check tomorrow. HMRC have done an appalling job explaining all of this.

Sorry for the scruffy layout - done in a hurry and may not get time to post tomorrow.

[hiriskpaul]

Hi ap,

with a clear head I have looked at the HMRC documentation again and agree with Gengulphus that PSA is not a band in the sense that new Dividend Allowance is. The 2 factsheets are here:

https://www.gov.uk/government/publicati ... -factsheet
https://www.gov.uk/government/publicati ... -allowance

The dividend allowance factsheet says this:

"Dividends within your allowance will still count towards your basic or higher rate bands, and may therefore affect the rate of tax that you pay on dividends you receive in excess of the £5,000 allowance."

I had assumed that the PSA worked in the same way, but there is no similar language in the PSA factsheet to say that it does, so I think it is better to assume that it does not.

If the Personal Savings Allowance does not work in the same way as the Dividend Allowance, that then begs the question at what point should the PSA be applied? Most of the time this will not matter, but in a few cases it will make a difference. For example, if someone has no salary/pension income, etc., has savings income of £10,000 and dividends of £10,000, then one way of applying the allowances is like this:

Personal allowance: £10,000 savings income
Personal allowance: £1,000 dividend income
dividend allowance: £5,000 dividend income
basic rate band:£4,000 dividend income

i.e. the personal allowance is not exhausted by savings income, so the PSA is not used. The alternative is the PSA band is used first, resulting in lower tax to pay on dividend income:

Personal Savings Allowance: £1,000 savings income
Personal allowance: £9,000 savings income
Personal allowance: £2,000 dividend income
dividend allowance: £5,000 dividend income
basic rate band:£3,000 dividend income

I am guessing the second way is the correct one, but I don't think I have come across anything to support this guess.

[hiriskpaul]

I have corrected my spreadsheet to treat the PSA as I think it now should be:

- savings allocated to PSA are not allocated to any tax band
- savings allocated to PSA before Personal Allowance

I have also had a tidy up.

New version uploaded.

[helfordpirate]

The dividend allowance factsheet says this:

"Dividends within your allowance will still count towards your basic or higher rate bands, and may therefore affect the rate of tax that you pay on dividends you receive in excess of the £5,000 allowance."

I had assumed that the PSA worked in the same way, but there is no similar language in the PSA factsheet to say that it does, so I think it is better to assume that it does not.

Except that here https://www.gov.uk/government/publicati ... nce-update it says :

"Income that is within an individual’s savings allowance will still count towards their basic or higher rate limits - and may therefore affect the level of savings allowance they are entitled to, and the rate of tax that is due on any savings income they receive in excess of this allowance."

Have to say I thought it was a genuine "allowance" but this and other wording in the document suggest otherwise!

[hiriskpaul]

Except that here https://www.gov.uk/government/publicati ... nce-update it says :

"Income that is within an individual’s savings allowance will still count towards their basic or higher rate limits - and may therefore affect the level of savings allowance they are entitled to, and the rate of tax that is due on any savings income they receive in excess of this allowance."

Have to say I thought it was a genuine "allowance" but this and other wording in the document suggest otherwise!

Oh, so I might have been right the first time?

The Dividend Allowance factsheet states:

"Dividends within your allowance will still count towards your basic or higher rate bands..."

Does anyone know of a way to resolve this? Precisely how and at what point should the PSA be incorporated into the tax calculation? In the past I have got nowhere speaking to someone at the HMRC about something like this. Anything remotely complicated and they immediately try to fob me off by saying they don't give advice and I should speak to a financial adviser.

(Well spotted by the way).

[helfordpirate]

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.

[hiriskpaul]

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 have just finished a very long web chat with someone at HMRC. According to them, the PSA operates very much like a normal allowance in that when income is allocated to it, this income does not count towards basic or higher rate tax band as well, which is the opposite of what the policy paper says. However, I am not overly confident about this answer becomes I tested them with a case involving dividends which she got wrong. She deducted £5,000 for the Dividend Allowance but did not deduct it from the Basic rate tax band as well. Only when I challenged her that this contradicted what it said in the Dividend Allowance factsheet, and in particular example 2.6, did she change her mind.

I have also had it confirmed that the PSA applies AFTER the Personal Allowance. So if Someone has £1,000 in Savings income, £20,000 in dividends, the £1,000 savings income goes to the personal allowance and NOT the PSA, leaving only £10,000 of the personal allowance available for dividends.

[air04]

I have also had it confirmed that the PSA applies AFTER the Personal Allowance. So if Someone has £1,000 in Savings income, £20,000 in dividends, the £1,000 savings income goes to the personal allowance and NOT the PSA, leaving only £10,000 of the personal allowance available for dividends.

Dear Paul,
Thanks a lot for trying to find it out. In case you have not come across this, I found http://www.pruadviser.co.uk/content/kno ... _2013_14/##2 (Order of taxation) useful.

Also, http://www.litrg.org.uk/sites/default/f ... 0FINAL.pdf suggests in an example "Example how personal savings allowance income counts towards basic rate band limit " [page 2] that PSA uses up the band allowance.

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.

Cheers,
ap

[air04]

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.

Got a reply from them,

We have had discussions with HMRC and have contributed to the understanding of how the allowances and bands work. We expect our software will be recognised but I expect the timetable will be later than last year because of the changes in the specification.
We will have a draft available in March, can you remind me again in March



From: ....
Sent: 01 February 2017 21:54
To: support@sa2000.co.uk
Subject: excel for 2016/17
Hello,
I have been using your excel to file returns for a few years now. Thanks a lot for your work.

I can see that your product was recognised in Feb 2016. If the 2016/17 excel is recognised by HMRC in Feb 2017, will it be released(or a draft copy?).

The real reason was that I wanted to find out how the PSA(personal savings allowance) works and plan for this tax year. Is it really an allowance or does it use up the bands. I tried hard on the internet to find it out, I have been unsuccessful to get a sure answer. I know that dividend allowance is not a allowance and it uses up the bands.

As a simple example
....

Their excel is free to use, and one pays only before filing returns. They are the cheapest online filing for my self-assessment needs(that includes non-residence pages).

Cheers,
ap

[hiriskpaul]

Excellent links thanks. I wish I had seen those a few days ago! They do seem to contradict the HMRC PSA factsheet and what I was told on the web chat. The examples are not helpful as the calculatons work out the same however the PSA is treated.

I did say on the web chat that the PSA factsheet was poor and reqested that it be improved. She said that she would pass my comments on. In comparison the Dividend Allowance factsheet is much clearer.

For my own part I will do the calculations both ways until I have confidence in the correct approach, which may not happen until next financial year.

[paulnumbers]

I went through this pain a few months back.

It beggers belief that HMRC can't release an approved spreadsheet.

[hiriskpaul]

I went through this pain a few months back.

It beggers belief that HMRC can't release an approved spreadsheet.

Even a coherent document along the lines of those linked to by air04 would do. They either go for over simplicity or impenetrability.

Did you get anywhere in understanding how the PSA is supposed to work? Anything within it counting towards tax bands or not?