## Excel Tax calculator for 16-17

Practical Issues
air04
Lemon Pip
Posts: 57
Joined: November 10th, 2016, 11:19 am
Has thanked: 6 times
Been thanked: 3 times

### Re: Excel Tax calculator for 16-17

benfool wrote: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?

The issue is not related to the glitch.

I assume you are using https://drive.google.com/open?id=0Bwpa1 ... EpUemQyS2s
From what I can see, highriskpaul's spreadsheet is not updated. And it seems that my suggestion is also not perfect.
I think the below will fix it. It basically involves allocating to the startingrateband(£5000) first, then nil rate band(PSA £1000), and then the Personal allowance
Changing
C53=MIN(D51,B29)
E53=MIN(B45,B29-C53)
F53=MIN(B46,B29-E53-C53)
to
C53==MIN(D51,B29-E53-F53)
E53=MIN(B45,B29)
F53=MIN(B46,B29-E53)

Good luck,
ap

XFool
Lemon Quarter
Posts: 2869
Joined: November 8th, 2016, 7:21 pm
Been thanked: 167 times

### Re: Excel Tax calculator for 16-17

OMG! Does anybody actually know of a genuine, free to use, online, WORKABLE, UK Income Tax Calculator?

I started with high hopes this morning to check my simple spread sheet calculations for Tax year ***2016-17*** using the Age UK Tax Calculator

http://www.ageuk.org.uk/money-matters/i ... alculator/

Page 1. Had some difficulty entering DOB.
Page 2. Fields for "Tax Paid" - Why? I want to know my tax calculation is correct... OK, just ignore these fields.
Page 3. Not available as I haven't filled in all fields.
Page 1. Start again
Page 2. Put explicit zero value in tax fields that don't apply.
Page 3. ...

"Income from savings and investment

Please enter any taxable interest you receive on your taxable savings and investments for the period 6 April 2016 to 5 April 2017.

Please ensure you enter the NET amount.
" (My emphasis)

Has nobody told them?

Give up.

PinkDalek
Lemon Quarter
Posts: 3302
Joined: November 4th, 2016, 1:12 pm
Has thanked: 740 times
Been thanked: 797 times

### Re: Excel Tax calculator for 16-17

XFool wrote:... http://www.ageuk.org.uk/money-matters/i ... alculator/

"Income from savings and investment

Please enter any taxable interest you receive on your taxable savings and investments for the period 6 April 2016 to 5 April 2017.

Please ensure you enter the NET amount.
" (My emphasis)

Has nobody told them?

Give up.

I wouldn't be so critical, as that page also includes a choice:

Taxable interest received with tax taken off
Taxable interest received with no tax taken off

So perhaps the charity should have put Please ensure you enter the NET amount (when applicable).

Anyway, they do appear to be aware of the changes, at least with regard to the abolition of the Income Tax deductions from bank interest, and if you enter the gross interest received the calculator will not assume you've had tax deducted.

You can check this when you get to the final page. Ensuring you click on How we calculated your tax when you get there.

You'll also note the narrative still states Net dividends from shares but when you get to Dividend tax credits there will be nothing shown. No good for foreign dividends though, of which I recall you have some.

I haven't tested the results themselves as there has already been a lengthy thread mentioning the Age UK calculator, as referenced in the OP, which linked to here https://web.archive.org/web/20170127120 ... e#13412106, as well as this 4 page thread itself (which I haven't read, at least not in detail).

XFool
Lemon Quarter
Posts: 2869
Joined: November 8th, 2016, 7:21 pm
Been thanked: 167 times

### Re: Excel Tax calculator for 16-17

Sorry for the delay in replying to this, PD.

PinkDalek wrote:I wouldn't be so critical, as that page also includes a choice:

Taxable interest received with tax taken off
Taxable interest received with no tax taken off

So perhaps the charity should have put Please ensure you enter the NET amount (when applicable).

Anyway, they do appear to be aware of the changes, at least with regard to the abolition of the Income Tax deductions from bank interest, and if you enter the gross interest received the calculator will not assume you've had tax deducted.

