Formula to calculate a taxation payment in currency - ie dollars and cents ($0.00)

Hi, I'm wanting to create a formula to calculate the amount of taxation paid in dollars and cents (format = $0.00), based upon data which I input.

Let me give an example of how I intend to use it.

Let's say, I'm running a business that want to have an after-tax, and after expenses profit of $50,000.

I have no capital invested in the business eg $0.00, but if I were, to invest accordingly, I want a rate of return of 10%. But for now, work on nothing ie $0.00. For argument's sake, let's assume my profit for future growth is $10,000. Add these two figures together, and I equal $60,000.

Based upon the $60,000, I want to use a formula which will calculate the amount of tax required, and display this figure in ($0.00) format. For example, the taxation rate for $60,000 falls with the following range: $37,001 - $80,000; the amount of taxation I would pay is calculated as follows: $3,572 plus 32.5c for each dollar "over" $37,000. So, $60,000-$37,000=$23,000*0.325=$11,047.00. I have written a temporary formula as =($60000-37000)*(0.325)+3572!! How do I write a formula which calculates this automatically based upon any figure being entered (tip: $60,000 is just an example, this will always change!!). Not that it matters for the purpose of this question, but I'm using Australian Currency (AUD), so all answers will be written as $0.00.

I hope anyone who replies, understands what I'm trying to ask here.

Thank you.

Matthew Hinds

Thanks, Crooza; I'll save it and see if it works in my spreadsheet. I'll get back to you if I need anything else. By the way, I got my figures of: $37,001 - $80,000; the amount of taxation I would pay is calculated as follows: $3,572 plus 32.5c for each dollar "over" $37,000. from the ATO site too.

Yes, this true.

I know, I wasd just using that as an example.

Crooza, how did you put the link (attachment) into your reply to me? I was thinking I'd post a copy of my spreadsheet and you can see for yourself what I'm doing.

Thanks. I'm going to post a spreadsheet that I'm working on for you to have a look at.

Crooza, here is the spreadsheet that I was talking about. You'll need to go to the Nett Profit Target tab, and look at all the cells that have data in them. Anything with $0.00 (displayed) means that any figure can be entered as required. Based upon what you enter, then cells C23:C25 should calculate automatically. Remember, my original reference to $60,000 is calculated on the assumption that (cell) C12=$50,000; but of course, this will change according to what data I enter. I hope this all makes sense. By the way, this is a new business, so I'm just starting out. As you will see, my spreadsheet does not include the Medicare Levy. If you want to include this (to show me) then please do. Thanks. Matthew

Thanks Crooza, I'll have a look at what you suggested and get back to you. By the way, (this information is on the ATO website), according to the ATO, the Medicare Levy is paid at 2%, but I don't know when it takes effect - eg over $$80,000. On the other hand, The figures I stated earlier do [U]not[U] include the Temporary Budget Repair Levy (2%) for all incomes $180,000 or above. Thanks.

No problems; thanks for your help today.

By the way, your reference to "the rick tax" (maybe a typo?); but what's this?

Cheers Crooza; thank you for your help today. Much appreciated.

I understand you're not an account - I have a brother who is - but it was more about the formula to do the calculation that I was interested in.

I'll contact you again, if I need further assistance. Thanks again.

Hi again Crooza (or anyone else)

Thanks for your help earlier today (7/12/14).

I'm sorry, but I've followed your instructions regarding the formula to do the calculation I require, but I guess I'm doing something wrong. I'm not familiar with the Lookup function, although, I do know that V = vertical, H = horizontal, but that's about it!!

I've attached my spreadsheet for you again; can you show me where I'm going wrong please? Thanks.

Again, go to the Nett Profit Target tab, and look at all the cells that have data in them. Anything with $0.00 (displayed) means that any figure can be entered as required. Based upon what you enter, then cells C23:C25 should calculate automatically. Remember, my original reference to $60,000 is calculated on the assumption that (cell) C12=$50,000; but of course, this will change according to what data I enter.

Any cell without a formula - ie figure entered manually, eg B15, C12, C19, C21, is supposed to display zero ($0.00), or whatever is entred, in this case, c12 = $50,000.

Cells C24:C25, may display the following "#REF" - I know you know what this means - you only need to formulate cell C24, and C25 will change automatically - ie Cell B25:c25 (merged into one cell), is = c23-c24!!

I cannot get the formula in cell C24 to work correctly - this figure must re-calculate automatically based upon whatever is entered. I'm sorry, if I sound repetitive, but I hope you can understand what I'm trying to say and/or ask, and whether you can fix it for me please. And based upon previous discussions, I don't expect you to be an accountant - so long as the calulation works, that's all that matters. You'll also notice, based upon your earlier communications, I redesigned an added an extra sheet titled 2014-2015 ATO Tax Rates. I hope this helps? Thanks. I look forward to your response.

Nothing in particular; just maybe I'm doing something wrong, i don't know.

Cheers

To Crooza and All

To everyone who provided me with assistance when requested last week, thank you.

It took me a while to understand how the formula worked - got there in the end!!

Thanks again; Merry Christmas and Happy New Year.

Matthew Hinds