Excel help and a couple of questions please?

Skinnydad

Are there any folks out there that could kindly give me a working example of a small Excel SS that would enable me to plug in all my income (everything) in separate rows and at the end calculate the amount of Tax I should actually pay over the year. I'm based in Scotland so we have Tax at 19%, 20% and 21% (think that should cover me) I also have the full SP and a couple of small pensions paying out and some interest from savings, dividends etc. The Taxman seems to change my code on a regular basis and I just want to be able to confirm that this is correct. Can I also ask the taxman to take all my tax from one source leaving the rest of my income Tax free so to speak. They always tax it over a couple of pensions I receive, but I'd rather just the one hit (which will accommodate this) so I've a better understanding of all my outgoings.

I also see on the tax code notice 'Less adjustment to rate bands' go to note 3 which also says.  We have included this adjustment as you have more than one job or pension and we estimate some of your income is taxable at a higher rate. I'm retired, so don't have a job, plus what's this with the estimate? my income is very transparent so can't really understand all this.

Thanks in advance 

molerat

Your tax is only an estimate because they cannot be certain until the year is over.  You could have your tax code split across all income streams but they cannot deduct more than 50% of a gross income so you could come unstuck there.  As they mention rate adjustment you must receive in excess of £26562 so for Excel just list all your different incomes then total them then total income - 26562 * .21 + 2775.14.  £2775.14 is the total tax on £2306 at 19% and £11685 at 20%

Skinnydad

With the exception of interest my incomes pretty fixed. ie. SP, pension 1, pension 2, drawdown and interest. I just think that the inland revenue never gets it correct. And I'd need to prove this to them. I'll look at your advice re Excel. It's not my strongest suite I'm afraid. Thanks Molerat

BikingBud

If you have fixed sums eg from DB or annuity that might only be increased once per year, ensure these are listed in your HMRC app on the PAYE tab and the tax codes should fall out OK and be steady for the year.

If there are frequent changes to income then this might prompt the reactive changes to your tax code. 

I look from the other end and try to ensure that my wage remains below 40% threshold so use one for working out my net pay from a total column (L) based upon the sum of 3 input columns (F+G+H) as follows:
=IF(L11<=12570,L11,IF(L11<=50270,12570+0.8*(L11-12570),IF(L11<=125140,42730+(L11-50270)*0.6)))

I also use E11-L11 which provides headroom against higher rate liability and advises where I need to earn less or put more into the pension. E11 is the 40% tax threshold with a small inflation factor after 2028 as fiscal drag gives friction burns

You would need to adjust the threshold and figure for the Scottish rates but if you add monthly elements for your incomes into F11, G11 and H11, then as you proceed through towards year end each of those cells will have 12 values it should give you running rough net income, no NI payable as pension income. 

Beddie

Have you tried https://listentotaxman.com/ - not quite what you asked for but I find it useful.

Skinnydad

Thanks Beddie I'll check out.  Looks okay if you're working not sure how it handles 2 small pensions and a SP, plus drawdown. Thanks bikingbud way out of my league re Excel, but I'll have a look later at this. Thank you..  

Cobbler_tone

Beddie said:

Have you tried https://listentotaxman.com/ - not quite what you asked for but I find it useful.

Heads up for anyone using this. The allowances field doesn't work for me on this link. Doesn't matter if I put a '+' or '-' figure the result is the same. It means I can't reconcile an accurate result of my actual net pay vs my circumstances.

BikingBud

Try this:

I use to forecast so Pen1,2 and state pension are inflated by a factor using =F10*(1+D11) where you can add the inflation forecast per year into column "CPI". You could just use it for just in year calcs with each column being a month and different rows for each pension.

As long as you have a reference "Total" that always is your comparison for net pay for this I10

Total Column "I10" Calc   =F10+H10+G10

Skinny Dad Net calc > =IF(I10<=12570,I10,IF(I10<=14876,12570+0.81*(I10-12570),IF(I10<=26561,23775+0.8*(I10-26561),IF(I10<=43662,37285+(I10-43662)*0.79))))

Comes out near as when comparing with:
https://listentotaxman.com/?year=2023&taxregion=scotland&married=true&excludeni=true&marriageallowance=true&age=0&time=1&ingr=42932

and 

https://www.thesalarycalculator.co.uk/twosalaries.php

Have a play, have a look on WWW how to drag formulas across ranges on excel, loads of videos and see what you get, use the 2 websites to assure confidence that you are correct.

Feel EDUCATED, Feel EMPOWERED.

Edit to Add - this will not work for income over £43662 it will need further clauses for "Higher" and "Advanced" Rate

Cobbler_tone

https://www.thesalarycalculator.co.uk/twosalaries.php

Like this one...within £4 of take home if all the inputs are completed correctly.

Skinnydad

Thanks Cobbler & Biking bud really appreciate your assistance with this.  Sorry for the late response, been a bit unwell and hectic time of year.  I'll have a look at this in more detail. Thanks again..