You are right. So I tried again. The result gave me a tax owing that was about £250 higher than my calculation.

PinkDalek wrote:You can check this when you get to the final page. Ensuring you click on How we calculated your tax when you get there.

I did this and all was revealed. Well, not quite all. The Age UK tax calculator was telling me I was a HR taxpayer in 2016/17, so the calculation would give a different result from mine, which assumes I was a BR taxpayer.

All in all, I think I can see how to make a calculation that will agree with the Age UK version - unfortunately while you can see the results with How we calculated your tax, you cannot see the precise details. This is the problem.

While I can see how to calculate to agree with the Age UK version, that doesn't mean I agree they are correct. Then again, I cannot say they are incorrect. I just don't know.

It all seems to be down to that Personal Saving Allowance again. I took it as an allowance not a zero tax band for 2016/17 (but won't for 2017/18 as per HMRC). By my idea of this (which may well be wrong) this would result in my just missing being a HR taxpayer, as with my own calculation. To me, the Age UK calculation seems NOT to be doing this, rather just using the PSA as a zero rate band (I think). This does make me a HR taxpayer and so my PSA would be £500 rather than £1000 and some of my dividends would be charged at HR tax. As per the Age UK calculation.

On that link you gave, Gengulphus discusses several points in detail (although originally focussed on a somewhat different query) and I think, if I understand correctly what he is saying, it implies my calculation might be correct. This would imply the Age UK one is wrong. But I really am not sure. I need to print out posts from that thread - I cannot get a printer friendly format as, looking at the old TMF site, sadly the discussion board archives now seem unavailable.

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

### Re: Excel Tax calculator for 16-17

benfool wrote:
hiriskpaul wrote:New version of my spreadsheet available here:

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.]

Just getting back to the example of a tax calculation in 2016/17 for income of £21,000, comprising £8,500 earned income, £6,500 savings income and £6,000 dividend income, I've temporarily used these values in my 2016-17 self-assessment from the HMRC website. The tax calculation shows no tax is due. Indeed, dividends could have been £7,000 and there would still be no tax due.

scrumpyjack
Lemon Slice
Posts: 347
Joined: November 4th, 2016, 10:15 am
Has thanked: 13 times
Been thanked: 90 times

### Re: Excel Tax calculator for 16-17

