Share this content

Formula help

Income tax and dividend

Didn't find your answer?

Hi, thought I would ask on here as everyone was very helpful last time.

i have managed to get all formulas to work on a spreadsheet bar one!

all my personal income is calculated and the first section of my dividend income however I can't get the second band to formulate through

what I need it to say is if taxable income in C8 (personal tax) is greater than £37,500 but less than £149,999 then times G8(dividend income) by 33.75% Aslong the dividend income greater than 37500 and less than 149,999

I did manage one formula but it still calculates everything over 149,999 and doesn't stick with in the tax band 

thank you 

Replies (23)

Please login or register to join the discussion.

avatar
By Tax Dragon
24th Jan 2022 00:57

You haven't even fully described what you want it to do - which may be why you are struggling to write it. Is the answer zero unless the number is in that range?

Thanks (0)
avatar
By Calculatorboy
24th Jan 2022 01:11

You'd be better off setting up a dummy client in your tax software ( assuming you use professional tax return software, and if you don't , now is the time to invest in one, otherwise you will waste lot of time )

Thanks (0)
avatar
By Tax Dragon
24th Jan 2022 06:25

Also, I think your whole spreadsheet might need a sense check. Sounds like you're either getting the whole thing horribly wrong or over-complicating massively. (Possibly both - the one a consequence of the other.)

Put some sample numbers through it:
a) C8 = £37,499
b) C8 = £37,501

Dividend income £10,000 in each case.

Thanks (0)
Replying to Tax Dragon:
avatar
By Tax Dragon
24th Jan 2022 06:34

And how is it coping with income of say £110,000?

Thanks (0)
Replying to Tax Dragon:
avatar
By Tax Dragon
24th Jan 2022 06:52
Thanks (1)
Replying to Tax Dragon:
avatar
By Confused78
24th Jan 2022 07:25

Thank you for the response, I am just comparing payroll and dividends and we are playing around with different amounts in each section

Thanks (0)
Replying to Confused78:
avatar
By Tax Dragon
24th Jan 2022 08:14

Then do you care whether it's right above £150k, if that was the only problem you perceived?

Thanks (0)
Replying to Tax Dragon:
avatar
By Confused78
24th Jan 2022 08:32

I managed the formula above the 150k as salary would never exceed 100k

My problem is the formula I have doesn’t calculate the remainder of the tax bracket ie 50k

Thanks (0)
Replying to Confused78:
avatar
By Tax Dragon
24th Jan 2022 08:34

Just do it manually. It'll be quicker.

Or hire an accountant.

Thanks (0)
avatar
By SXGuy
24th Jan 2022 07:46

Sounds like you need a few IF's somewhere in your formulas and most likely enclosed around other IF statements

Thanks (1)
avatar
By Truthsayer
24th Jan 2022 11:46

The formula you need is;

IF IQ>90,THEN HIRE ACCOUNTANT, ELSE COCKUP

Thanks (4)
Replying to Truthsayer:
avatar
By Confused78
24th Jan 2022 12:07

I can do it manually just not the formula!

If you can’t be helpful don’t comment I don’t value negativity! They is already plenty in the world without such comments

Thanks (0)
Replying to Confused78:
avatar
By Hugo Fair
24th Jan 2022 13:30

I took it as humorous rather than negative - but if you prefer a blunter message ... you are trying to use technology with which you're not sufficiently capable.

It feels like being asked by someone to tell them how to change the gear ratios in their car, when their knowledge of car mechanics stops shortly after 'where to insert the fuel'.

That's not being rude, just a statement of the facts evidenced so far. If you can't work out how to get cell formulae to produce what you want, then your knowledge of Excel will be insufficient to identify when errors have crept in later.

Thanks (2)
Replying to Confused78:
Stepurhan
By stepurhan
24th Jan 2022 15:33

Confused78 wrote:
If you can’t be helpful don’t comment I don’t value negativity! They is already plenty in the world without such comments

We don't value people seeking valuable advice without paying for it. This is a forum for accountants, not for the general public.

Also, just because it is your question, you don't get to choose who comments or how they do so. Provided comments don't break the site rules, people are free to say whatever they like.

If you want to get a complete answer to your question, without the respondent being "negative", pay someone for advice.

Thanks (1)
Replying to stepurhan:
avatar
By Confused78
24th Jan 2022 16:24

When I got my degree I didn’t have to be an expert on excel.
As I already stated I can do it manually just not get the correct formula on excel.

Thanks (0)
Replying to Confused78:
Stepurhan
By stepurhan
24th Jan 2022 18:37

Confused78 wrote:

When I got my degree I didn’t have to be an expert on excel.


Your point being?

Because this reads as "anything I didn't learn for my degree I should be able to get advice on for free online". Is that what you meant?

Thanks (1)
Replying to stepurhan:
avatar
By accountaholic
25th Jan 2022 13:37

Who's "we"?
I don't consider a bit of help with a spreadsheet formula as "valuable advice" on its own, and am happy to participate in such discussions with no charge.

A complete run down of dividend and payroll taxes might be another matter but not what is asked in this post.

Thanks (0)
Replying to Confused78:
Avatar
By I'msorryIhaven'taclue
24th Jan 2022 17:05

Confused78 wrote:

If you can’t be helpful don’t comment I don’t value negativity! They is already plenty in the world without such comments

At the risk of sounding negative, you should read Tax Dragon's link (above) to another thread which highlights an issue you haven't yet picked up on.

Thanks (0)
Replying to I'msorryIhaven'taclue:
avatar
By Tax Dragon
24th Jan 2022 17:49

More to the point, I'm not sure any of the suggested answers do what the OP asked for. (Actually, I am sure none of them does.) We've all sort of subconsciously framed our own spreadsheets and answered accordingly. But if this was the only missing/incorrect formula (quite a weighted if, that one... not redundant :-p), then the suggestions won't quite slide in as hoped. They're doing too much.

Thanks (0)
avatar
By accountaholic
24th Jan 2022 13:24

What is the formula you have, might be easier to debug your example.

Thanks (0)
avatar
By bettybobbymeggie
24th Jan 2022 14:42

=if(C8<37500,0,if(C8>149999,G8*39.35%,if(C8>37500,G8*33.75%)))

...or something along these lines

Thanks (1)
Replying to bettybobbymeggie:
Jim Clark, Lotus 49
By jimclark1967
24th Jan 2022 14:59

Looks like the final IF is redundant - try something like:

=if(C8<37500,0,if(C8>149999,G8*39.35%,G8*33.75%))

Thanks (1)
Replying to jimclark1967:
avatar
By Tax Dragon
24th Jan 2022 15:08

If C8 is always about 100k, all the ifs are redundant.

Just need (min(g8+c8,150k)-c8)@rate1 + max(g8+c8-150k,0)@rate2

Or summat.

Thanks (1)
Share this content