Tax help using vlookup and if function

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

• Hello everyone

Here's the outline. I have been told to use this to calculate tax: When calculating the tax, assume that the monthly gross profit taxed @ 20% for the first \$20,000 then 30% for the remainder of gross profit.

The monthly gross profit is \$20,153 (C14) and the tax table (table7) is below:

[TABLE="width: 363"]

[tr]

[TD="align: center"]Taxable Income[/TD]
[TD="align: center"]Fixed Tax[/TD]
[TD="align: center"]Marginal Tax Rate[/TD]

[/tr]

[tr]

[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0.2[/TD]

[/tr]

[tr]

[TD="align: center"]20000[/TD]
[TD="align: center"]3999.8[/TD]
[TD="align: center"]0.3[/TD]

[/tr]

[/TABLE]

I don't know how to calculate the 20% of the first \$20,000.

This is what I have done for the fixed rate: VLOOKUP(C14,Table7,2,TRUE) which gave me 3999.8

and for the 30% of the remainder (\$153) I did: (C14-VLOOKUP(C14,Table7,1,TRUE))*VLOOKUP(C14,Table7,3,TRUE) which gave me 45.90

Is there a simpler way to do all this? And can IF function be used along with VLOOKUP at all? Thanks in advance

Faye

Additional info: I also need to allow the formula to show an operational loss (i.e. profit < \$0), what does that mean?
Note: All formulae must contain VLOOKUP.

• Re: Tax help using vlookup and if function

20% for first 20,000 and 30% for the balance can be done in one step, with this formula:

=IF(B2>=20000,(B2*0.2)+((B2-20000)*0.3),B2*0.2)

which gave \$4076.50 for \$20,153

• Re: Tax help using vlookup and if function

Hello jproffer

Thank you very much for the IF funtion statement. However, the formulas must be VLOOKUP alone or with other functions. I'm very sorry if my original post wasn't worded correctly. Can I use the IF function you provided within a VLOOKUP?

• Re: Tax help using vlookup and if function

It just depends on how it needs to work...maybe.

I thought it was a flat 20% for the first \$20,000 and 30% for any amount over \$20,000, hence my first solution.

Could you post your workbook?...and I'll see what I can do.

• Re: Tax help using vlookup and if function

Yeah it is a flat 20% and then 30% but you have to use the vlookup to work it out.

Thanks for the help. It is much appreciated. The work is in the Profit/Loss sheet.

forum.ozgrid.com/index.php?attachment/44921/

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!