Excel spreadsheet for personal tax? 13 December 2022 at 12:46PM in Cutting tax 11 replies 558 views

gravlax

Does anyone know of or have a link to an Excel spreadsheet? Something that has all the UK personal tax bands and allowances (ideally editable) where you then enter all our sources of income, salary, self-employed, capital gains, pension, interest, dividends etc. and can calculate the tax?

Have tried making one, but calculation never quite matches results on websites with online calculators. The whole UK tax method appears wilfully complicated! Things like working out the savings allowance can only be done after working other income, but personal allowances have to be applied to other income before savings income, etc. So you go round in a loop. I'm sure someone must have set out a nice simple Excel file that calculates the tax in logical steps somewhere?

Help! Thanks.

The_Unready

Does this help?

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

gravlax

No that doesn't really do it. There's plenty of sites that do simple online tax calculations for salary.

I'm looking for something editable to download in Excel, and which allows you to input multiple sources of income on the one calculation (employment, dividends, interest, capital gains, etc.) 

frugalmacdugal

Hi,

what about THIS?

gravlax

frugalmacdugal said:

Hi,

what about THIS?

OK maybe the replies are having a laugh. Not sure why. I thought the question was reasonable.

Only three criteria:
1. UK personal tax calculator
2. In Excel available to download and edit
3. Tax calculation on multiple sources of income

Telling you what tax you pay on just salary or just dividends or anything else in isolation doesn't help calculate the total tax bill if you have rental income, dividends, capital gains, interest etc. to factor in. I'm looking for a single Excel file that allows for a full calculation. 

Maybe none exists. Having tried, and failed, it is complicated!

Jeremy535897

I don't think what you seek exists. I have suggested this to people asking for a multi-source calculator, but I don't know how well it works:
https://www.uktaxcalculators.co.uk/tax-calculators/personal-tax-calculators/multiple-income-tax-estimate/

gravlax

Jeremy, yes I have used that online calculator. It works well. An Excel spreadsheet that does the same is harder to find!

Pennywise

I think this is what you're after - it's the only one I've ever found that does everything you ask for all different types of income.  Not cheap, mind.

https://www.absolutetax.co.uk/products/absolute-taxpert-tax-planning-apps

Jeremy535897

I tend to work them out from scratch, but you can do too much work writing in things that you don't need. You could have a self employment loss, a partnership profit, losses brought forward, class 4 losses brought forward, bank interest, dividends, rental income with finance costs (furnished holiday lets, commercial unfurnished lets and uncommercial lets), overseas income with overseas tax credits, salary with expenses and pension contributions, onshore and offshore life insurance gains with top slicing relief etc. The software companies that write tax return programs have to cope with all of this stuff. You wouldn't need all that.

It is interesting to search on Google. Here is one person asking how to work out the tax on dividends in Excel. As you can see, it didn't work. Nobody explained why. There are complex nested IF formulae involved that might be better with VLOOKUP formulae. When I have some time I might try to look at it:
https://www.mrexcel.com/board/threads/dividend-formula.1170385/

Jeremy535897

Not so confident in her efforts, as the first formula I looked at for P14 does not cope with income below the personal allowance. I would have used in P14
=IFS(P12<12570,P12,P12<=100000,12570,P12>125140,0,TRUE,12570+(P12-100000)/2)

gravlax

Yes I have tried doing my own on Excel but the results are not always correct.

I was hoping to find the optimum/maximum amount of each income type for maximum income without any tax, i.e. using personal, savings, dividend, and cgt allowances and then once all allowances have been used, see which income type is most tax efficient thereafter. I think dividends, but could be non-property capital gains, or even interest. Or maybe the most tax efficient source of income changes again once you exceed the Basic Rate band.