Have just received Taxcalc for 2016/17 and find that the Revenue's system is wrong in several situations and cannot cope with online filing in these cases. Needless to say I am one of them so will have to submit a PAPER return! HMRC's incompetence has really reached a new level. It also seems they are unable to correct their system for the 2016/17 year (well it's only a little over 9 months till the online filing deadline!)

IRIS has produced a free spreadsheet that shows if your are affected

https://www.iris.co.uk/insight/blog/acc ... on-errors/

Ugh, they fine me if I screw up but presumably they are allowed to stuff up without any sanction.

DiamondEcho
Lemon Quarter
Posts: 2762
Joined: November 4th, 2016, 3:39 pm
Has thanked: 2006 times
Been thanked: 393 times

### Re: Excel Tax calculator for 16-17

Thanks for that ScrumpyJ. Luckily I believe the errors won't impact me. But how much chaos will there be when they shift to quarterly reporting in 2018, oh dear. It's amazing [and yet sadly not] that they release a product without adequate user-acceptance testing...

scrumpyjack
Lemon Slice
Posts: 347
Joined: November 4th, 2016, 10:15 am
Has thanked: 13 times
Been thanked: 90 times

### Re: Excel Tax calculator for 16-17

Yes and ironically software suppliers like Taxcalc are not allowed by HMRC to produce a product that does the calculations correctly and according to the law. They have to calculate the return as per the HMRC specification even though HMRC admit the spec is wrong. For some reason having issued the spec, HMRC are unable to change it!

The reason Taxcalc is not allowed to calculate the return correctly is that, if it did so, the return would be rejected by the online submission system as it would not agree with the HMRC calculation.

DiamondEcho
Lemon Quarter
Posts: 2762
Joined: November 4th, 2016, 3:39 pm
Has thanked: 2006 times
Been thanked: 393 times

### Re: Excel Tax calculator for 16-17

That really is mad, so much for the IRs aim of going paperless if this the shambles people face.
I happen to use Taxcalc, since IME it's so much simpler than filing direct via the IR. And ironically I'd trust Taxcalc to highlight problems [like those you linked to] and give useful advice on how best to deal with it, way more than I'd trust the IR.

shares4me2
Posts: 3
Joined: December 11th, 2017, 12:38 pm
Has thanked: 1 time

### Re: Excel Tax calculator for 16-17

H,
I know this is a very old thread but I stumbled across it while searching for test data to test my spreadsheet with.
Hopefully somebody is still watching.
I have actually been trying to get my head round this since I saw my accountant about six months ago. Because his figures came to Zero tax for my wife when both my spreadsheet which I must say is not as elegant as the work in the one by @hiriskpaul and his one give the same answer.

The Figures I am using for 2016-17 tax year are:
Income & Pensions = £11000, Savings = £996 & Dividends = £9067

My Calcs say there is Tax to Pay of £305.03 & hiriskpaul's spreadsheet says exactly the same even with the changes listed by air04 to the 3 cells in row 53.

But my Accountant insists that there is ZERO tax to pay after upping the dividends I calculated she can draw from our company to stay at ZERO tax.
As he says it is a revenue approved system used by thousands of accountants so HMRC will accept it, which of course they have.

So any body with more ideas, it is really bugging me or is there another thread which goes on after this one.

Even adding all the allowances together, to give 21,000 would leave £63 which is £4.72 at 7.5% dividend tax. Unless of course the £1000 tax free savings is also added on.

Thanks for any suggestions if anybody discovers this.
Steve

genou
Lemon Slice
Posts: 276
Joined: November 4th, 2016, 1:12 pm
Has thanked: 21 times
Been thanked: 61 times

### Re: Excel Tax calculator for 16-17

shares4me2 wrote:My Calcs say there is Tax to Pay of £305.03 & hiriskpaul's spreadsheet says exactly the same ...
Thanks for any suggestions if anybody discovers this.
Steve

Hmmm. I make it 305.02, but we needn't argue about that. The formatting is slightly wonky, but hey-ho:

Pay, pensions, profit etc.
Basic rate £0.00 x 20% = £0.00
Higher rate £0.00 x 40% = £0.00
Additional rate £0.00 x 45% = £0.00

Savings interest received from a bank or building society, securities etc.
Starting rate £996.00 x 0% = £0.00
Nil rate £0.00 x 0% = £0.00
Basic rate £0.00 x 20% = £0.00
Higher rate £0.00 x 40% = £0.00
Additional rate £0.00 x 45% = £0.00

Dividends from companies etc.
Nil rate £5,000.00 x 0% = £0.00
Basic rate £4,067.00 x 7.5% = £305.02
Higher rate £0.00 x 32.5%= £0.00
Additional rate £0.00 x 38.1%= £0.00
Total income on which tax has been charged £10,063.00

Income Tax due £305.02

Ask your accountant for the SA302 produced by his methodology. By my reckoning your wife has 4067 in dividends which are not covered by the dividend allowance. If she shifts the personal allowance to cover the dividends, she exposes 4067 of earnings @20%.

shares4me2
Posts: 3
Joined: December 11th, 2017, 12:38 pm
Has thanked: 1 time

### Re: Excel Tax calculator for 16-17

I used Excel rounding so £305.025 became £305.03 but of course tax should be rounded down so would be £305.

Well, as a result of your figures confirming mine and your suggestion that I look at the SA302, I have now been through my copy of the Tax Return and found the reason for the difference. Why didn't I spot it before and save all the work? My accountant has miss typed or miss stated for some reason the state pension figure, in fact on my return as well. Which gives us both a problem. If I had spotted it before January it could have been retrieved.

I am sure it is something HMRC will eventually spot, in fact I am surprised it wasn't automatically rejected as the figure is obviously wrong on the Tax Return. Shouldn't trust a professional completely I suppose. I just assumed I had misunderstood the new tax rules in my calculations and he had apportioned the dividends differently.

So thanks very much, I feel pretty daft now.

Steve

PinkDalek
Lemon Quarter
Posts: 3302
Joined: November 4th, 2016, 1:12 pm
Has thanked: 740 times
Been thanked: 797 times

### Re: Excel Tax calculator for 16-17

shares4me2 wrote:...

Well, as a result of your figures confirming mine and your suggestion that I look at the SA302, I have now been through my copy of the Tax Return and found the reason for the difference. Why didn't I spot it before and save all the work? My accountant has miss typed or miss stated for some reason the state pension figure, in fact on my return as well. Which gives us both a problem. If I had spotted it before January it could have been retrieved.

I am sure it is something HMRC will eventually spot, in fact I am surprised it wasn't automatically rejected as the figure is obviously wrong on the Tax Return. Shouldn't trust a professional completely I suppose. I just assumed I had misunderstood the new tax rules in my calculations and he had apportioned the dividends differently.

So thanks very much, I feel pretty daft now.

Steve

Are you certain the State pension figure is incorrect and how far "out" is it?

I ask as it is taxable on an accruals basis, rather than a receipts basis.

XFool
Lemon Quarter
Posts: 2869
Joined: November 8th, 2016, 7:21 pm
Been thanked: 167 times

### Re: Excel Tax calculator for 16-17

As I point out in an adjacent thread - Age UK Tax Calculator - Age UK has now thrown in the towel when it comes to their tax calculator.

"Age UK no longer provide a tax calculator. Instead, we recommend you use the HMRC tax calculator to check you're being correctly taxed."

shares4me2
Posts: 3
Joined: December 11th, 2017, 12:38 pm
Has thanked: 1 time

### Re: Excel Tax calculator for 16-17

PinkDalek wrote:Are you certain the State pension figure is incorrect and how far "out" is it?

I ask as it is taxable on an accruals basis, rather than a receipts basis.

Sorry not to answer before, I have'nt got used to this forum yet, I didn't notice the "notify me" button wasn't checked.

The differences are significant, 25% and 30% I have asked him for an explanation because I cannot think where the numbers came from.

XFool
Lemon Quarter
Posts: 2869
Joined: November 8th, 2016, 7:21 pm
Been thanked: 167 times

### Re: Excel Tax calculator for 16-17

PinkDalek wrote:Are you certain the State pension figure is incorrect and how far "out" is it?

I ask as it is taxable on an accruals basis, rather than a receipts basis.

At the risk of flogging a dead horse, or should that be 'waking sleeping dogs'? - can you please explain this in relation to the SP.

TIA

PinkDalek
Lemon Quarter
Posts: 3302
Joined: November 4th, 2016, 1:12 pm
Has thanked: 740 times
Been thanked: 797 times

### Re: Excel Tax calculator for 16-17

XFool wrote:
PinkDalek wrote:Are you certain the State pension figure is incorrect and how far "out" is it?

I ask as it is taxable on an accruals basis, rather than a receipts basis.

At the risk of flogging a dead horse, or should that be 'waking sleeping dogs'? - can you please explain this in relation to the SP.

TIA

We covered this on an earlier thread:

viewtopic.php?p=100938#p100938 which is followed by a reply of yours.

May be best to continue over there.

XFool
Lemon Quarter
Posts: 2869
Joined: November 8th, 2016, 7:21 pm
Been thanked: 167 times

### Re: Excel Tax calculator for 16-17

PinkDalek wrote:We covered this on an earlier thread:

viewtopic.php?p=100938#p100938 which is followed by a reply of yours.

May be best to continue over there.

Thanks PD. I know we have been here before - hence 'waking sleeping dogs' - I was just wondered what you actually meant by the terms "taxable on an accruals basis, rather than a receipts basis."