Donate to Remove ads

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

Thanks to johnstevens77,Bhoddhisatva,scotia,Anonymous,Cornytiv34, for Donating to support the site

Excel Tax calculator for 16-17

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

Re: Excel Tax calculator for 16-17

#42142

Postby air04 » March 29th, 2017, 3:37 pm

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
The full Lemon
Posts: 12636
Joined: November 8th, 2016, 7:21 pm
Been thanked: 2608 times

Re: Excel Tax calculator for 16-17

#43083

Postby XFool » April 2nd, 2017, 1:30 pm

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 Half
Posts: 6139
Joined: November 4th, 2016, 1:12 pm
Has thanked: 1589 times
Been thanked: 1801 times

Re: Excel Tax calculator for 16-17

#43134

Postby PinkDalek » April 2nd, 2017, 6:18 pm

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
The full Lemon
Posts: 12636
Joined: November 8th, 2016, 7:21 pm
Been thanked: 2608 times

Re: Excel Tax calculator for 16-17

#43908

Postby XFool » April 5th, 2017, 8:54 pm

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

#45223

Postby benfool » April 11th, 2017, 10:18 am

benfool wrote:
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.]

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 Quarter
Posts: 4812
Joined: November 4th, 2016, 10:15 am
Has thanked: 605 times
Been thanked: 2675 times

Re: Excel Tax calculator for 16-17

#45248

Postby scrumpyjack » April 11th, 2017, 12:02 pm

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: 3131
Joined: November 4th, 2016, 3:39 pm
Has thanked: 3060 times
Been thanked: 554 times

Re: Excel Tax calculator for 16-17

#45262

Postby DiamondEcho » April 11th, 2017, 1:40 pm

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 Quarter
Posts: 4812
Joined: November 4th, 2016, 10:15 am
Has thanked: 605 times
Been thanked: 2675 times

Re: Excel Tax calculator for 16-17

#45275

Postby scrumpyjack » April 11th, 2017, 2:40 pm

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.

It is utterly mad.

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

#45283

Postby DiamondEcho » April 11th, 2017, 2:47 pm

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: 14
Joined: December 11th, 2017, 12:38 pm
Has thanked: 2 times
Been thanked: 2 times

Re: Excel Tax calculator for 16-17

#130246

Postby shares4me2 » April 5th, 2018, 7:03 pm

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.
Oh Dear, my head hurts.

Thanks for any suggestions if anybody discovers this.
Steve

genou
Lemon Quarter
Posts: 1070
Joined: November 4th, 2016, 1:12 pm
Has thanked: 177 times
Been thanked: 370 times

Re: Excel Tax calculator for 16-17

#130253

Postby genou » April 5th, 2018, 7:29 pm

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: 14
Joined: December 11th, 2017, 12:38 pm
Has thanked: 2 times
Been thanked: 2 times

Re: Excel Tax calculator for 16-17

#130365

Postby shares4me2 » April 6th, 2018, 12:51 pm

Thank you very much @genou for your very quick reply.

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. :oops:

Steve

PinkDalek
Lemon Half
Posts: 6139
Joined: November 4th, 2016, 1:12 pm
Has thanked: 1589 times
Been thanked: 1801 times

Re: Excel Tax calculator for 16-17

#130368

Postby PinkDalek » April 6th, 2018, 12:56 pm

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. :oops:

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
The full Lemon
Posts: 12636
Joined: November 8th, 2016, 7:21 pm
Been thanked: 2608 times

Re: Excel Tax calculator for 16-17

#130485

Postby XFool » April 6th, 2018, 10:52 pm

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.

https://www.ageuk.org.uk/information-ad ... alculator/

"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: 14
Joined: December 11th, 2017, 12:38 pm
Has thanked: 2 times
Been thanked: 2 times

Re: Excel Tax calculator for 16-17

#130978

Postby shares4me2 » April 9th, 2018, 7:26 pm

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
The full Lemon
Posts: 12636
Joined: November 8th, 2016, 7:21 pm
Been thanked: 2608 times

Re: Excel Tax calculator for 16-17

#130984

Postby XFool » April 9th, 2018, 8:11 pm

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 Half
Posts: 6139
Joined: November 4th, 2016, 1:12 pm
Has thanked: 1589 times
Been thanked: 1801 times

Re: Excel Tax calculator for 16-17

#130986

Postby PinkDalek » April 9th, 2018, 8:21 pm

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
The full Lemon
Posts: 12636
Joined: November 8th, 2016, 7:21 pm
Been thanked: 2608 times

Re: Excel Tax calculator for 16-17

#130995

Postby XFool » April 9th, 2018, 8:57 pm

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

shares4me2
Posts: 14
Joined: December 11th, 2017, 12:38 pm
Has thanked: 2 times
Been thanked: 2 times

Re: Excel Tax calculator for 16-17

#298514

Postby shares4me2 » April 7th, 2020, 8:15 am

I have been using the calculations in this Spreadsheet in my own since "hiriskpaul" released it and have been very pleased with it and impressed that in my circumstances it agrees with my accountant's results so far.
Yesterday however I stumbled across an article by Anshu Diddee in this website: https://www.barnesroffe.com/allocation- ... allowance/ where he shows some examples where normal calculations do not give the correct result. I put his figures into the "hiriskpaul" calculations and they agreed with what this guy recons are the normal but wrong calculations. His method results in quite significant tax savings by using the rules that say that the Personal Allowance can be offset in the most beneficial manner for the taxpayer.
My Excel skills are not up to being able to alter the calculations accordingly to see if they affect my situation.
Does anybody have any comments as to whether this guy is correct in his thinking. I know there were a number of discussions earlier in the thread but it didn't appear to come up with anything concrete.
Cheers,
Shares4Me2

eisman
Lemon Pip
Posts: 58
Joined: November 9th, 2018, 6:25 pm
Been thanked: 45 times

Re: Excel Tax calculator for 16-17

#300159

Postby eisman » April 12th, 2020, 4:14 pm

The personal allowance can indeed be used in the most beneficial way to the taxpayer. These issues were discussed at some length on the ICAEW Tax faculty website at the time:

https://ion.icaew.com/taxfaculty/f/tax- ... -allowance

Example 2 of the Barnes Roffe blog seems to be very similar to the ICAEW example, albeit with slightly more dividend income.


Return to “Taxes (Practical)”

Who is online

Users browsing this forum: No registered users and 7 guests