Sales Tax Calculation

[LMB]

Hi,

What function or calculation could I use in a spreadsheet to calculate the
price of an item minus the local sales tax so that in the end the final
price would be a whole number. I would like to enter the local tax rate in
percent so I could figure out what the price for an item should be so in the
end the final price is $5, $10, $15 etc. Using Excel 2000.

Thanks,
Linda

 

[Roy Harrill]

If the tax rate is in cell B1 and the final (total) price is in B2, use this
formula in cell B3 (or any other cell):
=ROUND(B2/(1+B1),2)
Roy

 

[Bondi]

Hi,

What function or calculation could I use in a spreadsheet to calculate the
price of an item minus the local sales tax so that in the end the final
price would be a whole number. I would like to enter the local tax rate in
percent so I could figure out what the price for an item should be so in the
end the final price is $5, $10, $15 etc. Using Excel 2000.

Thanks,
Linda

Hi Linda,

Another way to do it would be to format the cell where you enter your
local tax as Percentage (Right Click the cell and chose Format Cells ->
Number and Chose Percentage on the right side). If we say that A1 is
the tax cell and B1 is the price including tax then i think that this
formula will give you the price net tax as a whole number:

=ROUND(B1-(B1*A1),0)

If you want the final price shown as currency then Right Click the cell
and chose Format Cells -> Number and Chose Currency on the right side
and chose $ as symbol.

Regards,

Bondi

 

[LMB]

Thanks Roy and Bondi. I am heading out on a trip and will try these
suggestions this weekend.

Linda

 

[LMB]

I tried this formula but I'm not getting what I need. I need to enter the
tax rate and the final price and my calculation will give me the Item Price.

Tax Rate 7.25
Total Price 15.00
Item Price $1.82 (Calculated Number based on Tax Rate and Total Price)

Thanks,
Linda

 

[LMB]

Hi Linda,

Another way to do it would be to format the cell where you enter your
local tax as Percentage (Right Click the cell and chose Format Cells ->
Number and Chose Percentage on the right side). If we say that A1 is
the tax cell and B1 is the price including tax then i think that this
formula will give you the price net tax as a whole number:

=ROUND(B1-(B1*A1),0)

If you want the final price shown as currency then Right Click the cell
and chose Format Cells -> Number and Chose Currency on the right side
and chose $ as symbol.

Regards,

Bondi

I tried this formula as well as the other one suggested but I'm still not
getting what I need. I need to enter the tax rate and the final price and
my calculation will give me the Item Price. I tried you suggestion and got
this. I am not the best at math but I think Tax on $14.00 is 1.015 so the
total price would be $15.02. I need the final price to be $15.00 even.

Tax Rate Total Price Item Price
7.25% $15.00 $14.00

Thanks

 

[Gord Dibben]

LMB

Try this formula.........=ROUND(B2/(1+B1),1) returns 14.00

Note that tax rate in B1 is .0725 not 7.25


Gord Dibben MS Excel MVP

 

[LMB]

Thanks Gord,

Ok..that seems better but if I have an item the is a total price of $20.00
with sales tax .0725 <g>, my Item Price calculates out to $18.60. If I plug
in 18.60 in a sales tax calculator, I get a total price of 19.95. Is the
Round part causing this?

Thanks,
Linda

 

[Gord Dibben]

Yes.

The rounding changes the actual value.

If you use this formula =(B2/(1+B1))

B1 = .0725

B2 = 20.00

Formula returns 18.65

..0725 * 18.65 = 20.00


Gord

Thanks Gord,

Ok..that seems better but if I have an item the is a total price of $20.00
with sales tax .0725 <g>, my Item Price calculates out to $18.60. If I plug
in 18.60 in a sales tax calculator, I get a total price of 19.95. Is the
Round part causing this?

Thanks,
Linda

Gord Dibben MS Excel MVP

 

[LMB]

Thanks...I could have just tried it...geesh, I got myself all
confused....not hard to do.

Linda