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
Related resources
Replies (23)
Please login or register to join the discussion.
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?
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 )
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.
And here www.accountingweb.co.uk/any-answers/allocation-of-profit-in-software-cal... is a useful test scenario.
Thank you for the response, I am just comparing payroll and dividends and we are playing around with different amounts in each section
Then do you care whether it's right above £150k, if that was the only problem you perceived?
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
Sounds like you need a few IF's somewhere in your formulas and most likely enclosed around other IF statements
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
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.
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.
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.
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?
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.
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.
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.
=if(C8<37500,0,if(C8>149999,G8*39.35%,if(C8>37500,G8*33.75%)))
...or something along these lines
Looks like the final IF is redundant - try something like:
=if(C8<37500,0,if(C8>149999,G8*39.35%,G8*33.75%))
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.
